如何在 RDS for PostgreSQL 或 Aurora PostgreSQL 執行個體中識別效能問題和緩慢執行查詢的問題，並進行疑難排解？

解決方式

您 Amazon RDS for PostgreSQL 執行個體的效能可能會因多種原因而受到影響，例如：

• 硬體大小不足
• 工作負載的變更
• 增加的流量
• 記憶體問題
• 不理想的查詢規劃

識別原因

使用這些工具的組合來識別緩慢執行查詢的原因：

• Amazon CloudWatch 指標
• 增強型監控指標
• 績效詳情指標
• 資料庫統計資料
• 原生資料庫工具

CloudWatch 指標

若要識別資源不足造成的效能瓶頸，請監控這些適用於 Amazon RDS 資料庫執行個體的常見 CloudWatch 指標。

• CPUUtilization – 使用的電腦處理能力百分比
• FreeableMemory – 資料庫執行個體上的可用 RAM (MB)
• SwapUsage – 資料庫執行個體使用的交換空間 (MB)

較高的 CPU 使用率百分比，通常表示執行個體上有作用中的工作負載，並且需要更多 CPU 資源。較高的記憶體使用率以及交換空間取用，表示由於工作負載的記憶體可用性不足而經常進行交換。這可能意味著您的執行個體無法跟上工作負載。CPU 和記憶體資源使用率較高通常是因為長時間執行查詢、流量突然增加，或大量閒置連線造成。

執行此命令以檢視執行時間的作用中查詢：

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

執行此命令以尋找資料庫中存在的空閒連接數：

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

然後，執行此命令以終止取用資源的閒置連線：

**注意：**請務必用您從 pg_stat_activity 取得的空閒連線 PID 取代 example-pid：

SELECT pg_terminate_backend(example-pid);

檢閱下列指標以確認 RDS 執行個體上是否達到所需的網路輸送量：

• NetworkReceiveThroughput
• NetworkTransmitThroughput

這些指標表示傳入和傳出網路流量 (位元組/秒)。規模過小或非 Amazon Elastic Block Service (Amazon EBS) 最佳化執行個體類別可能會影響網路輸送量，進而導致執行個體變慢。無論資料庫效能如何，低網路輸送量都會導致所有應用程式請求的回應速度變慢。

檢閱下列指標來評估 I/O 效能：

• ReadIOPS 和 WriteIOPS – 每秒磁碟讀取或寫入作業的平均數目
• ReadLatency 和 WriteLatency – 讀取或寫入作業花費的平均時間 (毫秒)
• ReadThroughput 和 WriteThroughput – 每秒從磁碟讀取或寫入磁碟的平均 MB 數
• DiskQueueDepth – 等待寫入磁碟或從磁碟讀取的 I/O 操作數目

如需詳細資訊，請參閱如何對 Amazon RDS 執行個體中 IOPS 瓶頸所導致的 Amazon EBS 磁碟區延遲進行疑難排解？

增強型監控指標

透過增強型監控，您可以檢視作業系統層級的指標，以及取用大量 CPU 和記憶體的前 100 個程序清單。您能夠以每秒級啟動增強型監控，以識別 RDS 執行個體上的間斷性效能問題。

您可以評估可用的作業系統指標，以診斷可能與 CPU, workload, I/O, memory, and network (CPU、工作負載、I/O、記憶體和網路) 相關的效能問題。

從程序清單中，識別 CPU％ 或 MEM ％ 高值的程序。然後，從資料庫中尋找相關連線。

例如：

名稱	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250.66 MB	27.7 MB	85.93	2.21	無限

連線至資料庫，然後執行此查詢，以尋找連線並查詢相關資訊：

SELECT * FROM pg_stat_activity WHERE pid = 10322;

績效詳情指標

效能洞察可讓您評估依等待、SQL、主機或使用者配量的資料庫工作負載。您還可以擷取資料庫和 SQL 層級指標。

「效能洞察」儀表板上的常用 SQL 索引標籤會顯示對資料庫負載貢獻最大的 SQL 陳述式。依等待 (AAS) 高於 vCPU 上限值的資料庫負載或負載，表示執行個體類別上的限流工作負載。

SQL 統計資料中每次呼叫的平均延遲可提供查詢的平均執行時間。通常會看到不同的 SQL 成為頂級資料庫負載貢獻者，而不是具有最高平均執行時間的 SQL。這是因為常用 SQL 清單以總執行時間為基礎。

資料庫統計資料

下列統計資料可協助您評估 PostgreSQL 中的資料庫效能：

• 資料分佈統計資料
• 擴充統計資料
• 監控統計資料

如需有關如何讀取和了解這些統計資料的資訊，請參閱了解 PostgreSQL 中的統計資料。

原生資料庫工具

如要識別慢速查詢，請使用原生 pgBadger 工具。如需詳細資訊，請參閱根據原生和外部工具在 Amazon RDS for PostgreSQL 中最佳化和調校查詢。

最佳化效能

調校記憶體設定

PostgreSQL 資料庫伺服器會在其整個生命週期分配特定的記憶體區域，以便快取資料，進而改善讀取和寫入存取。此記憶體區域稱為共用緩衝區。資料庫用於共用記憶體緩衝區的記憶體量由 shared_buffers 參數控制。

除了共用記憶體區域之外，每個後端程序會取用記憶體，以便在資料庫伺服器內執行作業。所使用的記憶體量是依據為 work_mem 和 maintenance_work_mem 參數所設定的值而定。如需詳細資訊，請參閱伺服器組態的 PostgreSQL 說明文件。

若您持續觀察到資料庫執行個體的記憶體不足壓力，請考慮降低這些參數的值。您可在連接至資料庫執行個體的自訂參數群組中降低這些參數的值。

Aurora PostgreSQL 查詢計劃管理

使用 Amazon Aurora PostgreSQL 相容版本查詢計劃管理，以控制查詢執行計劃變更的方式和時間。如需詳細資訊，請參閱 Aurora PostgreSQL 相容版本查詢計劃管理的最佳實務。

對緩慢執行查詢的問題進行疑難排解

當發生基礎結構問題或整體資源取用較高時，通常會遇到緩慢執行查詢的問題。緩慢執行查詢也可能是由於查詢規劃工具進行不理想的查詢規劃導致。PostgreSQL 查詢規劃工具會使用為資料表建立的統計資料，來建立查詢計畫。這些計畫可能會由於結構描述變更和過時的統計資料而受到影響。資料表和索引中的膨脹也可能導致緩慢執行查詢。

自動清理常駐程式負責建立自動清理工作者程序，可在滿足無效元組閾值時從資料表中移除無效元組。自動清理常駐程式還負責執行 ANALYZE 作業，以重新整理對特定資料表存放的統計資料。

執行下列查詢以找出相關資訊：

• 無效元組
• 自動清空或清空作業次數
• 自動分析或分析執行次數
• 上次執行這些作業的時間

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,
autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count 
FROM pg_stat_user_tables
ORDER BY 5 DESC;

您可使用 pg_stat_activity 檢視，以找出與目前活動相關的資料。此檢視提供後端 pid、查詢和其他詳細資訊。若要尋找長時間執行的查詢，請執行下列查詢：

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU' 
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

請注意，等待鎖定的查詢可能會較慢。因此，請執行下列查詢來驗證查詢是否等待鎖定：

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

您的 RDS for PostgreSQL 執行個體可讓您從資料庫內建立 pg_stat_statements 延伸：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

使用 pg_stat_statements，您可以檢視查詢的統計資料。在建立延伸之前，請務必先將 pg_stat_statements 項目新增至 shared_preload_libraries。

**注意：**只有當自訂參數群組連接至資料庫執行個體時，您才能修改此模組的參數。

您可以使用此查詢，識別會影響執行個體效能的常用 SQL 查詢。

若要尋找資料庫中時間花費更多的查詢，請針對 PostgreSQL 12 版及更舊版本執行此查詢：

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

針對 PostgreSQL 13 版及更新版本執行此查詢：

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

若要尋找緩衝區快取命中率較低的查詢，請針對 PostgreSQL 12 版及更舊版本執行此查詢：

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time
DESC LIMIT 10;

針對 PostgreSQL 13 版及更新版本執行此查詢：

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +
shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

如要適當設定執行個體的 log_min_duration_statement 參數，接著使用 auto_explain 模組，在資料庫錯誤日誌中擷取長時間執行的查詢或查詢計畫。如果陳述式在至少指定時長執行，則 log_min_duration_statement 參數會導致記錄每個已完成陳述式的持續時間。例如，如果您將此參數設定為 250 毫秒，則會記錄執行 250 毫秒或更長時間的所有 SQL 陳述式。auto_explain 模組可讓您擷取資料庫中執行之查詢的說明計畫。

您也可以使用說明和說明分析命令，來擷取該計畫。根據 auto_explain 模組或 explain 命令，識別查詢調整機會。如需詳細資訊，請參閱 PostgreSQL 文件中的使用 EXPLAIN。

如果您的系統經過適當調整，而您仍然面臨資源限流，請考慮向上擴展執行個體類別。向上擴展執行個體類別，以對資料庫執行個體配置更多的運算和記憶體資源。如需詳細資訊，請參閱資料庫執行個體類別的硬體規格。

---

相關資訊

如何對 Amazon RDS 或 Amazon Aurora PostgreSQL 相容版本的高 CPU 使用率問題進行疑難排解？

Working with parameters on your RDS for PostgreSQL DB instance (使用 RDS for PostgreSQL 資料庫執行個體上的參數)

當我擁有充足的記憶體時，為什麼我的 Amazon RDS 資料庫執行個體使用交換記憶體？