3.优化内存使用

MySQL 如何使用内存

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

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

InnoDB缓冲池是一个内存区域，保存表、索引和其他辅助缓冲区的缓存InnoDB数据。为了提高大容量读取操作的效率，缓冲池被划分为可以容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链表；使用 LRU 算法的变体，很少使用的数据会从缓存中老化。

缓冲池的大小对于系统性能很重要：
- InnoDB 在服务器启动时使用 malloc() 操作为整个缓冲池分配内存。 innodb_buffer_pool_size 系统变量定义缓冲池大小。通常，建议的 innodb_buffer_pool_size 值为系统内存的 50% 到 75%。 innodb_buffer_pool_size 可以在服务器运行时动态配置。
- 在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。 innodb_buffer_pool_instances 系统变量定义缓冲池实例的数量。
- 缓冲池太小可能会导致过度搅动，因为页面从缓冲池中刷新后不久又需要再次使用。
- 太大的缓冲池可能会因内存竞争而导致交换。
存储引擎接口使优化器能够提供有关记录缓冲区大小的信息，该记录缓冲区用于优化器估计可能读取多行的扫描。缓冲区大小可以根据估计的大小而变化。 InnoDB 使用这种可变大小的缓冲功能来利用行预取，并减少锁存和 B 树导航的开销。
所有线程共享 MyISAM 键缓冲区。 key_buffer_size 系统变量决定其大小。

服务器每打开一个MyISAM表，索引文件就打开一次；对于访问该表的每个并发运行的线程，数据文件都会打开一次。对于每个并发线程，分配一个表结构、每列的列结构以及大小为 3 * N 的缓冲区（其中 N 是最大行长度，不包括 BLOB 列）。 BLOB 列需要五到八个字节加上 BLOB 数据的长度。 MyISAM 存储引擎维护一个额外的行缓冲区供内部使用。
myisam_use_mmap 系统变量可以设置为 1 以启用所有 MyISAM 表的内存映射。
如果内部内存临时表变得太大（使用 tmp_table_size 和 max_heap_table_size 系统变量确定），MySQL 会自动将该表从内存中转换为磁盘格式，这使用 InnoDB 存储引擎。可以增加允许的临时表大小。

对于使用 CREATE TABLE 显式创建的 MEMORY 表，只有 max_heap_table_size 系统变量决定表可以增长的大小，并且不会转换为磁盘格式。
MySQL Performance Schema 是一个用于在低级别监控 MySQL 服务器执行情况的功能。性能架构以增量方式动态分配内存，将其内存使用扩展到实际服务器负载，而不是在服务器启动期间分配所需的内存。一旦分配了内存，它就不会被释放，直到服务器重新启动。
服务器用于管理客户端连接的每个线程都需要一些特定于线程的空间。以下列表指出了这些以及控制其大小的系统变量：
- 堆栈（thread_stack）
- 连接缓冲区（net_buffer_length）
- 结果缓冲区（net_buffer_length）
连接缓冲区和结果缓冲区均以等于 net_buffer_length 字节的大小开始，但根据需要动态扩大到 max_allowed_packet 字节。在每个 SQL 语句之后，结果缓冲区缩小到 net_buffer_length 字节。当语句运行时，还会分配当前语句字符串的副本。

每个连接线程都使用内存来计算语句摘要。服务器为每个会话分配 max_digest_length 字节。
所有线程共享相同的基础内存。
当不再需要某个线程时，分配给它的内存将被释放并返回到系统，除非该线程返回到线程缓存中。在这种情况下，内存仍保持分配状态。
每个执行表顺序扫描的请求都会分配一个读缓冲区。 read_buffer_size 系统变量确定缓冲区大小。
当以任意顺序读取行时（例如，排序后），可以分配随机读取缓冲区以避免磁盘寻道。 read_rnd_buffer_size 系统变量确定缓冲区大小。
所有联接都在一次传递中执行，并且大多数联接甚至可以在不使用临时表的情况下完成。大多数临时表是基于内存的哈希表。具有较大行长度（计算为所有列长度之和）或包含 BLOB 列的临时表存储在磁盘上。
大多数执行排序的请求都会分配一个排序缓冲区和零到两个临时文件，具体取决于结果集大小。
几乎所有的解析和计算都是在线程本地且可重用的内存池中完成的。小项目不需要内存开销，从而避免了正常的缓慢内存分配和释放。内存仅分配给意外大的字符串。

对于每个具有 BLOB 列的表，缓冲区会动态扩大以读取更大的 BLOB 值。如果扫描表，缓冲区将增长到与最大 BLOB 值一样大。
MySQL 需要内存和表缓存描述符。所有正在使用的表的处理程序结构都保存在表缓存中，并按“先进先出”(FIFO) 方式进行管理。 table_open_cache系统变量定义初始表缓存大小。

MySQL 还需要内存用于表定义缓存。 table_definition_cache 系统变量定义可以存储在表定义缓存中的表定义的数量。如果使用大量表，可以创建较大的表定义缓存，以加快表的打开速度。与表缓存不同，表定义缓存占用的空间更少，并且不使用文件描述符。
FLUSH TABLES 语句或 mysqladmin flash-tables 命令会立即关闭所有未使用的表，并在当前执行的线程完成时将所有正在使用的表标记为关闭。这有效地释放了大部分正在使用的内存。在关闭所有表之前，FLUSH TABLES 不会返回。
服务器将由于 GRANT、CREATE USER、CREATE SERVER 和 INSTALL PLUGIN 语句而产生的信息缓存在内存中。该内存不会被相应的 REVOKE、DROP USER、DROP SERVER 和 UNINSTALL PLUGIN 语句释放，因此对于执行导致缓存的语句的许多实例的服务器，缓存内存的使用会增加，除非使用 FLUSH 释放它特权。
在复制拓扑中，以下设置会影响内存使用情况，并且可以根据需要进行调整：
- 复制源上的 max_allowed_packet 系统变量限制源发送到其副本进行处理的最大消息大小。此设置默认为 64M。
- 多线程副本上的系统变量replica_pending_jobs_size_max设置可用于保存等待处理的消息的最大内存量。此设置默认为 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 内存使用情况。

默认情况下，大多数性能模式内存检测都是禁用的。可以通过更新性能模式 setup_instruments 表的 ENABLED 列来启用仪器。内存仪器的名称格式为内存/code_area/instrument_name，其中code_area是一个值，例如sql或innodb，instrument_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、内存、blackhole、archive、partition 等。

要启用内存工具，请将性能架构工具规则添加到 MySQL 配置文件中。例如，要启用所有内存仪器，请将此规则添加到配置文件中并重新启动服务器：

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

在启动时启用内存工具可确保对启动时发生的内存分配进行计数。

重新启动服务器后，性能架构 setup_instruments 表的 ENABLED 列应针对启用的内存工具报告 YES。对于内存仪器，setup_instruments 表中的 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    |
...

查询内存仪器数据。在此示例中，在性能架构 memory_summary_global_by_event_name 表中查询内存仪器数据，该表按 EVENT_NAME 汇总数据。 EVENT_NAME 是仪器的名称。

以下查询返回 InnoDB 缓冲池的内存数据。

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

启用大页面支持

某些硬件和操作系统体系结构支持大于默认值（通常为 4KB）的内存页。此支持的实际实现取决于底层硬件和操作系统。由于减少了转换后备缓冲区 (TLB) 未命中，执行大量内存访问的应用程序可以通过使用大页面来获得性能改进。

在MySQL中，InnoDB可以使用大页面为其缓冲池和附加内存池分配内存。

MySQL 中大页面的标准使用尝试使用支持的最大大小，最多 4MB。在 Solaris 下，“超大页面”功能允许使用高达 256MB 的页面。此功能适用于最新的 SPARC 平台。可以使用 --super-large-pages 或 --skip-super-large-pages 选项启用或禁用它。

MySQL还支持Linux实现大页面支持（在Linux中称为HugeTLB）。

在Linux上使用大页面之前，必须启用内核支持大页面，并且需要配置HugeTLB内存池。作为参考，HugeTBL API 记录在 Linux 源的 Documentation/vm/hugetlbpage.txt 文件中。

某些最新系统（例如 Red Hat Enterprise Linux）的内核可能默认启用大页面功能。要检查您的内核是否如此，请使用以下命令并查找包含“huge”的输出行：

$> grep -i huge /proc/meminfo
AnonHugePages:   2658304 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

非空命令输出表示存在大页面支持，但零值表示没有配置任何页面供使用。

如果内核需要重新配置以支持大页面，请参阅hugetlbpage.txt 文件以获取说明。

假设 Linux 内核启用了大页面支持，请使用以下步骤将其配置为供 MySQL 使用：

确定所需大页面的数量。这是 InnoDB 缓冲池的大小除以大页大小，我们可以将其计算为 innodb_buffer_pool_size / Hugepagesize。假设 innodb_buffer_pool_size 的默认值 (128MB) 并使用从 /proc/meminfo (2MB) 获取的 Hugepagesize 值，则为 128MB / 2MB，即 64 个大页。我们称这个值为P。
以系统 root 身份在文本编辑器中打开文件 /etc/sysctl.conf，然后添加此处显示的行，其中 P 是上一步中获取的大页面数：
```
vm.nr_hugepages=P
```
使用之前获得的实际值，附加行应如下所示：
```
vm.nr_huge_pages=64
```
保存更新的文件。
以系统 root 身份运行以下命令：
```
$> sudo sysctl -p
```
在某些系统上，大页面文件的命名可能略有不同；例如，某些发行版将其称为 nr_hugepages。如果 sysctl 返回与文件名相关的错误，请检查 /proc/sys/vm 中相应文件的名称并使用它。

要验证大页面配置，请按照前面所述再次检查 /proc/meminfo。现在应该在输出中看到一些额外的非零值，类似于：
```
$> grep -i huge /proc/meminfo
AnonHugePages:   2686976 kB
ShmemHugePages:        0 kB
HugePages_Total:     233
HugePages_Free:      233
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:          477184 kB
```
或者，可能希望压缩 Linux VM。可以使用一系列命令（可能在脚本文件中）来执行此操作，类似于此处所示的内容：
```
sync
sync
sync
echo 3 > /proc/sys/vm/drop_caches
echo 1 > /proc/sys/vm/compact_memory
```
检查服务器使用的任何配置文件（例如 my.cnf），并确保 innodb_buffer_pool_chunk_size 设置为大于大页大小。该变量的默认值是 128M。
默认情况下，MySQL 服务器中的大页面支持是禁用的。要启用它，请使用 --large-pages 启动服务器。还可以通过将以下行添加到服务器 my.cnf 文件的 [mysqld] 部分来实现此目的：
```
large-pages=ON
```
启用此选项后，InnoDB 自动为其缓冲池和附加内存池使用大页面。如果 InnoDB 无法做到这一点，它会回退到使用传统内存，并向错误日志写入警告：警告：使用传统内存池。

可以通过重新启动 mysqld 后再次检查 /proc/meminfo 来验证 MySQL 现在是否正在使用大页面，如下所示：

$> grep -i huge /proc/meminfo
AnonHugePages:   2516992 kB
ShmemHugePages:        0 kB
HugePages_Total:     233
HugePages_Free:      222
HugePages_Rsvd:       55
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:          477184 kB

MySQL 如何使用内存​

监控MySQL内存使用情况​

启用大页面支持​

MySQL 如何使用内存

监控MySQL内存使用情况

启用大页面支持