MySQL内存使用率高且不释放问题排查与总结
一、内存使用率高且不释放问题排查
生产环境MySQL 5.7数据库告警内存使用率95%。排查MySQL内存占用问题的思路方法可以参考叶老师这篇文章：https://mp.weixin.qq.com/s/VneUUnprxzRGAyQNaKi-7g 。TOP命令查看MySQL进程的RES指标，发现内存使用了10.6G，而数据库的innodb_buffer_pool_size只是设置了6G，所以这多出来的4个G的内存用在哪了呢？考虑到innodb_log_buffer_size、read_rnd_buffer_size等buffer的使用，performance_schema占用的内存，MySQL为每个session分配的内存为12M，估算一下这些内存的加起来使用不到2个G。那还有两个G的内存用在哪了呢？参数tmp_table_size设置为64M，连续执行两次show global status like ‘%tmp%’，发现数据库创建了大量的临时表，并且仍在频繁的创建。此外还出现了大量因内存临时表不够而使用到磁盘临时表的现象。由于该数据库版本是5.7，performance_schema功能尚不完善，在8.0版本中，可以通过memory_summary_global_by_event_name监控表排查内存使用的情况。
mysql> show global status like '%tmp%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Created_tmp_disk_tables | 28710340 | | Created_tmp_files | 1238018 | | Created_tmp_tables | 470261777 | +-------------------------+-----------+ 3 rows in set (0.00 sec) mysql> show global status like '%tmp%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Created_tmp_disk_tables | 28710340 | | Created_tmp_files | 1238018 | | Created_tmp_tables | 470261780 | +-------------------------+-----------+ 3 rows in set (0.00 sec) mysql> show global variables like '%tmp_table%'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | max_tmp_tables | 32 | | tmp_table_size | 67108864 | +----------------+----------+ 2 rows in set (0.01 sec)
排查MySQL慢日志可以发现，执行频率较高的SQL几乎都包含了子查询，这会产生大量的临时表。每次使用临时表都要消耗64M的内存空间，虽然MySQL有内存回收机制每次使用完内存临时表后会释放这部分内存空间，但MySQL的内存分配使用了系统glibc，而glibc本身的内存分配算法存在缺陷，导致内存释放不完全，产生内存碎片。可以通过gdb命令手动回收内存碎片：gdb --batch --pid ‘pidof mysqld’ --ex 'call malloc_trim(0)'，但是在生产环境这个操作应该谨慎使用。此外，将MySQL的内存分配机制修改为jemalloc，可以更好的释放内存。关于glibc和jemalloc机制对MySQL数据库内存回收的影响可以参考这篇文章：https://mp.weixin.qq.com/s/iUvi0xPtKng08fNu_5VWDg。
二、Linux内存分配机制
关于Linux的内存分配管理模块，主要有三种：
1. ptmalloc(glibc的malloc)是Linux提供的内存分配管理模块，MySQL默认使用系统的内存分配模块；
2. tcmalloc是Google提供的内存分配管理模块；
3. jemalloc是FreeBSD提供的内存分配管理模块；
Mariadb, Redis都使用了jemalloc内存管理模块。对于MySQL来说要使用jemalloc，首先需要在操作系统中安装jemalloc，然后再MySQL启动时配置环境变量：export LD_PRELOAD=/usr/lib64/libjemalloc.so。此外在my.cnf参数文件中配置[mysqld_safe]:malloc-lib=/usr/lib64/libjemalloc.so也可以达到此效果，具体可以参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/mysqld-safe.html#option_mysqld_safe_malloc-lib 。可以通过以下命令确认mysql进程是否使用了jemalloc：
lsof -p `pidof mysqld` | grep -i jemalloc
三、问题总结
总结一下MySQL内存使用率高且不释放的应对方法：
1.扩内存，有钱任性；
2.在停机窗口重启数据库；
3.在线修改减小innodb_buffer_pool_size参数（牺牲一定innodb性能）；
4.排查消耗内存的慢SQL，及时优化；
5.检查相关buffer的session参数是否设置合理，比如read_rnd_buffer_size是否设置过大；
6.使用gdb回收内存碎片：gdb --batch --pid ‘pidof mysqld’ --ex 'call malloc_trim(0)'；
7.对MySQL进程配置jemalloc内存管理模块；
8.配置读写分离，将读操作应用到从库，减少对主库的影响；