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
N
最大行长,不计算在内吗团
列)。一个团
列需要5到8个字节加上团
数据。的MyISAM
存储引擎维护一个额外的行缓冲区供内部使用。的
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内部临时表的使用”。为
内存
显式地使用创建表
,只有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
系统变量决定缓冲区大小。所有连接都在一次传递中执行,大多数连接甚至不需要使用临时表就可以完成。大多数临时表都是基于内存的哈希表。具有较大行长(按所有列长之和计算)或包含
团
列存储在磁盘上。大多数执行排序的请求根据结果集大小分配一个排序缓冲区和0到2个临时文件。看到章节B.3.3.5,“MySQL存放临时文件的地方”。
几乎所有解析和计算都在线程本地和可重用内存池中完成。对于较小的项目不需要内存开销,从而避免了正常的缓慢内存分配和释放。内存只分配给意外大的字符串。
MySQL需要内存和表缓存的描述符。所有正在使用的表的处理程序结构都保存在表缓存中,并作为”先进先出”(FIFO)。的
table_open_cache
系统变量定义初始表缓存大小;看到章节8.4.3.1,“MySQL如何打开和关闭表”。MySQL还需要用于表定义缓存的内存。的
table_definition_cache
系统变量定义了可以存储在表定义缓存中的表定义的数量。如果使用大量表,可以创建大型表定义缓存,以加快打开表的速度。与表缓存不同,表定义缓存占用更少的空间,并且不使用文件描述符。一个
刷新表
语句或mysqladmin刷新表命令关闭所有不使用的表,并在当前执行的线程结束时将所有正在使用的表标记为关闭。这有效地释放了大部分正在使用的内存。刷新表
直到所有表都关闭后才返回。服务器将信息缓存到内存中
格兰特
,创建用户
,创建服务器
,安装插件
语句。这种内存是不被相应释放的撤销
,减少用户
,减少服务器
,卸载插件
语句,因此对于执行许多导致缓存的语句实例的服务器来说,缓存内存的使用会增加,除非使用这些语句释放缓存内存冲洗的特权
。在复制拓扑中,以下设置会影响内存使用情况,您可以根据需要进行调整:
的
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将堆栈之间的未使用内存计算为已使用内存。要验证这一点,请检查可用的交换交换- s
。我们测试mysqld使用几个内存泄漏检测器(商业的和开源的),所以应该没有内存泄漏。
下面的示例演示如何使用性能模式而且系统模式监控MySQL内存使用情况。
默认情况下,大多数性能模式内存检测是禁用的。工具可以通过更新启用
列setup_instruments
表格记忆仪器的名称是内存/
,在那里code_area
/instrument_name
code_area
值是否为sql
或innodb
,instrument_name
是仪器细节。
要查看可用的MySQL内存工具,请查询Performance Schema
setup_instruments
表格下面的查询为所有代码区返回数百个内存工具。mysql> SELECT * FROM performance_schemasetup_instrumentsWHERE NAME LIKE '%memory%';
可以通过指定代码区域来缩小结果范围。例如,您可以将结果限制为
InnoDB
通过指定内存仪器innodb
作为代码区。mysql> SELECT * FROM performance_schemasetup_instrumentsWHERE 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
,客户端
,innodb
,myisam
,csv
,内存
,黑洞
,存档
,分区
等。若要启用内存仪器,请添加
performance-schema-instrument
规则到您的MySQL配置文件。例如,要启用所有内存工具,请将此规则添加到配置文件中并重新启动服务器:performance-schema-instrument =“内存/ % =统计”
请注意在启动时启用内存工具可以确保计算在启动时发生的内存分配。
重新启动服务器后,
启用
列setup_instruments
表应报告是的
用于您启用的内存工具。的定时
中的列。setup_instruments
表对于内存仪器被忽略,因为内存操作没有计时。mysql> SELECT * FROM performance_schemasetup_instrumentsWHERE 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_schemamemory_summary_global_by_event_nameWHERE 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。内存_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. txtx$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/') ORDER BY SUM(current_alloc) DESC;+---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | 内存/ innodb MiB | 843.24 | |内存/ performance_schema MiB | 81.29 | |内存/ mysys MiB | 8.20 | |内存/ sql | 2.47 MiB | |内存/内存简约| 174.01 | |内存/ myisam简约| | 46.53 | 512字节内存/黑洞| | | 512字节内存/联邦| | | 512字节内存/ csv | | |内存/ vio | 496字节 | +---------------------------+---------------+
请注意在MySQL 8.0.16之前,
sys.format_bytes ()
被用于FORMAT_BYTES ()
。欲了解更多有关
sys
模式,看到28章,MySQL系统架构。