关于MySQL内存泄露如何排查的一些思路

[[414461]]

本文转载自微信公众号「数据和云」，作者崔虎龙 。转载本文请联系数据和云公众号。

MySQL使用内存上升90%!在运维过程中50%的几率，会碰到这样的问题。算是比较普遍的现象。

MySQL内存使用率过高，有诸多原因。普遍原因是使用不当，还有MySQL本身缺陷导致的。到底是哪方面的问题，那就需要一个一个进行排查。

下面介绍排查思路：

1.参数配置需要确认，内存是否设置合理

MySQL内存分为全局和线程级：

• 全局内存(如：innodb_buffer_pool_size，key_buffer_size，innodb_log_buffer_size)。
• 线程级内存：(如：thread，read，sort，join,tmp 等)只是在需要的时候才分配，并且在操作完毕之后就释放。
• 线程级内存：线程缓存每个连接到MySQL服务器的线程都需要有自己的缓冲。默认分配thread_stack(256K,512k)，空闲时这些内存是默认使用，除此之外还有网络缓存、表缓存等。大致评估会在1M~3M这样的情况。可通过pmap观察内存变化：

mysql> SELECT @@query_cache_size, 
     @@key_buffer_size, 
     @@innodb_buffer_pool_size , 
     @@innodb_log_buffer_size , 
     @@tmp_table_size , 
     @@read_buffer_size, 
     @@sort_buffer_size, 
     @@join_buffer_size , 
     @@read_rnd_buffer_size, 
     @@binlog_cache_size, 
     @@thread_stack, 
     (SELECT COUNT(host) FROM  information_schema.processlist where command<>'Sleep')\G; 
*************************** 1. row *************************** 
@@query_cache_size:1048576 
@@key_buffer_size:8388608 
@@innodb_buffer_pool_size:268435456 
@@innodb_log_buffer_size:8388608 
@@tmp_table_size:16777216 
@@read_buffer_size:131072 
@@sort_buffer_size:1048576 
@@join_buffer_size:1048576 
@@read_rnd_buffer_size:2097152 
@@binlog_cache_size:8388608 
@@thread_stack:524288 
(select count(host) from information_schema.processlist where command<>'Sleep'): 1 

备注：query_cache_size 8.0版本已经废弃掉了。

2.存储过程&函数&触发器&视图

目前积累的使用经验中，存储过程&函数&触发器&视图 在MySQL场景下是不适合的。性能不好，又容易发现内存不释放的问题，所以建议尽量避免。

• 存储过程&函数

MySQL 5.7

mysql> SELECT db,type,count(*)  
FROM mysql.proc 
WHERE db not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY db, type; 

MySQL 8.0

mysql> SELECT  Routine_schema, Routine_type 
FROM information_schema.Routines 
WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY Routine_schema, Routine_type; 

• 视图

mysql> SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME)  
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY TABLE_SCHEMA ; 

• 触发器

mysql> SELECT TRIGGER_SCHEMA, count(*)  
 FROM information_schema.triggers  
WHERE  TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys') 
GROUP BY TRIGGER_SCHEMA; 

上面通过MySQL配置参数和设计层面检查了是否有可能内存泄露的问题。下面看看怎样分析实际使用的内存情况。

3.系统库统计查询

• 总内存使用

mysql> SELECT  
SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )  
FROM sys.memory_global_by_current_bytes 
WHERE current_alloc like '%MiB%'; 

• 分事件统计内存

mysql> SELECT event_name,     
SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )     
FROM sys.memory_global_by_current_bytes     
WHERE current_alloc like '%MiB%' GROUP BY event_name   
     ORDER BY SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  ) DESC ; 
 
mysql> SELECT event_name, 
       sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) 
FROM performance_schema.memory_summary_global_by_event_name 
ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
LIMIT 10; 

• 账号级别统计

mysql> SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED 
FROM performance_schema.memory_summary_by_account_by_event_name 
WHERE host<>"localhost" 
ORDER BY  current_number_of_bytes_used DESC LIMIT 10; 

备注：有必要统计用户级别内存，因为很多环境对接了第三方插件，模拟从库，这些插件容易内存不释放。

• 线程对应sql语句，内存使用统计

SELECT thread_id, 
       event_name, 
       sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)  
FROM performance_schema.memory_summary_by_thread_by_event_name 
ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
LIMIT 20; 

SELECT m.thread_id tid, 
       m.user, 
       esc.DIGEST_TEXT, 
       m.current_allocated, 
       m.total_allocated 
FROM sys.memory_by_thread_by_current_bytes m, 
     performance_schema.events_statements_current esc 
WHERE m.`thread_id` = esc.THREAD_ID \G 

• 打开所有内存性能监控，会影响性能，需注意

#打开 
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%'; 
#关闭 
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%'; 
#查看使用 
SELECT * FROM performance_schema.memory_summary_global_by_event_name         
WHERE EVENT_NAME LIKE 'memory/%'  
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC; 

• 系统表内存监控信息

select * from sys.x$memory_by_host_by_current_bytes; 
select * from sys.x$memory_by_thread_by_current_bytes; 
select * from sys.x$memory_by_user_by_current_bytes; 
select * from sys.x$memory_global_by_current_bytes; 
select * from sys.x$memory_global_total; 
select * from performance_schema.memory_summary_by_account_by_event_name; 
select * from performance_schema.memory_summary_by_host_by_event_name; 
select * from performance_schema.memory_summary_by_thread_by_event_name; 
select * from performance_schema.memory_summary_by_user_by_event_name; 
select * from performance_schema.memory_summary_global_by_event_name; 

备注：找到对应问题事件或线程后，可以进行排查，解决内存高的问题。

4.系统工具查看内存

1)top命令

显示系统中各个进程的资源占用状况。

• Shift + m 键 查看内存排名实际使用内存情况，关注RES指标。

2)free命令

free-h 命令显示系统内存的使用情况，包括物理内存、交换内存(swap)和内核缓冲区内存。

• used列显示已经被使用的物理内存和交换空间。
• buff/cache列显示被buffer和cache使用的物理内存大小。
• available列显示还可以被应用程序使用的物理内存大小。
• Swap行(第三行)是交换空间的使用情况。

3)ps命令

MySQL相关进程使用内存情况。

shell > ps eo user,pid,vsz,rss $(pgrep -f 'mysqld') 
USER         PID    VSZ   RSS 
root      215945  12960  2356 
mysql     217246 1291540 241824 
root      221056  12960  2428 
mysql     374243 1336924 408752 

4)pmap 命令

pmap是Linux调试及运维一个很好的工具，查看进程的内存映像信息。

用法1：执行一段时间记录数据变化，最少20个记录，下面22837是MySQL pid

while true; do pmap -d  22837  | tail -1; sleep 2; done 

用法2：linux 命令pmap MySQL pid导出内存，下面22837是MySQL pid

pmap -X -p 22837 > /tmp/memmysql.txt 

RSS就是这个process实际占用的物理内存。

Dirty: 脏页的字节数(包括共享和私有的)。

Mapping: 占用内存的文件、或[anon](分配的内存)、或[stack](堆栈)。

writeable/private：进程所占用的私有地址空间大小，也就是该进程实际使用的内存大小。

1.首先使用/top/free/ps在系统级确定是否有内存泄露。如有，可以从top输出确定哪一个process。

2.pmap工具是能帮助确定process是否有memory leak。确定memory leak的原则：writeable/private (‘pmap –d’输出)如果在做重复的操作过程中一直保持稳定增长，那么一定有内存泄露。

总结

对于MySQL内存泄露来说:

• 从参数设置和设计上尽量合理
• 通过ps库进行排查
• linux工具进一步确认
• 官方bug里memory leak查找，是否存在修复的版本

以上排查里都没有找到原因，可以换下服务器或主从切换观察。也可以进行版本升级(代价不小)。

如能提供一个实际环境，也可以一步一步进行调试，抓取内存变化，确定是什么导致内存泄露的问题。之后提交bug，让官方提供修复。

关于作者

 

崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业：金融(银行、理财)、物流、游戏、医疗、重工业等。