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

簡短說明

若要確定導致 CPU 使用率過高的原因，請使用下列工具：

• Amazon CloudWatch 指標
• 增強型監控指標
• Performance Insights 指標
• 原生 PostgreSQL 檢視和目錄，例如 pg_stat_statements、pg_stat_activity 和 pg_stat_user_tables
• 資料庫中的閒置連線
• PostgreSQL 網站上的 ANALYZE 命令
• PostgreSQL 記錄參數，用於記錄長時間執行的查詢、自動清理、鎖定等待，以及連線和中斷請求

解決方案

查看 CloudWatch 指標

使用 CloudWatch 指標來識別長時間內的 CPU 模式。將 WriteIOPs、ReadIOPs、ReadThroughput 和 WriteThroughput 圖表與 CPU 使用率進行比較，以找出工作負載導致 CPU 使用率過高的時間。

確定時間範圍後，請查看與資料庫執行個體關聯的增強型監控資料。您可以將增強型監控的收集間隔設定為 1、5、10、15、30 或 60 秒，以更細緻地收集資料。

使用增強型監控

增強型監控提供作業系統 (OS) 層級的檢視。例如，您可以查看工作負載平均值、CPU 分佈 (System% 或 Nice%) 以及作業系統程序清單。如需詳細資訊，請參閱作業系統監控。

您可以使用 1、5 和 15 分鐘的間隔檢查 loadAverageMinute 資料。當負載平均值大於 vCPU 數量時，表示執行個體處於高負載狀態。如果負載平均值小於資料庫執行個體類別的 vCPU 數量，則 CPU 限流可能不會導致應用程式延遲。當您對 CPU 使用率過高的原因進行疑難排解時，請檢查平均負載以避免誤判。

例如，您有一個資料庫執行個體，其執行個體類別為 db.m5.2xlarge，具有 3000 個達到 CPU 配額的佈建 IOPS。執行個體類別有八個與其關聯的 vCPU。如果相同的負載平均值超過 170，表示該機器在測量的時間範圍內正承受重度負載。

負載平均分鐘數：

• 十五： 170.25
• 五： 391.31
• 一： 596.74

CPU 使用率：

• 使用者 (%)： 0.71
• 系統 (％)： 4.9
• 不錯 (％)： 93.92
• 總計 (%)： 99.97

**注意：**在增強型監控中，Nice% 是您的工作負載針對資料庫所使用的 CPU 量。

開啟增強型監控後，您還可以檢查與資料庫執行個體關聯的作業系統程序清單。增強型監控可協助您識別最多 100 個影響效能的程序。您可以將增強型監控結果與 pg_stat_activity 結果結合使用，以協助識別查詢的資源使用量。

使用 Performance Insights

使用 Amazon RDS Performance Insights 來識別導致資料庫負載的查詢。檢查與特定時間範圍對應的 SQL 索引標籤。

檢查原生 PostgreSQL 檢視和目錄

在資料庫引擎層級，您可以使用 pg_stat_activity 和 pg_stat_statements。如果問題是即時發生的，請使用 pg_stat_activity 或 pg_stat_statements 對傳送最多流量的機器、用戶端和 IP 位址進行分組。

使用該資料來檢查隨著時間推移的增加或應用程式伺服器中的增加。您也可以檢查應用程式伺服器是否有工作階段停滯或鎖定問題。如需詳細資訊，請參閱 PostgreSQL 網站上的 pg_stat_activity 和 pg_stat_statements。

若要啟用 pg_stat_statements，請完成下列步驟：

1. 修改現有的自訂資料庫參數群組。

2. 將 pg_stat_statements 新增至 shared_preload_libraries。

3. 將 track_activity_query_size 設定為 4096。

4. 將 pg_stat_statements.track 設定為 ALL。

5. 將 pg_stat_statements.max 設定為 10000。

6. 選擇 Apply Immediately (立即套用)，然後重新啟動資料庫執行個體。

7. 在要監控的資料庫上，執行以下命令：

   demo=> select current_database();current_database------------------  
   demo  
   (1 row)  
   
   demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

   注意：上面的命令會將擴充功能安裝在示範資料庫中。

設定 pg_stat_statements 後，請使用下列其中一種方法來監控輸出。您可以查看在資料庫中執行時間最長、快取命中率較低，或是每次執行成本較高的查詢。

若要查看哪些查詢在資料庫中花費的時間最長，請根據您的 PostgreSQL 版本執行下列查詢。

PostgreSQL 版本 12 及更舊版本：

SELECT total_time, queryFROM pg_stat_statements  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL 版本 13 及更新版本：

SELECT total_plan_time+total_exec_time as total_time, queryFROM 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_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_percentFROM 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_writtenFROM 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。當您的執行個體的 CPU 使用率偏高時，請檢查資料庫上是否有閒置連線。如需詳細資訊，請參閱閒置 PostgreSQL 連線對效能的影響。

您可以使用增強型監控來查看作業系統程序清單中的閒置連線。但是，開清單最多只顯示 100 個程序。若要檢查資料庫層級的閒置連線，請執行下列查詢。

查看目前閒置和活動的工作階段：

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activityWHERE query != '<IDLE>  
'AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin'  
ORDER BY query_start desc;  
SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query  
FROM pg_stat_activity  
WHERE not pid=pg_backend_pid()  
AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin';

取得每個使用者和應用程式名稱的連線數：

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;    application_name    | count   
------------------------+-------  
 psql                   |     1  
 PostgreSQL JDBC Driver |     1   
                        |     5  
(3 rows)  
postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;  
 usename  | count  
----------+-------  
 master   |     4   
 user1    |     1  
 rdsadmin |     2  
(3 rows)

確定閒置連線後，執行下列其中一個查詢來結束連線：

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

-或-

SELECT pg_terminate_backend (example-pid);

如果您的應用程式導致過多的連線，請修改應用程式，以避免將記憶體和 CPU 資源用於管理這些連線。您可以限制連線數，或使用連線集區 (例如 PgBouncer)。您也可以使用 Amazon RDS Proxy 來設定連線集區。

執行 ANALYZE 命令

ANALYZE 命令會收集資料庫中資料表內容的統計資料，並將結果儲存在 pg_statistic 系統目錄中。然後，查詢規劃器會使用統計資料來幫助確定最有效率的查詢執行計劃。當您不常對資料庫中的資料表執行 ANALYZE 時，系統中的統計資料可能會過時，導致查詢使用更多的運算資源。

統計資料過時的原因如下：

• 不頻繁執行自動清理。
• 主要版本升級後，您沒有執行 ANALYZE 作業。

自動清理會檢查資料庫中是否有膨脹的資料表，並恢復空間以供重複使用。為了確保資料表統計資料定期更新，當設定的元組閾值失效時，自動清理常駐程式會執行 ANALYZE 命令。

如需詳細資訊，請參閱以下資源：

• 了解 Amazon RDS for PostgreSQL 環境中的自動清理
• Amazon RDS for PostgreSQL 中自動清理調整案例研究

若要了解自動清理和自動分析最後一次在資料表上執行的時間，請執行以下查詢：

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

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

為了避免因資源使用率過高而導致效能問題，請重新產生所有統計資料。若要在主要版本升級後，為目前資料庫中的所有一般資料表產生統計資料，請執行下列命令 (不含參數)：

ANALYZE VERBOSE

檢查 PostgreSQL 錯誤日誌

使用 Amazon RDS 為 PostgreSQL 啟用查詢記錄。然後，檢查 PostgreSQL 錯誤日誌，確認您將 log_min_duration_statement 和 log_statement 參數設定為適當的值。如需詳細資訊，請參閱 PostgreSQL 網站上的錯誤報告和記錄。

降低 CPU 使用率

找出造成高 CPU 的查詢後，請使用以下方法進一步降低 CPU 使用率：

• 使用 EXPLAIN 和 EXPLAIN ANALYZE 來決定調整查詢計畫的方法。如需詳細資訊，請參閱 PostgreSQL 網站上的使用 EXPLAIN。
• 如果有查詢反覆執行，請使用預備陳述式來減輕 CPU 的壓力。反覆執行的預備陳述式會快取查詢計劃。當計劃已在快取中供後續執行時，規劃查詢所需的時間會減少。

相關資訊

使用 PostgreSQL 的最佳做法