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.配置读写分离，将读操作应用到从库，减少对主库的影响；

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/235541.html

MySQL内存使用率高且不释放问题排查与总结

相关推荐

分享到: