* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。

老叶提醒:本文结合 MySQL内存为什么不断增高,怎么让它释放 阅读,收获更多。

  • 1.问题开端

  • 2.问题分析

  • 3.原因及意见

1.问题开端

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

2.问题分析

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

    d0214816fe95dd1eb90f6d957d678583.png

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

    acf30de78402716790d07062e1e5cc40.png

  • 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内存:

    50222338cba1f012cf4e5cad02b7e65b.png

  • 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内存:

    d0ae7ef5b0ae44706a46550d8eea37b0.png

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

    e38cbb5657332b48002a5cf96db794a6.png

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

    fa2b293efe9f4fadadba8e7519061eea.png

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

    59dbfc1b4607a03ef7defd0ad48f25d4.png

  • 由上图可知,此表是有索引的,delete涉及的查询列也有一个联合索引,但在联合索引中created_at列并不是前缀,因此SQL语句并未用到此联合索引,所以会有大量的临时表,并消耗一定128M*使用次数内存,在使用完成后内存回收时可能会产生碎片d76ec2967cb6efab731e60a13393a875.png

  • 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.制定相应的规范,加大测试审核要求。

Enjoy GreatSQL :)


《深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0



文章推荐:


想看更多技术好文,点个“在看”吧!

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐