盡可能改善執行個體 CPU 用量偏高的情形

高 CPU 使用率會對執行個體的效能造成負面影響。在執行個體上執行的任何活動都會使用 CPU。因此，如果出現 CPU 使用率過高的通知，請先找出問題的根本原因，可能是撰寫不佳的查詢、執行時間過長的交易，或是任何其他資料庫活動。

本文件說明如何找出執行個體中的 CPU 瓶頸，並減輕執行個體的 CPU 使用率問題。

找出 CPU 瓶頸

以下各節將討論不同的 CPU 情況。

運用查詢洞察功能找出 CPU 耗用量偏高的查詢

查詢洞察可協助您偵測、診斷及預防 Cloud SQL 資料庫的查詢效能問題。

使用 pg_proctab 擴充功能

使用 pg_proctab 擴充功能搭配 pg_top 公用程式，即可取得作業系統輸出內容，瞭解每個程序的 CPU 使用率資訊。

使用查詢

以下各節將說明您可以使用的不同查詢。

依狀態找出有效連線

每個與資料庫的有效連線都會占用一定量的 CPU，因此如果執行個體的連線數量偏高，累積使用率可能會偏高。使用下列查詢，取得狀態連線數量的相關資訊。

SELECT 
  state, 
  usename, 
  count(1) 
FROM 
  pg_stat_activity 
WHERE 
  pid <> pg_backend_pid() 
group by 
  state, 
  usename 
order by 
  1;

輸出看起來類似以下內容：


        state        |    usename    | count 
---------------------+---------------+-------
 active              | ltest         |   318
 active              | sbtest        |    95
 active              |               |     2
 idle                | cloudsqladmin |     2
 idle in transaction | ltest         |    32
 idle in transaction | sbtest        |     5
                     | cloudsqladmin |     3
                     |               |     4
(8 rows)

如果有效連線數量偏高，請檢查是否有長時間執行的查詢或等待事件，導致查詢無法執行。

如果閒置連線數量偏高，請在取得必要核准後，執行下列查詢來終止連線。

SELECT 
  pg_terminate_backend(pid) 
FROM 
  pg_stat_activity 
WHERE 
  usename = 'sbtest' 
  and pid <> pg_backend_pid() 
  and state in ('idle');

您也可以使用下列查詢，透過 pg_terminate_backend 個別終止連線：

SELECT pg_terminate_backend (<pid>);

您可以從 pg_stat_activity 取得 PID。

找出執行時間過長的連線

以下是傳回長時間執行查詢的查詢範例。在這種情況下，您可以找出已進行 5 分鐘以上的查詢。

SELECT 
  pid, 
  query_start, 
  xact_start, 
  now() - pg_stat_activity.query_start AS duration, 
  query, 
  state 
FROM 
  pg_stat_activity 
WHERE 
  (
    now() - pg_stat_activity.query_start
  ) > interval '5 minutes' order by 4 desc;

查看說明計畫，找出撰寫不佳的查詢

使用 EXPLAIN PLAN 調查撰寫不佳的查詢，並視需要改寫查詢。您也可以選擇使用下列指令取消長時間執行的查詢，並取得必要的核准。

SELECT pg_cancel_backend(<pid>);

監控 VACUUM 活動

清除無效元組的 AUTOVACUUM 活動需要大量 CPU 資源。如果您的執行個體使用 PostgreSQL 11 以上版本，請使用下列查詢，檢查是否有任何正在進行的 AUTOVACUUM 或 VACUUM 活動。

SELECT 
  relid :: regclass, 
  pid, 
  phase, 
  heap_blks_total, 
  heap_blks_scanned, 
  heap_blks_vacuumed, 
  index_vacuum_count, 
  max_dead_tuples, 
  num_dead_tuples 
FROM 
  pg_stat_progress_vacuum;

使用下列查詢，檢查執行個體中是否有持續進行的 VACUUM 活動：

SELECT 
  pid, 
  datname,
  usename, 
  query 
FROM 
  pg_stat_activity 
WHERE 
  query like '%vacuum%';

此外，您也可以在 PostgreSQL 中最佳化及排解 VACUUM 作業的問題。

新增 pg_stat_statements 擴充功能

設定 pg_stat_statements 擴充功能，取得有關執行個體活動的強化字典資訊。

頻繁的查核點

頻繁的檢查點會降低效能。如果 PostgreSQL 警示記錄顯示 checkpoint occurring too frequently 警告，請考慮調整 checkpoint_timeout 標記。

收集統計資料

請確認查詢規劃工具具有最新的資料表統計資料，以便選擇最適合查詢的規劃。ANALYZE 作業會收集資料庫中資料表內容的統計資料，並將結果儲存在 pg_statistic 系統目錄中。接著，查詢規劃器會使用這些統計資料，協助判斷查詢的最佳執行計畫。AUTOVACUUM 程序會自動定期分析資料表，因此請執行下列指令，確認所有資料表都已分析，並提供最新的中繼資料給規劃工具。

SELECT 
  relname, 
  last_autovacuum, 
  last_autoanalyze 
FROM 
  pg_stat_user_tables;

系統設定不足

其他因素、標記設定或系統因素也會影響查詢效能。執行下列查詢，檢查等待事件和等待事件類型，以便深入瞭解其他系統設定的效能。

SELECT 
  datname, 
  usename, 
  (
    case when usename is not null then state else query end
  ) AS what, 
  wait_event_type, 
  wait_event, 
  backend_type, 
  count(*) 
FROM 
  pg_stat_activity 
GROUP BY 
  1, 
  2, 
  3, 
  4, 
  5, 
  6 
ORDER BY 
  1, 
  2, 
  3, 
  4 nulls first, 
  5, 
  6;

輸出結果看起來與下列內容相似：

  
 ..  | .. | what           | wait_event_type |      wait_event      | ..    | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
 ..
 ..  | .. | active         | IO              | CommitWaitFlush      | ..    |   750
 ..  | .. | idle           | IO              | CommitWaitFlush      | ..    |   360
 ..  | .. | active         | LWLock          | BufferMapping        | ..    |   191

監控順序掃描

資料表中資料列超過數十個時，如果經常執行順序掃描，通常表示缺少索引。掃描作業涉及數千或數十萬個資料列時，可能會導致 CPU 用量過高。

針對有數十萬個資料列的表格執行頻繁的順序掃描作業，可能會導致 CPU 用量過高。建立必要的索引，避免對這類資料表進行順序掃描。

執行以下查詢，檢查在任何資料表上啟動序列掃描的次數。

SELECT 
  relname, 
  idx_scan,  
  seq_scan, 
  n_live_tup 
FROM 
  pg_stat_user_tables 
WHERE 
  seq_scan > 0 
ORDER BY 
  n_live_tup desc;

最後，如果 CPU 仍處於高使用率狀態，且您認為這些查詢是正常流量，建議您考慮增加執行個體的 CPU 資源，以免資料庫發生當機或停機情形。

後續步驟