疑難排解 MySQL 記憶體問題

如果程序嘗試分配記憶體，但因伺服器沒有可用的記憶體而失敗，就會發生記憶體不足 (OOM) 的狀況。這通常會導致 Linux OOM 終止程序 (在系統的記憶體容量嚴重不足時，Linux 核心所採用的處理程序) 終止資料庫程序。

不適合因為 OOM 狀況而造成資料庫當機的情況，因為資料庫程序是使用 SIGKILL 指令終止，會讓應用程式沒有機會正常關閉 MySQL。這會導致以下問題：

• MySQL 伺服器停止運作 (完成當機復原程序可能需要幾分鐘時間) 會影響使用者和業務

• MySQL 伺服器需要額外的時間才能完成緩衝區和快取的暖機，以達到最佳效能
• 意外當機導致資料損毀的可能性

MySQL 從 5.7 版開始新增 performance_schema 的記憶體檢測作業，有助於瞭解 MySQL 的記憶體配置。本文將協助您透過 performance_schema 來解決 MySQL 記憶體問題。

每個 Cloud SQL 執行個體都是由在代管 Google Cloud 伺服器上運作的虛擬機器 (VM) 提供。每個 VM 都會操作資料庫程式，例如 MySQL 伺服器和提供支援服務 (例如記錄與監控) 的服務代理程式。Cloud SQL 會為作業系統 (OS) 和服務代理程式保留部分記憶體，並為資料庫程式 (例如 mysqld) 分配剩餘的記憶體。

下表顯示 mysqld 程序的記憶體配置，以及每個機器類型的預設 InnoDB 緩衝區集區設定。

如果執行個體擁有 10 GB 以上的 RAM，則 mysqld 程序僅限於使用 92% 的執行個體 RAM，而 InnoDB 緩衝區集區則是設定在 mysqld 程序可用記憶體範圍的 75-80% 範圍內。除了 InnoDB 緩衝區集區，MySQL 也會分配幾個緩衝區和快取，藉此提高效能。如要瞭解 MySQL 中的記憶體配置，請參閱這篇文章。

當 mysqld 記憶體用量成長超過 mysqld 程序的設定限制時，Linux OOM 終止程序會終止 mysqld 程序，因為該程序是執行個體中最大的記憶體消費者。

performance_schema 是一項 MySQL 功能，能夠以低階監控伺服器執行作業。MySQL 適用的 Cloud SQL 允許在 RAM 大小為 3 GB 以上的執行個體中，啟用 performance_schema。從 MySQL 8.0 版開始，至少有 15 GB RAM 的執行個體預設會啟用這項功能。

MySQL 5.6 和 5.7 版預設會停用 performance_schema，如要啟用，請使用資料庫旗標。

啟用 performance_schema 會導致效能負擔，且會影響記憶體。如要瞭解其記憶體配置，請參閱效能結構定義記憶體配置模型。

performance_schema 記憶體檢測功能可協助監控 MySQL 的記憶體用量。記憶體檢測的名稱格式為 memory/code_area/instrument_name，其中 code_area 是如 sql 或 Innodb 等值，而 instrument_name 則是檢測的詳細資料。

MySQL 5.7 預設會啟用部分的記憶體檢測，但並非全部。如要啟用這些檢測，請更新 performance_schema.setup_instruments 資料表的「已啟用」欄。

mysql> select version();

+-------------------+

| version()         |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |       70 |

| NO      |      306 |

+---------+----------+

資料集中的 2 列 (0.00 秒)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376  Changed: 306  Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      376 |

+---------+----------+

1 row in set (0.00 sec)

在執行階段啟用記憶體檢測，只會計算啟用檢測後建立的記憶體物件。因此，建議您等待伺服器收集足夠的資料來進行疑難排解。

MySQL 8.0 預設會啟用所有的記憶體檢測功能。

mysql> select version();

+---------------+

| version()     |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      490 |

+---------+----------+

1 row in set (0.00 sec)

Sys 結構定義包含許多檢視畫面，會將 performance_schema 資料彙整成易於理解的形式。這些檢視畫面大多是成對的。兩者的名稱相同，其中一個的前置字串為 x$。MySQL 5.7 和 8.0 提供下列檢視畫面，方便您查看不同層級的記憶體配置。

mysql> use sys;

資料庫已變更

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)            |

+-------------------------------------+

| memory_by_host_by_current_bytes     |

| memory_by_thread_by_current_bytes   |

| memory_by_user_by_current_bytes     |

| memory_global_by_current_bytes      |

| memory_global_total                 |

| x$memory_by_host_by_current_bytes   |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes   |

| x$memory_global_by_current_bytes    |

| x$memory_global_total               |

+-------------------------------------+

資料集中的 10 列 (0.02 秒)

sys.memory_global_total 檢視畫面匯總了記憶體總用量，並採用可讀的格式。

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB       |

+-----------------+

資料集中的 1 列 (0.44 秒)

sys.x$memory_global_total 會顯示相同的資訊，但以位元組為單位。

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|     13351482992 |

+-----------------+

資料集中的 1 列 (1.39 秒)

沒有 x$ 前置字串的檢視畫面旨在提供更容易使用且更容易閱讀的輸出內容。帶有 x$ 前置字串的檢視畫面是以原始格式顯示相同的值，旨在與其他自行處理資料的工具搭配使用。

sys.memory_global_by_current_bytes 檢視畫面會顯示目前的記憶體用量，並按照分配類型 (event_name) 分組，例如：memory/innodb/buf_buf_pool (InnoDB 緩衝區集區)。根據預設，資料列會按照記憶體的使用量遞減排序。

在深入探討各種分配類型前，建議您先概略瞭解 Innodb、sql、performance_schema 等各代碼區的記憶體用量。

下列查詢會根據代碼區匯總目前已分配的記憶體。

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

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/innodb             | 11.28 GiB     |

| memory/performance_schema | 520.02 MiB    |

| memory/temptable          | 514.00 MiB    |

| memory/sql                | 106.12 MiB    |

| memory/mysys              | 43.25 MiB     |

| memory/vio                | 372.15 KiB    |

| memory/myisam             |  696 bytes    |

| memory/csv                |   88 bytes    |

| memory/blackhole          |   88 bytes    |

+---------------------------+---------------+

資料集中的 9 列 (1.02 秒)

這個輸出內容顯示 innodb 是最大規模的記憶體取用端，其次是 performance_schema 和臨時資料表。

現在，直接查詢 sys.memory_global_by_current_bytes 時，會以 event_name 欄說明，顯示 MySQL 內部結構的記憶體用量。如果您要解決記憶體的異常增加問題，請查看此檢視畫面的輸出結果，找出來源。

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name                    | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool    | 10.72 GiB     | 10.72 GiB  |

| memory/temptable/physical_ram | 514.00 MiB    | 515.00 MiB |

| memory/innodb/hash0hash       | 292.69 MiB    | 292.69 MiB |

| memory/innodb/memory          | 191.77 MiB    | 197.94 MiB |

| memory/sql/TABLE              | 60.39 MiB     | 62.35 MiB  |

+-------------------------------+---------------+------------+

資料集中的 5 列 (0.45 秒)

以下是各種分配類型的詳細資料。

這個檢視畫面會匯總依使用者分組的記憶體用量，若某位使用者有多個連線，則會匯總該使用者所有執行個體的記憶體用量。

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60.30 MiB         |

| background      | 2.68 MiB          |

| root            | 1.52 MiB          |

| event_scheduler | 16.38 KiB         |

+-----------------+-------------------+

資料集中的 4 列 (1.16 秒)

如果您為每個應用程式/工作使用不同的使用者 (建議做法)，這個檢視畫面可協助您判斷特定應用程式或工作是否耗用大量記憶體。

這個檢視畫面會摘要列出依主機名稱分類的記憶體用量。

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host       | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB         |

| background | 2.70 MiB          |

| 127.0.0.1  | 311.01 KiB        |

| localhost  | 16.38 KiB         |

+------------+-------------------+

資料集中的 4 列 (1.58 秒)

這個檢視畫面有助於確認每個用戶端主機的記憶體用量。現今擁有多個用戶端/應用程式伺服器的情況非常常見，這種檢視方式可協助您確認任何特定用戶端或工作負載是否造成高記憶體用量。您也可以先在某個用戶端/應用程式伺服器上部署變更，監控其對記憶體的影響 (比較其他用戶端主機的記憶體用量)，然後做出明智的決策。

這個檢視畫面彙整了記憶體用量 (依執行緒分組)，可協助您找出佔用最多記憶體的執行緒。

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user              | current_allocated |

+-----------+-------------------+-------------------+

|     12999 | sbtest@10.128.0.8 | 3.80 MiB          |

|     12888 | sbtest@10.128.0.8 | 3.75 MiB          |

|     12760 | sbtest@10.128.0.8 | 3.68 MiB          |

|     13128 | sbtest@10.128.0.8 | 3.67 MiB          |

|     13221 | sbtest@10.128.0.8 | 3.47 MiB          |

+-----------+-------------------+-------------------+

資料集中的 5 列 (2.29 秒)

如果您發現特定執行緒耗用了大量記憶體，則可使用 thread_id 篩選 MySQL 慢速查詢記錄或一般記錄，並找出導致記憶體用量增加的工作階段/查詢。

解決大量記憶體用量問題並不容易。幸好，performance_schema 記憶體檢測功能可協助使用者瞭解內部 MySQL 結構使用記憶體的方式。sys 結構定義檢視畫面可讓您更輕鬆地解讀資訊。如果您遇到記憶體相關問題，這項功能可協助您找出問題來源，並透過變更設定/查詢來修正該問題。

例如：

1. 如果內部暫存資料表需要大量記憶體，您可以使用 tmp_table_size 限制臨時資料表的大小，或是縮減 innodb_buffer_pool_size 以騰出空間給每個工作階段的緩衝區
2. 如果特定執行緒耗用大量記憶體，您可以使用 thread_id 篩選慢速查詢記錄，找出有問題的查詢並加以調整，藉此減少該執行緒/工作階段的記憶體用量

如果您遇到 MySQL 執行個體使用的記憶體量高於正常值的情況，且記憶體用量持續增加直到伺服器記憶體用盡，而 performance_schema 顯示出總量與檢測記憶體之間有差距，或是無法判斷記憶體去了哪裡，那就有可能是記憶體流失的問題。如果您懷疑記憶體流失，可採取下列步驟：

• 請仔細查看 MySQL 設定，確認沒有過度分配緩衝區/快取。這篇文章可協助您瞭解 MySQL 如何為不同的緩衝區/快取分配記憶體。
• 找出造成記憶體問題的功能/設定/查詢，嘗試重現問題。
• 請查看同一系列中下一個子版本的 MySQL 版本資訊，確認是否有任何具類似行為的錯誤已修正。
• 將 MySQL 升級至最新的子版本，看看是否能修正記憶體問題。
• 搜尋 MySQL 錯誤資料庫，查看其他社群使用者是否有回報類似問題。
• 如果目前不存在相同問題的錯誤，請使用可重現的測試案例建立新的錯誤。