8 抽丝剥茧：慢 SQL 诊断方法论

“数据库慢”不是根因，甚至还不是一个足够好的问题。它可能表示某个请求在连接池排队、某条 SQL 被事务锁住、一个参数命中了错误的通用计划、结果集已经算完却写不进慢客户端，也可能只是用户把一次偶发抖动概括成了整体退化。

本章把第 5 章的事务与锁、第 7 章的计划与统计放回真实请求链路，建立一条可复核的诊断闭环：

定义症状与时间窗
  → 界定服务、实例、数据库、查询族与参数
  → 观察 activity、wait 与 blocking edge
  → 关联查询统计、日志、计划、主机资源和变更事件
  → 按证据排列可证伪假设
  → 只改变一个解释变量
  → 比较效果、正确性与副作用
  → 修复、回退、复位并沉淀证据

顺序很重要。先跑 EXPLAIN 会漏掉锁与客户端背压；先建索引会把相关性当因果；只看面板截图则容易丢失指标定义、时间范围与原始身份。正确做法是从用户可见 SLI 向内收敛，再从平台视图回到 PostgreSQL 原生证据。

本章目标

完成本章后，读者应当能够：

• 用时间窗、样本数、p50/p95/p99、吞吐、并发和错误率定义“慢”；
• 区分单次慢、参数簇慢、持续退化、实例退化与全链路退化；
• 把端到端时间拆成排队、应用、数据库执行、传输和客户端消费；
• 正确联合解释 pg_stat_activity.state、wait_event_type、wait_event；
• 用 pg_blocking_pids() 证明阻塞边，不把等待者误当根因；
• 从 pg_stat_statements 按总预算、调用数、均值、最大值和资源量排序；
• 知道累计查询统计没有原生延迟分位数，且不能跨边界滥用 queryid；
• 建立带会话、查询、时间和变更身份的日志最小基线；
• 关联 SQL 指标、主机资源、锁、日志、部署和配置变更；
• 把“计划、锁、I/O、CPU、内存、客户端、网络、连接池”写成可证伪假设；
• 设计单变量、可回退、记录冷热缓存与参数分布的实验；
• 在 Pigsty 中定位范围，并用 SQL、日志和机器可读计划复核；
• 独立区分估算/计划、锁等待与客户端慢消费三种相似的“请求不返回”；
• 产出证据包、假设树、修复对照、负对照与复位结果。

实验边界

实验基线为 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04 L1；SQL 与诊断原则保持 PostgreSQL 14–18 可用。实验复用：

• ch07 的 90000/10 tenant skew，制造 generic/custom estimate 对照；
• ch05 的 rollback-only 行锁编排，制造一条可证明的 blocker edge；
• generate_series 派生结果与受控慢 reader，制造 Client/ClientWrite。

本章不创建持久对象。锁实验最终回滚，客户端实验只生成结果流，estimate 实验只读 ch07 fixture。若 ch07 fixture 缺失，task.sh setup/all 会通过 marker guard 受控重建专属对象，属于 L1/R1；不会自动清理业务对象、全局重置 pg_stat_statements、修改日志/连接池参数或取消非本章会话。

每个并发 worker 都有唯一 application_name。取消动作必须同时匹配 PID、backend_start、database 与 application identity；最终验收要求 active_lab_workers=0，并重新计算 ch04-v1 业务 checksum。

下载资产：

• 实验合同
• 诊断记录模板
• estimate case
• lock case
• client case
• ClientWrite 编排
• 慢 reader
• 中性信号构建器
• 无答案诊断器
• 盲测入口
• 稳定断言
• v0.3 规则提案
• 状态验收
• 任务入口

本章目录

8.1 先定义“慢”

• 8.1.1 延迟分位数、吞吐、并发与错误率
• 8.1.2 单次慢、持续慢与整体退化
• 8.1.3 应用时间、排队时间与数据库时间

8.2 从会话到语句定位范围

• 8.2.1 活跃、等待、阻塞与空闲事务
• 8.2.2 按调用、总时长、均值和尾延迟排序
• 8.2.3 查询指纹、参数与时间窗口

8.3 关联日志、指标与计划

• 8.3.1 日志最小基线与慢语句记录
• 8.3.2 SQL 指标、主机资源与部署事件
• 8.3.3 用同一时间轴排除巧合

8.4 建立而不是猜测假设

• 8.4.1 计划与估算问题
• 8.4.2 锁、I/O、CPU、内存与临时文件
• 8.4.3 客户端取数、网络与连接池排队

8.5 设计受控实验

• 8.5.1 每次只改变一个解释变量
• 8.5.2 冷热缓存、参数与数据规模控制
• 8.5.3 反证、回退与副作用观察

8.6 从可观测面板回到原生证据

• 8.6.1 用指标语义定位时间、实例与查询
• 8.6.2 用 SQL、日志和计划复核面板判断
• 8.6.3 不把截图、颜色或当前点击路径当作知识

8.7 实战：三种“慢”只修真正瓶颈

• 8.7.1 估算错误、锁等待与客户端慢消费
• 8.7.2 随机隐藏一种根因，先独立诊断再揭晓
• 8.7.3 输出证据包、假设树、修复前后对照与复位结果

实测摘要

一次 PostgreSQL 18.6 全量验收得到：

estimate:
  generic estimate=100 / actual=90000 / error=900x
  custom  estimate=90000 / actual=90000 / error=1x
lock:
  state=active / wait=Lock/transactionid / blockers=1
client:
  state=active / wait=Client/ClientWrite / blockers=0
mystery:
  diagnosis=client-slow-consumer / reveal matched=true
  wrong guess rejected=true / answer mode=0600
final:
  same seed reproducible=true / remaining workers=0
  relation checksum=f8a7bfae59c6d16cd323abecfefe1014

节点类型、cost、buffers、PID 和时间不是 golden。稳定断言是证据关系：generic estimate 严重偏离且 custom 对照改善；Lock wait 必须存在 blocker edge；ClientWrite 必须没有数据库 blocker；错误盲测答案必须失败；所有会话与业务状态最终恢复。

章节验收

1. 事件描述包含 UTC 时间窗、样本数、分位数、吞吐、并发、错误率和影响范围；
2. 不平均不同窗口的 p99，不用单次最大值冒充分位数；
3. 能解释端到端慢为什么可能完全发生在 PostgreSQL 之外；
4. 联合读取 state 与 wait，不把普通 idle/ClientRead 当慢查询；
5. 取消会话前验证 PID + backend_start + database + application；
6. 只用 pg_blocking_pids()/锁证据确认 blocker，不按最长 SQL 猜；
7. pg_stat_statements 排序至少覆盖总预算、均值、调用数和资源；
8. 知道累计统计的 reset/采样边界，不为一次调查全局 reset；
9. 日志含时间、PID/session、用户、数据库、application 与必要 query identity；
10. 参数日志有脱敏、长度、成本与保留策略；
11. 假设写明预测、反证、最小实验与回退条件；
12. 对比实验控制 cache、参数、数据量、并发与重复次数；
13. 面板发现必须能落回 SQL、日志、计划或 exporter 指标语义；
14. 三类 case 均能在不读取 answer artifact 时正确分类；
15. 错误 diagnosis 的 reveal 返回失败；
16. task.sh all 通过，answer mode 为 0600，worker 为 0，业务 checksum 不变。

下一章 ch09《巧夺天工：索引设计与效果验证》 将在诊断已证明访问路径是主要瓶颈后，再讨论该不该建、建什么、怎样验证和怎样安全发布索引。

参考资料

• PostgreSQL 18：Monitoring Database Activity
• PostgreSQL 18：Cumulative Statistics System
• PostgreSQL 18：pg_stat_statements
• PostgreSQL 18：Error Reporting and Logging
• PostgreSQL 18：Using EXPLAIN
• Pigsty：PostgreSQL Dashboards

上一章：追本溯源：执行计划与统计信息 · 返回上卷导读 · 下一章：巧夺天工：索引设计与效果验证 · 查看全书目录 · 查看索引中心

8.1 先定义“慢”

一次有效诊断从可检验的事件定义开始。若工单只有“数据库今天很慢”，不同的人会任意选择时间、实例和指标，最后得到彼此矛盾却都无法反驳的故事。

最小事件边界可以写成：

2026-07-29 12:04:00Z–12:09:00Z，
checkout/create 路由 18432 次请求，
p99 从同星期基线 180 ms 升至 2.4 s，
吞吐从 70 req/s 降至 61 req/s，错误率从 0.1% 升至 1.8%，
影响租户 A/B；只发生在写流量，读流量与其他区域正常。

这段话尚未宣称 PostgreSQL 有问题，却已经给出了可在 trace、应用、连接池、代理和数据库各层复核的同一范围。

8.1.1 延迟分位数、吞吐、并发与错误率

延迟不是一个数，而是一组事件的分布。p99 的含义是：在给定样本集合中，约 99% 的观测不超过该值；它必须和以下限定一起出现：

• 测量对象：HTTP 路由、业务动作、数据库 query family，还是单个 backend；
• 起止时间与时区；
• 成功请求、全部请求还是某类错误；
• 样本数与采样方式；
• histogram bucket、客户端 timer 或 tracing span 的来源；
• 是否包含重试、排队、结果传输和超时。

没有样本数的 p99 很危险。一个窗口只有 40 个请求时，所谓 p99 几乎就是最大样本；一个窗口有 100 万请求时，尾部才有足够事件可供分层。也不要把十个一分钟 p99 求平均来冒充十分钟 p99：分位数一般不可加、不可平均。只有保留可合并的原始分布或边界一致的 histogram bucket，才可能重新计算整体分位数。

分位数还必须和吞吐、并发、错误率联合阅读：

在近似稳定系统中，Little 定律给出：

其中 $L$ 是系统内平均并发，$\lambda$ 是吞吐，$W$ 是平均停留时间。它不是用来从三个噪声瞬时值“算根因”的，而是做一致性检查：若吞吐近似不变、停留时间增大，系统内请求数理应上升；若监控没有上升，可能是测量边界不同、采样漏掉队列，或请求已在上游被拒绝。

一个适合告警和诊断的 SLI 组合通常至少包括：

event count
success/error/timeout count
latency histogram or quantiles
throughput
in-flight/queue depth
measurement scope and labels

平均值仍有价值，它适合预算、容量和与累计 query time 对齐；但它不能替代尾延迟。反过来，p99 能说明尾部体验，却不能告诉你该 query family 消耗了多少总 CPU/执行时间。

8.1.2 单次慢、持续慢与整体退化

“慢”至少要从时间范围和影响范围两个轴分类：

单次慢样本适合做法证：保存 trace、PID、参数、日志与当时 wait，但不能据此证明系统长期退化。持续慢需要同口径的对照窗口，例如：

incident:  12:04Z–12:09Z
baseline:  前七天同星期、同五分钟、相近 QPS
control:   同集群未受影响的路由/租户/只读实例

“昨天平均 10 ms，今天一次 2 s”混合了统计粒度；“所有数据库都慢”也常把一个共享连接池、一个可用区或一个应用版本误写成数据库全局。诊断前依次收窄：

1. 哪个用户动作或后台任务；
2. 哪个路由、租户、参数簇与返回规模；
3. 哪个应用版本、实例、可用区和服务入口；
4. 哪个 PostgreSQL cluster、instance、database、user、application；
5. 哪个查询族与事务；
6. 是执行慢、等待慢、排队慢，还是消费结果慢。

持续退化也不等于“从某次发布起就一定由发布造成”。发布是高优先级假设，因为时间顺序与作用范围吻合；它仍需机制证据，例如 SQL shape 改变、calls/rows 改变、plan estimate 偏离、连接池并发改变，或错误重试放大负载。

建议把事件分为三个状态：

• 未确认：用户报告存在，但监控口径或范围尚未复现；
• 已确认：同一时间窗的 SLI 证明退化，根因未知；
• 已归因：存在机制、对照与反证，修复后同口径指标恢复。

这样能避免在“已确认慢”和“已证明 PostgreSQL 根因”之间偷换概念。

8.1.3 应用时间、排队时间与数据库时间

端到端时间可以用核账式分解：

用户等待
≈ 网关/应用排队
 + 业务代码与外部调用
 + 等待连接池 slot
 + 建连/认证/路由
 + PostgreSQL 内规划、执行与数据库等待
 + 结果传输与客户端消费
 + 序列化/响应发送

这不是说各组件一定能无缝相加。计时器可能使用不同 clock，span 可能重叠，重试会创建多次数据库调用，连接池也可能没有 trace。分解的价值是找出“缺失时间”：例如应用记录 2.4 s，而数据库完成日志与 server-side EXPLAIN ANALYZE 都约 80 ms，那么剩余 2.32 s 不能继续用索引解释。

PostgreSQL planner 的 cost 不包含把值转换为文本以及把结果传给客户端的时间；EXPLAIN ANALYZE 的服务器执行时间也不等于用户看到的完整取数时间。相反，连接池排队发生在 backend 分配之前，pg_stat_activity 根本看不到这批尚未进入 PostgreSQL 的请求。

要让时间可以关联，最低限度应统一：

• 日志与展示使用 UTC，并保留原始 timezone；
• duration 使用 monotonic clock，wall clock 只做跨系统关联；
• trace/request ID 贯穿应用，database span 记录 database/service；
• PostgreSQL application_name 能映射应用/worker；
• 日志保存 PID 与 session identity，而不是只保存 SQL 文本；
• 明确数据库 span 是“从发起驱动调用到返回”，还是 server duration。

一个常见对照：

request span                       2400 ms
  pool acquire                     1700 ms
  database driver call              620 ms
    server log duration              85 ms
    ClientWrite observed            500 ms
  application work                  80 ms

这里 PostgreSQL 只用了约 85 ms 计算，但连接池排队与客户端接收共同制造了“SQL 调用 620 ms”。给查询加索引几乎不会改变 1700 ms 排队，也不能修复 500 ms 的慢消费；正确方向是分别调查 pool saturation 与返回规模/客户端读速。

本节的产出不是一张性能图，而是一份写入诊断记录模板的事件边界：

谁慢、哪里慢、何时慢、慢多少、多少样本、
当时流量/并发/错误怎样、与哪个基线相比、
端到端时间已分解多少、尚有多少无法解释。

有了这条边界，下一节才开始从会话和查询统计定位 PostgreSQL 内部范围。

返回本章目录 · 下一节：从会话到语句定位范围 · 查看全书目录 · 查看索引中心

8.2 从会话到语句定位范围

事件边界确定后，先回答“backend 此刻在做什么”，再回答“过去一段时间哪个查询族消耗最大”。前者来自动态 activity snapshot，后者来自累计查询统计；把两者混为一谈，会拿一个瞬时会话解释一小时预算，或拿一小时均值解释当前阻塞。

8.2.1 活跃、等待、阻塞与空闲事务

下面的只读查询保留会话身份、事务年龄、当前状态、等待与直接 blocker：

SELECT
    clock_timestamp() AT TIME ZONE 'UTC' AS captured_at_utc,
    pid,
    backend_start,
    datname,
    usename,
    application_name,
    client_addr,
    state,
    CASE WHEN state = 'active'
         THEN clock_timestamp() - query_start
    END AS active_for,
    CASE WHEN xact_start IS NOT NULL
         THEN clock_timestamp() - xact_start
    END AS xact_age,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query_id,
    left(regexp_replace(query, '[[:space:]]+', ' ', 'g'), 240)
        AS query_excerpt
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND datname = current_database()
ORDER BY
    (state = 'active') DESC,
    query_start NULLS LAST;

读取时必须联合解释 state 与 wait：

PostgreSQL 明确把 state 与 wait 定义为相互独立的维度。采样也可能遇到短暂不一致，所以重要结论应跨数个短间隔采样，而不是冻结一行就下结论。

idle in transaction 尤其需要看 xact_start、backend_xmin、锁和业务上下文。它可能保留锁、阻碍 vacuum 清理旧版本、延长事务边界，却不是“运行很久的当前 query”；query 字段此时是上一条语句。治理措施应优先修复应用事务边界，并使用经过评估的 idle_in_transaction_session_timeout，而非定时粗暴终止所有 idle session。

阻塞关系使用：

SELECT
    waiting.pid AS waiting_pid,
    waiting.backend_start AS waiting_backend_start,
    blocker_pid,
    blocker.application_name AS blocker_application,
    blocker.state AS blocker_state,
    blocker.xact_start AS blocker_xact_start,
    blocker.wait_event_type AS blocker_wait_type,
    blocker.wait_event AS blocker_wait_event
FROM pg_stat_activity AS waiting
CROSS JOIN LATERAL unnest(pg_blocking_pids(waiting.pid))
    AS edge(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = edge.blocker_pid
WHERE waiting.datname = current_database();

等待最久的 PID 未必是 root blocker；它可能也是链中受害者。先建立边，再沿边找到不再被别人阻塞的上游会话。第 5 章已给出锁模式与事务语义，本章强调诊断身份。

若必须缓解，先保存证据，再精确重查：

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = :captured_pid
  AND backend_start = :'captured_backend_start'::timestamptz
  AND datname = :'captured_database'
  AND application_name = :'captured_application';

PID 会复用，单凭截图里的数字取消有伤及无关会话的风险。pg_cancel_backend 取消当前 query，pg_terminate_backend 结束 session；后者影响事务和客户端重连，不能当默认按钮。生产动作还要经过本地权限、SOP 和风险分级。

普通用户只能完整看到自己的会话；调查角色通常需要内置角色 pg_read_all_stats，但这也会暴露 SQL 与活动信息。权限应授予受控诊断角色，不应为了面板方便把业务用户升为 superuser。

最后注意视图一致性：累计统计可能延迟刷新，并在事务内缓存；activity 信息也会在同一事务首次读取后形成一致快照。交互调查若持续开着事务重复查询，先结束事务或按需调用 pg_stat_clear_snapshot()，否则可能把旧快照当实时状态。

8.2.2 按调用、总时长、均值和尾延迟排序

pg_stat_statements 把结构相同、常量不同的语句归一化，适合回答“哪些查询族消耗了累计预算”。先确认扩展已加载、目标数据库有 view，并记录 reset 起点：

SELECT stats_reset
FROM pg_stat_statements_info;

不要为一次调查执行全局 pg_stat_statements_reset()：它会破坏其他人正在使用的基线。更好的做法是保存两个时点的快照并计算 counter delta，或让监控系统持续抓取 counter。

一次基础排序：

SELECT
    userid,
    dbid,
    queryid,
    calls,
    total_exec_time,
    total_exec_time / NULLIF(calls, 0) AS mean_from_total_ms,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time,
    rows,
    rows::numeric / NULLIF(calls, 0) AS rows_per_call,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_written,
    wal_bytes,
    left(query, 240) AS query
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 30;

至少从四种视角排序：

• total_exec_time：谁吃掉最多执行时间预算，适合容量与总体收益；
• mean_exec_time / max_exec_time / stddev_exec_time：谁单次慢或波动大；
• calls：谁极高频，单次少量改善也可能有大收益；
• blocks、temp、WAL、rows：谁制造 I/O、spill、写放大或大结果。

一个总时间第一的查询可能只是每次 2 ms、调用数巨大；一个均值第一的查询可能每天只跑一次；一个调用数第一的查询可能返回 0 行且预算很小。索引、缓存、批处理、限流和 SQL 改写针对的是不同问题，不能只保留一个“Top SQL”榜。

pg_stat_statements 记录 min/max/mean/stddev，但不保存每次执行的完整分布，也没有原生 p95/p99 列。因此：

• max_exec_time 不是 p99；
• 不能从 mean/stddev 假定任意分布再可靠推算 p99；
• 尾延迟要来自请求 histogram、trace、采样日志或保存单次事件的系统；
• 累计 max 可能来自很久以前，必须结合 stats_reset/快照窗口。

执行统计只覆盖 PostgreSQL server 侧的一部分时间。连接池等待不在其中；结果传输造成的 server wait 与驱动计时也未必与 total_exec_time 完全同口径。把查询榜与应用 SLI 对齐是下一步，不是直接宣布榜首为根因。

8.2.3 查询指纹、参数与时间窗口

查询身份不是只有一段 SQL 文本。建议至少保存：

cluster/instance/database
userid/dbid/toplevel/queryid
normalized query text
application/release/route
representative parameter class
UTC window and stats reset boundary

queryid 来自 parse analysis 后的结构 hash。它比字符串更适合在同一环境中关联，但保证有限：

• 同一文本可能因 search_path 或对象含义不同而分成多个 ID；
• 常量通常被归一化，hot tenant 与 cold tenant 可能落在同一 query family；
• drop/recreate 对象、catalog OID 与平台架构会影响身份；
• 不应假设跨 PostgreSQL major version 稳定；
• 物理复制节点通常可对应，逻辑复制环境不能据此保证对应；
• 极低概率仍可能 hash collision。

因此长期证据键通常是 (cluster identity, major version, dbid, userid, toplevel, queryid) 加归一化文本 hash，而不是一个裸 queryid。升级、重建或迁移后应建立新的 epoch。

归一化是优点也是盲区。第 7 章的 tenant 实验中，同一语句：

... WHERE tenant_id = $1

参数 1 返回 90000 行，参数 1001 只返回 10 行。累计均值可以同时掩盖两端。要恢复参数语义，使用经过脱敏的 trace tag、业务参数分桶、受控日志采样或可重放 fixture；不要把 token、密码、个人数据和任意 payload 倾倒进日志。

时间窗口也必须对齐三种数据：

1. pg_stat_activity 是采样瞬间；
2. pg_stat_statements 是自 reset/entry 起的累计量；
3. Prometheus/日志/trace 是各自 scrape、采样或保留窗口。

例如事故发生五分钟，直接查询累计三个月的 mean 会稀释变化。正确做法是从监控 counter 计算事故窗口 delta/rate，或在事故前后保存两份 snapshot：

calls_delta
total_exec_time_delta
rows_delta
blocks/temp/WAL delta
mean_in_window = total_exec_time_delta / calls_delta

counter reset、entry eviction、实例重启和 failover 都会造成不连续，计算前应检查 reset/instance identity，不能把负 delta 当真实负负载。

本节最终应得到一个优先调查列表，而不是一个榜首判决：

query family + parameter class + affected window
current state/wait/blocker evidence
window calls/total/mean/resource deltas
identity and reset boundaries
two or three competing explanations

下一节把这份列表与日志、主机指标、计划和变更事件放到同一时间轴。

上一节：先定义“慢” · 返回本章目录 · 下一节：关联日志、指标与计划 · 查看全书目录 · 查看索引中心

8.3 关联日志、指标与计划

activity 告诉你“此刻”，查询累计量告诉你“这段 epoch”，日志保存离散事件，指标保存时间序列，计划解释 executor 怎样处理数据。四者只有共享时间、实例、会话和查询身份，才能形成证据链。

8.3.1 日志最小基线与慢语句记录

诊断日志的第一个目标不是“把所有 SQL 打出来”，而是让一条事件可定位：

timestamp + timezone
cluster/instance
PID/session identity
user/database/application/client
severity/SQLSTATE
query or query identity
duration
transaction/session context

PostgreSQL 的 log_line_prefix 可以提供这些身份。一个需结合环境评估的示意：

log_line_prefix = '%m [%p] %c %q%u@%d/%a '

其中 %m 是带毫秒时间，%p 是 PID，%c 由 backend start time 与 PID 组成近似唯一的 session ID，%u/%d/%a 分别是用户、数据库和 application。若启用 compute_query_id，还可评估 %Q；但官方明确指出 log_statement 产生日志时 query ID 可能尚未计算，因此不能把某一类日志里的 %Q=0 当作真实 query identity。

生产日志更适合使用 csvlog 或 jsonlog 等结构化目标，由采集器保留字段，而不是依赖脆弱正则拆纯文本。无论格式如何，都要实测 rotation、磁盘上限、采集延迟、丢弃策略与敏感字段。

记录慢完成语句的核心参数是：

log_min_duration_statement = '...ms'

它在语句完成且 duration 达阈值时记录，因此能发现“已结束的慢”，却不能解释当前一直没结束的 blocker。阈值不是通用常数：OLTP、批处理与维护任务的正常时长不同。先根据 SLO、流量和日志预算设基线，再用 role/database/session 或采样策略控制范围。

相关开关解决不同问题：

参数值比 SQL shape 更敏感。extended query protocol 的日志可能包含 bind parameters；log_parameter_max_length 和 log_parameter_max_length_on_error 可限制或禁止记录，但非零错误参数记录也会增加保存文本表示的开销。策略至少应覆盖：

• token、密码、密钥、个人数据和业务 payload 的禁止/脱敏；
• 每值长度和整条消息长度；
• 谁能读取、传输是否加密、保留多久；
• exporter/log pipeline 是否会复制到更多系统；
• 调查结束后如何恢复临时配置。

不要在事故中即兴全局打开 log_statement=all、auto_explain.log_analyze=on 和完整参数。先估算事件率、单条字节、磁盘余量与性能成本；能按单一 role/database/session 小范围复现时，就不要扩大到全局。

8.3.2 SQL 指标、主机资源与部署事件

慢查询证据通常跨四层：

再叠加一条“变化流”：

application release
schema/index/statistics change
PostgreSQL/Pigsty/configuration change
failover/restart
data load/backfill/maintenance
traffic or tenant mix change

正确关联不是看到两条曲线同时升高，而是提出机制：

发布改变 SQL predicate
  → query family calls 与 rows/call 上升
  → plan estimate/actual 偏离
  → shared blocks 与 CPU 同范围上升
  → endpoint p99 上升
  → 回滚 SQL shape 后上述指标按预期恢复

反例也同样重要：

• 主机 CPU 高，但目标 query 在 Lock wait：CPU 可能是另一 workload；
• shared block hit 高：只说明 PostgreSQL buffer hit，不代表没有 CPU 或 kernel page-cache I/O；
• disk latency 上升，但目标 plan buffers 几乎全 hit：时间相关不等于该 query 被磁盘拖慢；
• temp files 上升，但来自报表 user，而事故来自 OLTP application；
• 发布与事故同时发生，但未发布的 control instance 也同样退化：优先调查共享依赖。

PostgreSQL 的 pg_stat_io 和 pg_statio_* 能补充数据库 I/O 视角，但官方提醒它们不能区分数据是从物理介质还是 kernel page cache 取得；仍需与操作系统工具联合。track_io_timing 能提供时间，但有平台计时开销，是否常开需基准验证。

计划证据沿用第 7 章的机器可读格式：

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    SUMMARY,
    FORMAT JSON
)
SELECT ...;

它只应在安全、代表性环境执行。ANALYZE 会真实运行 SQL；写语句即使包在 rollback 中也可能有 sequence、外部函数等不可回滚副作用。事故时已有一条正在阻塞的生产写语句，不应为了“看计划”再执行一遍。

8.3.3 用同一时间轴排除巧合

把证据转换成 UTC 事件表，比叠十张截图更容易看出顺序：

这条链同时满足：

1. 先后：候选原因发生在结果之前；
2. 同范围：实例、database、user、application/query 能对应；
3. 剂量/机制：blocker 存在时等待积累，释放后 waiter 前进；
4. 负对照：未受影响路由或无 blocker 窗口不呈现同样现象；
5. 可逆性：受控动作后结果按预测变化。

时间对齐时记录数据本身的分辨率：

• Prometheus scrape interval 与 recording rule window；
• Grafana 查询 step、rate window、timezone；
• 日志采集/索引延迟；
• trace sampling rate；
• PostgreSQL 累计统计刷新延迟与事务内 snapshot；
• 应用与数据库主机的 clock synchronization；
• failover 后 instance identity/计数器是否改变。

不要把面板像素对齐当毫秒级因果。某条一分钟 rate 曲线的点代表一个区间，日志 timestamp 代表事件，activity 是一次采样，它们的语义不同。先把每项转换成“事件或区间 + 误差/分辨率”，再讨论顺序。

一个实用的反巧合问题集：

候选原因是否先于症状？
是否只出现在受影响范围？
它通过什么数据库机制产生该症状？
该机制应留下什么额外证据？
未受影响对照是否缺少这些证据？
只移除这一原因后，哪些指标应先后恢复？
若未恢复，这个假设怎样被判错？

输出时保留原始 artifact，而非只有解释。原始 CSV/JSON plan、日志行、Prometheus expression、时间范围、source hash 和查询参数分桶使其他人能够重算；截图最多是导航附件。

下一节将这些关联结果整理为一个有优先级、可反驳的假设树。

上一节：从会话到语句定位范围 · 返回本章目录 · 下一节：建立而不是猜测假设 · 查看全书目录 · 查看索引中心

8.4 建立而不是猜测假设

假设不是“可能是磁盘”“可能缺索引”的清单。它必须把机制写成一条可被事实推翻的预测：

因为 tenant 1 的真实选择率为 90%，generic plan 仍估 0.1%，
所以 hot parameter 会读取远多于估算的行；
若强制 custom plan 且其他条件不变，estimate/actual 应接近，
访问路径或资源量应随之改善。

优先级由“现有证据支持度 × 用户影响 × 最小验证成本 ÷ 风险”决定，而不是由团队最熟悉什么决定。

8.4.1 计划与估算问题

计划假设应在 wait 排查之后进入。一个正在 Lock 等待的 backend，即使计划里有 Seq Scan，当前不返回的直接原因仍是锁；一个 ClientWrite backend 可能已经算出大量结果，planner cost 又不包含把结果传给客户端的时间。

确认计划方向时，从第一个显著偏差节点而非根节点名称开始：

query semantics and representative parameter
  → estimated rows vs actual rows × loops
  → filter/recheck rows and join multiplicity
  → buffers/WAL/temp/settings
  → custom vs generic plan
  → statistics age/distribution/extended statistics
  → predicate/index/partition expression match

可量化 cardinality error：

若 actual 为 0，应单独描述“估算 N、实际 0”，不要用无穷大排序掩盖业务含义。高误差是调查入口，不是固定阈值自动修复；它是否影响路径选择、内存分配、join order 或响应目标，还要看对照。

常见假设与反证：

禁用 planner 方法（如 enable_seqscan=off）最多是受控诊断探针，不是生产修复；它也不能绝对禁止所有路径。不要把“强迫 Index Scan 后这一次更快”直接推广为长期结论，必须覆盖参数分布、cache、并发、写成本和磁盘占用。

计划 change 同样不是根因。统计、参数、配置、数据量或版本变化可能让 planner 合理换路；判断回归要比较结果正确性、SLO、estimate、资源与 workload，而不是 diff 节点名。

8.4.2 锁、I/O、CPU、内存与临时文件

wait event 是“backend 在采样瞬间等待哪里”，不是完整时间账本。把它与 blocker、查询计划、累计资源和 OS 指标组合：

锁

锁诊断必须保存等待边两端的：

PID + backend_start
user/database/application/client
xact_start/query_start/state
wait_event and lock modes
current/last query
transaction owner and business action

真正修复通常是缩短事务、统一锁顺序、避免事务中等外部 I/O、减少过宽写集合，或把冲突转为显式业务协议。增加 statement timeout 只是限制损失，不能替代根因修复。

I/O

EXPLAIN (ANALYZE, BUFFERS) 的 shared read/hit 是 executor 访问证据；track_io_timing 开启后可补 read/write time；pg_stat_io 给出 backend type/context/target 维度。它们都不能单独证明物理盘读取：PostgreSQL miss 仍可能由 kernel page cache 满足。应与设备层 latency、queue、吞吐和同主机 control workload 对照。

CPU

CPU 没有一个叫 CPU 的 wait event。backend 在执行用户态工作时往往 active 且 wait 为 NULL，但短采样也可能恰好落在两个 wait 之间。证明 CPU 瓶颈需要：

• 多次采样而非一行 activity；
• query calls/rows/plan work 与 CPU 时间窗同范围；
• 主机 run queue、利用率、throttling/steal；
• 限制并发或减少工作量后吞吐/延迟按模型变化。

内存与临时文件

sort/hash spill 是“该节点的内存预算与数据规模/并发不匹配”的证据。全局提高 work_mem 很危险，因为它不是整个实例固定池，而可能被一条 query 的多个节点、多个并发 backend 分别使用。优先：

1. 确认 rows/width estimate 与返回规模；
2. 减少不必要数据、改善计划；
3. 对单一 role/session 做对照；
4. 计算最坏并发内存；
5. 观察 spill 改善、RSS/pressure 与其他 workload 副作用。

8.4.3 客户端取数、网络与连接池排队

数据库算得快，不代表用户收得快。第 8 章实验用一个大 COPY TO STDOUT 和受控慢 reader 复现：

state=active
wait_event_type=Client
wait_event=ClientWrite
blocking_pid_count=0

这组证据表示 server 正在尝试把数据写给客户端，而 socket backpressure 让它等待。可能原因包括：

• 客户端逐行做昂贵处理，读取速度低；
• 客户端线程暂停、GC 或 event loop 被阻塞；
• 网络丢包、拥塞或带宽受限；
• 返回行/列过多、payload 过大；
• 游标/fetch size 与消费方式不合理；
• 下游已放弃请求但连接尚未及时取消。

ClientRead 则表示 server 等客户端发数据。普通 idle backend 经常在 ClientRead 等下一条命令，这不是慢 SQL；active session 在 COPY FROM、协议交互等场景也可能等客户端。必须结合 state、query、协议阶段与应用 trace。

客户端慢消费的修复候选是限制返回规模、分页/流式语义、修复 consumer、调整驱动读取方式、网络与超时传播，而不是先建索引。索引也许能缩短产生第一批行的时间，却不能让慢 reader 更快接收 400 MB。

连接池排队位于另一个边界：

request arrives
  → waits for application/PgBouncer pool slot
  → obtains PostgreSQL backend/transaction
  → statement becomes visible in pg_stat_activity

未获得 slot 的请求不会出现在 pg_stat_activity。如果应用 p99 高、数据库 active sessions 刚好打满 pool size、server 单条执行仍快，应同时看：

• application pool acquire duration、waiter count、timeout；
• PgBouncer client/server active/waiting 与 pool mode；
• HAProxy/service route、连接拒绝与 backend health；
• PostgreSQL max_connections、可用连接与角色/database 限额；
• 事务长度、连接泄漏、重试风暴与并发上限。

“把 pool size 加倍”也是需要实验的假设。若数据库已经 CPU/I/O 饱和，更多并发会增加排队与上下文切换；吞吐不升而尾延迟更差。连接池的作用是排队和保护下游，不是消灭容量边界。

一棵够用的初始假设树可以写成：

请求慢
├─ 尚未进入 PostgreSQL
│  ├─ 应用队列/连接池
│  ├─ 路由/建连/认证
│  └─ 上游重试或限流
├─ backend 正等待
│  ├─ Lock → blocker edge
│  ├─ IO/LWLock/BufferPin → 具体 wait + 资源
│  └─ ClientWrite/Read → client/protocol
├─ backend 正执行
│  ├─ estimate/path/join/scan
│  ├─ CPU/JIT/expression
│  └─ sort/hash/temp/WAL
└─ server 已完成
   ├─ 结果传输/消费
   └─ 应用后处理/下游

这棵树不是固定排障脚本。它的价值是强迫每个解释声明边界和证据，下一节再从中选一个最小、可逆的实验。

上一节：关联日志、指标与计划 · 返回本章目录 · 下一节：设计受控实验 · 查看全书目录 · 查看索引中心

8.5 设计受控实验

诊断实验的目标不是“让一次执行变快”，而是区分竞争解释。一次同时 ANALYZE、建索引、改 SQL、增大内存又重启实例的操作即使奏效，也不知道哪项有效、哪项多余、哪项埋下副作用。

8.5.1 每次只改变一个解释变量

把假设写成实验合同：

现象：
  hot tenant 的同一 query family 资源量异常。

假设：
  generic plan 使用全体 tenant 平均选择率，严重低估 hot tenant。

保持不变：
  PostgreSQL 版本、数据快照、SQL 语义、参数、连接、cache 条件、
  并发、session settings（除 plan_cache_mode）。

唯一改变：
  force_generic_plan → force_custom_plan。

预测：
  estimate/actual error 从 >=100x 降到 <=2x；
  无 Lock/Client wait；可能改变路径和 buffers。

判错：
  custom estimate 仍严重偏离，或主要时间其实属于 wait/client。

回退：
  session 结束即恢复 plan_cache_mode；不修改持久对象。

“每次只改变一个变量”不等于一次只能执行一个命令。重建同一 deterministic fixture、采集 before/after、运行 analyzer 可以是一项完整操作；关键是两组之间只有被检验机制不同。

实验级别应逐级提升：

1. 只读观察：activity、wait、统计、日志、已有计划；
2. session-local probe：同一 session 改一个 setting，事务结束恢复；
3. 隔离 fixture/副本：重放代表数据与并发；
4. 受控 canary：少量真实流量、明确 SLO 与自动退出；
5. 生产变更：审批、回退、监控和审计齐全。

不要跳级只是为了快。特别是：

• 不在生产对未知写 SQL 随意 EXPLAIN ANALYZE；
• 不为对比清空全局 query statistics；
• 不用 pg_terminate_backend 代替理解事务；
• 不把 planner debug setting 留在连接池 session；
• 不在没有磁盘/写放大评估时直接创建大索引；
• 不把生产 traffic 同时承担探索、验证和上线三个阶段。

若无法控制某个重要变量，应把它记录为混杂因素，而不是从报告中删掉。例如 A/B 两次恰好跨 checkpoint、流量 mix 不同，则结论降级为“支持但未确认”，需要新的对照。

8.5.2 冷热缓存、参数与数据规模控制

数据库性能至少受四种实验条件影响：

缓存

“冷缓存”可能指：

application cache
PgBouncer/prepared state
PostgreSQL shared buffers
kernel page cache
storage controller/device cache

DISCARD ALL 不会清空 shared buffers 或 OS page cache；重连也不等于冷缓存。生产执行 echo 3 > /proc/sys/vm/drop_caches 或重启实例会影响全机 workload，不是普通诊断动作。若真的需要 cold/warm 对照，应在隔离、可重建环境明确 cache 层并记录方法。

更常见也更安全的策略是：

• 先跑 warm-up，不计入测量；
• A/B 交替或随机顺序，减少随时间漂移；
• 重复多次，报告分布和原始值，不只选最快一次；
• 同时保存 buffers、I/O timing 与 OS 指标；
• 若无法制造 cold，明确结论只适用于 warm steady state。

参数

均匀随机参数会掩盖业务分布。先按机制分层：

hot/cold tenant
existent/missing key
small/large time range
few/many result rows
common/rare status combination
first/subsequent page

每层使用脱敏、可重现的代表值，并按真实 traffic weight 汇总。一个只让 cold tenant 快 2 ms、却让占 90% 流量的 hot tenant 慢 100 ms 的索引/计划不是总体改善。

数据规模与分布

一万行测试库上的 Index Scan 不能证明十亿行生产行为。fixture 至少应保持：

• relation/partition 数量级；
• row count、row width、null fraction；
• distinct count、MCV、列间相关；
• index 与 heap physical correlation；
• dead tuple/bloat 与 statistics 状态；
• 参数访问倾斜。

无法复制完整规模时，目标是复制决策边界，而不是复制全部数据。例如第 7/8 章用 90000/10 的 tenant skew 让 generic/custom plan 面临清晰选择率差异。

并发

单 session 提速不保证系统吞吐改善。并发会改变：

• buffer/cache 命中与 I/O queue；
• CPU run queue；
• lock contention；
• 每 query 可用内存与 spill；
• connection pool queue；
• background vacuum/checkpoint 干扰。

至少分别回答：

single-query latency 是否改善？
固定并发下 throughput/p95/p99 是否改善？
达到 SLO 的最大可持续吞吐是否改善？
错误、超时、WAL、CPU/I/O、内存副作用怎样？

不要用无限并发压到崩溃后，只比较“谁最后倒下”。容量实验应有 ramp、steady state、 abort threshold 与恢复验证，第 26 章会完整展开。

8.5.3 反证、回退与副作用观察

一个强实验既设计正结果，也预先声明什么会证明自己错。示例：

“未能反证”不等于“证明”。尽量加入负对照：

• 同一 SQL 的 unaffected parameter；
• 同时间的 unaffected instance/route；
• 同 seed 重跑；
• 故意错误的诊断必须被 reveal 拒绝；
• 恢复原设置后现象按预测返回。

效果报告应同时包含：

correctness/result equivalence
latency distribution + sample count
throughput/concurrency/errors
calls/rows/buffers/temp/WAL
CPU/I/O/memory/locks
plan/estimate/settings
change and rollback duration
known confounders

副作用经常决定一个“快方案”不可上线：

• 新索引增加写 latency、WAL、磁盘、vacuum 工作；
• 提高 work_mem 增加并发 OOM 风险；
• 缩短 timeout 降低资源占用，却提高错误/重试风暴；
• 增大 pool 提高 backend 并发，却让数据库饱和；
• 缓存结果改善读取，却引入失效与一致性问题；
• denormalization 减少 join，却增加写路径和校验复杂度。

回退不是文档最后一行“必要时回滚”。实验前就应验证：

谁有权限回退
回退触发阈值
是否真的可逆
回退耗时和锁影响
回退后怎样证明状态恢复
外部副作用如何补偿

本章三个 case 都把复位写进执行路径：

• estimate 只改变 session-local plan mode；
• lock 的 blocker 与 waiter 最终 rollback；
• client 只生成派生结果并精确取消本实验 backend；
• final verify.sql 检查 worker=0、ch07 fixture 和 ch04-v1 checksum。

运行全量实验：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch08/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all
cat "$PG36_EVIDENCE_DIR/review.json"

这里 all 是 L1 教学环境动作。它会在必要时受控重建 ch07 专属 fixture、制造短暂锁与 socket backpressure，并精确取消自己的 worker；不会成为生产诊断脚本。

下一节把同一方法映射到 Pigsty：用面板快速发现范围，用原生证据确认，而不依赖某个版本的按钮位置。

上一节：建立而不是猜测假设 · 返回本章目录 · 下一节：从可观测面板回到原生证据 · 查看全书目录 · 查看索引中心

8.6 从可观测面板回到原生证据

Pigsty 的价值之一，是把 PostgreSQL、主机、连接池、代理、日志和 catalog 信息放进同一套时间与标签体系。面板擅长发现“何时、哪里、哪个查询族”异常；最终解释仍要能落回 exporter expression、PostgreSQL view、日志字段和计划 artifact。

8.6.1 用指标语义定位时间、实例与查询

从宽到窄浏览，而不是从某张“慢查询”表直接猜根因：

用户 SLI / 告警时间窗
  → cluster/service 是否整体异常
  → primary/replica/instance 差异
  → database/user/application/session
  → query family/queryid
  → wait/lock/plan/resource
  → 原生证据与受控实验

在当前 Pigsty dashboard 分类中，可按问题选择入口：

这些名字是 Pigsty 的当前参考实现，不是永恒导航路径。若版本调整 dashboard，仍按“影响范围 → 实例 → 会话/query → 原生证据”的语义寻找。

打开任何图前，先读变量和 expression：

• 当前 cluster/service/instance/database/query 变量是什么；
• timezone 与 absolute start/end 是什么；
• unit 是 seconds、milliseconds、bytes、rows 还是 ratio；
• 原始 metric 是 counter、gauge 还是 histogram；
• rate()/increase() 窗口与 Grafana step 是多少；
• label 是否在 recording rule 中被聚合掉；
• primary role/failover 前后 instance identity 是否变化；
• No data 表示 0、未抓取、权限不足还是 exporter 错误。

颜色是展示规则，不是 PostgreSQL 语义。同一个红色可能代表固定阈值、动态 baseline 或只是主题配置；同一个“QPS”可能是 query counter rate、transaction rate 或 application request rate。下结论前保存 panel query、变量、时间范围与 datasource。

一个可靠的面板收敛过程：

1. 把用户事故窗口扩大到前后各一段，观察变化点；
2. 用同星期/相近流量窗口做 baseline；
3. 对比 primary/replica、受影响/未受影响实例；
4. 找到 database/user/application/queryid，而非只看 cluster 总量；
5. 同屏核对 calls/total/rows、wait/locks、CPU/I/O、pool queue；
6. 保存 absolute UTC window 和 query identity；
7. 用下一目的 SQL/log/plan 复核。

8.6.2 用 SQL、日志和计划复核面板判断

面板提示 Lock wait 后，原生复核应至少得到一条边：

SELECT
    clock_timestamp() AT TIME ZONE 'UTC' AS captured_at_utc,
    pid,
    backend_start,
    datname,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    xact_start,
    query_start,
    query_id
FROM pg_stat_activity
WHERE datname = :'database'
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST;

面板提示某 query family 预算上升后，保存累计边界：

SELECT stats_reset
FROM pg_stat_statements_info;

SELECT
    userid, dbid, queryid, calls,
    total_exec_time, mean_exec_time, max_exec_time,
    rows, shared_blks_hit, shared_blks_read,
    temp_blks_written, wal_bytes,
    query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid FROM pg_database WHERE datname = :'database'
)
  AND queryid = :'queryid'::bigint;

若 panel 来自 Prometheus counter，优先导出事故窗口的 expression/result，而不是把当前累计 SQL 行与历史五分钟 rate 直接比较。记录：

datasource + expression
absolute UTC range + step
all template variables
returned labels
counter reset/failover boundary

日志复核要能串到同一 session 或 query：

timestamp
PID + session id/backend_start
database/user/application/client
SQLSTATE
duration
query/queryid
lock/temp/auto_explain context

计划复核则保存可机器比较的 JSON 与环境：

SELECT version();
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'plan_cache_mode',
    'work_mem',
    'random_page_cost',
    'effective_cache_size',
    'track_io_timing',
    'compute_query_id'
)
ORDER BY name;

再在安全环境使用代表参数执行：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY, FORMAT JSON)
SELECT ...;

如果生产语句正在 Lock/ClientWrite 等待，activity 与日志可能已经足够证明直接机制；不要为了补一张计划图冒险重跑。计划回答“executor 选择与处理了什么”，wait 回答“采样时为什么没前进”，两者互补。

面板提示“连接用满”也要回到边界：

• PostgreSQL session count 与 state；
• PgBouncer client/server active/waiting；
• application pool acquire duration；
• HAProxy backend/session；
• service route 与 primary role。

仅看到 PostgreSQL connections 接近上限，无法区分连接泄漏、长事务、pool 配太大、流量上升或 failover 重连风暴。

8.6.3 不把截图、颜色或当前点击路径当作知识

截图可以证明“当时有人看见某个画面”，却通常缺少：

• panel expression 与数据源；
• 变量值与隐藏 filters；
• absolute start/end、timezone 和 step；
• unit、legend 聚合与 null handling；
• dashboard/version/commit；
• 原始样本与可重算结果；
• query/session identity；
• 图外的对照与反证。

因此证据包的优先级应是：

1. 原始 SQL/CSV/JSON/log/Prometheus result
2. query/expression、变量、时间范围、版本与 source hash
3. 机器断言和人工解释
4. 截图作为定位/沟通附件

可长期保留的知识应写成语义：

若 endpoint p99 退化：
  先锁定 absolute UTC window 和 event count；
  比较 cluster/instance/database/application/query scope；
  若 activity 显示 active + Lock，保存 blocker edge；
  若 active + ClientWrite 且 blockers 为空，转查结果消费；
  若无稳定 wait，再进入 plan/CPU/I/O 假设；
  所有结论回到原始 artifact。

而不是：

打开左边第 3 个 dashboard，
点右上角红色方块，
再点第二行蓝色链接。

前一种写法能跨 Pigsty/Grafana 版本、主题和自定义 dashboard；后一种在下一次升级就失效。需要记录当前实现时，附上：

Pigsty version
dashboard UID/title/revision
Grafana/Prometheus datasource
exporter and recording-rule source hash
PostgreSQL major/minor

面板本身也需要验证。常见故障包括 exporter down、scrape timeout、label 重命名、recording rule 计算错误、queryid 类型/符号处理、counter reset 未处理和 dashboard variable 选错实例。若面板与原生 SQL 矛盾，先核对口径与时间，不要自动相信“更漂亮”的一方。

本章的方法把 Pigsty 定位为可观测性工作台：

Pigsty 快速收敛范围
  + PostgreSQL 证明机制
  + 应用/代理补全数据库外时间
  + 受控实验区分竞争解释
  + evidence bundle 供复核

下一节把这条链放进三个外观相似、修复方向完全不同的可复现实验。

参考资料

• Pigsty：PostgreSQL Dashboards
• PostgreSQL 18：Cumulative Statistics System
• PostgreSQL 18：pg_stat_statements

上一节：设计受控实验 · 返回本章目录 · 下一节：实战：三种“慢”只修真正瓶颈 · 查看全书目录 · 查看索引中心

8.7 实战：三种“慢”只修真正瓶颈

三个 case 都制造“请求没有及时返回”，但修复方向互斥：

实验不以单次 elapsed time 或节点名作为 golden。它保存机器可读 signal，再由一个不能读取答案文件的分类器只按证据关系判断。

8.7.1 估算错误、锁等待与客户端慢消费

确认 service 指向可写、可演练的 L1：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
cd static/labs/ch08
export PG36_EVIDENCE_DIR="$PWD/evidence/ch08/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

若 ch07 fixture 已通过，all 直接复用；若缺失，则调用 ch07 marker guard 做受控重建。之后顺序为：

ch04/ch05 preflight
  → estimate case → normalize signals → diagnose
  → lock case     → normalize signals → diagnose
  → client case   → normalize signals → diagnose
  → seeded mystery
  → deliberately wrong diagnosis → reveal must fail
  → answer-blind diagnosis → reveal must pass
  → final worker/model/fixture verify
  → semantic review

一次实测输出：

status=ok
classifications=estimate-plan,lock-wait,client-slow-consumer
mystery=client-slow-consumer/matched=true/wrong-guess-rejected=true
same-seed-reproducible=true/answer-mode=0600
state-restored=true/remaining-workers=0/
relation-checksum=f8a7bfae59c6d16cd323abecfefe1014

case A：estimate/plan

estimate-case.sh 复用 ch07 tenant skew，用同一 SQL、同一 hot parameter 分别捕获：

force_generic_plan:
  estimate=100
  actual=90000
  cardinality error=900x

force_custom_plan:
  estimate=90000
  actual=90000
  cardinality error=1x

本次机器上 generic 是 Index Scan、custom 是 Seq Scan，但分类器不以节点名判定；不同硬件、cost setting 或 PostgreSQL 版本可能选择不同 shape。稳定事实是 generic 用总体平均选择率严重低估 hot parameter，custom 能看见具体参数并改善估算。

这也不自动宣布永久修复应是 force_custom_plan。生产还要比较：

• hot/cold traffic weight；
• planning 与 execution 成本；
• prepared statement/driver/pool 行为；
• statistics、SQL/index 是否有更好解；
• 并发下延迟、blocks 与写副作用。

case B：lock wait

lock-case.sh 调用第 5 章的确定性编排：

blocker 在事务内更新 order 1002 并进入受控 hold
  → 普通 reader 仍看见旧 committed version
  → waiter 尝试更新同一行
  → observer 捕获 waiter → blocker 精确边
  → 只取消 exact blocker
  → blocker rollback
  → waiter 前进并 rollback
  → order fingerprint 不变

中性 signals：

{
  "state": "active",
  "wait_event_type": "Lock",
  "wait_event": "transactionid",
  "blocking_pid_count": 1,
  "plan": null,
  "state_restored": true,
  "remaining_workers": 0
}

直接原因是锁等待，不是 waiter 的执行计划。长期修复应回到 blocker 所属事务：为何持锁、是否在事务内睡眠/调用外部服务、锁顺序是否一致、更新集合是否过宽。取消是止血且只允许精确命中实验身份。

case C：client slow consumer

client-write-lab.sh 用 generate_series 生成大结果，由 slow-reader.py 每次只读少量字节并等待。它不读业务表，也不创建对象。

observer 必须同时看到：

state=active
wait_event_type=Client
wait_event=ClientWrite
blocking_pid_count=0

随后用 PID + backend_start epoch + database + unique application name 精确执行 pg_cancel_backend，等待 pipeline 退出并确认 worker=0。

这个 case 特意证明 planner 的 elapsed/cost 边界：server 可能很快产生数据，却因客户端不读而长时间不返回。修复应查返回规模、分页/流式协议、driver fetch、客户端线程与网络；取消任意 PID或加索引都没有解释证据。

8.7.2 随机隐藏一种根因，先独立诊断再揭晓

为避免“知道脚本名再写结论”，运行 seeded mystery：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch08/mystery-$(date -u +%Y%m%dT%H%M%SZ)"

# 可选：同一 seed 会选择同一 case；不设置则自动生成
export PG36_CASE_SEED='team-drill-2026-07-29'

./task.sh mystery
python3 -m json.tool "$PG36_EVIDENCE_DIR/public/signals.json"

PG36_CASE_SEED 经 SHA-256 后按三类取模。public 目录只给出 metadata、raw evidence 与中性 signals.json；ground truth 写到：

$PG36_EVIDENCE_DIR/.sealed/answer.json

其 mode 为 0600。这防止正常步骤误读，不是同一 OS 用户之间的加密安全边界；知道源代码和 seed 的人可以作弊。盲测的纪律是先不读取 .sealed，独立填写诊断记录模板：

观察到的 state/wait/blocker/plan 事实
最可能分类
两个被排除的替代解释
还缺什么证据
最小修复实验与回退

再运行答案盲诊断器：

# diagnose 只读 public/signals.json；离线运行，不需要数据库连接
env -u PGSERVICEFILE ./task.sh diagnose
python3 -m json.tool "$PG36_EVIDENCE_DIR/public/diagnosis.json"

diagnose.py 的三条规则是：

active + Lock + blockers>0
  → lock-wait

active + Client/ClientWrite + blockers=0
  → client-slow-consumer

no Lock/ClientWrite + generic error>=100x + custom error<=2x
  → estimate-plan

它的输出固定声明：

"answer_artifact_read": false

最后揭晓：

env -u PGSERVICEFILE ./task.sh reveal
python3 -m json.tool "$PG36_EVIDENCE_DIR/public/reveal.json"

若 diagnosis 与 sealed ground truth 不一致，reveal 写 matched=false 并返回非零，不能把错误猜测包装成通过。task.sh all 内置一个故意错误的负对照，稳定验收要求该负对照失败。

想提交人工分类时，可在 public 目录创建最小 JSON：

{
  "diagnosis": "lock-wait"
}

然后指定：

export PG36_DIAGNOSIS_FILE="$PG36_EVIDENCE_DIR/public/my-diagnosis.json"
export PG36_REVEAL_FILE="$PG36_EVIDENCE_DIR/public/my-reveal.json"
./mystery.sh reveal

先写假设再 reveal 的顺序比“猜错后改答案”更接近真实事件复盘。

8.7.3 输出证据包、假设树、修复前后对照与复位结果

task.sh all 的 evidence 结构按用途分层：

manifest.txt                  versions + target facts + source hashes
preflight.txt                 ch04/ch05 state before
ch07-fixture.txt              reused or controlled-rebuild
estimate/
  generic-hot.json
  custom-hot.json
  signals.json
  diagnosis.json
lock/
  raw/activity.csv
  raw/locks.csv
  raw/summary.txt
  signals.json
  diagnosis.json
client/
  raw/activity.csv
  raw/summary.txt
  signals.json
  diagnosis.json
mystery/
  public/...
  .sealed/answer.json
verify.txt
review.json

其中 raw artifact 负责可复核，中性 signals 负责教学分类，diagnosis 负责解释和排除项，review 只断言稳定关系。不要删 raw 只保留 status=ok。

baseline-v0.3-proposal.json 把本章证据追加到 DEFAULT-EVID-009：慢请求证据必须包含影响范围、 UTC 时间窗、wait/blocking edge、query/parameter identity、竞争假设和 复位结果；盲测诊断不得读答案，错误负对照必须失败。它绑定不可变 v0.1 checksum，并绑定 ch07 v0.2 proposal 的 canonical checksum；在 v0.2 尚未晋升前，它仍只是有依赖的 v0.3 candidate，不冒充已发布规约。

一份生产级证据包还应补齐：

• 用户 SLI 时间窗、样本数、吞吐、并发、错误；
• request/trace/application release identity；
• service/cluster/instance/database/user/application；
• PID + backend_start（若做实时会话动作）；
• query family、参数分桶与数据敏感处理；
• Prometheus expression、Grafana variables/step/datasource；
• 日志 session、SQLSTATE、duration 与采样率；
• before/after 计划、statistics、settings、版本；
• 假设排序、反证、审批动作与回退阈值；
• 修复前后 correctness/SLO/resource 副作用；
• artifact hash、访问权限与保留期限。

本章没有持久 ch08 对象，所以没有“删库式 reset”。复位是每个 case 的成功条件：

estimate session ends
lock transactions rollback
client worker exactly cancelled
all lab workers disappear
ch07 fixture remains valid
ch04-v1 business checksum unchanged

手动复核：

export PG36_EVIDENCE_DIR="$PWD/evidence/ch08/verify-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh verify
cat "$PG36_EVIDENCE_DIR/verify.txt"

若 all 为本章自动重建了 ch07 fixture，它会保留供后续计划/索引实验使用。清理 ch07 属于另一个 R2 动作，只能按第 7 章的双 token reset 执行；本章不会替读者擅自删除。

完成实验后，读者应能在看到“请求卡住”时先问：

它尚未进入数据库、正在执行、正在等谁，还是等客户端？
哪条原生证据能区分？
最小动作怎样让不同假设产生不同结果？
怎样证明动作没有留下新问题？

下一章只有在证据指向访问路径时才进入索引设计，避免把索引当所有慢请求的通用药方。

上一节：从可观测面板回到原生证据 · 返回本章目录 · 下一章：巧夺天工：索引设计与效果验证 · 查看全书目录 · 查看索引中心