MySQL 如何使用記憶體
使用 MySQL 執行個體通常會耗用大量記憶體或產生記憶體不足 (OOM) 的問題。使用記憶體壓力執行的資料庫執行個體常會引發效能問題、暫停，甚至應用程式停機。
開始為 MySQL 執行個體分配記憶體之前，請務必先瞭解 MySQL 如何使用記憶體。本文重點介紹使用記憶體的 MySQL 功能，這類功能通常會導致 OOM 問題；也會討論 MySQL 適用的 Cloud SQL 這項 Google Cloud 的全代管服務如何設定記憶體管理。
全域緩衝區
MySQL 會在伺服器啟動時分配全域緩衝區，且所有連線之間會共用這些緩衝區。MySQL 的記憶體大多由全域緩衝區使用，例如 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 等。
InnoDB 緩衝區集區
InnoDB 緩衝區集區通常使用最多的 MySQL 執行個體中的記憶體。系統會使用 innodb_buffer_pool_size 參數進行設定。可用來快取資料表資料和索引、變更緩衝區、自動調整雜湊索引和其他內部結構。MySQL 適用的 Cloud SQL 是 Google Cloud 的代管 MySQL 產品，可根據執行個體的大小為 innodb_buffer_pool_size 設定最多使用 72% 的執行個體記憶體容量。
InnoDB 會為緩衝區和相關資料結構保留額外的記憶體，分配的記憶體總量比指定緩衝區集區大小高出約 10%。您可以在 show engine innodb status\G 輸出內容中查看 InnoDB 緩衝區集區記憶體用量。
|
mysql> show engine innodb status\G … ---------------------- 緩衝區集區和記憶體 ---------------------- 分配的記憶體總量 11511349248 … |
InnoDB 記錄檔緩衝區
InnoDB 記錄檔緩衝區是用來保留要寫入磁碟中 InnoDB 重做記錄檔的變更。系統會使用 innodb_log_buffer_size 進行設定。MySQL 社群的預設值是 16 MB，MySQL 適用的 Cloud SQL 則使用同樣的值。
金鑰緩衝區大小
MySQL 會使用金鑰緩衝區來快取記憶體中的 MyISAM 索引。系統會使用 key_buffer_size 來設定。MySQL 社群的預設值為 8 MB，MySQL 適用的 Cloud SQL 則使用同樣的值。MySQL 適用的 Cloud SQL 不支援 MyISAM 資料表，因此可保留預設值。
查詢快取大小
query_cache_size 設定變數會定義快取查詢結果分配的記憶體量。它在 MySQL Community 5.7 和 MySQL 適用的 Cloud SQL 5.7 會預設為停用。
眾所周知，查詢快取有嚴重的擴充性問題，因此在 MySQL 5.7.20 中已淘汰，並在 MySQL 8.0 中移除。如果您仍在 MySQL 5.7 執行個體中使用查詢快取，請檢查它是否確實適用於您的工作負載。如需更多資訊，請參閱這篇網誌。
全域快取
MySQL 會分配所有連線共用的全域快取，這些配置會動態分配，而設定變數會定義上限。
資料表快取
MySQL 會使用資料表快取功能加快資料表開啟速度。MySQL 資料表快取分為兩個部分：使用 table_open_cache 設定的開放式資料表快取，以及使用 table_definition_cache 設定的資料表定義快取。
table_open_cache 是記憶體快取，可儲存所有連線執行緒的開放式資料表的檔案描述元。提高此值增加 mysqld 程式 (也就是 MySQL 伺服器) 所需的檔案描述元數量。請確認作業系統可以處理 table_open_cache 設定所指示的開放式檔案描述元數量。
多個用戶端工作階段可同時存取指定資料表，且每個並行用戶端工作階段會各自開啟資料表。因此，您看到的開放式資料表數量可能高於伺服器中的資料表數量。資料表快取已滿時，伺服器會從目前最少使用的資料表開始釋出目前未使用的資料表。
table_definition_cache 是用於儲存資料表定義的記憶體快取。它是全域連線，在所有連線之間共用。
MySQL 適用的 Cloud SQL 5.7 使用 2000 和 1400 做為 table_open_cache 和 table_definition_cache 的預設值。
MySQL 適用的 Cloud SQL 8.0 使用 4000 和 2000 做為 table_open_cache 和 table_definition_cache 的預設值。
執行緒快取
MySQL 會為每個用戶端連線指派一個專屬執行緒，用於執行所有查詢，並將結果傳回用戶端，直到用戶端中斷連線。MySQL 會快取執行緒，這樣就不用為每個連線建立並刪除執行緒。執行緒快取中的執行緒數量是使用 thread_cache_size 變數來設定。
MySQL 適用的 Cloud SQL 使用 48 做為 thread_cache_size 的預設值。
InnoDB 資料字典快取
InnoDB 有儲存資料表定義的快取，與資料表開啟快取和資料表定義快取不同。您可以在 show engine innodb status\G 輸出中查看為 InnoDB 資料字典分配的記憶體。
|
---------------------- 緩衝區集區和記憶體 ---------------------- … 分配的字典記憶體 65816817 |
如果 InnoDB 資料字典快取中的資料表執行個體數量超過 table_definition_cache 的限制，table_definition_cache 設定會對 InnoDB 資料字典快取中的資料表執行個體數量設定非強制性限制，而 LRU 機制會開始將資料表執行個體標示為待移出，並且最終將其從這個快取中移除。
是否代表 InnoDB 資料字典快取中的資料表執行個體數量一律低於 table_definition_cache 限制？並非如此，具有外鍵關係的資料表執行個體不會列在 LRU 清單中。這些資料表執行個體會保持快取狀態，進而超出 table_definition_cache 限制，導致額外的記憶體用量。具有外鍵關係的資料表所使用的記憶體只會在 MySQL 關機/重新啟動事件中釋出。這個問題同時存在於 MySQL 5.7 和 8.0 中，而且有已知的已驗證錯誤。
如果您的 MySQL 執行個體含有大量與外鍵關係的資料表，InnoDB 資料字典快取可能會耗用多個 GB 的記憶體。在設定 MySQL 緩衝區/快取時，這個值經常遭到忽略，且可能是非預期的高記憶體用量或記憶體不足 (OOM) 問題的原因之一。
輸出範例，顯示使用 InnoDB 資料字典快取耗用 4.16 GB 的小型執行個體。
|
$ mysql -e "show engine innodb status\G" | grep -i memory 緩衝區集區和記憶體 分配的大記憶體總量 7696023552 分配的字典記憶體 4465193358 |
工作階段緩衝區
使用記憶體的另一個 MySQL 功能是工作階段緩衝區。這些緩衝區會按工作階段分配，在某些情況下，可為單一查詢 (特別是 join_buffer_size) 分配這些緩衝區的多個執行個體。
只有在查詢需要這些緩衝區 (用於排序、彙整、索引/完整資料表掃描等) 時，才會分配這些緩衝區。但在需要時，這些緩衝區會分配為原尺寸，即使只需要極小部分。將這些緩衝區設為較高的值可能會導致記憶體浪費。
預設值在 MySQL Community 與 MySQL 適用的 Cloud SQL 中都相同。
二進位記錄檔快取
MySQL 使用二進位記錄檔快取功能，在交易執行期間保留對二進位記錄檔所做的變更。系統會使用 binlog_cache_size 進行設定。啟用二進位記錄檔功能之後，系統會為每個用戶端分配一個記錄檔 (log_bin=ON)。
在 MySQL Community 和 MySQL 適用的 Cloud SQL 中，binlog_cache_size 的預設值相同。
臨時資料表
MySQL 會建立內部暫存資料表來儲存中繼結果，同時處理某些類型的查詢，例如 GROUP BY、ORDER BY、DISTINCT 和 UNION。這些內部臨時資料表會先在記憶體中建立，當容量達到上限時，就會轉換成磁碟上的資料表。內部暫存資料表的大小上限取決於 tmp_table_size 和 max_heap_table_size 變數的最小值。
在 MySQL Community 和 MySQL 適用的 Cloud SQL 中，tmp_table_size 與 max_heap_table_size 的預設值都是相同的。
注意事項：由於每個工作階段的緩衝區和記憶體內臨時資料表會分別針對各個連線分配記憶體，視大量連線的需求，整體記憶體用量可能會非常高。建議您不要將這些值設為太高，以嘗試找出最適合您工作負載的值。
每個連線記憶體
每個執行緒都只需要少量記憶體來管理管理用戶端連線。下列變數可控制其大小。
- thread_stack：每個執行緒的堆疊大小，預設為 256KB。
- net_buffer_length：每個用戶端都與連線緩衝區和 net_buffer_length 結果緩衝區相關聯。這會進一步增加 max_allowed_packet 的大小。
performance_schema
如果已啟用 performance_schema，這有助於以低階監控 MySQL 伺服器執行作業。performance_schema 會動態分配記憶體，而且只在 MySQL 關機/重新啟動時才會釋出。
MySQL 適用的 Cloud SQL 允許在 RAM 大小為 15 GB 以上的執行個體中啟用 performance_schema，根據預設，從 MySQL 8.0.26 版本開始啟用。MySQL 5.6、5.7 和 8.0.18 版本預設會停用 performance_schema，如要啟用，請使用資料庫旗標。
結語
MySQL 適用的 Cloud SQL 會自動設定記憶體相關參數，提供立即可用的效能。如果工作負載需要較大的快取來支援大量資料表和/或連線，您的執行個體仍然可能發生 OOM 問題。許多執行緒同時分配工作階段緩衝區也可能導致記憶體相關問題。分配至全域緩衝區/快取的記憶體越多，可用於連線和工作階段緩衝區使用的記憶體也越少，反之亦然；關鍵是取得平衡。
如果您的工作負載需要的快取/工作階段緩衝區較大，可以使用 innodb_buffer_pool_size 縮減全域緩衝區的大小。您可以使用資料庫旗標來變更 MySQL 適用的 Cloud SQL 執行個體的設定參數。如果您仍遇到 OOM 或效能問題，可以升級執行個體大小，增加記憶體。
相關產品和服務
Google Cloud 提供代管的 MySQL 資料庫，可滿足您的業務需求，包括淘汰地端部署資料中心、執行軟體式服務 (SaaS) 應用程式，以及遷移核心業務系統等。
