PostgreSQL / 系统课时
用 pg_stat_statements 找入口
用 pg_stat_statements 找入口
慢查询排障最怕从一条偶发日志开始猜。pg_stat_statements 能把同类 SQL 聚合成稳定统计入口,本课用总耗时、均值、调用次数和波动筛出值得优先分析的语句,并保存可重复对比的查询标识与性能基线。
学习目标
- 用总耗时、均值、调用次数和波动筛选高价值 SQL
- 正确使用
dbid、userid、toplevel、queryid标识统计项 - 通过两次采样建立可比较的区间性能基线
先确认统计入口可用
pg_stat_statements 需要在实例侧预加载,并在目标数据库中安装扩展。先做只读检查,不要一上来重置统计;一旦执行重置,之前的累计基线就不能继续直接比较。
SELECT
current_setting('shared_preload_libraries') AS shared_preload_libraries,
EXISTS (
SELECT 1
FROM pg_extension
WHERE extname = 'pg_stat_statements'
) AS extension_installed;预期输出:
shared_preload_libraries | extension_installed
--------------------------+--------------------
<VALUE> | t实际的预加载列表会因实例而异,但其中应包含 pg_stat_statements,并且 extension_installed 应为 t。若未满足,应由具备权限的管理员完成配置、重启和 CREATE EXTENSION,再开始采样。
用四个维度排出优先级
总耗时回答“谁消耗了最多数据库时间”,均值回答“单次是否昂贵”,调用次数回答“是否高频”,标准差则帮助发现同一语句的延迟波动。不要只按单次最慢排序,否则很容易被偶发样本带偏。
SELECT
dbid,
userid,
toplevel,
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
round(
(stddev_exec_time / NULLIF(mean_exec_time, 0))::numeric,
2
) AS cv,
rows,
left(query, 100) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY total_exec_time DESC
LIMIT 10;预期输出:
dbid | userid | toplevel | queryid | calls | total_ms | mean_ms | stddev_ms | cv | rows | query_sample
------+--------+----------+---------+---------+----------+---------+-----------+---------+---------+----------------
<ID> | <ID> | t | <ID> | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <SQL>这里的 calls >= 20 只是降噪示例,应按业务量调整。cv 是标准差与均值的比值,适合在同一工作负载内比较波动;均值很低时它会被放大,因此不能脱离 mean_ms 单独判断。
固定查询身份并建立区间基线
queryid 不是永久业务编号。更稳妥的做法是在同一统计周期内连同 dbid、userid 和 toplevel 一起保存,并记录采样时间。升级大版本、统计被重置或条目被淘汰后,应重新建立基线。
SELECT
now() AS captured_at,
dbid,
userid,
toplevel,
queryid,
calls,
total_exec_time,
mean_exec_time,
stddev_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;预期输出:
captured_at | dbid | userid | toplevel | queryid | calls | total_exec_time | mean_exec_time | stddev_exec_time | rows
-------------+------+--------+----------+---------+---------+-----------------+----------------+------------------+---------
<TIME> | <ID> | <ID> | t | <ID> | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <VALUE>对同一复合标识在 T0、T1 两次采样后,用 Δtotal_exec_time / Δcalls 计算区间平均执行时间,比直接拿累计均值做前后对比更可靠。若 Δcalls 为 0,则该区间没有新的执行样本,不应计算区间均值。
把入口变成可复查的排障起点
优先处理通常落在三类:累计耗时高的高频 SQL、均值高的昂贵 SQL、标准差明显偏高的波动 SQL。确定目标后再进入 EXPLAIN (ANALYZE, BUFFERS)、统计信息和索引检查,而不是直接从配置参数开始试错。
生产上不要为了“方便比较”随意执行 pg_stat_statements_reset()。保留采样时间、复合查询标识和区间指标,才能让优化前后使用同一把尺子。