mysql8 性能优化_mysql8 参考手册--mysql使用内存优化

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

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

所述InnoDB缓冲器池是保持高速缓存的存储区域InnoDB表，索引，及其它辅助缓冲器中的数据。为了提高大容量读取操作的效率，缓冲池分为多个页面 ，这些页面可以潜在地容纳多行。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表。使用LRU算法的变体，将很少使用的数据从缓存中老化掉 。有关更多信息，请参见第15.5.1节“缓冲池”。

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

(1)、InnoDB使用malloc()操作在服务器启动时为整个缓冲池分配内存 。所述 innodb_buffer_pool_size 系统变量定义缓冲池大小。通常，建议 innodb_buffer_pool_size 值为系统内存的50％到75％。 innodb_buffer_pool_size 可以在服务器运行时动态配置。

(2)、在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性 。所述 innodb_buffer_pool_instances 系统变量定义缓冲池实例的数量。

(3)、太小的缓冲池可能会引起过多的搅动，因为从缓冲池中刷新页面只是在短时间内再次需要。

(4)、缓冲池太大可能会由于争用内存而导致交换。

使用存储引擎接口，优化器可以提供有关记录缓冲区大小的信息，该记录缓冲区将用于优化器估计将读取多行的扫描。缓冲区的大小可以根据估计的大小而变化。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性能模式是在低级别监控MySQL服务器执行的功能。性能架构动态地增量分配内存，将其内存使用量扩展到实际服务器负载，而不是在服务器启动期间分配所需的内存。一旦分配了内存，就不会释放它，除非重新启动服务器。有关更多信息，请参见 第26.17节“性能模式内存分配模型”。

服务器用来管理客户端连接的每个线程都需要一些特定于线程的空间。下表列出了这些内容以及哪些系统变量控制它们的大小：

(1)、堆栈(thread_stack)

(2)、连接缓冲区(net_buffer_length)

(3)、结果缓冲区(net_buffer_length)

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

每个连接线程都使用内存来计算语句摘要。服务器max_digest_length为每个会话分配 字节。

所有线程共享相同的基本内存。

当不再需要线程时，分配给它的内存将释放并返回系统，除非该线程返回线程高速缓存。在这种情况下，内存将保持分配状态。

每个执行表顺序扫描的请求都分配一个读取缓冲区。的 read_buffer_size系统变量决定缓冲器大小。

当以任意顺序(例如，按照排序)读取行时， 可以分配一个 随机读取的缓冲区以避免磁盘查找。的 read_rnd_buffer_size 系统变量决定缓冲器大小。

所有联接都在一次通过中执行，并且大多数联接甚至都可以使用临时表来完成。大多数临时表是基于内存的哈希表。具有较大行长度(按所有列长度的总和计算)或包含BLOB列的临时表 存储在磁盘上。

大多数执行排序的请求根据结果集的大小分配一个排序缓冲区和两个临时文件零。请参见第B.4.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 USER， CREATE SERVER，和 INSTALL PLUGIN语句。该内存不能由相应的释放 REVOKE， DROP USER， DROP SERVER，和 UNINSTALL PLUGIN 语句，所以执行该语句的许多情况下，这导致高速缓存，会出现在缓存内存使用的增加，除非它与释放服务器 FLUSH PRIVILEGES。

在复制拓扑中，以下设置会影响内存使用情况，并且可以根据需要进行调整：

(1)、max_allowed_packet 复制主服务器上 的 系统变量限制了主服务器发送给从服务器进行处理的最大消息大小。此设置默认为64M。

(2)、slave_pending_jobs_size_max 多线程从属服务器上 的 系统变量设置可用于保存等待处理的消息的最大内存量。此设置默认为128M。仅在需要时才分配内存，但是如果您的复制拓扑有时可以处理大事务，则可以使用该内存。这是一个软限制，可以处理更大的交易。

(3)、rpl_read_size 复制主服务器或从属服务器上 的系统变量控制从二进制日志文件和中继日志文件读取的最小数据量(以字节为单位)。默认值为8192字节。为从二进制日志和中继日志文件读取的每个线程(包括主服务器上的转储线程和从服务器上的协调器线程)分配一个具有此值大小的缓冲区。

(4)、所述 binlog_transaction_dependency_history_size 系统变量限制保持为一个内存历史行哈希的数量。

(5)、所述 max_binlog_cache_size 系统变量指定由单个事务存储器使用的上限。

(6)、max_binlog_stmt_cache_size 系统变量指定由语句缓存内存使用的上限。

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

监控MySQL内存使用情况

下面的示例演示如何使用 性能架构 和sys架构来监视MySQL内存使用情况。

1、默认情况下，大多数性能架构内存检测是禁用的。可以通过更新ENABLED“性能模式” 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，和其他人。

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

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

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

已启用的内存工具报告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 |

...

3、查询存储仪器数据。在此示例中，在Performance Schema memory_summary_global_by_event_name 表中查询存储仪器数据，该 表通过汇总数据 EVENT_NAME。该 EVENT_NAME是仪器的名称。

以下查询返回InnoDB缓冲池的内存数据 。有关列的说明，请参见 第26.12.17.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()。