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

簡短描述

如果您了解負載的高 CPU 使用量，請使用下列工具組合找出原因：

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

解決方案

Amazon CloudWatch 指標

使用 CloudWatch 指標以找出長期的 CPU 模式。比較 WriteIOPs、ReadIOPs、ReadThroughput 及 WriteThroughput 圖表與 CPU 使用率，以找出工作負載何時會導致高 CPU 使用率。

找出時間範圍之後，請檢閱與資料庫執行個體相關聯的增強型監控資料。您可以設定增強型監控，以 1、5、10、15、30 或 60 秒的間隔收集資料。然後，您可以使用比 CloudWatch 更精細的層級收集資料。

增強型監控

增強型監控提供作業系統 (OS) 層級的檢視。此檢視可以協助在精細層級找出高 CPU 負載的原因。例如，您可以檢閱負載平均值、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
Nice (%)	93.92
總計 (%)	99.97

**注意事項：**在增強型監控中，Nice% 代表您的工作負載對資料庫使用的 CPU 數量。

開啟增強型監控後，您也可以檢查與資料庫執行個體相關聯的作業系統處理程序清單。增強型監控最多可顯示 100 個處理程序，並可協助您找出對效能影響最大的處理程序。您可以將增強型監控結果與 pg_stat_activity 結果合併，以協助找出查詢的資源使用量。

效能洞察

使用 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，請修改現有的自訂參數群組並設定下列值：

• 將 pg_stat_statements 新增至 shared_preload_libraries
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

選擇立即套用，然後重新啟動資料庫執行個體。然後，在您要監控的資料庫上執行類似於下列項目的命令：

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

設定 pg_stat_statements 之後，使用下列其中一種方法來監控輸出。

若要依 total_time 列出查詢，並查看哪個查詢在資料庫中花費最多時間，請執行下列其中一個查詢：

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 第 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 第 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。當執行個體具有高 CPU 使用率時，請檢查資料庫上的閒置連線。如需詳細資訊，請參閱閒置 PostgreSQL 連線的效能影響。若要檢查閒置連線，請使用增強型監控以檢閱作業系統處理程序清單。但是，此清單最多會顯示 100 個處理程序。

若要檢查閒置連線，請在資料庫層級執行下列查詢。

執行下列查詢以檢視閒置和作用中的目前工作階段：

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE 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，則查詢可能會消耗更多的運算資源。由於存取關係的系統中會出現過時的統計資料，查詢會消耗更多資源。在下列情況下會發生這些問題：

• Autovacuum 不經常執行。
• ANALYZE 未在主要版本升級後執行。

**Autovacuum 不經常執行：**Autovacuum 是自動執行 VACUUM 和 ANALYZE 命令的常駐程式。Autovacuum 會檢查資料庫中是否有膨脹的表格，並恢復空間以供重複使用。當元組的設定臨界值失效時，Autovacuum 常駐程式會透過執行 ANALYZE 操作來確保定期更新表格統計資料。然後，查詢規劃器可以根據最近的統計資料使用最有效率的查詢計畫。如果 Autovacuum 未執行，則查詢規劃器可能會建立次佳查詢計畫，並導致查詢消耗更多的資源。如需詳細資訊，請參閱下列資源：

• 了解 Amazon RDS for PostgreSQL 環境中的 Autovacuum
• 在 Amazon RDS for PostgreSQL 中 調整 Autovacuum 的案例研究

若要取得最後一次在表格上執行 autovacuum 和 autoanalyze 的資訊，請執行下列查詢：

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

**ANALYZE 未在主要版本升級後執行：**PostgreSQL 資料庫通常會在任何主要引擎版本升級後遇到效能問題。這些問題的常見原因是在升級後未執行 ANALYZE 操作以重新整理 pg_statistic 表格。在 RDS for PostgreSQL 資料庫執行個體中的每個資料庫執行 ANALYZE 操作。在主要版本升級期間不會傳輸最佳化程式統計資料。因此，為了避免因高資源使用率而導致效能問題，請重新產生所有統計資料。

若要在主要版本升級後，為目前資料庫中的所有一般表格產生統計資料，請執行下列命令 (不含任何參數)：

ANALYZE VERBOSE

PostgreSQL 記錄參數

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

降低 CPU 使用量

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

• 若要尋找調整的機會，請使用 EXPLAIN 和 EXPLAIN ANALYZE 識別警示。如需詳細資訊，請參閱 PostgreSQL 網站上的 EXPLAIN。
• 如果有查詢重複執行，請使用準備好的陳述式來減輕 CPU 的壓力。重複執行準備好的陳述式即可快取查詢計畫。由於計畫已經存在於快取中以供進一步執行，因此計畫的時間會縮短得多。

相關資訊

使用 PostgreSQL 的最佳實務