# 规划器、并行与连接参数 LLMS 索引： [llms.txt](/llms.txt) --- 规划器参数决定 PostgreSQL 如何比较候选 plan；并行参数决定 plan 可请求多少 worker； 连接参数决定多少 backend 能同时竞争资源。 三者形成一条链： ```text 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 成本参数只能用硬件和计划证据校准 {#item-27-4-1} ### cost 是相对单位 常见： ```text 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。正确 证据： ```text 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 昂贵时则可能需要较高值。 不能这样校准： ```text 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： ```sql 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。校准时考虑： ```text 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 错数个数量级： ```sql EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; ``` 先查： ```text 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_*` 是诊断探针 ```sql SET LOCAL enable_hashjoin = off; SET LOCAL enable_nestloop = off; SET LOCAL enable_seqscan = off; ``` 可以回答： > planner 若被迫使用另一路径，实际是否更好？ 不应默认成为永久 global 配置。某种 plan type 对当前一条 query 不好，不代表对全库 无用。 ### plan cache prepared statement 有： ```text 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。可以用： ```sql SELECT name, generic_plans, custom_plans, parameter_types FROM pg_prepared_statements; ``` 第 27 章实验中，两个 probe 在 auto 下均为： ```text custom_plans 5 generic_plans 5 ``` 说明自动策略已经切换。强制 generic 的长期 TPS 益处没有达到 2% 置信下界。 对于 tenant skew： ```text 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 决定： ```text jit_above_cost jit_inline_above_cost jit_optimize_above_cost ``` OLTP point query 常低于阈值；把 `jit=off` 后看到无差异不证明 JIT 没用，只说明当前 query 没触发或收益不足。分析 query 要把： ```text planning/JIT generation time execution time repetition/cache CPU ``` 分开。 ### calibration report | query class | estimated rows | actual rows | plan | buffers/I/O | execution | alternative | result | |---|---:|---:|---|---|---:|---|---| | point | | | | | | | | | range | | | | | | | | | tenant-small | | | | | | | | | tenant-large | | | | | | | | | analytic | | | | | | | | 只从两条 query 推导 cluster cost constants 很危险。官方文档也指出，没有定义良好的 “理想 cost”求法，应把它视为 workload average。 ## 27.4.2 并行 worker 的全局预算与退化条件 {#item-27-4-2} ### 三层上限 ```text 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 ``` 还包括： ```text max_parallel_maintenance_workers extension background workers logical replication workers ``` 提高 per-gather 而不提高上层 pool，可能没有效果；提高上层 pool 又会增加 cluster CPU/ memory contention。 ### plan request 不等于实际 worker ```text Workers Planned: 4 Workers Launched: 2 ``` worker unavailable 时，query 通常以更少 worker 运行；某些 parallel plan 在 worker 不足时效率很差。要记录： ```text 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 倍。 因此： ```text 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。 接受时比较： ```text 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。 ### 角色级治理 分析角色： ```sql ALTER ROLE dbuser_analytics SET max_parallel_workers_per_gather = 4; ``` OLTP 角色： ```sql ALTER ROLE dbuser_app SET max_parallel_workers_per_gather = 0; ``` 只是示意，不是默认建议。要验证新连接、pool lifecycle 和 query class。role scope 比 global 更贴近 ownership，但一个 role 内也可能混合 workload。 ## 27.4.3 连接上限、超时和锁等待边界 {#item-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。 参考沙箱： ```text 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} $$ 两条都要满足。通常： ```text logical client population > pool client connections > active server connections ``` ### reserved slots PostgreSQL 18： ```text 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： ```text 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杀掉： ```text DDL backup catalog maintenance replica diagnostic legitimate report ``` 优先按 role/database： ```sql ALTER ROLE dbuser_app IN DATABASE app SET statement_timeout = '2s'; ``` 新 session 生效。 ### `lock_timeout` 只在等待 lock 时计时，并且每次 lock acquisition 单独应用。若它等于或大于 `statement_timeout`，往往 statement timeout 先触发。 常见关系： ```text lock_timeout < statement_timeout <= request deadline ``` 但 transaction 内多条 statement、client network 和 retry 仍需 budget。 DDL migration 常用短 `lock_timeout` 来避免排队阻塞业务： ```sql BEGIN; SET LOCAL lock_timeout = '500ms'; SET LOCAL statement_timeout = '5min'; ALTER TABLE ...; COMMIT; ``` 失败要退出/重试，不应无限 loop。 ### idle 与 transaction timeout ```text 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 后就结束 应用必须： ```text 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。 调查： ```sql 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。 ### 一份连接/超时合同 ```yaml 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、检查点与写入平滑](../03/) · [返回本章目录](../) · [下一节：参数作用域与变更方式](../05/) · [查看全书目录](/toc/) · [查看索引中心](/indexes/)