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

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

本文說明如何找出執行個體中的 CPU 瓶頸,以及如何緩解執行個體中的 CPU 使用率問題。

找出 CPU 瓶頸

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

使用查詢洞察找出 CPU 耗用量高的查詢

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

使用 pg_proctab 擴充功能

搭配使用 pg_top 公用程式和 pg_proctab 擴充功能,即可取得作業系統輸出內容,其中包含每個程序的 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 資源,以免資料庫當機或停機。

後續步驟