老司机开车@如何拯救沦陷的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.制定相应的规范，加大测试审核要求。

本文转载公众号GreatSQL社区