监控 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.num_dead_tuples / p.max_dead_tuples,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_memtemp_buffers 大小的有效性

如需确定 work_memtemp_buffers 的大小是否符合您的需求,您可以查询 pg_stat_database 视图并检查 postgres.log 文件。使用 pg_stat_database 执行以下查询,如果 temp_filestemp_bytes 在两次执行之间有任何增长,则可能需要对 work_memtemp_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_memtemp_buffers 时,需要在宿主机上的可用内存与需要内存的连接数量之间取得平衡。若要正确设置这些参数,需要了解每个具体工作负载。