故障排除指南：MySQL執行記憶體不足怎麼辦？

tianxiaoxu發表於2018-07-23
MySql記憶體

故障排除對於所有人來說都不會是一件有趣的事情，尤其是在沒有崩潰報告的情況下。如果MySQL因記憶體不足而崩潰時應該怎麼辦？Peter Zaitsev曾在2012年寫過的一篇部落格中給出了許多有用的提示，而利用新版本的MySQL（5.7及以上）和performance_schema，我們可以更輕鬆地解決MySQL記憶體分配問題。

本文將向大家介紹如何解決MySQL記憶體分配問題。

首先，我們先來看一下在哪些情況下MySQL會因記憶體不足而崩潰：

  • 當MySQL嘗試分配比可用記憶體更多的記憶體時，例如沒有正確設定innodb_buffer_pool_size；

  • 伺服器上還有一些其他程式可以分配RAM，比如它應用程式（java，python，php）、Web伺服器，或者是備份（即mysqldump）。

  • MySQL中的記憶體洩漏。這是最糟糕的情況，我們需要進行故障排除。

以上，就是常見的三種MySQL因記憶體不足而崩潰的情況，其中前兩種情況比較好解決，而第三種情況就比較棘手。

從哪裡開始排除MySQL記憶體洩漏問題

假設這是一個Linux伺服器，首先我們要 檢查Linux作業系統和配置 ，

  1. 檢查mysql錯誤日誌和Linux日誌檔案（即/ var / log / messages或/ var / log / syslog）來識別崩潰。你可能會看到OOM Killer殺死MySQL的條目，可以使用“dmesg”來顯示相關的詳細資訊。

  2. 檢查可用的RAM： free -g    cat / proc / meminfo

  3. 檢查哪些應用程式正在使用RAM： “top”或“htop”

  4. 檢查mysql配置： /etc/my.cnf 或general /etc/my* (including /etc/mysql/*等檔案)，MySQL可能正在執行不同的my.cnf（run ps  ax | grep mysql  ）

  5. 執行 vmstat 5 5  以檢視系統是否正在通過虛擬記憶體進行讀/寫以及是否正在進行交換

  6. 對於非生產環境，我們可以使用其他工具（如Valgrind，gdb等）來檢查MySQL的使用情況.

檢查MySQL內部

我們也可以通過檢查MySQL內部來發現潛在的MySQL記憶體洩露。MySQL在很多地方都會有記憶體分配，尤其是在以下情況下：

現在我們可以檢查MySQL內部的東西來尋找潛在的MySQL記憶體洩漏。

MySQL在很多地方分配記憶體。特別：

  1. Table cache

  2. Performance_schema（執行： show engine performance_schema status ，並檢視最後一行）。

  3. InnoDB（執行 show engine innodb status   並檢查緩衝池部分，為buffer_pool和相關快取分配的記憶體）

  4. RAM中的臨時表（通過執行以下語句查詢所有記憶體表： select * from information_schema .tables where engine ='MEMORY'  ）

  5. Prepared statements。

不過，從MySQL 5.7版本開始，我們就可以在performance_schema中檢視記憶體分配。那麼，如何使用呢？

首先，我們需要啟用收集記憶體指標。Run： 

UPDATE setup_instruments SET ENABLED =   'YES'
WHERE NAME LIKE 'memory/%';

sys schema 執行 report ： 

select event_name, current_alloc, high_alloc
from sys.memory_global_by_current_bytes
where current_count > 0;

通常，分配記憶體時會提供程式碼，所以在某些情況下搜尋某些錯誤時，我們可能需要檢查 MySQL 原始碼。例如，對於在觸發器中過度分配記憶體的錯誤：

某些情況下搜尋某些錯誤時，我們可能需要檢查MySQL原始碼。例如，對於在觸發器中過度分配記憶體的錯誤： 

mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name                                                                     | current_alloc | high_alloc  |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool                                                     | 7.29 GiB      | 7.29 GiB    |
| memory/sql/sp_head::main_mem_root                                              | 3.21 GiB      | 3.62 GiB

RAM中最大的塊通常是緩衝池，但儲存過程中的3G似乎也太高了。

根據MySQL原始碼文件，SPHead表示儲存程式的一個例項，該程式可能是任何型別（儲存過程、函式、觸發器、事件）。在這種情況下，就會有潛在的記憶體洩漏。此外，如果我們想要更清楚的知道MySQL記憶體情況，還可以得到一個更高階別的總報告。 

mysql> select  substring_index(
    ->     substring_index(event_name, '/', 2),
    ->     '/',
    ->     -1
    ->   )  as event_type,
    ->   round(sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) as MB_CURRENTLY_USED
    -> from performance_schema.memory_summary_global_by_event_name
    -> group by event_type
    -> having MB_CURRENTLY_USED>0;
+--------------------+-------------------+
| event_type         | MB_CURRENTLY_USED |
+--------------------+-------------------+
| innodb             |              0.61 |
| memory             |              0.21 |
| performance_schema |            106.26 |
| sql                |              0.79 |
+--------------------+-------------------+
4 rows in set (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31137683/viewspace-2158344/，如需轉載，請註明出處，否則將追究法律責任。

相關文章