8.12.3.1 MySQL如何使用内存_MySQL 8.0 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.12 优化MySQL服务器 / 8.12.3 优化内存使用 / 8.12.3.1 MySQL如何使用内存

8.12.3.1 MySQL如何使用内存

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

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

InnoDB缓冲池是一个内存区域，用于保存InnoDB表、索引和其他辅助缓冲区的缓存数据。为了提高大容量读取操作的效率，缓冲池被分成可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页面链表；使用LRU算法的变体，很少使用的数据会从缓存中老化。有关详细信息，请参阅第 15.5.1 节，“缓冲池”。
缓冲池的大小对系统性能很重要：
- InnoDBmalloc()在服务器启动时使用操作为整个缓冲池分配内存。系统 innodb_buffer_pool_size 变量定义缓冲池大小。通常，推荐 innodb_buffer_pool_size 值为系统内存的 50% 到 75%。 innodb_buffer_pool_size 可以在服务器运行时动态配置。有关更多信息，请参阅第 15.8.3.1 节，“配置 InnoDB 缓冲池大小”。
- 在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。系统变量定义缓冲池实例的 innodb_buffer_pool_instances 数量。
- 缓冲池太小可能会导致过度搅动，因为从缓冲池中刷新页面只是在短时间内再次需要。
- 太大的缓冲池可能会由于内存竞争而导致交换。
存储引擎接口使优化器能够提供有关用于优化器估计可能读取多行的扫描的记录缓冲区大小的信息。缓冲区大小可以根据估计的大小而变化。InnoDB使用这种可变大小的缓冲功能来利用行预取，并减少锁存和 B 树导航的开销。
所有线程共享MyISAM 密钥缓冲区。key_buffer_size系统变量决定了它的大小。
对于服务器打开的每个MyISAM表，索引文件打开一次；数据文件为访问该表的每个并发运行的线程打开一次。对于每个并发线程，分配一个表结构、每列的列结构和一个大小的缓冲区（其中是最大行长度，不包括列）。一列需要五到八个字节加上数据的长度。存储引擎维护一个额外的行缓冲区供内部使用。 3 * NNBLOBBLOBBLOBMyISAM
系统myisam_use_mmap 变量可以设置为 1 以启用所有MyISAM表的内存映射。
如果内部内存临时表变得太大（使用 tmp_table_size和 max_heap_table_size 系统变量确定），MySQL 会自动将表从内存格式转换为磁盘格式。从 MySQL 8.0.16 开始，磁盘临时表始终使用 InnoDB 存储引擎。（以前，用于此目的的存储引擎由 internal_tmp_disk_storage_engine 不再支持的系统变量决定。）您可以增加允许的临时表大小，如第 8.4.4 节，“MySQL 中的内部临时表使用”中所述。
对于使用MEMORY显式创建的表CREATE TABLE，只有 max_heap_table_size 系统变量决定表可以增长到多大，并且没有转换为磁盘格式。
MySQL Performance Schema是一种用于在低级别监视 MySQL 服务器执行的功能。Performance Schema 动态地增量分配内存，将其内存使用扩展到实际服务器负载，而不是在服务器启动期间分配所需的内存。一旦内存被分配，直到服务器重新启动它才会被释放。有关详细信息，请参阅第 27.17 节，“性能模式内存分配模型”。
服务器用来管理客户端连接的每个线程都需要一些特定于线程的空间。下面的列表指出了这些以及哪些系统变量控制它们的大小：
- 堆栈 ( thread_stack)
- 连接缓冲区 ( net_buffer_length)
- 结果缓冲区 ( net_buffer_length)
连接缓冲区和结果缓冲区 net_buffer_length均以字节大小开始，但会根据需要动态扩大到 max_allowed_packet字节。结果缓冲区 net_buffer_length在每个 SQL 语句之后缩小为字节。语句运行时，还会分配当前语句字符串的副本。
每个连接线程都使用内存来计算语句摘要。服务器为每个会话分配 max_digest_length字节。请参阅第 27.10 节，“性能模式语句摘要和采样”。
所有线程共享相同的基本内存。
当不再需要某个线程时，分配给它的内存将被释放并归还给系统，除非该线程返回到线程缓存中。在这种情况下，内存保持分配状态。
每个对表执行顺序扫描的请求都会分配一个读取缓冲区。系统 read_buffer_size变量确定缓冲区大小。
当以任意顺序读取行时（例如，在排序之后），可以分配随机读取缓冲区以避免磁盘寻道。系统 read_rnd_buffer_size 变量确定缓冲区大小。
所有连接都在一次传递中执行，大多数连接甚至可以在不使用临时表的情况下完成。大多数临时表都是基于内存的哈希表。具有较大行长度（计算为所有列长度的总和）或包含 BLOB列的临时表存储在磁盘上。
大多数执行排序的请求都会根据结果集的大小分配一个排序缓冲区和零到两个临时文件。请参阅第 B.3.3.5 节，“MySQL 存储临时文件的位置”。
几乎所有的解析和计算都是在线程本地和可重用的内存池中完成的。小项目不需要内存开销，从而避免了正常的缓慢内存分配和释放。仅为意外大的字符串分配内存。
对于每个BLOB 包含列的表，缓冲区会动态扩大以读取更大的BLOB值。如果扫描表，缓冲区会增长到 BLOB最大值。
MySQL 需要表缓存的内存和描述符。所有正在使用的表的处理程序结构都保存在表缓存中，并按照“先进先出” (FIFO) 的方式进行管理。系统 table_open_cache变量定义初始表缓存大小；参见第 8.4.3.1 节，“MySQL 如何打开和关闭表”。
MySQL 还需要内存用于表定义缓存。table_definition_cache 系统变量定义可以存储在表定义缓存中的表定义的数量。如果你使用大量的表，你可以创建一个大的表定义缓存来加速表的打开。与表缓存不同，表定义缓存占用的空间更少，并且不使用文件描述符。
FLUSH TABLES语句或 mysqladmin flush-tables命令立即关闭所有未使用的表，并将所有使用中的表标记为在当前执行的线程完成时关闭。这有效地释放了大部分正在使用的内存。FLUSH TABLES直到所有表都关闭后才返回。
作为、、和语句的结果，服务器将信息缓存在内存中 GRANT。此内存不会由相应的、、和语句释放，因此对于执行许多导致缓存的语句实例的服务器，缓存内存使用量会增加，除非使用释放它。 CREATE USERCREATE SERVERINSTALL PLUGINREVOKEDROP USERDROP SERVERUNINSTALL PLUGINFLUSH PRIVILEGES
在复制拓扑中，以下设置会影响内存使用，并且可以根据需要进行调整：
- 复制源上的 max_allowed_packet 系统变量限制源发送到其副本进行处理的最大消息大小。此设置默认为 64M。
- 多线程副本上的系统变量 replica_pending_jobs_size_max （来自 MySQL 8.0.26）或 slave_pending_jobs_size_max （MySQL 8.0.26 之前）设置可用于保存等待处理的消息的最大内存量。此设置默认为 128M。内存仅在需要时分配，但如果您的复制拓扑有时处理大型事务，则可能会使用它。这是一个软限制，可以处理更大的交易。
- 复制源或副本上的rpl_read_size 系统变量控制从二进制日志文件和中继日志文件读取的最小数据量（以字节为单位）。默认值为 8192 字节。为从二进制日志和中继日志文件读取的每个线程分配此值大小的缓冲区，包括源上的转储线程和副本上的协调器线程。
- binlog_transaction_dependency_history_size 系统变量限制作为内存历史记录保存的行哈希的数量。
- 系统变量指定单个事务的 max_binlog_cache_size 内存使用上限。
- 系统变量指定语句缓存使用内存的 max_binlog_stmt_cache_size 上限。

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

监控 MySQL 内存使用情况

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

默认情况下禁用大多数性能模式内存检测。可以通过更新 Performance Schema表的ENABLED列来启用 Instruments。setup_instruments记忆仪器的名称格式为，其中是一个值，例如或，并且是仪器详细信息。 memory/code_area/instrument_namecode_areasqlinnodbinstrument_name

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

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和其他。

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

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

笔记

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

重新启动服务器后， ENABLEDPerformance Schemasetup_instruments 表的列应该报告YES您启用的内存工具。对于内存仪器，表中的 TIMED列 setup_instruments被忽略，因为内存操作不定时。

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_name 表中查询内存仪器数据，该表将数据汇总为 EVENT_NAME. 这 EVENT_NAME是仪器的名称。

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

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_bytes 表查询相同的底层数据，该表显示了服务器中全局的当前内存使用情况，按分配类型细分。

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, 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     |
+---------------------------+---------------+

笔记

在 MySQL 8.0.16 之前， sys.format_bytes()用于 FORMAT_BYTES().

有关 sys模式的更多信息，请参阅第 28 章，MySQL 系统模式。