關于MySQL內存泄露如何排查的一些思路
本文轉載自微信公眾號「數據和云」，作者崔虎龍 。轉載本文請聯系數據和云公眾號。
MySQL使用內存上升90%!在運維過程中50%的幾率，會碰到這樣的問題。算是比較普遍的現象。
MySQL內存使用率過高，有諸多原因。普遍原因是使用不當，還有MySQL本身缺陷導致的。到底是哪方面的問題，那就需要一個一個進行排查。
下面介紹排查思路：
1.參數配置需要確認，內存是否設置合理
MySQL內存分為全局和線程級：
- 全局內存(如：innodb_buffer_pool_size，key_buffer_size，innodb_log_buffer_size)。
- 線程級內存：(如：thread，read，sort，join,tmp 等)只是在需要的時候才分配，并且在操作完畢之后就釋放。
- 線程級內存：線程緩存每個連接到MySQL服務器的線程都需要有自己的緩沖。默認分配thread_stack(256K,512k)，空閑時這些內存是默認使用，除此之外還有網絡緩存、表緩存等。大致評估會在1M~3M這樣的情況。可通過pmap觀察內存變化：
- mysql> SELECT @@query_cache_size,
- @@key_buffer_size,
- @@innodb_buffer_pool_size ,
- @@innodb_log_buffer_size ,
- @@tmp_table_size ,
- @@read_buffer_size,
- @@sort_buffer_size,
- @@join_buffer_size ,
- @@read_rnd_buffer_size,
- @@binlog_cache_size,
- @@thread_stack,
- (SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep')\G;
- *************************** 1. row ***************************
- @@query_cache_size:1048576
- @@key_buffer_size:8388608
- @@innodb_buffer_pool_size:268435456
- @@innodb_log_buffer_size:8388608
- @@tmp_table_size:16777216
- @@read_buffer_size:131072
- @@sort_buffer_size:1048576
- @@join_buffer_size:1048576
- @@read_rnd_buffer_size:2097152
- @@binlog_cache_size:8388608
- @@thread_stack:524288
- (select count(host) from information_schema.processlist where command<>'Sleep'): 1
備注：query_cache_size 8.0版本已經廢棄掉了。
2.存儲過程&函數&觸發器&視圖
目前積累的使用經驗中，存儲過程&函數&觸發器&視圖 在MySQL場景下是不適合的。性能不好，又容易發現內存不釋放的問題，所以建議盡量避免。
- 存儲過程&函數
MySQL 5.7
- mysql> SELECT db,type,count(*)
- FROM mysql.proc
- WHERE db not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY db, type;
MySQL 8.0
- mysql> SELECT Routine_schema, Routine_type
- FROM information_schema.Routines
- WHERE Routine_schema not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY Routine_schema, Routine_type;
- 視圖
- mysql> SELECT TABLE_SCHEMA , COUNT(TABLE_NAME)
- FROM information_schema.VIEWS
- WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY TABLE_SCHEMA ;
- 觸發器
- mysql> SELECT TRIGGER_SCHEMA, count(*)
- FROM information_schema.triggers
- WHERE TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
- GROUP BY TRIGGER_SCHEMA;
上面通過MySQL配置參數和設計層面檢查了是否有可能內存泄露的問題。下面看看怎樣分析實際使用的內存情況。
3.系統庫統計查詢
- 總內存使用
- mysql> SELECT
- SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
- FROM sys.memory_global_by_current_bytes
- WHERE current_alloc like '%MiB%';
- 分事件統計內存
- mysql> SELECT event_name,
- SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
- FROM sys.memory_global_by_current_bytes
- WHERE current_alloc like '%MiB%' GROUP BY event_name
- ORDER BY SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) ) DESC ;
- mysql> SELECT event_name,
- sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
- FROM performance_schema.memory_summary_global_by_event_name
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
- LIMIT 10;
- 賬號級別統計
- mysql> SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED
- FROM performance_schema.memory_summary_by_account_by_event_name
- WHERE host<>"localhost"
- ORDER BY current_number_of_bytes_used DESC LIMIT 10;
備注：有必要統計用戶級別內存，因為很多環境對接了第三方插件，模擬從庫，這些插件容易內存不釋放。
- 線程對應sql語句，內存使用統計
- SELECT thread_id,
- event_name,
- sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
- FROM performance_schema.memory_summary_by_thread_by_event_name
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
- LIMIT 20;
- SELECT m.thread_id tid,
- m.user,
- esc.DIGEST_TEXT,
- m.current_allocated,
- m.total_allocated
- FROM sys.memory_by_thread_by_current_bytes m,
- performance_schema.events_statements_current esc
- WHERE m.`thread_id` = esc.THREAD_ID \G
- 打開所有內存性能監控，會影響性能，需注意
- #打開
- UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
- #關閉
- UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';
- #查看使用
- SELECT * FROM performance_schema.memory_summary_global_by_event_name
- WHERE EVENT_NAME LIKE 'memory/%'
- ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;
- 系統表內存監控信息
- select * from sys.x$memory_by_host_by_current_bytes;
- select * from sys.x$memory_by_thread_by_current_bytes;
- select * from sys.x$memory_by_user_by_current_bytes;
- select * from sys.x$memory_global_by_current_bytes;
- select * from sys.x$memory_global_total;
- select * from performance_schema.memory_summary_by_account_by_event_name;
- select * from performance_schema.memory_summary_by_host_by_event_name;
- select * from performance_schema.memory_summary_by_thread_by_event_name;
- select * from performance_schema.memory_summary_by_user_by_event_name;
- select * from performance_schema.memory_summary_global_by_event_name;
備注：找到對應問題事件或線程后，可以進行排查，解決內存高的問題。
4.系統工具查看內存
1)top命令
顯示系統中各個進程的資源占用狀況。
- Shift + m 鍵 查看內存排名實際使用內存情況，關注RES指標。
2)free命令
free-h 命令顯示系統內存的使用情況，包括物理內存、交換內存(swap)和內核緩沖區內存。
- used列顯示已經被使用的物理內存和交換空間。
- buff/cache列顯示被buffer和cache使用的物理內存大小。
- available列顯示還可以被應用程序使用的物理內存大小。
- Swap行(第三行)是交換空間的使用情況。
3)ps命令
MySQL相關進程使用內存情況。
- shell > ps eo user,pid,vsz,rss $(pgrep -f 'mysqld')
- USER PID VSZ RSS
- root 215945 12960 2356
- mysql 217246 1291540 241824
- root 221056 12960 2428
- mysql 374243 1336924 408752
4)pmap 命令
pmap是Linux調試及運維一個很好的工具，查看進程的內存映像信息。
用法1：執行一段時間記錄數據變化，最少20個記錄，下面22837是MySQL pid
- while true; do pmap -d 22837 | tail -1; sleep 2; done
用法2：linux 命令pmap MySQL pid導出內存，下面22837是MySQL pid
- pmap -X -p 22837 > /tmp/memmysql.txt
RSS就是這個process實際占用的物理內存。
Dirty: 臟頁的字節數(包括共享和私有的)。
Mapping: 占用內存的文件、或[anon](分配的內存)、或[stack](堆棧)。
writeable/private：進程所占用的私有地址空間大小，也就是該進程實際使用的內存大小。
1.首先使用/top/free/ps在系統級確定是否有內存泄露。如有，可以從top輸出確定哪一個process。
2.pmap工具是能幫助確定process是否有memory leak。確定memory leak的原則：writeable/private (‘pmap –d’輸出)如果在做重復的操作過程中一直保持穩定增長，那么一定有內存泄露。
總結
對于MySQL內存泄露來說:
- 從參數設置和設計上盡量合理
- 通過ps庫進行排查
- linux工具進一步確認
- 官方bug里memory leak查找，是否存在修復的版本
以上排查里都沒有找到原因，可以換下服務器或主從切換觀察。也可以進行版本升級(代價不小)。
如能提供一個實際環境，也可以一步一步進行調試，抓取內存變化，確定是什么導致內存泄露的問題。之后提交bug，讓官方提供修復。
關于作者
崔虎龍，云和恩墨MySQL技術顧問，長期服務于金融、游戲、物流等行業的數據中心，設計數據存儲架構，并熟悉數據中心運營管理的流程及規范，自動化運維等。擅長MySQL、Redis、MongoDB數據庫高可用設計和運維故障處理、備份恢復、升級遷移、性能優化。自學通過了MySQL OCP 5.6和MySQL OCP 5.7認證。2年多開發經驗，10年數據庫運維工作經驗，其中專職做MySQL工作8年;曾經擔任過項目經理、數據庫經理、數據倉庫架構師、MySQL技術專家、DBA等職務;涉及行業：金融(銀行、理財)、物流、游戲、醫療、重工業等。
