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

数据库 MySQL
MySQL内存使用率过高,有诸多原因。普遍原因是使用不当,还有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观察内存变化:

  1. mysql> SELECT @@query_cache_size, 
  2.      @@key_buffer_size, 
  3.      @@innodb_buffer_pool_size , 
  4.      @@innodb_log_buffer_size , 
  5.      @@tmp_table_size , 
  6.      @@read_buffer_size, 
  7.      @@sort_buffer_size, 
  8.      @@join_buffer_size , 
  9.      @@read_rnd_buffer_size, 
  10.      @@binlog_cache_size, 
  11.      @@thread_stack, 
  12.      (SELECT COUNT(host) FROM  information_schema.processlist where command<>'Sleep')\G; 
  13. *************************** 1. row *************************** 
  14. @@query_cache_size:1048576 
  15. @@key_buffer_size:8388608 
  16. @@innodb_buffer_pool_size:268435456 
  17. @@innodb_log_buffer_size:8388608 
  18. @@tmp_table_size:16777216 
  19. @@read_buffer_size:131072 
  20. @@sort_buffer_size:1048576 
  21. @@join_buffer_size:1048576 
  22. @@read_rnd_buffer_size:2097152 
  23. @@binlog_cache_size:8388608 
  24. @@thread_stack:524288 
  25. (select count(host) from information_schema.processlist where command<>'Sleep'): 1 

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

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

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

  • 存储过程&函数

MySQL 5.7

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

MySQL 8.0

  1. mysql> SELECT  Routine_schema, Routine_type 
  2. FROM information_schema.Routines 
  3. WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys'
  4. GROUP BY Routine_schema, Routine_type; 
  • 视图
  1. mysql> SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME)  
  2. FROM information_schema.VIEWS 
  3. WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys'
  4. GROUP BY TABLE_SCHEMA ; 
  • 触发器
  1. mysql> SELECT TRIGGER_SCHEMA, count(*)  
  2.  FROM information_schema.triggers  
  3. WHERE  TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys'
  4. GROUP BY TRIGGER_SCHEMA; 

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

3.系统库统计查询

  • 总内存使用
  1. mysql> SELECT  
  2. SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )  
  3. FROM sys.memory_global_by_current_bytes 
  4. WHERE current_alloc like '%MiB%'
  • 分事件统计内存
  1. mysql> SELECT event_name,     
  2. SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )     
  3. FROM sys.memory_global_by_current_bytes     
  4. WHERE current_alloc like '%MiB%' GROUP BY event_name   
  5.      ORDER BY SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  ) DESC ; 
  6.  
  7. mysql> SELECT event_name, 
  8.        sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) 
  9. FROM performance_schema.memory_summary_global_by_event_name 
  10. ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
  11. LIMIT 10; 
  • 账号级别统计
  1. mysql> SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED 
  2. FROM performance_schema.memory_summary_by_account_by_event_name 
  3. WHERE host<>"localhost" 
  4. ORDER BY  current_number_of_bytes_used DESC LIMIT 10; 

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

  • 线程对应sql语句,内存使用统计
  1. SELECT thread_id, 
  2.        event_name, 
  3.        sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)  
  4. FROM performance_schema.memory_summary_by_thread_by_event_name 
  5. ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC 
  6. LIMIT 20; 
  1. SELECT m.thread_id tid, 
  2.        m.user
  3.        esc.DIGEST_TEXT, 
  4.        m.current_allocated, 
  5.        m.total_allocated 
  6. FROM sys.memory_by_thread_by_current_bytes m, 
  7.      performance_schema.events_statements_current esc 
  8. WHERE m.`thread_id` = esc.THREAD_ID \G 
  • 打开所有内存性能监控,会影响性能,需注意
  1. #打开 
  2. UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%'
  3. #关闭 
  4. UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%'
  5. #查看使用 
  6. SELECT * FROM performance_schema.memory_summary_global_by_event_name         
  7. WHERE EVENT_NAME LIKE 'memory/%'  
  8. ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
  • 系统表内存监控信息
  1. select * from sys.x$memory_by_host_by_current_bytes; 
  2. select * from sys.x$memory_by_thread_by_current_bytes; 
  3. select * from sys.x$memory_by_user_by_current_bytes; 
  4. select * from sys.x$memory_global_by_current_bytes; 
  5. select * from sys.x$memory_global_total; 
  6. select * from performance_schema.memory_summary_by_account_by_event_name; 
  7. select * from performance_schema.memory_summary_by_host_by_event_name; 
  8. select * from performance_schema.memory_summary_by_thread_by_event_name; 
  9. select * from performance_schema.memory_summary_by_user_by_event_name; 
  10. 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相关进程使用内存情况。

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

4)pmap 命令

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

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

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

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

  1. 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等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。

 

责任编辑:武晓燕 来源: 数据和云
相关推荐

2011-07-13 09:13:56

Android设计

2021-04-19 17:25:08

Kubernetes组件网络

2009-06-18 09:51:25

Java继承

2013-04-07 10:40:55

前端框架前端

2011-07-27 15:01:48

MySQL数据库内存表

2023-01-04 18:32:31

线上服务代码

2018-11-15 08:19:47

大流量高并发限流

2015-08-24 09:26:18

Java内存问题见解

2012-09-25 10:03:56

JavaJava封面Java开发

2011-07-29 09:33:21

iPhone 设计

2011-03-11 09:27:11

Java性能监控

2017-12-21 07:54:07

2012-04-19 10:06:55

微软Windows 8 E

2009-06-04 16:28:43

EJB常见问题

2015-12-04 10:04:53

2020-09-28 06:45:42

故障复盘修复

2022-04-14 10:22:44

故事卡业务

2020-05-19 14:35:42

Shell脚本循环

2020-04-10 08:50:37

Shell脚本循环

2017-09-20 15:07:32

数据库SQL注入技巧分享
点赞
收藏

51CTO技术栈公众号