PostgreSQL / 系统课时

用 pg_stat_statements 找入口

用 pg_stat_statements 找入口

慢查询排障最怕从一条偶发日志开始猜。pg_stat_statements 能把同类 SQL 聚合成稳定统计入口,本课用总耗时、均值、调用次数和波动筛出值得优先分析的语句,并保存可重复对比的查询标识与性能基线。

学习目标

  • 用总耗时、均值、调用次数和波动筛选高价值 SQL
  • 正确使用 dbid、userid、toplevel、queryid 标识统计项
  • 通过两次采样建立可比较的区间性能基线

先确认统计入口可用

pg_stat_statements 需要在实例侧预加载,并在目标数据库中安装扩展。先做只读检查,不要一上来重置统计;一旦执行重置,之前的累计基线就不能继续直接比较。

sql
SELECT
    current_setting('shared_preload_libraries') AS shared_preload_libraries,
    EXISTS (
        SELECT 1
        FROM pg_extension
        WHERE extname = 'pg_stat_statements'
    ) AS extension_installed;

预期输出:

text
 shared_preload_libraries | extension_installed
--------------------------+--------------------
 <VALUE>                  | t

实际的预加载列表会因实例而异,但其中应包含 pg_stat_statements,并且 extension_installed 应为 t。若未满足,应由具备权限的管理员完成配置、重启和 CREATE EXTENSION,再开始采样。

用四个维度排出优先级

总耗时回答“谁消耗了最多数据库时间”,均值回答“单次是否昂贵”,调用次数回答“是否高频”,标准差则帮助发现同一语句的延迟波动。不要只按单次最慢排序,否则很容易被偶发样本带偏。

sql
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;

预期输出:

text
 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 一起保存,并记录采样时间。升级大版本、统计被重置或条目被淘汰后,应重新建立基线。

sql
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;

预期输出:

text
 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()。保留采样时间、复合查询标识和区间指标,才能让优化前后使用同一把尺子。

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

目录与阅读设置

阅读设置

17

记录一个想法

用 pg_stat_statements 找入口

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

登录哪都通