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

執行個體通常會耗用大量記憶體或產生記憶體不足 (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_sizeInnodb_log_buffer_sizekey_buffer_size 等參數控管。

快取記憶體用量

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

其他記憶體消耗量

彙整和排序作業也會使用記憶體。如果查詢使用聯結或排序作業,這些查詢會根據 join_buffer_sizesort_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 說明文件

建議

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

使用 Metrics Explorer 找出記憶體用量

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

如果 database/memory/components.cachedatabase/memory/components.free 的合併記憶體用量低於 5%,發生 OOM 事件的風險就會很高。為監控記憶體用量並避免發生 OOM 事件,建議您在 database/memory/components.usage 中設定警報政策,並將指標閾值條件設為 95% 以上。

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

執行個體記憶體 建議的警示門檻
最多 100 GB 95%
100 GB 至 200 GB 96%
200 GB 至 300 GB 97%
超過 300 GB 98%

計算記憶體耗用量

計算 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 控制台變更旗標值,請編輯「旗標」部分,選取旗標並輸入新值。

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

後續步驟