如何在 Amazon RDS PostgreSQL 或 Aurora PostgreSQL 相容的資料庫執行個體中識別並疑難排解效能問題和查詢執行速度緩慢？

解決方法

硬體規模不足、工作負載變更、流量增加、記憶體問題或未最佳化的查詢都會影響資料庫執行個體效能。若要解決效能問題，請執行下列動作。

識別原因

檢查 CloudWatch 指標

若要識別資源不足所造成的效能瓶頸，請監控 CPUUtilization、FreeableMemory 和 SwapUsage Amazon CloudWatch 指標。

當 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';

若要終止閒置連線，請執行下列命令：

SELECT pg_terminate_backend(example-pid);

**注意：**使用閒置連線的程序 ID 取代 example-pid。

若要驗證資料庫執行個體是否達到預期的網路輸送量，請檢查 NetworkReceiveThroughput 和 NetworkTransmitThroughput 指標。規模不足或未最佳化的 Amazon Elastic Block Service (Amazon EBS) 執行個體類別可能會影響網路輸送量並導致執行個體緩慢。無論資料庫效能如何，網路輸送量低都會導致所有應用程式請求的回應速度緩慢。

若要評估 I/O 效能，請檢查 ReadIOPS、WriteIOPS、ReadLatency、WriteLatency、ReadThroughput、WriteThroughput 和 DiskQueueDepth 指標。如需詳細資訊，請參閱如何疑難排解 Amazon RDS 執行個體中 IOPS 瓶頸所造成的 Amazon EBS 磁碟區延遲？

使用增強型監控

使用增強型監控檢視作業系統 (OS) 層級的指標，並列出使用高 CPU 和記憶體的前 100 程序。啟用增強型監控，將精度設定為 1，以識別資料庫執行個體上的間歇性效能問題。

評估可用的作業系統指標，以診斷與 CPU、工作負載、I/O、記憶體和網路相關的效能問題。從程序清單中，識別具有高 CPU% 或 em% 值的程序。

範例：

名稱	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	無限制

連線至資料庫，然後執行下列查詢，以尋找資料庫中具有高 CPU 的連線：

SELECT * FROM pg_stat_activity WHERE pid = 10322;

**注意：**使用連線的程序 ID 取代 10322。

使用效能洞察指標

使用效能洞察，依照等待、SQL、主機或使用者來評估資料庫工作負載。您也可以取得資料庫和 SQL 層級指標。

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

使用 SQL 統計資料中的每次呼叫平均延遲來檢視查詢的平均執行時間。常用 SQL 是以總執行時間為基礎。因此，最高執行時間的 SQL 通常與常用資料庫負載貢獻者的 SQL 不同。

檢查資料庫統計資料

若要評估 PostgreSQL 中的資料庫效能，請檢查資料分佈統計資料、擴充的統計資料和監控統計資料。如需有關統計資料的資訊，請參閱瞭解 PostgreSQL 中的統計資料。

檢查原生資料庫工具

若要識別慢速查詢，請使用 GitHub 網站上的原生 pgbadger 工具。如需詳細資訊，請參閱根據原生和外部工具最佳化和調整 Amazon RDS for PostgreSQL 中的查詢。

最佳化效能

調整記憶體設定

您可以將 shared_buffers 參數設定為有助於提高查詢效能的值。

work_mem 和 maintenance_work_mem 參數定義用於後端程序的記憶體數量。如需詳細資訊，請參閱 PostgreSQL 網站上的 20.4 資源消耗量。如果您經常在資料庫執行個體上遇到高記憶體使用量，請降低附加到執行個體自訂參數群組中的參數值。

使用 Aurora PostgreSQL 相容的查詢計畫管理

使用 Aurora PostgreSQL 相容的查詢計畫管理來控制查詢執行計畫變更的方式和時間。如需詳細資訊，請參閱 Aurora PostgreSQL 查詢計畫管理的最佳做法。

疑難排解查詢執行速度緩慢

基礎架構問題、未最佳化的查詢計畫或高整體資源使用量會導致查詢執行速度緩慢。PostgreSQL 查詢規劃器使用資料表統計資料來建立查詢計畫。結構描述變更和舊統計資料可能會影響計畫。超載的資料表和索引也可能導致查詢執行速度緩慢。

當資料表達到無效元組臨界值時，Autovacuum 常駐程式會建立 Autovacuum 工作程序，從資料中移除無效元組。Autovacuum 程序也會執行 ANALYZE 作業，以重新整理資料表的統計資料。

執行下列查詢以檢查是否有無效元組和 Autovacuum 或 vacuum 作業，並自動分析或分析執行下列動作：

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 檢視來尋找與目前活動相關的資料，例如後端程序 ID 或查詢。若要尋找長時間執行的查詢，請執行下列查詢：

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;

使用 pg_stat_statements 檢視查詢的統計資料。在建立 pg_stat_statements 擴充功能之前，請將 pg_stat_statements 項目新增至 shared_preload_libraries。若要在資料庫中建立 pg_stat_statements 擴充功能，請執行下列查詢：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

**注意：**僅當自訂參數群組附加到資料庫執行個體時，才能修改 pg_stats_statements 的參數。

若要識別影響資料庫執行個體效能的 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_percentFROM 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 模組。

您也可以使用 explain 和 explain analyze 命令來取得查詢計畫。使用 auto_explain 模組或 explain 命令來識別如何調整查詢調整。如需詳細資訊，請參閱 14.1 使用 EXPLAIN 和 PostgreSQL 網站上 F3. auto_explain - 緩慢查詢的日誌執行計畫。

如果您已最佳化系統，但仍然遇到效能問題，則最佳做法為向上擴展資料庫執行個體類別。向上擴展資料庫執行個體時，會配置更多運算和記憶體資源。

相關資訊

如何針對 Amazon RDS 或 Amazon Aurora PostgreSQL 高 CPU 使用率進行疑難排解？

在 RDS for PostgreSQL 資料庫執行個體上使用參數

為什麼 Amazon RDS 資料庫執行個體在具有足夠的記憶體時使用交換記憶體？