前言

在一個陽光明媚的下午，電腦右下角傳來一片片郵件提醒，同時伴隨著微信釘釘的震動，打開一看，應用各種出錯，天兔告警，數據庫服務器內存爆紅，Mysql數據庫實例掛掉了。

排查

先交代一下數據庫版本：

崩潰故障排除絕不是一項有趣的任務，特別是如果MySQL沒有報告崩潰的原因。例如，當MySQL內存不足時。

數據庫郵件告警提醒發來的消息：

登錄 Grafana 監控面板，數據庫連接在哪個時間段曾有幅度的增長。

順手檢查一下之前的服務器郵件監控告警記錄，上一個時間點，內存佔用率99%，這說明了數據庫連接的幅度增長，可能是壓垮服務器的最後一根稻草。

其實導致OOM的直接原因並不複雜，就是因為服務器內存不足，內核需要回收內存，回收內存就是kill掉服務器上使用內存最多的程序，而MySQL服務可能就是使用內存最多，所以就OOM了。

查看系統日誌

我們帶著這個疑問來排查一下日誌：

小夥伴們繼續往下看：

當out of memory發生時，out_of_memory函數會選擇一個內核認為犯有分配過多內存 “罪行”的進程，並殺死該進程。顯然 Mysql 就是哪個“罪人”。

隨後 MySql 會自動重啟。重啟以後，內存是下來了，但是臨近下班的時候，差不多又又又佔滿了。

找到MySql進程，執行以下top -p pid，內存使用52.4g

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
935 mysql 20 0 79.7g 52.4g 7336 S 0.3 96.1 255:44.76 mysqld

計算內存使用

1)查看MySQL全局佔用多少內存

SELECT (@@innodb_buffer_pool_size
+@@innodb_log_buffer_size
+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;

查詢結果為：

+----------------+
| MEMORY_MB |
+----------------+
| 20512.00000000 |
+----------------+

2）查看performance_schema佔用多少內存

SELECT SUBSTRING_INDEX(event_name,'/',2) AS
 code_area, sys.format_bytes(SUM(current_alloc))
 AS current_alloc
 FROM sys.x$memory_global_by_current_bytes
 GROUP BY SUBSTRING_INDEX(event_name,'/',2)
 ORDER BY SUM(current_alloc) DESC;

查詢結果為：

+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/performance_schema | 349.80 MiB |
+---------------------------+---------------+

3）查看每個線程佔用多少內存

SELECT ( ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@max_allowed_packet
+ @@net_buffer_length )
) / (1024*1024) AS MEMORY_MB;

查詢結果為：

+-----------+
| MEMORY_MB |
+-----------+
| 87.5156 |
+-----------+

查看當前線程

show full processlist

最終結果為：

+-----------+
| MEMORY_MB |
+-----------+
| 87.5156*37|
+-----------+

4）查看 memory 存儲引擎佔用多少內存

SELECT SUM(max_data_length)/1024/1024 AS MEMORY_MB FROM information_schema.tables WHERE ENGINE='memory';

查詢結果為：

+---------------+
| MEMORY_MB |
+---------------+
| 3857.37713909 |
+---------------+

以上四項加起來差不多也就27975MB，差不錯28G的樣子，但是 MySql 進程顯示佔用了52.4G，那麼剩下24.4G去哪了？

線程池

此線程池非彼連接池，其實兩者是有很大區別的，連接池一般在客戶端設置，而線程池是在DB服務器上配置；另外連接池可以取到避免了連接頻繁創建和銷燬，但是無法取到控制MySQL活動線程數的目標，在高併發場景下，無法取到保護DB的作用。比較好的方式是將連接池和線程池結合起來使用。

關於線程池的一些參數：

thread_handling：

該參數是配置線程模型，默認情況是one-thread-per-connection，也就是不啟用線程池。將該參數設置為pool-of-threads即啟用了線程池。

thread_pool_size：

該參數是設置線程池的Group的數量，默認為系統CPU的個數，充分利用CPU資源。

thread_pool_oversubscribe：

該參數設置group中的最大線程數，每個group的最大線程數為thread_pool_oversubscribe+1，注意listener線程不包含在內。

thread_pool_high_prio_mode：

高優先級隊列的控制參數，有三個值（transactions/statements/none），默認是transactions，三個值的含義如下：

• transactions：對於已經啟動事務的語句放到高優先級隊列中，不過還取決於後面的thread_pool_high_prio_tickets參數
• statements：這個模式所有的語句都會放到高優先級隊列中，不會使用到低優先級隊列
• none：這個模式不使用高優先級隊列

thread_pool_high_prio_tickets：

該參數控制每個連接最多語序多少次被放入高優先級隊列中，默認為4294967295，注意這個參數只有在thread_pool_high_prio_mode為transactions的時候才有效果。

thread_pool_idle_timeout：

worker線程最大空閒時間，默認為60秒，超過限制後會退出。

thread_pool_max_threads：

該參數用來限制線程池最大的線程數，超過該限制後將無法再創建更多的線程，默認為100000。

thread_pool_stall_limit：

該參數設置timer線程的檢測group是否異常的時間間隔，默認為500ms。

最終配置如下：

#thread pool
thread_handling=pool-of-threads
#Group的數量，默認為系統CPU的個數，充分利用CPU資源
thread_pool_size=24
#每個group的最大線程數為thread_pool_oversubscribe+1
thread_pool_oversubscribe=3
performance_schema=off
#extra connection，防止線程池滿的情況下無法登錄MySQL
extra_max_connections = 8
extra_port = 33333

備註：線程池在Percona,MariaDB,Oracle MySQL企業版中提供，Oracle MySQL社區版並不提供。

線程池貌似並不會直接導致內存不回收，網上有說同時開啟Thread pool和PS會出現內存洩露，但是
目前Percona server 5.7.21-20+版本已經修復了這個問題，顯然是不存在的。

慢查詢

由於是生產環境，這個問題拖得時間有點長，那麼慢查詢會不會影響內存使用問題呢？帶著這個問題，查看了慢查詢後臺列表，在數據庫奔潰的前一個時間段，的確有不少慢查詢語句。但是這並不能在一定程度上說明問題，由於服務器的 MySql 服務在殺死之前，內存已經見底，此時連接數並不多，也就三四十來個左右，大多處於休眠狀態，並且此時已經佔用了大部分的Swap空間。也就是說，在資源有限的情況下必定會出現不少慢查詢語句。

小結

其實這個"意外"一點也不意外，其實已經發生了多次了。但是還是做個小結吧，因為最終沒有確認問題出現在哪裡，所以還是發佈了吧，萬一有專業的DBA遇到類似的問題還可以小小的解惑一下。