PostgreSQL / 系统课时

日志与统计扩展

日志与统计扩展

本节把 PostgreSQL 18 的慢查询日志与 pg_stat_statements 组合成一套可验证的观测基线,并补齐 I/O 计时。完成后,你能从日志定位单次慢 SQL,也能从统计视图找出累计最耗时的语句。

学习目标

  • 配置并验证慢查询日志,控制日志量而不是记录所有 SQL。
  • 正确加载 pg_stat_statements,按累计时间、调用次数和 I/O 指标定位热点。
  • 启用必要的统计采集,并理解重启、重载与统计重置的边界。

检查扩展与当前配置

先确认服务器能找到 pg_stat_statements,再查看关键参数。若查询不到扩展,说明当前 PostgreSQL 安装缺少对应扩展文件,应先用发行版的软件包补齐,而不是直接修改预加载配置。

sql
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name = 'pg_stat_statements';

SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name IN (
  'logging_collector',
  'log_min_duration_statement',
  'shared_preload_libraries',
  'track_io_timing',
  'track_wal_io_timing'
)
ORDER BY name;

预期结果

text
        name        | default_version | installed_version
--------------------+-----------------+-------------------
 pg_stat_statements | <VALUE>         | <VALUE>

             name              | setting | unit | pending_restart
-------------------------------+---------+------+----------------
 log_min_duration_statement    | <VALUE> | ms   | f
 logging_collector             | <VALUE> |      | <VALUE>
 shared_preload_libraries      | <VALUE> |      | <VALUE>
 track_io_timing               | <VALUE> |      | f
 track_wal_io_timing           | <VALUE> |      | f

<VALUE> 取决于当前实例。重点是第一条查询必须返回 pg_stat_statements;若 shared_preload_libraries 已有其他模块,后续配置必须保留它们。

配置慢查询与统计采集

实验环境可以把 500 ms 作为起始慢查询阈值。log_min_duration_statement 只记录达到阈值的已完成语句,比 log_statement = 'all' 更适合长期观察。以下是需要写入 postgresql.conf 的配置片段。

text
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_min_duration_statement = 500
log_lock_waits = on
deadlock_timeout = '1s'

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'
pg_stat_statements.save = on

track_io_timing = on
track_wal_io_timing = on

如果现有 shared_preload_libraries 不是空值,应把 pg_stat_statements 合并进逗号分隔列表,不能覆盖已有模块。shared_preload_libraries 与 logging_collector 需要重启实例;只执行配置重载不足以让它们生效。

用 SHOW data_directory 找到数据目录后重启实例。下面使用 PostgreSQL 自带的 pg_ctl,实际二进制路径和数据目录可能不同。

bash
DATA_DIR="$(psql -X -d postgres -Atc 'show data_directory')"
pg_ctl restart -D "$DATA_DIR" -l "$DATA_DIR/server.log"

预期输出

text
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

某些平台的提示文字略有差异,但命令应成功完成并重新接受连接。

创建扩展并验证慢日志

共享库加载后,在需要分析的数据库中创建扩展。扩展是数据库级对象,因此切换到另一个数据库时要单独创建。

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

SELECT pg_sleep(0.6);

预期结果

text
CREATE EXTENSION
      extname       | extversion
--------------------+-----------
 pg_stat_statements | <VALUE>

 pg_sleep
----------

当阈值为 500 ms 时,最后一条语句会在日志中出现一条约 600 ms 的 duration 记录,并包含 SELECT pg_sleep(0.6)。日志文件位于数据目录下的 log 目录;时间戳和文件名属于动态值。

用 pg_stat_statements 找累计热点

慢日志回答“哪一次执行很慢”,pg_stat_statements 更适合回答“哪类 SQL 累计最贵”。先重置实验统计,再执行两次结构相同但参数不同的查询,最后查看归一化后的语句记录。

sql
SELECT pg_stat_statements_reset();

SELECT sum(i)
FROM generate_series(1, 200000) AS g(i);

SELECT sum(i)
FROM generate_series(1, 300000) AS g(i);

SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows,
  query
FROM pg_stat_statements
WHERE query LIKE 'SELECT sum(i)%generate_series%'
ORDER BY total_exec_time DESC;

预期结果

text
 pg_stat_statements_reset
--------------------------

(1 row)
     sum
-------------
 20000100000

     sum
-------------
 45000150000

 calls | total_ms | mean_ms | rows |                         query
-------+----------+---------+------+-------------------------------------------------------
     2 | <VALUE>  | <VALUE> |    2 | SELECT sum(i) FROM generate_series($1, $2) AS g(i)

总耗时会随硬件变化,因此用 <VALUE> 表示。相同结构的 SQL 会按归一化查询聚合,calls、total_exec_time、mean_exec_time 能区分“偶发慢”与“高频累计慢”;启用 I/O 计时后,还可以结合块读取与 I/O 时间继续判断瓶颈是否来自存储。

统计视图是累计数据,不应直接把实例启动以来的总量当成一次实验结果。做基准测试前先记录快照或调用 pg_stat_statements_reset(),测试结束后再读取同一组指标,才能得到可比较的观测窗口。

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

目录与阅读设置

阅读设置

17

记录一个想法

日志与统计扩展

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

登录哪都通