監控 AlloyDB Omni 資料庫效能

本頁面說明如何使用 PostgreSQL 觀測能力指令碼，監控 AlloyDB Omni 資料庫效能。

查看已連結程序和等待事件的狀態

您可以查詢 pg_stat_activity 檢視表，判斷與 AlloyDB Omni 執行個體連結的任何程序，以及等待活動的任何後端。

SELECT
    pid,
    datname,
    age(backend_xid) AS age_in_xids,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    state,
    wait_event_type,
    wait_event,
    query_id,
    query
FROM
    pg_stat_activity
WHERE
    state != 'idle'
    AND pid <> pg_backend_pid()
ORDER BY
    4 DESC
LIMIT 10;

查看最大資料表

您可以查詢 pg_stat_user_tables 檢視表，判斷最大資料表的大小。

SELECT
    oid,
    oid::regclass table_name,
    pg_size_pretty(pg_relation_size(oid)),
    relpages,
    s.seq_scan,
    s.idx_scan
FROM
    pg_class,
    pg_stat_user_tables s
WHERE
    s.relid = oid
    AND oid > 16383
    AND relpages > 100
    AND relkind = 'r'
ORDER BY
    relpages DESC
LIMIT 20;

查看熱門依序掃描

您可以查詢 pg_stat_user_tables 檢視表，查看熱門依序掃描。

SELECT
    relid,
    relname,
    seq_scan,
    pg_size_pretty(pg_relation_size(relid))
FROM
    pg_stat_user_tables
ORDER BY
    seq_scan DESC
LIMIT 15;

查看主要索引掃描

您可以查詢 pg_stat_user_tables 檢視畫面，查看熱門索引掃描。

SELECT
    relid,
    relid::regclass table_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(relid))
FROM
    pg_stat_user_tables
WHERE
    idx_scan > 10
ORDER BY
    idx_scan DESC
LIMIT 15;

查看執行時間最長的交易

您可以查詢 pg_stat_activity 檢視表並檢查交易存在時間，查看執行時間最長的交易。

SELECT
    pid,
    age(backend_xid) AS age_in_xids,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    state,
    query
FROM
    pg_stat_activity
WHERE
    state != 'idle'
ORDER BY
    2 DESC
LIMIT 10;

查看吸塵進度

如要查看清理作業的進度，請查詢 pg_stat_progress_vacuum 檢視表，並使用程序 ID 將其與 pg_stat_activity 檢視表彙整。

SELECT
  p.pid,
  now() - a.xact_start AS duration,
  coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
  CASE
    WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
    WHEN a.query ~*'^vacuum' THEN 'user'
  ELSE
    'regular'
  END AS mode,
  p.datname AS database,
  p.relid::regclass AS table,
  p.phase,
  pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
  pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
  round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
  round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
  p.index_vacuum_count,
  round(100.0 * p.dead_tuple_bytes / p.max_dead_tuple_bytes,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;

查看非同步查詢

如要查看以非同步方式執行的查詢，您可以查詢 pg_stat_activity 檢視表，並篩選出非領導者程序的查詢。

SELECT
    query,
    leader_pid,
    array_agg(pid) FILTER (WHERE leader_pid != pid) AS members
FROM
    pg_stat_activity
WHERE
    leader_pid IS NOT NULL
GROUP BY
    query,
    leader_pid;

查看封鎖鎖定的 SQL

您可以查詢 pg_locks 檢視表，並將其與 pg_stat_activity 檢視表彙整，查看遭封鎖的活動。

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocked_activity.wait_event AS blocked_wait_event,
       blocking_activity.wait_event AS blocking_wait_event,
       blocking_activity.query   AS current_statement_in_blocking_process
 FROM  pg_catalog.pg_locks         blocked_locks
  JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
  JOIN pg_catalog.pg_locks         blocking_locks
      ON blocking_locks.locktype = blocked_locks.locktype
      AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
      AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
      AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
      AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
      AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
      AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
      AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
      AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
      AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
      AND blocking_locks.pid != blocked_locks.pid
  JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.granted;

判斷`work_mem`和`temp_buffers`大小的成效

如要判斷 work_mem 和 temp_buffers 的大小是否符合需求，您可以查詢 pg_stat_database 檢視表，並查看 postgres.log 檔案。使用 pg_stat_database 執行下列查詢，如果執行作業之間的 temp_files 或 temp_bytes 有任何成長，則可能需要調整 work_mem 或 temp_buffers。

SELECT
    datname,
    temp_files,
    temp_bytes
FROM
    pg_stat_database;

執行後，請檢查 postgres.log 檔案，確認是否使用了暫存檔案：

LOG: [fd.c:1772] temporary file: path "base/pgsql_tmp/pgsql_tmp4640.1", size 139264

目標是盡可能減少建立暫存檔案，而非完全避免。這是因為設定 work_mem 和 temp_buffers 時，必須在主機上的可用記憶體和需要記憶體的連線數量之間取得平衡。如要正確設定這些參數，必須瞭解各項工作負載。

使用查詢洞察查看查詢效能

您可以使用 AlloyDB Omni 網頁使用者介面中的查詢洞察，找出速度緩慢的查詢和高磁碟 I/O 陳述式。如要使用查詢洞察，必須在資料庫啟用 pg_stat_statements 擴充功能。如需設定操作說明，請參閱「安裝網頁版 UI」。

如要在網頁 UI 中查看查詢成效指標，請完成下列步驟：

連線至網頁 UI，然後選取叢集。
在導覽選單中，點選「查詢洞察」。
選用：使用「資料庫」清單，依特定資料庫篩選陳述式，或選取「所有資料庫」。
查看「查詢」資料表，分析 SQL 執行統計資料。每一列都會顯示下列指標，以及該列下方的完整 SQL 陳述式：
- 資料庫名稱：執行陳述式的邏輯資料庫。
- 呼叫：陳述式的執行總次數。
- 資料列：陳述式擷取或影響的資料列總數。
- Execution time: mean：每次呼叫的平均執行時間。
- Execution time: total：所有呼叫的累計執行時間。
- 共用區塊命中次數：共用緩衝區快取命中總次數。
- Shared block read：從磁碟讀取的共用區塊總數。
- 查詢類型：將查詢效能影響分類，例如 Slow query 或 High disk IO。