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.配置讀寫分離，將讀操作應用到從庫，減少對主庫的影響；