盡可能改善執行個體 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 資源，以免資料庫當機或停機。

後續步驟