故障排除指南：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)