运行监控与统计视图

背景：运营反馈"每周三上午报表平台超时"，其他日子正常。DBA 一周后复现排查。

第一步  pg_stat_statements 排行 → 发现一条 SELECT count(*) 全表扫，单次 40s，
        每周三上午被定时任务调起 20 次。
第二步  pg_stat_user_tables → 该表 n_dead_tup 高达 400 万（表才 500 万行）。
第三步  pg_stat_activity 复现时段观察 → 长事务 hold 住，autovacuum 反复被取消。
第四步  找到业务侧"周三上午批量 UPDATE 状态"的长事务；拆小后 autovacuum 正常完成，
        死元组回收，报表恢复 2s。

CREATE EXTENSION pg_stat_statements;
-- postgresql.conf 需有：
--   shared_preload_libraries = 'pg_stat_statements'   （改后重启）
--   pg_stat_statements.max = 10000                    （保留语句数）
--   pg_stat_statements.track = all

-- 耗时总榜（先抓"总账"最大户）
SELECT query, calls, total_exec_time::int AS total_ms,
       mean_exec_time::int AS mean_ms, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 平均耗时榜（低频但每次很慢的）
SELECT query, calls, mean_exec_time::int AS mean_ms
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC LIMIT 10;

-- 命中率榜：shared_blks_hit/read 比值低 = 缓存白费，多为大扫描
SELECT query, calls,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 10;

-- I/O 时间榜（PG 13+ 字段）
SELECT query, temp_blks_written   -- 临时文件写入量，溢盘信号
FROM pg_stat_statements
ORDER BY temp_blks_written DESC LIMIT 5;

-- 1. 正在跑什么、跑了多久
SELECT pid, usename, state,
       now() - query_start AS run_time,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND backend_type = 'client backend'
ORDER BY run_time DESC;

-- 2. 谁在等锁、等谁的（锁等待链）
SELECT blocked.pid AS blocked_pid,
       left(blocked.query, 50) AS blocked_query,
       blocking.pid AS blocking_pid,
       left(blocking.query, 50) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid));

-- 3. 等待事件分布（PG 9.6+，一秒看出瓶颈类型）
SELECT wait_event_type, wait_event, COUNT(*)
FROM pg_stat_activity
WHERE state <> 'idle'
GROUP BY 1, 2 ORDER BY 3 DESC;

SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze,
       seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

# postgresql.conf：慢查询日志（线上常态开着，别等出事再开）
log_min_duration_statement = 500     -- 超过 500ms 记录
log_lock_waits = on                  -- 锁等待记录（配合 deadlock 报警）
log_checkpoints = on                 -- checkpoint 压力可见

pgbadger /var/log/postgresql/postgresql-17-main.log -o report.html
# 增量分析：每天跑前一天，月度汇总
pgbadger -I -O /var/www/pgbadger /var/log/postgresql/*.log

-- ============ 每日巡检 SQL ============
-- 1. 连接水位（对照 max_connections）
SELECT count(*) AS conns,
       current_setting('max_connections') AS max_conn
FROM pg_stat_activity;

-- 2. 长事务（>5 分钟都要过目）
SELECT pid, usename, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - xact_start > interval '5 minutes';

-- 3. 死元组 TOP5
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

-- 4. 复制延迟（若有备库，见高可用篇）
SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 5. 索引缺失嫌疑：顺序扫描大户
SELECT relname, seq_scan, pg_size_pretty(pg_relation_size(relid))
FROM pg_stat_user_tables
WHERE seq_scan > 1000 ORDER BY seq_tup_read DESC LIMIT 5;

-- 6. 未使用索引（观察期后考虑删除，先确认非主键/约束）
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname = 'public';