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

本文將說明如何找出 Cloud SQL 執行個體的記憶體用量過高問題，並提供解決記憶體相關問題的建議。

如要瞭解如何設定 Cloud SQL 執行個體的記憶體用量，請參閱管理記憶體用量的最佳做法。

找出記憶體用量偏高的情況

以下各節將討論記憶體用量高的情況。

使用 Metrics Explorer 找出記憶體用量

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

使用查詢洞察資料分析資源消耗量高的查詢說明計畫

查詢洞察可協助您偵測、診斷及預防 Cloud SQL 資料庫的查詢效能問題。查詢洞察會列出執行時間較長的查詢，以及相關說明計畫 (PostgreSQL 說明文件)。查看說明計畫，找出記憶體用量掃描方法偏高的查詢部分。無論查詢執行時間為何，查詢洞察都能提供所有查詢的說明計畫。找出耗費較多時間的複雜查詢，瞭解哪些查詢會阻礙記憶體較長的時間。

常見的 PostgreSQL 掃描方法會使用大量記憶體，包括：

  • 點陣圖堆積掃描
  • 快速排序
  • 雜湊彙整或雜湊

啟用 Gemini 的執行個體記憶體用量偏高，以及相關記錄

如果您已啟用 Gemini，系統會終止執行記憶體用量高的查詢的連線，避免資料庫停機，而非發生導致資料庫停機的記憶體不足 (OOM) 錯誤。如要找出預設查詢，請查看資料庫記錄中的以下項目：

  (...timestamp….) db=postgres, user=customer FATAL: terminating connection due to administrator command

系統會顯示下列 PostgreSQL 適用的 Cloud SQL 資料庫記錄，其中記錄了系統為避免 OOM 而終止的記憶體用量高查詢。這項查詢是原始查詢的標準化版本：

  db=postgres,user=customer LOG:  postgres process with PID 1734 for the query "SELECT COUNT(product) AS item_count FROM test_table WHERE product_type = $1 AND product LIKE $2 AND c6_2 IN ($3,$4,$5,$6,$7)" has been cancelled.

系統也會在 Cloud SQL 執行個體頁面上顯示下列事件的通知：

  • 執行個體過去 24 小時的記憶體使用率。
  • 過去 24 小時內取消的標準化查詢清單。
  • 關於如何最佳化記憶體用量的 Google 說明文件連結。

記憶體用量偏高 - 建議

以下建議可解決常見的記憶體相關問題。如果執行個體持續使用大量記憶體，很可能會最終發生 out of memory 問題。如果 PostgreSQL 或其他程序的記憶體需求導致系統記憶體不足，PostgreSQL 記錄中就會顯示 Out of Memory 核心訊息，而 PostgreSQL 執行個體最終也會停止。例如：

Out of Memory: Killed process 12345 (postgres)

最常見的 OOM 問題發生情境，是 work_mem 值較高，且有大量有效連線。因此，如果您經常在 Cloud SQL for PostgreSQL 執行個體中遇到 OOM 問題，或想避免發生 OOM 問題，請考慮遵循下列建議：

  • 設定 work_mem

    使用快速排序的查詢比使用外部合併排序的查詢更快。不過，前者可能會導致記憶體耗盡。如要解決這個問題，請將 work_mem 值設為合理的值，以便平衡記憶體和磁碟中的排序作業。您也可以考慮在工作階段層級設定 work_mem，而非為整個例項設定。

  • 監控運作中的工作階段

    每個連線都會使用一定量的記憶體。使用下列查詢，檢查有效連線數量：

      SELECT
    state,
    usename,
    count(1)
  FROM
    pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
  GROUP BY
    state,
    usename
  ORDER BY
    1;

    如果您有大量的執行中工作階段，請分析造成大量執行中工作階段的根本原因，例如交易鎖定。

  • 設定 shared_buffers

    如果 shared_buffers 設為較高的值，建議您降低 shared_buffers 值，以便將記憶體用於其他作業，例如 work_mem 或建立新連線。

    快取命中率

    PostgreSQL 通常會嘗試在快取中保留您最常存取的資料。當用戶端要求資料時，如果資料已在共用緩衝區中快取，就會直接提供給用戶端。這稱為「在快取中找到了所需資料」。如果資料不在共用緩衝區中，系統會先從磁碟擷取資料到共用緩衝區，然後再提供給用戶端。這稱為「在快取中找不到所需資料」。快取命中率是指快取處理的內容要求數量與收到的要求數量之比。執行下列查詢，檢查 PostgreSQL 例項中表格要求的快取命中率：

    SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;

    執行下列查詢，檢查 PostgreSQL 例項中索引要求的快取命中率：

      SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit)  as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
  FROM
    pg_statio_user_indexes;

    一般來說，快取命中率達到 95% 至 99% 視為理想值。

  • 啟用超大頁面 PostgreSQL 適用的 Cloud SQL 預設會啟用 huge_pages，以便妥善管理記憶體。建議您啟用這項功能。如要進一步瞭解 huge_pages，請參閱 PostgreSQL 說明文件。

  • 設定 max_locks_per_transaction

    max_locks_per_transaction 值表示可同時上鎖的資料庫物件數量。在大多數情況下，預設值 64 就已足夠。不過，如果您要處理大型資料集，可能會遇到 OOM 問題。建議您將 max_locks_per_transaction 的值提高到足以避免 OOM 的程度。

    max_locks_per_transaction 值應為 max_locks_per_transaction * (max_connections + max_prepared_transactions) 物件。也就是說，如果您有 30 萬個物件，且 max_connections 的值為 200，那麼 max_locks_per_transaction 應為 1500。

  • 設定 max_pred_locks_per_transaction

    如果客戶在單一可序列化的交易中觸及多個不同的資料表，交易可能會失敗。在這種情況下，建議將 max_pred_locks_per_transaction 提高至合理的高值。max_pred_locks_per_transaction 和 max_locks_per_transaction 一樣，也會使用共用記憶體，因此請勿設定過高的值。

  • 如果記憶體用量仍偏高，且您認為這些查詢是正常流量，建議您考慮增加執行個體的記憶體資源數量，以免資料庫停止運作或發生停機情形。

後續步驟