跳至内容
27 精益求精：参数调优与资源治理
27.1 调优是一套实验方法

27.1 调优是一套实验方法

参数调优回答的不是：

这个参数设多大最好？

而是：

在哪一个 workload、SLO、硬件、版本和失效状态下，改变哪个机制，能以可接受的 副作用改善哪一个目标？

这句话里每一项都不能省。

一个参数没有脱离上下文的最优值：

work_mem=256MB

对一条串行分析查询可能合理；对 300 个并发 backend、每条 plan 有多个 hash/sort、 每条还启动 4 个 parallel worker 的系统，可能是 OOM 设计。

调优因此和第 26 章容量实验使用同一套科学方法，只把 factor 从 workload/load 换成 GUC：

question
  -> mechanism hypothesis
      -> baseline
          -> one controlled change
              -> repeated measurement
                  -> effect + uncertainty
                      -> non-regression
                          -> accept / reject / inconclusive

27.1.1 先定义目标、瓶颈和不可退化指标

先写 objective function

“数据库变快”不可验收。把目标写成有 scope 的 metric：

workload: checkout-v7
traffic: 1800 offered requests/s
dataset: 1.2 TiB, tenant skew P95
objective:
  checkout_p95_ms: "<= 120"
  completion_ratio: ">= 99.95%"
constraints:
  replica_freshness_p95_s: "<= 2"
  archive_backlog_recovery_min: "<= 15"
  node_memory_available_gib: ">= 8"
  cpu_busy_p95: "<= 65%"
  durability: "local WAL flush before success"
failure_state:
  - normal
  - one_read_replica_lost

没有 constraints 的“优化”会把成本转移到别处。

metric 分四类

类别例子作用
primary objectivep95、throughput、batch duration想改善什么
correctnessrows、checksum、serialization result不能算错
safetyOOM、disk、WAL、replica/archive gap不能失控
operabilityrestart、rollback、recovery、observability不能不可运维

性能 A/B 必须同时校验结果正确。一次 hash join 因错误 filter 少处理 30% 行而“快了” 不是调优。

找 service center，不找最高指标

候选瓶颈：

CPU execution
planning/JIT
data read
WAL write/sync
lock and transaction queue
pool/admission queue
memory spill/reclaim
checkpoint/writeback
replica/archive backpressure
network/client

每个假设至少需要：

symptom
mechanism evidence
corroborating resource evidence
plausible counter-evidence

例如：

假设symptomPostgreSQLOS/Pigsty反证
sort spillp95 随数据量跳升EXPLAIN disk、temp blocksdisk temp I/O无 Sort/Hash 节点
WAL synccommit tailWAL I/O waitWAL device fsync tailCPU saturated
CPUthroughput kneeactive/no wait、exec timebusy/run queueclient/pool/lock wait
locklong tailblockers/wait eventCPU 可低无 lock queue

“CPU 84%”本身不是参数假设。它只说明 CPU 是候选 service center。还要问：

CPU 花在 executor、planner、JIT、compression、TLS、context switch，
还是 client/hypervisor 计费误差？

把 evidence 映射到 parameter mechanism 

observed spill
  -> work_mem/hash_mem_multiplier candidate

requested checkpoints too frequent
  -> max_wal_size/checkpoint_timeout candidate

full-page-image WAL dominates
  -> checkpoint interval/wal_compression candidate

planner systematically misprices random I/O
  -> cost parameter candidate

worker launch starvation
  -> worker-pool budget candidate

反例：

slow query
  -> work_mem?

high CPU
  -> shared_buffers?

high connection count
  -> max_connections?

问号前缺少机制，不应进入变更。

不可退化指标要在实验前写

如果跑完才决定什么算 regression，人会自然挑对 candidate 有利的解释。预先写：

correctness equality
failure/timeout/skipped
p50/p95/p99/max
CPU and memory
temp and WAL bytes
lock waits/deadlocks
replica/archive lag
backup/maintenance duration
recovery behavior

某些指标是 hard gate：

wrong result             reject
durability changed       reject or separate product decision
OOM / restart            reject
unbounded WAL retention  reject
rollback unproven        reject

某些是 tradeoff：

batch 20% faster
OLTP p95 2% slower
CPU 8% higher

是否接受取决于预先声明的 objective 和 budget。

baseline 必须仍然存在

“改前数据是上个月 dashboard，改后是今天”不是 A/B。至少固定：

source/version
hardware/topology
data snapshot/generator
statistics
workload/arrival
connection path
background policy
measurement window

对于 restart 参数，不能同时：

升级 minor version
修改 kernel
换 storage
ANALYZE 全库
改参数

然后把差异归给参数。

27.1.2 一次改变一个机制并准备回退

one factor 不一定等于 one GUC

有些机制需要一组一致变化：

parallel worker budget:
  max_worker_processes
  max_parallel_workers
  max_parallel_workers_per_gather

它们可以作为一个 factor，但要明确：

  • 为什么必须一起变化；
  • 每项如何约束同一机制；
  • 哪项是 hard upper bound；
  • 如何整体回退。

相反，同时改：

work_mem
random_page_cost
max_connections
checkpoint_timeout

是四个机制，不能从结果归因。

优先用最小作用域证伪

PostgreSQL 的 parameter context 决定最小 scope。若参数允许 user context，先尝试：

BEGIN;
SET LOCAL work_mem = '32MB';

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

ROLLBACK;

或为一次客户端进程：

env PGOPTIONS="-c plan_cache_mode=force_generic_plan" \
  pgbench ...

PostgreSQL 官方 Setting Parameters 说明，session SET 和 libpq PGOPTIONS 不影响其他 session。这使证伪成本和 blast radius 最小。

但“session 能设”不代表生产应由每个请求随便设。通过实验后仍要决定：

query/transaction
role-in-database
role
database
instance
cluster

哪个 scope 与 ownership 一致。

paired experiment

当环境噪声随时间变化，A/B 配对比“先跑十次 A，再跑十次 B”更可靠：

pair 1  A -> B, same seed/data
pair 2  B -> A, same seed/data
pair 3  A -> B
pair 4  B -> A

每对 effect：

$$ r_i

\frac{X_{B,i}}{X_{A,i}} $$

报告：

median(r_i)
bootstrap interval of median(r_i)

不是：

$$ \frac{\text{median}(B)} {\text{median}(A)} $$

二者在配对噪声下可能不同。

最小显著收益

统计上可分辨不等于工程上值得。预声明：

minimum material gain  2%
measurement noise      assessed from repetition
tail regression budget <= 5%

若 candidate 提高 0.3%，却增加 configuration exception、plan risk 和 review burden， 通常不值得持久化。

第 27 章正式 run 的规则：

paired TPS ratio bootstrap 95% lower >= 1.02
candidate/baseline pooled p95 <= 1.05
plan shapes equal
failure/late/skipped = 0
global settings unchanged

结果 lower bound 0.9808，所以拒绝。

rollback 不是“把值改回去”

回退合同包含：

previous value and source
previous desired-state commit
change scope
whether reload/restart is needed
session/pool lifecycle
HA member order
data/WAL produced under candidate
stop condition
rollback validation

例子：把 synchronous_commit 改回 on，不会重新赋予此前已向客户端确认但尚未 flush 的 transaction durability。参数值能回退，历史语义不能。

增加 max_connections 后又减回去，若当前 session 已超过新上限，现有 session 不会 神奇消失；还要协调 pool 与 reconnect。

变更状态机 

proposed
  -> static reviewed
      -> sandbox falsified
          -> canary
              -> observation window
                  -> accepted
                  -> rolled back
                  -> inconclusive

每一步有 evidence 和 owner。不要把“SQL 执行成功”当作 accepted。

stop condition

实验中发现以下任何一项立即停止：

  • correctness mismatch；
  • failed/late/skipped 越界；
  • memory safety floor；
  • replica/archive gap 无界增长；
  • HA role/topology 变化；
  • client saturation；
  • config source/pending restart 异常；
  • cleanup 不再可证明。

stop 后保留失败 evidence，不重新跑到“漂亮”为止。

27.1.3 参数变化不修复错误 SQL 和错误模型

参数是系统 policy，不是 query patch

慢查询的优先顺序通常是：

correctness and transaction semantics
  -> query shape
      -> schema/index/statistics
          -> workload/admission
              -> parameter policy
                  -> hardware/topology

不是所有问题都严格按此顺序解决，但越靠前的错误越不应由全局参数掩盖。

cardinality 错误

planner 估 10 行，实际 10,000,000 行，可能选择 nested loop。把：

enable_nestloop = off

设为全局只是在压制一个 plan type。更好的证据路径：

ANALYZE freshness
per-column statistics target
extended statistics
correlated predicates
parameter-sensitive plan
expression/cast mismatch

PostgreSQL Query Planning 也把 enable_* 描述为粗粒度临时影响手段，并优先建议统计与 cost calibration。

缺索引或错误索引

把 random_page_cost 调低不会创建缺失的 access path。它可能让 planner 更偏爱现有 索引，但：

  • index key/order 不支持 predicate/order；
  • expression/collation/type 不匹配；
  • partial predicate 无法证明；
  • selectivity 太低；
  • index-only scan visibility 不足；

仍然存在。

无界查询

没有 pagination/limit、一次返回数百万行：

work_mem ↑
parallel workers ↑

可能让数据库更快地向网络和客户端制造巨大结果集，但没有修复 API contract。

长事务

idle_in_transaction_session_timeout 可以限制 idle transaction 伤害，却不应代替：

  • 应用 transaction boundary；
  • retry/timeout handling；
  • connection return；
  • cursor lifecycle；
  • batch chunking。

timeout 是最后防线，触发时 transaction 被中断；它不是无副作用的清理器。

N+1 与 chatty workload

把 max_connections 从 100 提到 1,000，不会把 1,000 次 serial round trip 变成一条 set-based SQL。它只允许更多 N+1 同时进入。

数据模型不匹配

把大 JSON 文档塞进一行，再频繁修改一个字段，可能带来：

TOAST rewrite
WAL amplification
index expression cost
MVCC churn

wal_compression 或 checkpoint 参数只能改变代价的一部分，不能修复 update model。

durability 不是性能参数

以下变化会改变业务承诺：

fsync=off
full_page_writes=off
synchronous_commit=off
unlogged table

它们不能与普通性能参数放在同一 A/B 后，仅因 TPS 更高就接受。

synchronous_commit=off 在某些明确允许“数据库 crash 时丢失最近成功确认事务”的 业务中可以成为产品设计，但需要：

business owner
loss window
idempotent recovery
observability
failure drill

而不是 DBA 的秘密加速。

参数模板是起点

Pigsty 的 tiny、oltp、olap、crit profile 根据硬件和 workload 类别生成合理 起点；官方 parameter optimization policy 也按场景区分模板。模板不知道：

  • 你的 query；
  • tenant skew；
  • SLO；
  • batch overlap；
  • failure budget；
  • hardware/storage tail；
  • 应用 pool 与 retry。

所以正确流程：

template baseline
  -> measure
      -> hypothesis
          -> scoped experiment
              -> ADR

不是：

internet parameter list
  -> production

一张问题分类表

现象先查参数候选出现的条件
单条 query 慢plan/rows/buffers/I/O机制已指向 memory/cost/parallel
全局 p95 上升load/queue/wait/resourceservice center 已定位
temp 暴增Sort/Hash、并发spill 与 memory budget 同时量化
WAL 暴增write mix/FPI/checkpointWAL composition 已分解
connection 拒绝pool/leak/admissiondemand 与 reserved slot 已设计
replica lagWAL/replay/query conflictsource/replay bottleneck 已区分
OOMactive plan/node/worker最坏并发模型已还原

本章后续所有参数都遵循这个约束：先解释机制和预算，再讲值。

返回本章目录 · 下一节：内存预算 · 查看全书目录 · 查看索引中心

最后更新于
27.2 内存预算