MySQL 如何使用記憶體

MySQL 會在伺服器啟動時分配全域緩衝區，且所有連線會共用這些緩衝區。MySQL 的記憶體大多由全域緩衝區使用，例如 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 等。

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_log_buffer_size 進行其設定。MySQL Community 預設值為 16 MB，而 MySQL 適用的 Cloud SQL 也使用相同的值。

MySQL 會使用索引鍵緩衝區來快取記憶體中的 MyISAM 索引。系統會使用 key_buffer_size 來設定。MySQL Community 預設值為 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 有用於儲存資料表定義的專屬快取，不同於資料表開啟快取和資料表定義快取。您可以在 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) 分配這些緩衝區的多個執行個體。

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_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，這有助於以低階監控 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 或效能問題，可以升級執行個體大小來增加記憶體。