PostgreSQL / 系统课时
会话与 SQL 热点监控
会话与 SQL 热点监控
性能监控不能只看“当前最慢的一条 SQL”。更稳定的做法是同时观察活动会话、数据库级负载和归一化 SQL 的累计统计,从而区分瞬时阻塞、持续高频调用与少量超慢语句。
学习目标
- 用
pg_stat_activity找到长时间运行、阻塞或异常状态的会话。 - 用
pg_stat_database判断连接、事务、缓存与临时文件是否出现整体压力。 - 用
pg_stat_statements按总耗时、平均耗时和调用次数识别 SQL 热点。
先看当前会话是否异常
pg_stat_activity 适合回答“现在谁在跑、跑了多久、在等什么”。长事务、长查询和 idle in transaction 都值得优先检查,因为它们可能持续占用锁或阻碍 VACUUM 清理旧版本。
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;预期结果
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 有问题,但非常适合判断“问题是单条语句还是整个数据库都在变重”。
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();预期结果
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;。
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;预期结果
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)、索引和参数层面的优化。