[ACDU 翻译] 8.12.3.1 MySQL 如何使用内存

原创由迪 2021-07-08

730

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

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

所述InnoDB缓冲器池是保持高速缓存的存储区域InnoDB表，索引，及其它辅助缓冲器中的数据。为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页面的链表；使用LRU算法的变体，很少使用的数据从缓存中老化。有关更多信息，请参阅第 15.5.1 节，“缓冲池”。

缓冲池的大小对系统性能很重要：
- InnoDB在服务器启动时使用malloc()操作为整个缓冲池分配内存。所述 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 * *N*NBLOBBLOBBLOBMyISAM
所述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性能模式是在低级别监控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直到所有表都关闭后才返回。
服务器在内存中缓存信息的结果 GRANT， CREATE USER， CREATE SERVER，和 INSTALL PLUGIN语句。相应的REVOKE、 DROP USER、 DROP SERVER和 UNINSTALL PLUGIN 语句不会释放此内存，因此对于执行导致缓存的语句的许多实例的服务器，除非使用释放，否则缓存内存使用量会增加 FLUSH 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 内存检测是禁用的。可以通过更新ENABLEDPerformance Schemasetup_instruments表的列来启用仪器。内存仪器的名称为，其中是或等值，是仪器详细信息。 memory/*code_area*/*instrument_name*code_areasql``innodbinstrument_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 Schemamemory_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 系统架构。

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

朝花夕拾系列6-MySQL 8 一起学官方文档优化（共120篇）

朝花夕拾系列5 MySQL 官方文档　优化！欢迎大家订阅

[ACDU 翻译] 8.12.3.1 MySQL 如何使用内存

监控 MySQL 内存使用情况

文章被以下合辑收录

评论

相关阅读