盡可能改善執行個體記憶體用量偏高的情形

MySQL  |  PostgreSQL  |  SQL Server

執行個體耗用大量記憶體或發生記憶體不足 (OOM) 事件是常見問題。如果資料庫執行個體在記憶體用量偏高的情況下執行，通常會導致效能問題、停滯，甚至資料庫停機。

部分 MySQL 記憶體區塊會全域使用。也就是說，所有查詢工作負載都會共用記憶體位置，且會一直佔用記憶體，只有在 MySQL 程序停止時才會釋出。部分記憶體區塊是以工作階段為基礎，也就是說，工作階段一關閉，該工作階段使用的記憶體也會釋放回系統。

如果 MySQL 適用的 Cloud SQL 執行個體記憶體用量偏高，Cloud SQL 建議您找出並釋放耗用大量記憶體的查詢或程序。MySQL 記憶體消耗量可分為三個主要部分：

• 執行緒和處理程序記憶體用量
• 緩衝區記憶體用量
• 快取記憶體用量

執行緒和處理程序記憶體用量

每個使用者工作階段都會耗用記憶體，具體用量取決於該工作階段執行的查詢、緩衝區或快取，並由 MySQL 的工作階段參數控管。主要參數包括：

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

如果在特定時間執行 N 個查詢，則每個查詢在工作階段期間會根據這些參數耗用記憶體。

緩衝區記憶體用量

所有查詢都會共用這部分記憶體，並由 innodb_buffer_pool_size、innodb_log_buffer_size 和 key_buffer_size 等參數控管。

由 innodb_buffer_pool_size 旗標設定的 InnoDB 緩衝區集區會佔用 MySQL 適用的 Cloud SQL 執行個體大量記憶體，並做為快取來提升效能。如要降低記憶體不足 (OOM) 事件的風險，可以啟用受管理緩衝區集區 (搶先體驗版)。

快取記憶體用量

快取記憶體包含查詢快取，用於儲存查詢及其結果，以便後續更快擷取相同查詢的資料。其中也包含 binlog 快取，用於保留交易執行期間對二進位記錄檔所做的變更，並由 binlog_cache_size 控制。

其他記憶體消耗量

彙整和排序作業也會使用記憶體。如果查詢使用聯結或排序作業，這些查詢會根據 join_buffer_size 和 sort_buffer_size 使用記憶體。

此外，啟用效能結構定義會耗用記憶體。如要檢查效能結構定義的記憶體用量，請使用下列查詢：

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

MySQL 提供許多工具，您可以設定這些工具，透過效能結構定義監控記憶體用量。詳情請參閱 MySQL 說明文件。

大量資料插入作業的 MyISAM 相關參數為 bulk_insert_buffer_size。

如要瞭解 MySQL 如何使用記憶體，請參閱 MySQL 說明文件。

建議

以下各節提供一些建議，協助您充分運用記憶體。

啟用代管緩衝區集區

預先發布版

這項產品或功能 適用《服務專屬條款》。 正式發布前的產品和功能是按照「原樣」提供，支援範圍可能有限。 詳情請參閱推出階段說明。

如果記憶體用量偏高，執行個體可能會發生記憶體不足 (OOM) 事件。為避免發生 OOM 事件，請啟用受管理緩衝區集區，以減少 innodb_buffer_pool_size 的值，藉此釋放記憶體。當記憶體用量穩定在較低的值時，MySQL 會逐步將 innodb_buffer_pool_size 的值調高至原始值。

您無法為共用核心執行個體，或 MySQL 5.6 或 MySQL 5.7 啟用受管理緩衝區集區。

如要為執行個體啟用受管理緩衝區集區，請將 innodb_cloudsql_managed_buffer_pool 標記設為 on。如要進一步瞭解如何設定資料庫旗標，請參閱「設定資料庫旗標」。

變更 innodb_cloudsql_managed_buffer_pool 旗標的值時，不需要重新啟動 Cloud SQL 執行個體。

根據預設，如果 MySQL 資料庫超過分配記憶體的 95%，Cloud SQL 就會開始縮減 innodb_buffer_pool_size 的大小。如要修改 95% 的門檻，請將 innodb_cloudsql_managed_buffer_pool_threshold_pct 旗標設為不同的百分比值。舉例來說，如要將門檻調整為 97%，請使用下列指令：

gcloud sql instances patch INSTANCE_NAME
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

您可以將 innodb_cloudsql_managed_buffer_pool_threshold_pct 旗標設為介於 50 至 99 之間的整數值。變更記憶體用量門檻值時，不需要重新啟動 Cloud SQL 執行個體。

當受管理緩衝區集區調整 innodb_buffer_pool_size 的值時， Google Cloud 控制台中顯示的旗標值不會反映這些變更。如要查看啟用受管理緩衝區集區時 innodb_buffer_pool_size 的目前值，可以使用 MySQL 用戶端查詢標記值：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

縮減緩衝區集區大小不一定能避免所有情況下的 OOM。舉例來說，部分工作負載可能會耗用過多記憶體或突然增加，部分 Cloud SQL 執行個體可能資源不足，或緩衝區集區可能未預熱。Cloud SQL 可能無法快速釋出足夠的記憶體，以因應記憶體工作負載的突然變化。此外，Cloud SQL 無法處理其他記憶體旗標的設定錯誤值。

注意： 減少 innodb_buffer_pool_size_size 的值可能會影響查詢延遲和效能。不過，只有在執行個體有發生 OOM 事件的風險時，啟用受管理緩衝區集區才會降低 innodb_buffer_pool_size_size 的值。記憶體用量穩定後，innodb_buffer_pool_size_size 的值會逐漸還原為原始值。

使用 Metrics Explorer 找出記憶體用量

您可以在 Metrics Explorer 中使用 database/memory/components.usage 指標，查看執行個體的記憶體用量。

一般來說，如果 database/memory/components.cache 和 database/memory/components.free 的合併記憶體用量低於 10%，發生 OOM 事件的風險就會很高。為監控記憶體用量並避免 OOM 事件，建議您在 database/memory/components.usage 中設定快訊政策，並加入指標門檻條件。

下表顯示執行個體記憶體與建議的警報觸發門檻之間的關係：

執行個體記憶體	建議的警示門檻
小於或等於 16 GB	90%
大於 16 GB	95%

計算記憶體耗用量

計算 MySQL 資料庫的最高記憶體用量，為 MySQL 資料庫選取合適的執行個體類型。使用下列公式：

MySQL 記憶體用量上限 = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

公式中使用的參數如下：

• innodb_buffer_pool_size：緩衝區集區的大小 (以位元組為單位)，InnoDB 會在該記憶體區域中快取資料表和索引資料。
• innodb_additional_mem_pool_size：InnoDB 用於儲存資料字典資訊和其他內部資料結構的記憶體集區大小 (以位元組為單位)。
• innodb_log_buffer_size：InnoDB 用來寫入磁碟記錄檔的緩衝區大小 (以位元組為單位)。
• tmp_table_size：MEMORY 儲存引擎建立的內部記憶體內暫存資料表，以及 MySQL 8.0.28 以上版本 TempTable 儲存引擎建立的內部記憶體內暫存資料表，其大小上限。
• key_buffer_size：用於索引區塊的緩衝區大小。MyISAM 資料表的索引區塊會經過緩衝處理，並由所有執行緒共用。
• read_buffer_size：針對掃描的每個資料表，執行 MyISAM 資料表循序掃描的每個執行緒都會分配這個大小的緩衝區 (以位元組為單位)。
• read_rnd_buffer_size：這個變數用於從 MyISAM 資料表讀取資料、任何儲存引擎，以及多範圍讀取最佳化。
• sort_buffer_size：每個必須執行排序作業的工作階段都會配置這個大小的緩衝區。sort_buffer_size 不屬於任何儲存空間引擎，一般會用於最佳化。
• join_buffer_size：用於一般索引掃描、範圍索引掃描，以及不使用索引的聯結 (因此會執行完整資料表掃描) 的緩衝區最小大小。
• max_connections：允許的用戶端連線數量上限。

注意： 還有許多其他耗用記憶體的小型元件，這些元件取決於查詢，因此也應預留空間給這些元件。

排解記憶體用量偏高的問題

• 執行 SHOW PROCESSLIST，查看正在消耗記憶體的查詢。這個頁面會顯示所有已連線的執行緒，以及執行中的 SQL 陳述式，並嘗試進行最佳化。請注意「狀態」和「時間長度」欄。

  mysql> SHOW [FULL] PROCESSLIST;
  
  

• 查看 BUFFER POOL AND MEMORY 部分的 SHOW ENGINE INNODB STATUS，瞭解目前的緩衝區集區和記憶體用量，有助於設定緩衝區集區大小。

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• 使用 MySQL 的 SHOW variables 指令檢查計數器值，即可取得臨時資料表數量、執行緒數量、資料表快取數量、髒頁、開啟的資料表數量和緩衝區集區使用量等資訊。

  mysql> SHOW variables like 'VARIABLE_NAME'
  

套用變更

分析不同元件的記憶體用量後，請在 MySQL 資料庫中設定適當的旗標。如要在 MySQL 適用的 Cloud SQL 執行個體中變更旗標，可以使用 Google Cloud 控制台或 gcloud CLI。如要使用 Google Cloud 控制台變更旗標值，請編輯「Flags」部分，選取旗標並輸入新值。

最後，如果記憶體用量仍偏高，且您認為查詢和旗標值已最佳化，請考慮增加執行個體大小，以免發生 OOM 錯誤。

後續步驟

• Google Cloud 建議工具