View a markdown version of this page

Aurora PostgreSQL RDSfor PostgreSQL 中常見 PostgreSQL 效能問題的初始疑難排解 - Amazon Relational Database Service
快速診斷檢查清單資料表和索引膨脹平行查詢資源耗盡高連線和身分驗證壓力使用績效詳情等待事件進行故障診斷自動清空調校相關資訊

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

Aurora PostgreSQL RDSfor PostgreSQL 中常見 PostgreSQL 效能問題的初始疑難排解

本指南涵蓋影響 RDS for PostgreSQL 資料庫的四個最常見效能問題：資料表和索引膨脹、平行查詢資源耗盡、高連線和身分驗證壓力，以及自動清空調校。當您遇到效能降低時，請在開始更深入的調查之前，使用本指南做為初次通過診斷檢查清單。

每個區段都會說明您可能會觀察到的症狀、提供診斷查詢以確認根本原因，以及建議特定的修補步驟。

了解「無變更」效能迴歸

PostgreSQL 工作負載通常會執行數週或數月沒有問題，即使應用程式程式碼和查詢模式看起來沒有改變，也會突然發生效能降低。這是因為資料庫環境從未真正靜態 — 數個不可見因素會隨著時間而轉移，並可能觸發計畫變更或資源爭用：

  • Bloat 累積是工作負載變更。PostgreSQL 的多版本並行控制 (MVCC) 會保留舊的資料列版本，直到自動清空回收為止。當無效元組累積速度比自動清空可以處理它們更快時，資料表和索引會實際增加。查詢規劃器接著可能會從有效率的索引掃描切換到循序掃描，因為成本預估會隨著資料表大小的增加而轉移。您的 SQL 尚未變更，但規劃器看到的資料。

  • 新的參數值是工作負載變更。當應用程式開始使用不同的範圍時，對某個範圍的值執行良好的參數化查詢可能會效能不佳。PostgreSQL 可能會重複使用不會考慮新範圍內資料扭曲的一般執行計畫，或者規劃器的統計資料可能無法準確反映這些值的分佈。當膨脹也存在時，影響複合：次佳計劃現在會掃描更多無效資料。

  • 即使自動清空執行，統計資料也可能過時。根據插入或更新的列ANALYZE數自動清空觸發，而不是資料分佈是否已有意義地變更。如果您的應用程式轉移到查詢不同的值範圍或時間範圍，即使最近已執行自動清空，規劃器的成本估算可能不準確。

  • 整體資料庫成長是工作負載變更。隨著資料表隨著時間的推移而成長，查詢必須掃描的資料頁面量也會增加。對較小資料表執行良好的查詢可能會在資料表大小增加時產生延遲，即使查詢邏輯和索引保持不變。監控 FreeStorageSpace 以追蹤儲存體成長趨勢。

當您調查「沒有變更」的效能迴歸時，請將膨脹累積、新的參數值範圍、整體資料庫成長和過時統計資料視為最可能的根本原因。使用本指南中的診斷步驟來確認適用的因素。

如需詳細資訊，請參閱下列內容：

快速診斷檢查清單

當您第一次調查效能問題時，請使用下列排序的分類步驟：

  1. 檢查 pg_stat_activity。查看連線計數、idle-in-transaction工作階段和長時間執行的查詢。如需詳細資訊，請參閱 RDS for PostgreSQL 調校的基本概念。

  2. 檢查膨脹。尋找高n_dead_tup輸入，pg_stat_user_tables並考慮使用 pgstattuple進行精確測量。如需詳細資訊，請參閱使用 pg_repack 從資料表移除膨脹。

  3. 檢查 pg_stat_user_tables。尋找高n_dead_tup值和過時的last_autovacuum時間戳記。如需詳細資訊，請參閱在 Amazon RDS 上使用 PostgreSQL 自動清空在 。

  4. EXPLAIN ANALYZE檢閱慢查詢。尋找大型資料表上的平行計畫和循序掃描。如需詳細資訊，請參閱 RDS for PostgreSQL 中平行查詢的最佳實務。

  5. 檢查 CloudWatch 和績效詳情指標。檢閱 CPU 使用率、連線計數、IOPS 和可用記憶體。如需詳細資訊，請參閱監控 Amazon RDS。如需常見的等待事件和修正動作，請參閱 RDS for PostgreSQL 等待事件。

  6. 檢閱您的資料庫參數群組。檢查max_parallel_workers_per_gather和自動清空設定。如需詳細資訊，請參閱在 Amazon RDS 和 Amazon Aurora 中調校 PostgreSQL 參數。

資料表和索引膨脹

當無效元組在資料表中累積的速度比自動清空更快時，會發生資料表和索引膨脹。隨著時間的推移，這會導致逐漸降低查詢效能、增加儲存用量和次佳查詢計劃。

徵狀

  • 在數週或數月內逐漸降低查詢效能

  • 儘管資料量穩定，儲存體用量仍持續增加

  • 由於統計資料過時，查詢規劃器會在索引掃描上選擇循序掃描

  • 資料表統計資料dead_tuple_count高

診斷

您可以透過查詢系統目錄來估計所有資料表的膨脹。此方法不需要任何擴充功能：

SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

若要解決膨脹問題，您可以使用 pg_repack擴充功能，以最小的鎖定來重組資料表和索引。如需詳細資訊，請參閱從具有 pg_repack 的資料表移除膨脹，以及從具有 pg_repack 的 Amazon Aurora 和 RDS for PostgreSQL 移除膨脹。

重要

與其依賴手動維護，請確保已啟用自動清空並正確調整工作負載。如需調校建議自動清空調校，請參閱 。

平行查詢資源耗盡

PostgreSQL 可以平行執行查詢，以提高大型循序掃描和彙總的效能。不過，每個平行工作者都是完整的後端程序，會計入 max_worker_processes（和次限制 max_parallel_workers) 並配置自己的 work_mem。具有 4 個平行工作者的單一查詢可以耗用數百 MB 的記憶體和大量的 CPU。在高度並行下，過度平行處理可能會快速耗盡 CPU 和記憶體。

常見的症狀包括突然的 CPU 峰值、每個查詢的高記憶體使用量，以及在 CloudWatch DatabaseConnections中升高，而不會變更應用程式。您也可以觀察等待事件，例如 IPC:BgWorkerStartup、 IPC:ExecuteGather和 IPC:ParallelFinish。如需這些等待事件的詳細資訊，請參閱 IPC：平行等待事件。

對於大多數 OLTP 和高並行生產工作負載，請在資料庫參數群組max_parallel_workers_per_gather = 0中設定 以停用自動平行處理。然後，您可以設定每個工作階段或每個角色的 參數，選擇性地為特定分析或報告工作階段啟用平行處理。

如需診斷和控制平行查詢行為的詳細指引，請參閱 RDS for PostgreSQL 中平行查詢的最佳實務。

高連線和身分驗證壓力

連線流失 — 頻繁開啟和關閉資料庫連線而不進行集區 — 會建立身分驗證額外負荷，並可能耗盡可用的連線插槽。保持開啟的閒置連線也會耗用插槽，而不會執行有用的工作。

徵狀

  • 在績效詳情監控total_auth_attempts中提升。如需詳細資訊，請參閱 RDS for PostgreSQL 的非原生計數器。

  • 連線建立時間緩慢

  • FATAL: too many connections for role 或 remaining connection slots are reserved 錯誤

  • CPU 峰值與連線流失相關

診斷

執行下列查詢以檢查您目前的連線狀態：

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';

相對於 的大量 idle或 idle in transaction 連線max_connections表示連線未正確釋放。Idle-in-transaction連線特別有問題，因為它們會保留鎖定並防止自動清空回收無效元組。

修補

  • 部署連線集區。使用 PgBouncer 或 Amazon RDS Proxy 來減少與資料庫的直接連線數量。連線集區會重複使用現有的連線，而不是為每個請求建立新的連線。

  • 設定 idle_in_transaction_session_timeout。此參數會自動終止在超過指定持續時間的交易中保持閒置的工作階段。這可防止長時間執行的閒置交易持有鎖定並封鎖自動清空。

  • 檢閱應用程式連線處理。確保您的應用程式立即關閉連線，並且不會讓交易開啟超過必要的時間。

注意

平行查詢工作者會耗用 CPU 和記憶體。如果您觀察到資源耗盡與平行查詢活動，請參閱 平行查詢資源耗盡 以取得控制平行工作者用量的指引。

使用績效詳情等待事件進行故障診斷

Performance Insights 會擷取顯示資料庫花費時間的等待事件。當您調查效能問題時，等待事件可協助您識別瓶頸是 CPU、I/O、鎖定、網路還是程序間通訊。在本指南所述問題期間出現的常見等待事件類別包括：

  • CPU — 工作階段在 CPU 上處於作用中狀態或正在等待 CPU。高 CPU 等待事件通常與掃描膨脹資料表的過度平行處理或效率低的查詢計劃相關。

  • IPC （程序間通訊） — 等待事件，例如 IPC:BgWorkerStartup、 IPC:ExecuteGather和 IPC:ParallelFinish，指出平行查詢協調額外負荷。

  • IO — 等待事件，例如 IO:DataFileRead 表示查詢正在從儲存體讀取資料，因為所需的頁面不在共用記憶體中。這在膨脹的資料表超過緩衝區快取時很常見。

  • 鎖定 — 等待 等事件Lock:transactionid，並Lock:tuple指出工作階段之間的爭用。Idle-in-transaction連線可以保留封鎖其他查詢和自動清空的鎖定。

  • 用戶端 — 等待事件，例如Client:ClientRead指出資料庫正在等待應用程式傳送資料。高用戶端等待事件可能表示連線流失或網路延遲。

如需通常表示效能問題及其建議修正動作的等待事件完整參考，請參閱 RDS for PostgreSQL 等待事件。

自動清空調校

自動清空是回收無效元組、防止資料表和索引膨脹、更新規劃器統計資料，以及防止交易 ID 包圍的背景程序。預設的自動清空設定是保守的，專為小型資料庫而設計。高寫入生產工作負載幾乎一律需要調校。

當自動清空無法跟上寫入工作負載時，膨脹累積、規劃器統計資料變得過時，交易 ID 包圍的風險也會增加。如果 age(relfrozenxid)接近 20 億，資料庫會關閉以防止資料損毀。

如需調校自動清空參數、監控清空活動和設定每個資料表覆寫的詳細指導，請參閱在 Amazon RDS 上使用 PostgreSQL 自動清空在 。

相關資訊