跳至内容
27 精益求精：参数调优与资源治理
27.4 规划器、并行与连接参数

27.4 规划器、并行与连接参数

规划器参数决定 PostgreSQL 如何比较候选 plan；并行参数决定 plan 可请求多少 worker； 连接参数决定多少 backend 能同时竞争资源。

三者形成一条链：

cost/statistics
  -> chosen plan and requested workers
      -> active backend/worker population
          -> CPU/memory/I/O/lock demand
              -> pool queue and SLO

把它们分开调，容易得到一个单 query 很快、cluster 却更慢的系统。

27.4.1 成本参数只能用硬件和计划证据校准

cost 是相对单位

常见：

seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
parallel_setup_cost
parallel_tuple_cost

它们不是毫秒，默认以 sequential page cost 为相对基准。把所有 cost 同乘 10，plan 通常不变；重要的是相对值。

random_page_cost

降低它会让 random/index access 相对便宜，可能使 planner 更偏向 index scan。正确 证据：

actual storage random vs sequential latency
cache residency
concurrent workload
tablespace/storage difference
representative plans and actual time/buffers

PostgreSQL 官方指出，默认 4.0 已隐含一部分 random access 会命中 cache；完全 cached 时较低值可合理，random 物理 I/O 昂贵时则可能需要较高值。

不能这样校准：

query uses seq scan
  -> random_page_cost = 1.0

seq scan 可能就是正确 plan，或根因是 statistics/index/predicate。

tablespace 可有不同 cost

若 hot index 在 NVMe、archive table 在 HDD，可在 tablespace scope 设置 page cost， 不必用一个 cluster-global average 强迫两种 storage：

ALTER TABLESPACE fast_ssd
SET (
    random_page_cost = 1.1,
    seq_page_cost = 1.0
);

仍需把 filesystem/cache 与 production I/O 测量纳入。

effective_io_concurrency

它表达 PostgreSQL 可以向 storage 发起的并发 I/O 提示能力；合理值取决于 device/ filesystem/RAID/cloud volume 和 PostgreSQL I/O implementation。大值不是免费吞吐：

  • device queue 可能更深；
  • tail latency 可能变差；
  • shared storage 邻居受影响；
  • query/scan type 可能不使用；
  • OS/backend 的实现随 major version 演进。

参考沙箱为 200、source 是 configuration file；这不能直接复制到 HDD 或网络块存储。

effective_cache_size

只影响 planner estimate，不分配 memory。校准时考虑：

shared_buffers
+ PostgreSQL data 可实际使用的 kernel cache
- concurrent queries sharing cache
- same blocks duplicated in two caches

不要写成 host RAM 总量，也不要当作 cache guarantee。

statistics 优先于 cost hack

如果 estimated rows 错数个数量级：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

先查：

ANALYZE recency/sample
default_statistics_target
ALTER COLUMN SET STATISTICS
extended statistics: dependencies/ndistinct/mcv
expression statistics
partition statistics
parameter/cast/collation

成本模型在错误 cardinality 上做得再精细，也会比较错误规模的 plan。

enable_* 是诊断探针 

SET LOCAL enable_hashjoin = off;
SET LOCAL enable_nestloop = off;
SET LOCAL enable_seqscan = off;

可以回答：

planner 若被迫使用另一路径，实际是否更好？

不应默认成为永久 global 配置。某种 plan type 对当前一条 query 不好，不代表对全库 无用。

plan cache

prepared statement 有：

custom plan
  parameter value known
  planning repeated
  can adapt to skew

generic plan
  reusable
  lower planning cost
  cannot adapt to specific value

plan_cache_mode=auto 通常先生成若干 custom plan，再比较 generic cost。可以用：

SELECT
    name,
    generic_plans,
    custom_plans,
    parameter_types
FROM pg_prepared_statements;

第 27 章实验中，两个 probe 在 auto 下均为：

custom_plans  5
generic_plans 5

说明自动策略已经切换。强制 generic 的长期 TPS 益处没有达到 2% 置信下界。

对于 tenant skew：

tenant small -> index/nested loop
tenant huge  -> bitmap/hash/seq alternative

强制 generic 可能让其中一类严重退化。probe 必须覆盖高低/MCV/边界，而不是只用 id=1。

JIT

JIT 有 compilation/setup cost，也可能加快长 CPU-intensive execution。阈值由 plan estimated cost 决定：

jit_above_cost
jit_inline_above_cost
jit_optimize_above_cost

OLTP point query 常低于阈值；把 jit=off 后看到无差异不证明 JIT 没用，只说明当前 query 没触发或收益不足。分析 query 要把：

planning/JIT generation time
execution time
repetition/cache
CPU

分开。

calibration report

query classestimated rowsactual rowsplanbuffers/I/Oexecutionalternativeresult
point
range
tenant-small
tenant-large
analytic

只从两条 query 推导 cluster cost constants 很危险。官方文档也指出，没有定义良好的 “理想 cost”求法，应把它视为 workload average。

27.4.2 并行 worker 的全局预算与退化条件

三层上限 

max_worker_processes
  hard pool for background workers

max_parallel_workers
  cluster parallel operation subset

max_parallel_workers_per_gather
  one Gather/Gather Merge request

还包括：

max_parallel_maintenance_workers
extension background workers
logical replication workers

提高 per-gather 而不提高上层 pool，可能没有效果；提高上层 pool 又会增加 cluster CPU/ memory contention。

plan request 不等于实际 worker 

Workers Planned: 4
Workers Launched: 2

worker unavailable 时，query 通常以更少 worker 运行；某些 parallel plan 在 worker 不足时效率很差。要记录：

planned
launched
launch wait/starvation
leader participation
other concurrent parallel queries

PostgreSQL 18 的 database statistics 还提供 parallel worker launch 相关累计信息； system view 随版本变化，先查当前列。

leader 也可能工作

parallel_leader_participation=on 时 leader 可以执行 parallel plan，也要负责读取 worker tuple。若 worker 输出大量 tuple，leader 可能主要消耗在汇总/传输；parallel speedup 不会等于 worker 数。

Amdahl：

$$ S(N)

\frac{1} {(1-P)+P/N+\text{parallel overhead}} $$

serial fraction、launch、tuple transfer 与 skew 限制 speedup。

memory 按 process 放大

PostgreSQL 官方给出的关键边界：parallel query 的 resource limit 通常按 worker process 应用。4 个 worker 加 leader，某些 node 的内存/CPU/I/O footprint 可接近串行的 5 倍。

因此：

max_parallel_workers_per_gather=4
work_mem=256MB

不能解释成“一条 query 最多 256MB”。

全局 CPU budget

若 host 有 $C$ cores、需要为 OLTP 保留 $R$ cores：

$$ C_{\text{parallel budget}} \le C-R-\text{background/failure headroom} $$

并行分析的 admission：

$$ \sum_q \text{active}_q \times (1+\text{workers}_q) \le \text{process/CPU budget} $$

不是“每条报表允许 8 worker，所以十条报表都允许 8”。

什么时候 parallel 反而慢

  • query 太短，launch/setup 占比高；
  • output 太大，leader/tuple transfer 成为瓶颈；
  • worker skew，一人做绝大多数工作；
  • storage 已饱和；
  • memory spill 按 worker 放大；
  • concurrent OLTP 被抢 CPU；
  • worker pool 不足；
  • parallel-unsafe/restricted function；
  • serialization/ordering overhead。

接受时比较：

single-query duration
cluster throughput
OLTP tail
CPU/I/O/memory
worker availability
N+1 state

维护 worker

parallel CREATE INDEX/VACUUM 与 parallel query 的 memory limit 语义不完全相同， 但仍争抢 CPU/I/O/worker。DDL window 中提高 maintenance worker，可能缩短单项任务， 却把 replica/archive 与 OLTP 推过 SLO。

角色级治理

分析角色：

ALTER ROLE dbuser_analytics
SET max_parallel_workers_per_gather = 4;

OLTP 角色：

ALTER ROLE dbuser_app
SET max_parallel_workers_per_gather = 0;

只是示意，不是默认建议。要验证新连接、pool lifecycle 和 query class。role scope 比 global 更贴近 ownership，但一个 role 内也可能混合 workload。

27.4.3 连接上限、超时和锁等待边界

connection slot 不是并发目标

max_connections 是 server 能接纳的 backend 上限。提高它：

  • 预留更多 shared structures；
  • 允许更多 private backend；
  • 扩大 active query/memory/lock population；
  • 增加 context switch；
  • 可能降低每条 query cache locality；
  • 使 overload 更深。

它不增加 CPU、memory bandwidth、IOPS 或 lock throughput。

参考沙箱：

max_connections = 500
context         = postmaster
source          = command line
RAM             ~1.91 GiB

这是 platform/template fact，不表示 500 个 64MB-work-mem query 可同时 active。

连接预算

$$ \text{max connections} \ge \text{app server connections} + \text{replication} + \text{maintenance} + \text{monitoring} + \text{reserved/emergency} + \text{migration overlap} $$

同时：

$$ \text{active app connections} \le \text{resource/SLO envelope} $$

两条都要满足。通常：

logical client population
  > pool client connections
  > active server connections

reserved slots

PostgreSQL 18：

reserved_connections
superuser_reserved_connections

前者供拥有 pg_use_reserved_connections 的角色，后者是 superuser 最后保留。设计：

  • emergency role 最小权限；
  • pool 不耗尽 reserved；
  • monitoring/replication 配额；
  • incident 时实际演练能连接；
  • standby 的 max_connections 不低于 primary。

把 emergency slot 留给日常应用，相当于没有 reserve。

pool queue 比 database overload 更可控

在 pool：

queue depth
wait time
timeout
admission/fairness

可以观察和限制。把 server connection 上限提高，会让更多 request 同时进入 executor/ lock/memory，queue 仍然存在，只是搬到了更危险的位置。

pool sizing 要配合第 22 章 transaction/session semantics：

  • session state；
  • prepared statement；
  • temp table；
  • advisory lock；
  • LISTEN/NOTIFY；
  • transaction pooling reset。

statement_timeout

从 command 到达 server 开始计，extended protocol 对 Parse/Bind/Execute/Sync 有具体 边界。它终止 statement，不等于 HTTP request deadline。

不要设置一个过于激进的 global value杀掉：

DDL
backup catalog
maintenance
replica diagnostic
legitimate report

优先按 role/database：

ALTER ROLE dbuser_app
IN DATABASE app
SET statement_timeout = '2s';

新 session 生效。

lock_timeout

只在等待 lock 时计时，并且每次 lock acquisition 单独应用。若它等于或大于 statement_timeout，往往 statement timeout 先触发。

常见关系：

lock_timeout < statement_timeout <= request deadline

但 transaction 内多条 statement、client network 和 retry 仍需 budget。

DDL migration 常用短 lock_timeout 来避免排队阻塞业务：

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5min';
ALTER TABLE ...;
COMMIT;

失败要退出/重试，不应无限 loop。

idle 与 transaction timeout 

idle_in_transaction_session_timeout
  session 在 open transaction 中 idle
  防止长期持锁/阻碍 vacuum

idle_session_timeout
  无 transaction 的 idle session
  pool 中要谨慎，middleware 未必处理意外关闭

transaction_timeout
  整个 transaction 存活时间
  prepared transaction 不受其约束

PostgreSQL 18 官方指出，不建议把某些 timeout 粗暴写成影响所有 session 的 postgresql.conf default。scope 应跟 workload。

timeout 不是 cancel 后就结束

应用必须：

observe SQLSTATE
rollback failed transaction
release/replace connection
respect outer deadline
limit retries
preserve idempotency

否则 database cancel 后，client 立即重试可能制造 retry storm。

lock diagnosis

不要为了更快报 deadlock，把 deadlock_timeout 全局调到 1ms。deadlock check 有成本， 普通 lock wait 不是 deadlock。

调查：

SELECT
    pid,
    pg_blocking_pids(pid) AS blockers,
    wait_event_type,
    wait_event,
    xact_start,
    query_start
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

配合 log_lock_waits 与适当 deadlock_timeout，在诊断窗口使用。最终修复通常是：

  • transaction order；
  • shorten critical section；
  • index/access path；
  • hot-key design；
  • admission；
  • application retry；

而不是无限提高 timeout。

一份连接/超时合同 

application_deadline: 2500ms
pool_wait_timeout: 200ms
connect_timeout: 300ms
lock_timeout: 400ms
statement_timeout: 1800ms
transaction_timeout: 2200ms
idle_in_transaction_timeout: 30s
retry:
  max_attempts: 2
  budget_included_in_deadline: true
server_connections:
  active_cap: 48
  reserved_platform: 12
  emergency: 5

数值只是示意。关键是所有 timeout 和 slot 在同一个 end-to-end budget 中，不互相 矛盾。

上一节：WAL、检查点与写入平滑 · 返回本章目录 · 下一节：参数作用域与变更方式 · 查看全书目录 · 查看索引中心

最后更新于
27.3 WAL、检查点与写入平滑27.5 参数作用域与变更方式