PostgreSQL / 系统课时
日志与统计扩展
日志与统计扩展
本节把 PostgreSQL 18 的慢查询日志与 pg_stat_statements 组合成一套可验证的观测基线,并补齐 I/O 计时。完成后,你能从日志定位单次慢 SQL,也能从统计视图找出累计最耗时的语句。
学习目标
- 配置并验证慢查询日志,控制日志量而不是记录所有 SQL。
- 正确加载
pg_stat_statements,按累计时间、调用次数和 I/O 指标定位热点。 - 启用必要的统计采集,并理解重启、重载与统计重置的边界。
检查扩展与当前配置
先确认服务器能找到 pg_stat_statements,再查看关键参数。若查询不到扩展,说明当前 PostgreSQL 安装缺少对应扩展文件,应先用发行版的软件包补齐,而不是直接修改预加载配置。
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;预期结果
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 的配置片段。
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,实际二进制路径和数据目录可能不同。
DATA_DIR="$(psql -X -d postgres -Atc 'show data_directory')"
pg_ctl restart -D "$DATA_DIR" -l "$DATA_DIR/server.log"预期输出
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started某些平台的提示文字略有差异,但命令应成功完成并重新接受连接。
创建扩展并验证慢日志
共享库加载后,在需要分析的数据库中创建扩展。扩展是数据库级对象,因此切换到另一个数据库时要单独创建。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';
SELECT pg_sleep(0.6);预期结果
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 累计最贵”。先重置实验统计,再执行两次结构相同但参数不同的查询,最后查看归一化后的语句记录。
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;预期结果
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(),测试结束后再读取同一组指标,才能得到可比较的观测窗口。