PostgreSQL / 系统课时

会话与 SQL 热点监控

会话与 SQL 热点监控

性能监控不能只看“当前最慢的一条 SQL”。更稳定的做法是同时观察活动会话、数据库级负载和归一化 SQL 的累计统计,从而区分瞬时阻塞、持续高频调用与少量超慢语句。

学习目标

  • 用 pg_stat_activity 找到长时间运行、阻塞或异常状态的会话。
  • 用 pg_stat_database 判断连接、事务、缓存与临时文件是否出现整体压力。
  • 用 pg_stat_statements 按总耗时、平均耗时和调用次数识别 SQL 热点。

先看当前会话是否异常

pg_stat_activity 适合回答“现在谁在跑、跑了多久、在等什么”。长事务、长查询和 idle in transaction 都值得优先检查,因为它们可能持续占用锁或阻碍 VACUUM 清理旧版本。

sql
SELECT pid,
       usename,
       state,
       clock_timestamp() - xact_start AS xact_age,
       clock_timestamp() - query_start AS query_age,
       wait_event_type,
       wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state <> 'idle'
ORDER BY query_start NULLS LAST;

预期结果

text
 pid  | usename | state               | xact_age | query_age | wait_event_type | wait_event | query
<ID>  | app     | active              | <TIME>   | <TIME>    | IO              | <VALUE>    | SELECT ...
<ID>  | app     | idle in transaction | <TIME>   | <TIME>    | Client          | ClientRead | <VALUE>

PID、时长和 SQL 文本会随现场变化。idle in transaction 不是“没事做”,它仍可能持有事务快照或锁;对这类会话应先确认业务逻辑,再决定是否终止。

用数据库级统计判断是不是全局压力

pg_stat_database 提供每个数据库的事务、元组、块命中和临时文件累计统计。它不负责告诉你具体哪条 SQL 有问题,但非常适合判断“问题是单条语句还是整个数据库都在变重”。

sql
SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_read,
       blks_hit,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database
WHERE datname = current_database();

预期结果

text
 datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | temp_files | temp_written
<VALUE>  | <VALUE>     | <VALUE>     | <VALUE>       | <VALUE>   | <VALUE>  | <VALUE>    | <VALUE>

这些列多数是累计值,单次快照只能看规模,不能直接代表“每秒”。生产监控应以固定间隔采样后计算增量;若 temp_files 和 temp_bytes 快速增长,常见原因是排序、哈希或聚合超出可用工作内存。

用 pg_stat_statements 找真正的 SQL 热点

pg_stat_statements 会把结构相同、常量不同的 SQL 归一化后累计调用次数和执行耗时。它需要预先加载扩展;若视图不存在,先检查 shared_preload_libraries 是否包含 pg_stat_statements,重启实例后再执行 CREATE EXTENSION pg_stat_statements;。

sql
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_written,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

预期结果

text
 queryid | calls   | total_ms | mean_ms | rows    | shared_blks_hit | shared_blks_read | temp_blks_written | query
<ID>     | <VALUE> | <VALUE>  | <VALUE> | <VALUE> | <VALUE>         | <VALUE>          | <VALUE>           | SELECT ...

按 total_exec_time 排序能找到“最耗总资源”的 SQL;按 mean_exec_time 更容易发现单次很慢的 SQL;按 calls 可识别极高频的小查询。三种排名要结合看,否则容易只优化一条看起来慢、但对总负载贡献很小的语句。

把热点变成可执行的排查顺序

先用 pg_stat_activity 处理正在发生的长事务、阻塞和异常等待,再用 pg_stat_database 判断是否存在全局缓存或临时文件压力,最后用 pg_stat_statements 找长期贡献最大的 SQL。定位到具体语句后,再进入 EXPLAIN (ANALYZE, BUFFERS)、索引和参数层面的优化。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

会话与 SQL 热点监控

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通