• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
    •
    位置： MySQL 8 中文手册 -> MySQL 性能优化 -> 优化MySQL 服务器

    优化内存使用

    MySQL如何使用内存

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

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

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

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

      • InnoDB使用malloc()操作在服务器启动时为整个缓冲池分配内存。所述innodb_buffer_pool_size系统变量定义缓冲池大小。通常，建议innodb_buffer_pool_size值为系统内存的50％到75％。innodb_buffer_pool_size可以在服务器运行时动态配置。有关更多信息，请参见“配置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系统变量确定，不再受支持。）您可以按照“ MySQL中的内部临时表使用”中所述增加允许的临时表大小。

      对于使用MEMORY显式创建的表CREATE TABLE，只有max_heap_table_size系统变量才能确定表可以增长到多少，并且不会转换为磁盘格式。

    • 在MySQL性能模式是在低级别监控MySQL服务器执行的功能。性能架构动态地增量分配内存，将其内存使用量扩展到实际服务器负载，而不是在服务器启动期间分配所需的内存。一旦分配了内存，就不会释放它，除非重新启动服务器。有关更多信息，请参见“性能模式内存分配模型”。

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

      • 堆栈（thread_stack）
      • 连接缓冲区（net_buffer_length）
      • 结果缓冲区（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系统变量定义初始表高速缓存大小；请参见“ 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。

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

      • max_allowed_packet复制主服务器上的系统变量限制了主服务器发送给从服务器进行处理的最大消息大小。此设置默认为64M。
      • slave_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内存使用情况

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

    默认情况下，大多数性能架构内存检测是禁用的。可以通过更新ENABLED“性能模式”setup_instruments表的列来启用工具。内存仪器的名称形式为，其中是诸如或的值，并且是仪器的详细信息。memory/code_are /instrument_namecode_are sqlinnodbinstrument_name


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

      mysql> SELECT * FROM performance_schem .setup_instruments
       WHERE NAME LIKE '%memory%';

      您可以通过指定代码区域来缩小结果范围。例如，您可以InnoDB通过指定innodb为代码区域来将结果限制为内存仪器。

      mysql> SELECT * FROM performance_schem .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_schem ，sql，client，innodb，myisam，csv，memory，blackhole， rchive，partition，和其他人。

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

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

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

      重新启动服务器后，ENABLED性能架构setup_instruments表的列应报告YES已启用的内存工具。对于内存工具TIMED，setup_instruments表中的列将被忽略，因为内存操作未计时。

      mysql> SELECT * FROM performance_schem .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缓冲池的内存数据。有关列的说明，请参见“内存摘要表”。

      mysql> SELECT * FROM performance_schem .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_are , 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模式的更多信息，请参见MySQL sys模式。


    启用大页面支持

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

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

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

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

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

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

    shell>cat /proc/meminfo | grep -i huge
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       4096 kB

    nonempty命令输出表明存在大页面支持，但零值表明未配置任何页面供使用。

    如果需要将内核重新配置为支持大页面，请查阅该hugetlbpage.txt文件以获取指示。

    假设您的Linux内核启用了大页面支持，请使用以下命令将其配置为供MySQL使用。通常，将它们放在rc系统引导序列中执行的文件或等效启动文件中，以便命令在每次系统启动时执行。这些命令应在MySQL服务器启动之前的引导顺序中早执行。确保根据您的系统更改分配号和组号。

    # Set the number of pages to be used.
# Each page is normally 2MB, so a value of 20 = 40MB.
# This command actually allocates memory, so this much
# memory must be available.
echo 20 > /proc/sys/vm/nr_hugepages

# Set the group number that is permitted to access this
# memory (102 in this case). The mysql user must be  
# member of this group.
echo 102 > /proc/sys/vm/hugetlb_shm_group

# Increase the amount of shmem permitted per segment
# (12G in this case).
echo 1560281088 > /proc/sys/kernel/shmmax

# Increase total amount of shared memory.  The value
# is the number of pages. At 4KB/page, 4194304 = 16GB.
echo 4194304 > /proc/sys/kernel/shmall

    对于MySQL使用，您通常希望的值shmmax接近的值shmall。

    要验证大页面配置，请/proc/meminfo按照前面所述再次检查。现在您应该看到一些非零值：

    shell>cat /proc/meminfo | grep -i huge
HugePages_Total:      20
HugePages_Free:       20
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       4096 kB

    利用的最后一步hugetlb_shm_group是为mysql用户提供一个“无限”的门锁限制值。可以通过编辑/etc/security/limits.conf或将以下命令添加到mysqld_safe脚本中来完成：

    ulimit -l unlimited

    在mysqld_safe中添加ulimit命令会导致用户在切换到用户之前将memlock限制设置为。（这假定mysqld_safe由启动。）rootunlimitedmysqlroot

    默认情况下，MySQL中的大页面支持处于禁用状态。要启用它，请使用--large-pages选项启动服务器。例如，您可以在服务器my.cnf文件中使用以下几行：

    [mysqld]
large-pages

    使用此选项，InnoDB自动将大页用于其缓冲池和其他内存池。如果InnoDB无法执行此操作，则退回到传统内存的使用，并向错误日志写入警告：警告：使用常规内存池

    要验证是否使用了大页面，请/proc/meminfo再次检查：

    shell>cat /proc/meminfo | grep -i huge
HugePages_Total:      20
HugePages_Free:       20
HugePages_Rsvd:        2
HugePages_Surp:        0
Hugepagesize:       4096 kB

    上篇：使用符号链接

    下篇：优化网络使用