針對 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 相容版執行個體，如何排解高 CPU 使用率問題？

4 分的閱讀內容

針對 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 或 Amazon Aurora PostgreSQL 相容版執行個體，我想要排解高 CPU 使用率問題。

解決方法

若要確定導致高 CPU 使用率的原因，請執行下列動作。然後，減少資料庫執行個體的 CPU 使用量。

檢閱資料庫執行個體指標

若要找出什麼時候工作負載會造成高 CPU 使用率，請使用 Amazon CloudWatch 將 WriteIOPs、ReadIOPs、ReadThroughput 及 WriteThroughput 指標，與 CPUUtilization 指標進行比對。針對 Aurora PostgreSQL 相容版，您還可以比對 BufferCacheHitRatio 指標。如果指標值與 CPUUtilization 指標同樣偏高，表示高 CPU 使用率可能是工作負載所引起。

使用增強型監控

使用增強型監控，檢查資料庫執行個體的作業系統 (OS)。若要收集詳細資料，請將 Granularity 屬性設為 1、5、10、15、30 或 60 秒的間隔。

若要排解高 CPU 使用率的原因，請檢查 LoadAverageMinute 作業系統指標。如果負載平均值大於 vCPU 數量，表示執行個體處於高使用率狀態。如果負載平均值小於資料庫執行個體類別的 vCPU 數量，則應用程式延遲可能不是 CPU 限流所致。

您也可以檢查資料庫執行個體的作業系統處理程序清單。增強型監控最多可識別 100 個影響執行個體效能的處理程序。若要確認查詢的資源使用情況，請參考增強型監控結果來執行 PostgreSQL pg_stat_activity 查詢。

使用 CloudWatch Database Insights

開啟 Amazon CloudWatch Database Insights，找出造成資料庫負載的查詢。然後，請針對 CPU 使用率升高的特定時間點，查看資料庫負載圖表上的 Top SQL (最高 SQL) 索引標籤。

查看原生 PostgreSQL 檢視表和目錄

如果問題是即時發生的，請開啟 pg_stat_activity 或 pg_stat_statements，對傳送最多流量的機器、用戶端和 IP 位址進行分組。詳情請參閱 PostgreSQL 網站上的 pg_stat_activity 和 pg_stat_statements 說明。

開啟 pg_stat_statements

請完成下列步驟：

修改自訂資料庫參數群組的下列值：
將 pg_stat_statements 新增至 shared_preload_libraries。
將 track_activity_query_size 設為 4096。
將 pg_stat_statements.track 設為 ALL。
將 pg_stat_statements.max 設為 10000。
選擇 Apply Immediately (立即套用)，然後重新啟動資料庫執行個體。

**選取要監控的資料庫 **

請執行下列查詢：

select current_database();

在目前的資料庫上安裝擴充功能

請執行下列命令：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

檢視在資料庫中花費最多時間的查詢

針對您的 PostgreSQL 版本執行下列查詢。

PostgreSQL 版本 12 及更舊版本：

SELECT total_time, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更新版本：

SELECT total_plan_time+total_exec_time as total_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 10;

列出緩衝快取命中率較低的查詢

針對您的 PostgreSQL 版本執行下列查詢。

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 3 DESC LIMIT 10;

取樣分析一段時間內的查詢

針對您的 PostgreSQL 版本執行下列查詢。

PostgreSQL 版本 12 及更舊版本：

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written FROM pg_stat_statements WHERE calls != 0

ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更新版本：

SELECT query,calls,  (total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,   
 rows/calls as avg_rows,  
temp_blks_read/calls as avg_tmp_read,  
 temp_blks_written/calls as avg_temp_written  
FROM pg_stat_statements  
WHERE calls != 0  
ORDER BY 3 DESC LIMIT 10;

檢查資料庫中的閒置連線

如果資料庫中有閒置連線，資料庫執行個體可能會占用大量 CPU 資源。若要解決此問題，請檢查並結束閒置連線。詳情請參閱「閒置 PostgreSQL 連線對效能的影響」。

若要檢查閒置超過 10 分鐘的工作階段，請執行下列查詢：

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled','active')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

若要僅結束閒置狀態超過 10 分鐘的工作階段，請執行下列查詢：

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

若要結束所有閒置連線，請執行下列其中一個查詢：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'example-username'  
AND pid <> pg_backend_pid()  
AND state in ('idle');

**注意：**請將 example-user-name 替換成您的使用者名稱。

-或-

SELECT pg_terminate_backend (example-pid);

**注意：**請將 example-pid 替換成您的查詢 PID。

如果您的應用程式建立太多資料庫連線，請減少連線數量。或者，請使用連線集區工具，例如 PgBouncer。詳情請參閱 PgBouncer 網站上的 PgBouncer 說明。您也可以使用 Amazon RDS Proxy 來設定連線集區。

檢查資料庫鎖定狀況

如果資料庫鎖定造成累積查詢並拉長執行時間，則資料庫執行個體的 CPU 使用率可能會增加。若要排解鎖定相關問題，請檢查 Database Insights 中的等待事件，例如 Lock:Relation、Lock:tuple、Lock:transactionid 或其他鎖定相關事件。

若要找出造成查詢受阻的原因，請參閱 “How do I identify what blocked a query on my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance?” (如何找出導致 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容版資料庫執行個體查詢受阻的原因？)。

如果造成程序受阻的工作階段並非 ACTIVE 狀態，請使用 Database Insights 找出受阻的查詢。若要解決此問題，請結束所有受阻的工作階段。

執行 ANALYZE 命令

如果不常對資料庫中的資料表執行 ANALYZE，系統中的統計資料可能會過時，導致查詢使用更多運算資源。詳情請參閱 PostgreSQL 網站上的 ANALYZE 說明。

autovacuum 會移除資料表中未使用的空間，並回收資料庫空間。autovacuum 常駐程式也會執行 ANALYZE 命令。只要達到先前設定的臨界值版本，它就會定期更新資料表的統計資料。

若要瞭解 autovacuum 和 autoanalyze 最後一次在資料表上執行的時間，請執行以下查詢：

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

為了防止主要引擎版本升級後出現效能問題，請執行 ANALYZE 命令，重新整理資料庫執行個體中每個資料庫的 pg_statistic 資料表。

為了防止因資源用量過高而造成的效能問題，請執行下列不含參數的命令，以重新產生所有統計資料：

ANALYZE VERBOSE;

檢查 PostgreSQL 錯誤日誌

在 Amazon RDS for PostgreSQL 中開啟查詢記錄功能。然後，檢查 PostgreSQL 錯誤日誌，確認已設定 log_min_duration_statement 和 log_statement 參數。詳情請參閱 PostgreSQL 網站上的錯誤報告和記錄。

降低 CPU 使用率

若要減少 CPU 使用率，請執行下列動作：

使用 EXPLAIN 和 EXPLAIN ANALYZE 找出調整查詢計畫的方法。詳情請參閱 PostgreSQL 網站上的「使用 EXPLAIN」說明。
如果發現造成程序受阻的工作階段是不必要的，請使用該工作階段的 PID 來結束工作階段。
如果有查詢重複執行，請使用預備陳述式來降低 CPU 使用率。詳情請參閱 PostgreSQL 網站上的 PREPARE 說明。