8.12.4.1 MySQL 如何使用内存

MySQL 分配缓冲区和高速缓存以提高数据库操作的性能。默认配置旨在允许 MySQL 服务器在具有大约 512MB RAM 的虚拟机上启动。您可以通过增加某些与缓存和缓冲区相关的系统变量的值来提高 MySQL 性能。您还可以修改默认配置，以在内存有限的系统上运行 MySQL。

下面的列 table 描述了 MySQL 使用内存的一些方式。如果适用，将引用相关的系统变量。有些项目是存储引擎或特定于功能的。

InnoDB缓冲池是一个存储区，用于保存 table，索引和其他辅助缓冲区的InnoDB高速缓存数据。为了提高大容量读取操作的效率，缓冲池被分为pages，它们可以潜在地容纳多行。为了提高缓存 Management 的效率，缓冲池被实现为页面的链接列 table。使用LRU算法的变体，将很少使用的数据从缓存中老化掉。有关更多信息，请参见第 14.5.1 节“缓冲池”。

缓冲池的大小对于系统性能很重要：

InnoDB使用malloc()操作在服务器启动时为整个缓冲池分配内存。 innodb_buffer_pool_size系统变量定义缓冲池大小。通常，推荐的innodb_buffer_pool_size值是系统内存的 50％到 75％。可以在服务器运行时动态配置innodb_buffer_pool_size。有关更多信息，请参见第 14.8.3.1 节“配置 InnoDB 缓冲池大小”。
- 在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。 innodb_buffer_pool_instances系统变量定义缓冲池实例的数量。
- 太小的缓冲池可能会引起过多的搅动，因为从缓冲池中刷新页面只是在短时间内再次需要。
- 缓冲池太大可能会由于争用内存而导致交换。
所有线程共享MyISAM键缓冲区。 key_buffer_size系统变量确定其大小。

对于服务器打开的每个MyISAMtable，索引文件都会打开一次；对于访问该 table 的每个并发运行的线程，该数据文件都会打开一次。对于每个并发线程，分配一个 table 结构，每个列的列结构以及大小为3 * N的缓冲区(其中* N *是最大行长，不计BLOB列)。 BLOB列需要 5 到 8 个字节加上BLOB数据的长度。 MyISAM存储引擎维护一个额外的行缓冲区供内部使用。

可以将myisam_use_mmap系统变量设置为 1 以启用所有MyISAMtable 的内存 Map。
如果内部内存临时 table 太大(使用tmp_table_size和max_heap_table_size系统变量确定)，MySQL 会自动将 table 从内存格式转换为磁盘格式。磁盘临时 table 使用internal_tmp_disk_storage_engine系统变量定义的存储引擎。您可以按照第 8.4.4 节“ MySQL 中的内部临时 table 使用”中所述增加允许的临时 table 大小。

对于使用CREATE TABLE显式创建的MEMORYtable，只有max_heap_table_size系统变量确定 table 可以增长到多少，并且不转换为磁盘格式。

MySQL 性能架构是用于在较低级别监视 MySQL 服务器执行的功能。性能模式将动态递增地分配内存，将其内存使用量扩展到实际服务器负载，而不是在服务器启动期间分配所需的内存。一旦分配了内存，就不会释放它，除非重新启动服务器。有关更多信息，请参见第 25.17 节“性能模式内存分配模型”。
服务器用来 ManagementClient 端连接的每个线程都需要一些特定于线程的空间。下 table 列出了这些内容以及哪些系统变量控制它们的大小：
堆栈(thread_stack)
- 连接缓冲区(net_buffer_length)
- 结果缓冲区(net_buffer_length)

连接缓冲区和结果缓冲区均以等于net_buffer_length字节的大小开头，但根据需要动态扩展到max_allowed_packet字节。每个 SQL 语句后，结果缓冲区缩小到net_buffer_length个字节。在运行语句时，还会分配当前语句字符串的副本。

每个连接线程都使用内存来计算语句摘要。服务器为每个会话分配max_digest_length个字节。参见第 25.10 节“性能模式语句摘要”。

所有线程共享相同的基本内存。
当不再需要线程时，分配给它的内存将释放并返回系统，除非线程返回线程高速缓存。在这种情况下，内存将保持分配状态。
每个执行 tableSequences 扫描的请求都分配一个读取缓冲区。 read_buffer_size系统变量确定缓冲区大小。
当以任意 Sequences(例如，按照排序)读取行时，可以分配一个随机读取的缓冲区以避免磁盘查找。 read_rnd_buffer_size系统变量确定缓冲区大小。
所有联接都在一次通过中执行，并且大多数联接甚至都可以使用临时 table 来完成。大多数临时 table 是基于内存的哈希 table。具有较大行长(按所有列长的总和计算)或包含BLOB列的临时 table 存储在磁盘上。
大多数执行排序的请求根据结果集的大小分配一个排序缓冲区和两个临时文件零。参见第 B.4.3.5 节“ MySQL 在哪里存储临时文件”。
几乎所有的解析和计算都是在线程本地的和可重用的内存池中完成的。小项目不需要内存开销，从而避免了正常的慢速内存分配和释放。内存仅分配给意外大的字符串。
对于具有BLOB列的每个 table，将动态扩大缓冲区以读取更大的BLOB值。如果扫描 table，则缓冲区的大小将增大到最大BLOB值。
MySQL 需要用于 table 缓存的内存和 Descriptors。所有使用中的 table 的处理程序结构都保存在 table 缓存中，并作为“先进先出”(FIFO)进行 Management。 table_open_cache系统变量定义了初始 table 缓存大小；参见第 8.4.3.1 节“ MySQL 如何打开和关闭 table”。

MySQL 还需要用于 table 定义缓存的内存。 table_definition_cache系统变量定义可以存储在 table 定义高速缓存中的 table 定义的数量(来自.frm文件)。如果使用大量 table，则可以创建大 table 定义缓存以加快 table 的打开速度。与 table 高速缓存不同，table 定义高速缓存占用的空间更少，并且不使用文件 Descriptors。

FLUSH TABLES语句或mysqladmin flush-tables命令会立即关闭所有未使用的 table，并在当前执行的线程结束时将所有正在使用的 table 标记为关闭。这样可以有效释放大多数使用中的内存。 FLUSH TABLES在关闭所有 table 之前不会返回。
由于GRANT，CREATE USER，CREATE SERVER和INSTALL PLUGIN语句，服务器将信息缓存在内存中。相应的REVOKE，DROP USER，DROP SERVER和UNINSTALL PLUGIN语句不会释放此内存，因此对于执行许多导致缓存的语句实例的服务器，除非使用FLUSH PRIVILEGES释放它，否则缓存的内存使用量将会增加。

ps 和其他系统状态程序可能会报告mysqld使用了大量内存。这可能是由于不同内存地址上的线程堆栈引起的。例如，Solaris 版本的 ps 将堆栈之间未使用的内存计为已用内存。要验证这一点，请使用swap -s检查可用的交换。我们使用多个内存泄漏检测器(商用和开源)测试mysqld，因此应该没有内存泄漏。

监视 MySQL 内存使用情况

以下示例演示了如何使用Performance Schema和sys schema监视 MySQL 内存使用情况。

默认情况下，大多数性能架构内存检测是禁用的。可以通过更新 Performance Schema setup_instrumentstable 的ENABLED列来启用工具。内存仪器的名称以memory/code_area/instrument_name的形式 table 示，其中* code_area 是诸如sql或innodb的值，而 instrument_name *是仪器详细信息。

要查看可用的 MySQL 内存工具，请查询 Performance Schema setup_instrumentstable。以下查询返回所有代码区域的数百种内存工具。

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory%';

您可以通过指定代码区域来缩小结果范围。例如，通过将innodb指定为代码区域，可以将结果限制为InnoDB个存储仪器。

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

根据您的 MySQL 安装，代码区域可能包括performance_schema，sql，client，innodb，myisam，csv，memory，blackhole，archive，partition等。

要启用内存工具，请在您的 MySQL 配置文件中添加performance-schema-instrument规则。例如，要启用所有内存工具，请将此规则添加到配置文件中，然后重新启动服务器：

performance-schema-instrument='memory/%=COUNTED'

Note

启动时启用内存工具可确保计算启动时发生的内存分配。

重新启动服务器后，“性能模式setup_instruments”table 的ENABLED列应报告YEStable 示已启用的内存工具。内存工具会忽略setup_instrumentstable 中的TIMED列，因为内存操作未计时。

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

查询存储仪器数据。在此示例中，在 Performance Schema memory_summary_global_by_event_nametable 中查询存储仪器数据，该 table 按EVENT_NAME汇总数据。 EVENT_NAME是仪器的名称。

以下查询返回InnoDB缓冲池的内存数据。有关列的说明，请参见第 25.12.15.9 节“内存摘要 table”。

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 137428992
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
   HIGH_NUMBER_OF_BYTES_USED: 137428992

可以使用sys模式memory_global_by_current_bytestable 查询相同的基础数据，该 table 显示了全局服务器中当前的内存使用情况，并按分配类型进行了细分。

mysql> SELECT * FROM sys.memory_global_by_current_bytes
       WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 1
    current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
       high_count: 1
       high_alloc: 131.06 MiB
   high_avg_alloc: 131.06 MiB

此sys模式查询按代码区域汇总当前分配的内存(current_alloc)：

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, sys.format_bytes(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 843.24 MiB    |
| memory/performance_schema | 81.29 MiB     |
| memory/mysys              | 8.20 MiB      |
| memory/sql                | 2.47 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 46.53 KiB     |
| memory/blackhole          | 512 bytes     |
| memory/federated          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+

有关sys模式的更多信息，请参见第 26 章，MySQL sys 模式。

首页

MySql 中文文档

8.12.4.1 MySQL 如何使用内存

监视 MySQL 内存使用情况