MySQL内存使用情况分析：配置及实际占用介绍，应急调优必备

在实际系统环境中，MySQL实例的内存使用随着业务的增长缓慢增长，有些时候并没有及时的释放。本文简要介绍下MySQL数据库中和内存相关的配置，以及分析内存的实际使用情况，以进行应急和调优处理。

1、MySQL内存结构

在MySQL中内存的占用主要由两部分组成：全局共享缓存 和所有线程独立缓存 。

MySQL中使用的内存大小等于全局共享缓存（+）和所有线程缓存之和，如下图所示：

1）：包括 部分+

2）：(当前活跃连接数) * (会话级内存分配总和)

调整对应的配置后，相应的内存使用大小可以通过计算得到（参考网址）：

另外，也可以通过SQL语句获得各个配置的大小（假设最大连接为200），如下所示：

select VARIABLE_NAME,VARIABLE_VALUE/1024/1024 MB from performance_schema.global_variables WHERE VARIABLE_NAME in ('key_buffer_size','query_cache_size','tmp_table_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size')
union all
SELECT 'sort_buffer_size',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'sort_buffer_size' ) AS v2 
union all
SELECT 'read_buffer_size',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'read_buffer_size' ) AS v2 
union all
SELECT 'read_rnd_buffer_size',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'read_rnd_buffer_size' ) AS v2 
union all
SELECT 'join_buffer_size',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024  vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'join_buffer_size' ) AS v2 
union all
SELECT 'thread_stack',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'thread_stack' ) AS v2 
union all
SELECT 'binlog_cache_size',(v1.VARIABLE_VALUE*v2.vv) MB
FROM ( select 200 as VARIABLE_VALUE from performance_schema.global_variables WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  ( SELECT VARIABLE_VALUE/1024/1024 vv FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'binlog_cache_size' ) AS v2

对于连接配置，可以查看系统中历史最大连接情况进行评估

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 28    |
+----------------------+-------+
1 row in set (0.00 sec)

2、MySQL中内存相关表

在MySQL中有内存相关的表监控内存的使用情况

mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
+-----------------------------------------+
5 rows in set (0.00 sec)

以上表的监控统计，分别统计帐户、主机、线程、用户和全局事件执行内存操作等信息。

1）

按MySQL账户和事件名称分类的内存使用情况摘要，识别哪些账户正在使用最多的内存，以及这些内存是如何分配的，如查询、系统锁等特定事件。

select USER, HOST, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_account_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;

2）

按连接到MySQL的主机和事件名称分类的内存使用情况摘要，用来识别识别哪些远程主机或IP地址正在消耗最多的内存。

select HOST, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;

3）

按MySQL线程ID和事件名称分类的内存使用情况摘要，对于特定的长时间运行查询或事务的内存使用情况特别有用，因为每个线程通常与一个特定的查询或事务相关联。

select thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_thread_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;

得到后，查找.表获得对应的和：

mysql> select * from performance_schema.threads where THREAD_ID=49\G
*************************** 1. row ***************************
          THREAD_ID: 49
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 10
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: tango-DB01
     PROCESSLIST_DB: tango
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 84
  PROCESSLIST_STATE: User sleep
   PROCESSLIST_INFO: select count(1),sleep(2000) from tango.t2 for update
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 1657
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

也可以通过，查找.rrent和.story获得对应的历史记录信息，定位到具体的SQL语句信息，以后续应急或优化。

4）

按MySQL用户和事件名称分类的内存使用情况摘要，更侧重于基于用户名而不是完整的账户信息（包括主机）的内存使用。

select USER, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_user_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;

5）

包括全局的、不按任何特定账户、主机、线程或用户分类的内存使用情况摘要，提供整个MySQL服务器内存使用的概览，并允许按事件名称来查看这些内存是如何分配的。

select event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

3、MySQL内存分配管理

MySQL的内存虽然划分为 +和 ，但实际上是按照层和层进行内存管理，而且这两部分是按照不同的内存管理方式进行管理的。

MySQL首先通过「」函数初始化一块较大的内存空间，实际上最终是「通过函数向内存分配器申请内存空间」，然后每次再调用函数在这块内存空间中分配出内存进行使用，其目的就是将多次零散的操作合并成一次大的操作，以提升性能。

在Linux中常用的内存分配管理器有三种：(Glibc)、()、()。MySQL 默认使用的是glibc的作为内存分配器。

在实际生产系统中，遇到MySQL实例实际占用的内存比配置的高很多，但是没有及时释放的现象。大部分是因为内存分配管理器占用很多内存不释放，另一部分是因为内存碎片。另外在部分场景下，原生的MySQL使用的内存管理存在内存释放不及时的问题，所以在基于MySQL系列的国产数据库在内存管理上进行了部分优化，比如使用替代进行内存管理。通过以下命令，也可以查看实际使用的内存管理方式：

pt-mysql-summary  -S /tmp/mysql.sock --user root --password xxxxxx|grep -A 5 "Memory management"
# Memory management library ##################################
jemalloc is not enabled in mysql config for process with id 1339
# The End ####################################################

针对（glibc）内存管理的缺陷（调用glibc申请的内存使用完毕后，归还给OS时没有被正常回收，而变成了碎片，随着碎片的不断增长，就能看到进程占用的内存不断上升）。在测试环境，可以调用gdb函数主动回收释放内存碎片空间：

gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'

以上是MySQL数据库内存配置和管理的相关知识。

参考资料：

:///dc666///:///p/

内存 VARIABLE by performance MySQL VALUE