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

寻技术 Mysql 2023年07月11日 1614

一、内存使用率高且不释放问题排查

生产环境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.配置读写分离,将读操作应用到从库,减少对主库的影响;

关闭

用微信“扫一扫”