6.4 统计视图与性能调优：让内幕可观测

6.4 统计视图与性能调优：让内幕可观测

本节摘要：前五章的每个内部机制都在统计视图里留了痕迹：pg_stat_activity 看会话与等待，pg_stat_user_tables 看扫描与死元组，pg_statio 看物理读写，pg_stat_statements 看语句级耗时汇总。调优不是玄学，是"看最贵的、修最亏的"循环。本节给出五个必看视图与一份巡检顺序。

五个视图各管一段内幕

-- 1. 现在谁在跑、谁在等
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS dur, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY dur DESC;

-- 2. 哪些表在被全表扫、死元组堆积
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

-- 3. 缓冲区命中率（第 1 章的指标在此）
SELECT round(100.0*sum(blks_hit)/nullif(sum(blks_hit)+sum(blks_read),0),2)
FROM pg_stat_database;

-- 4. 语句级账单（需加载扩展）
SELECT calls, round(total_exec_time::numeric,1) AS total_ms,
       round(mean_exec_time::numeric,2) AS avg_ms, left(query,60)
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 5. 复制延迟（第 3 章的观测面）
SELECT application, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
​

一份巡检顺序：从症状到病根

性能问题的排查不该从改参数开始，按链条走：

pg_stat_activity   → 有没有等待、有没有长事务（第 2 章的毒药）
pg_stat_statements → 最贵的十条语句是哪些
EXPLAIN ANALYZE    → 对最贵语句：估错行数（第 5 章）还是真扫太多
pg_stat_user_tables→ 高 seq_scan 表：缺索引（第 4 章）还是统计过期
pg_statio_user_tables + 命中率 → IO 集中在哪张表、缓冲区是否够（第 1 章）
​

每一步都对应前几章的一个机制，病因与药方一一挂钩：

图：巡检闭环

💡 关键直觉：参数调优是最后 20% 的事。前 80% 是让每条贵语句命中正确的索引、让统计信息保持新鲜、让 VACUUM 与检查点平稳运行——这些全是前五章的机制，只是换成了数字出现在视图里。

pg_stat_statements 从启用到读透

语句账本是巡检起点，先把它开起来：

-- 预加载库参数加入 pg_stat_statements 后重启，再建扩展
CREATE EXTENSION pg_stat_statements;

-- 一行看清"最贵的语句"的三个维度
SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS avg_ms,
       round((shared_blks_hit + shared_blks_read) * 8 / 1024.0, 1) AS mb_touched,
       rows,
       left(query, 70) AS q
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 8;
​

读这份输出先看 total（总账，决定全局优化收益）、再看 mean 与 calls 的组合（高频中速语句常是隐形大户——单次三毫秒乘以每秒两千次调用，总账比偶发的慢查询更重）、最后看 rows 与 calls 的商（每次调用返回的行数异常大，多半是应用侧在用数据库做传输）。治理优先级永远按 total 排序，这是"修最亏的"的账面依据。

等待事件：症状的器官定位

pg_stat_activity 的 wait_event_type 加 wait_event 把"慢"定位到器官：

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;
​

 wait_event_type |     wait_event      | count
-----------------+---------------------+-------
 Lock            | transactionid       |     3
 IO              | DataFileRead        |     2
 Client          | ClientRead          |     5
​

读法对照前几章：Lock 类的 transactionid 等待指向行锁冲突（6.1 的写写相争）；IO 类的 DataFileRead 堆积指向缓冲区不足或大扫描（第 1 章）；Client 类的 ClientRead 数量大不是数据库慢——是客户端处理慢拖着连接不放，该修应用或加池化上限。等待事件把全书机制变成一张器官表，巡检时先看分布再看个案，方向感由此而来。

一份可直接落地的巡检脚本

把前面的视图收拢成每日例行，十五分钟出全景：

-- 例行四查：长事务、死元组前十、命中率、最贵语句
SELECT pid, now() - xact_start AS dur, state, left(query, 50)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY dur DESC LIMIT 3;

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

SELECT round(100.0*sum(blks_hit)/nullif(sum(blks_hit)+sum(blks_read),0),2)
FROM pg_stat_database WHERE datname = current_database();

SELECT calls, round(total_exec_time::numeric,0), left(query,60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
​

四段分别对应第 2 章的清理健康、第 1 章的缓存健康、语句账本与长事务毒药。固定跑一周，各指标的正常波动范围自然浮现——巡检的价值不在单次数值，在于把每个数字的"平常"记进脑子，异常才有被认出的机会。

本节要点回顾

• 五视图分工：会话、表扫描、IO、语句账单、复制各守一段
• 巡检有链条：从等待到语句到计划到统计，别直接跳到改参数
• 症状映射机制：每种慢都对应某一章的某个内幕
• pg_stat_statements 是起点：没有语句级汇总的库等于没有账本