诊断一个正在跑的数据库

目标

接手一个同时出现三种症状的数据库，用数字区分各自原因，最后汇总成一页诊断报告。本实验的任务不是修复，而是诊断。

为什么重要

数据库通常不会直接宕机，而是保持运行却出现异常。症状出现的位置与根因所在的位置几乎总是不同。被阻塞会话指向的 pid 可能本身也是受害者；查询变慢的原因可能不在查询本身；表无法缩小的原因也可能不在该表中。

所以本实验不要求背命令，只要求提取并记录证据。评分器会从当前运行的数据库重新提取这些数字进行核对。查找方式不限，但诊断必须正确。

环境

此 Pod 以 postgres 账号运行。直接输入 psql 即可连接 labdb。

export PATH=/usr/lib/postgresql/16/bin:$PATH
export PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdb
psql

所有产物放在 /root/inc/ 下。先执行 mkdir -p /root/inc。

需要多个会话。 由于只有一个终端，请在后台启动。以下形式会创建一个保持事务开启但处于空闲的会话——保持 stdin 打开后，psql 会等待下一条命令，并停留在 idle in transaction。

( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } \
    | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

三个重定向必须全部保留，缺少任意一个，shell 都会等待该会话。

步骤

1. 重现事故并保存活动快照 → /root/inc/01-activity.txt
2. 找到锁链末端 → /root/inc/02-root.txt
3. 记录等待占用的连接与 lock_timeout → /root/inc/03-waiting.txt
4. 记录崩坏的执行计划 → /root/inc/04-plan.txt
5. 修复统计信息后记录 → /root/inc/05-stats.txt
6. 记录死元组与 vacuum 的回答 → /root/inc/06-bloat.txt
7. 找出握住地平线的会话 → /root/inc/07-horizon.txt
8. 编写诊断报告 → /root/inc/08-report.md

参考

• 让制造事故的会话一直存活到最后。 评分会与实时数据库核对，中途断开会导致前面步骤无法再次评分。实际行动写入第 8 步报告。
• events 表只关闭了统计信息自动更新。生产中，大量导入后到 autoanalyze 运行前的几分钟会自然出现这种状态；这里是为了保留第 4 步现象。autovacuum 仍然开启。
• 第 4 步的聚合查询需要数秒，这是正常现象，也是证据。

重现事故并截取完整快照

重现事故并保存活动快照 → /root/inc/01-activity.txt

先制造事故。向 events 表为 tenant 41 导入 6 万行，但不要 ANALYZE；启动一个修改 orders 后不提交的会话，以及两个修改同一行的会话。

保持事务开启的空闲会话可通过保持 stdin 打开来创建： ( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

随后把完整的 pg_stat_activity 保存到 /root/inc/01-activity.txt，务必包含 state、wait_event_type、pg_blocking_pids。

找到锁链末端

锁链末端 → /root/inc/02-root.txt

被阻塞会话指向的 pid 可能自身也是被阻塞的受害者。请找到阻塞别人、但自己未被任何会话阻塞的 pid。

用 unnest(pg_blocking_pids(pid)) 展开后，只保留 cardinality(pg_blocking_pids(b)) = 0 的项。

在 /root/inc/02-root.txt 中写三行：root_pid=、root_app=、root_state=。评分器会从实时数据库重新提取核对。

等待正在消耗什么

等待占用的连接与 lock_timeout → /root/inc/03-waiting.txt

统计被阻塞会话数（cardinality(pg_blocking_pids(pid)) > 0），并与 show max_connections 一起记录。这些会话不是单纯变慢，而是各自占着一个连接停止运行。

然后执行 set lock_timeout = '1s'; 并尝试修改同一行。它不会无限等待，而会在 1 秒后返回错误。把该错误行原样留在文件中。

文件：/root/inc/03-waiting.txt

未修改的查询却变慢了

崩坏的执行计划 → /root/inc/04-plan.txt

使用连接 events 与 customers、汇总 tenant 41 的查询，并通过 EXPLAIN (ANALYZE, BUFFERS) 运行。耗时数秒正是本步骤要观察的现象。

最好关闭并行后分别测量估算行数和实际行数，因为 loops 不为 1 时显示值是每次循环平均值： 先执行 set max_parallel_workers_per_gather = 0;，再执行 explain (analyze) select count(*) from events where tenant_id = 41

在 /root/inc/04-plan.txt 中写入 est_rows=、actual_rows=、exec_ms=，并附完整计划。本步骤尚不可运行 ANALYZE。

需要修复的不是查询

修复统计信息后 → /root/inc/05-stats.txt

不要创建索引，只执行 analyze events，然后与第 4 步完全相同地重新测量。

在 /root/inc/05-stats.txt 中写入 est_rows_after=、exec_ms_after=、join_after=，并附完整计划。评分器会重新生成计划并确认优化器估算是否接近实际值；不修复统计信息则无法通过。

更新后表反而变大

死元组与 vacuum 的回答 → /root/inc/06-bloat.txt

执行类似 update events set kind = 'view' where tenant_id = 41 的语句只修改一列，并在前后以字节测量 pg_total_relation_size('events')。再运行 vacuum (verbose) events，vacuum 会说明原因。

在 /root/inc/06-bloat.txt 中写入 dead_tuples=、removable_cutoff=、size_before=、size_after=，并附 vacuum 输出。cutoff 不能编造，评分器会与当前打开事务核对。

找到阻止清理的会话

握住地平线的会话 → /root/inc/07-horizon.txt

找到握住第 6 步 removable cutoff 的会话。只查看 backend_xmin 会找不到——打开事务后空闲的写会话 xmin 为空，真正握住地平线的是其 backend_xid。

where backend_xid is not null order by age(backend_xid) desc limit 1 会给出答案。

在 /root/inc/07-horizon.txt 中写入 holder_pid=、holder_xid=、holder_app=。holder_xid 应与第 6 步 cutoff 相同，并将 holder_pid 与第 2 步 pid 比较。

一页诊断报告

诊断报告 → /root/inc/08-report.md

汇总前七步数字，在 /root/inc/08-report.md 中编写诊断书。包含 증상、원인、조치 三节，并把锁链末端 pid、vacuum cutoff、实际行数原样以数字写入。

关键是区分三种症状中两个具有同一根因，另一个独立。预防复发时必须提到 lock_timeout。评分器会将报告数字与实时数据库核对。