2.关于一则Mysql的内存消耗问题

老司机开车@如何拯救沦陷的MySQL内存少女

• 1.问题开端

• 2.问题分析

• 3.原因及意见

1.问题开端

工程师反馈数据库服务器内存使用率高，并且之前曾触发告警，登录服务器使用top -u mysql查看进程使用内存信息：发现mysqld进程使用内存达到了61.5%，并有缓慢增长趋势。但是数据库的innodb_buffer_pool_size设置是6G,与9.2g还是有一些差距。

2.问题分析

• 1.查看MySQL进程相关信息，再次确认使用的物理内存大小通过cat /proc/21680/status查看MySQL进程id的实际内存使用情况，发现当前MySQL进程使用的物理内存为9.13g：

  

• 2.确认数据库的全局buffer设置大小，这里以当前运行的配置为准，因为可能有在线修改过数据库配置，但未更新到配置文件中，所以要以数据库当前值为准，由此发现innodb_buffer_pool_size设置就是6g，innodb_log_buffer_size为64M,key_buffer_size为128M：

  

• 3.查看数据库中performance_schema配置信息，分析当前为线程和用户分配的内存信息，查询sys下与memory相关的视图,查询sys库的相关表报错，经过沟通，由于sys库在MySQL导入备份时损坏，无法查询sys库下的相关视图:
• memory_by_host_by_current_bytes
• memory_by_thread_by_current_bytes
• memory_by_user_by_current_bytes
• memory_global_by_current_bytes
• 4.排查performance schema本身占用的内存大小,使用show engine performance_schema status;命令查看，发现performance schema本身占用了161M内存：

  

• 5.排查MySQL为当前session会话分配的内存,查看session级别的buffer和cache占用内存大小,从这里我们可以看出，MySQL为每个session分配的buffer为12M，通过show processlist命令查看到当前有155个会话连接，那么MySQL为每个session一共分配了1860M。到这里发现innodb_buffer_pool_size设置为6g，performance schema使用了161M，session会话使用了1860M，总共7.9736g，距离9.2g还相差1.2g内存：

  

• 6.排查当前临时表占用内存情况,发现tmp_table_size分配了128M，连续执行两次show global status like ‘Created_tmp%’命令查看当前是否有临时表产生，发现频繁使用了临时表，并且出现了因内存临时表不够而使用到磁盘临时表:

  

• 7.排查频繁使用临时表情况,通过慢日志发现有大量的delete语句，并且执行时间最长的达51s，同时也有不走缓存的查全表的慢sql:

  

• 分析此慢SQL涉及的表是否有索引，此SQL语句是否走索引

  

• 有上图可知，此表是有索引的，delete涉及的查询列也有一个联合索引，但在联合索引中created_at列并不是前缀，因此SQL语句并未用到此联合索引，所以会有大量的临时表，并消耗一定128M*使用次数内存，在使用完成后内存回收时可能会产生碎片
  
  
• 8.通过pmap -x <msyql pid>查看进程使用内存的详细信息,通过排查，发现有大量的anon（匿名内存块）并通过pstack进行分析，但因其他原因，此处暂时无法提供pmap信息截图:

#0  0x00007f322e3a1e9d in poll () from /lib64/libc.so.6
#0  0x00007f322f8f6945 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#0  0x00007f322f8f6945 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#0  0x00007f322f8f6945 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#0  0x00007f322f8f6945 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#0  0x00007f322e3a1e9d in poll () from /lib64/libc.so.6

• 从pstack堆栈信息发现mysqld的现场有大量的状态是线程空闲状态，并且函数都在glibc上,所以从这里怀疑剩余使用的内存是否是因为glibc导致的内存碎片问题，查看MySQL相关的bug，确实存在相关的问题，但是这些问题不是MySQL本身的bug，因为对于MySQL来说，它只是调用系统函数去分配内存：

• 参考：https://bugs.mysql.com/bug.php?id=94647

• 9.通过商讨处理方式，最后决定确认是否是此原因导致导致占用剩下的1.2g内存,然后使用gdb命令手动回收内存碎片，把内存还给系统:gdb --batch --pid 21680 --ex 'call malloc_trim(0)'命令执行完成之后发现mysqld的内存使用率由61.5%降至58.5% ，从这里验证了因为glibc本身的缺陷导致内存没有释放完全。

3.原因及意见

本次内存缓慢增长属于正常现象，但存在因为一些慢sql频繁执行并因MySQL的内存分配使用了系统glibc，而glibc本身的内存分配算法存在缺陷，导致内存释放不完全。

处理意见：

• 1.增加物理内存。
• 2.修改MySQL的内存分配方式，不使用glibc，使用JEMALLOC内存管理，来规避此问题的再次发生。
• 3.数据库的session 参数设置的比较大，并且有些参数设置的不合理，比如read_rnd_buffer_size，建议修改数据库的参数：read_rnd_buffer_size = 2M
• 4.完善监控系统，监控目前不完善，没有对慢SQL进行监控。
• 5.制定相应的规范，加大测试审核要求。

转载于：https://mp.weixin.qq.com/s/SVIltbk1Pvmj_CeLraKpvQ