参数调优与连接池

准备中…

调参放在全书最后，是因为不理解前面的机制，调参就只能抄模板。这一节把几个关键参数和它们背后的机制串起来。

先看当前配置

SELECT name,
     setting,
     unit,
     CASE unit
       WHEN '8kB' THEN pg_size_pretty(setting::bigint * 8192)
       WHEN 'kB'  THEN pg_size_pretty(setting::bigint * 1024)
       ELSE setting
     END AS 换算后
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
             'effective_cache_size', 'max_connections',
             'random_page_cost', 'seq_page_cost', 'effective_io_concurrency')
ORDER BY name;

⌘/Ctrl + Enter

shared_buffers：为什么不是越大越好

Postgres 有两层缓存：自己的 shared_buffers，以及操作系统的 page cache。读一个页面时先查前者，没有再向 OS 要（可能命中 page cache，也可能真读盘）。

CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT count(*) AS 缓冲区总数,
     count(*) FILTER (WHERE relfilenode IS NOT NULL) AS 已使用,
     count(*) FILTER (WHERE isdirty) AS 脏页数,
     pg_size_pretty(count(*) * 8192::bigint) AS 总容量
FROM pg_buffercache;

⌘/Ctrl + Enter

看看当前哪些表占着缓冲区：

SELECT c.relname AS 对象,
     count(*) AS 缓冲页数,
     pg_size_pretty(count(*) * 8192::bigint) AS 占用
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 8;

⌘/Ctrl + Enter

把 shared_buffers 设成内存的 80%，直觉上「缓存越多命中越高」。实际结果往往更差：

同一个页面在两层缓存里各存一份，等于浪费一半内存；
shared_buffers 越大，检查点要刷的脏页越多，I/O 尖峰越猛；
OS page cache 有更成熟的预读和淘汰算法，把内存全给 Postgres 反而放弃了它。

经验值是内存的 25%，上限一般不超过 40%。对内存特别大的机器（512GB+），继续往上加通常没有收益——工作集要么已经装下了，要么大到怎么都装不下。

判断方法是看缓存命中率（真实环境）：

SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0) AS 命中率
FROM pg_stat_database;

低于 99% 才考虑加。注意这个「未命中」里有相当一部分其实命中了 OS page cache，所以真实的磁盘读远比数字看起来少。

work_mem：最容易配错的参数

work_mem 限制单个执行节点在内存里能用多少空间做排序或哈希。超了就溢出到磁盘临时文件。

一个查询里如果有 3 个 Sort 节点和 2 个 Hash 节点，峰值可能占用 5 份 work_mem。再加上并行查询——每个 worker 各算各的，并行度 4 就是 20 份。

所以真实的内存上限估算是：

最大内存 ≈ 活跃连接数 × 每查询节点数 × 并行度 × work_mem

按 max_connections × work_mem 估算会严重低估。这是 OOM 事故的常见成因。

正确的做法不是全局调大，而是全局保守 + 按需放宽：

-- postgresql.conf 里保持较小，比如 16MB
-- 需要大排序的报表查询单独放宽
SET LOCAL work_mem = '512MB';
SELECT ... ORDER BY ... ;   -- 大排序

怎么知道该不该调？看执行计划里有没有 temp read/written：

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*) FROM orders GROUP BY user_id ORDER BY count(*) DESC;

⌘/Ctrl + Enter

maintenance_work_mem 是给 VACUUM、CREATE INDEX、ALTER TABLE 用的，可以设得大得多（1~2GB），因为同时跑的维护操作很少。建索引慢的时候，调它比调什么都管用。

effective_cache_size：一个纯粹的提示

这个参数不分配任何内存。它只是告诉优化器「操作系统大概能缓存多少数据」，用来估算索引扫描时有多少页能命中缓存。

设小了，优化器会高估索引扫描的 I/O 成本，倾向于选全表扫描。经验值是内存的 50~75%。

random_page_cost：SSD 上必须改

SELECT name, setting, short_desc
FROM pg_settings WHERE name IN ('random_page_cost', 'seq_page_cost');

⌘/Ctrl + Enter

默认 random_page_cost = 4.0 表示「随机读一个页比顺序读贵 4 倍」——这是为机械硬盘设的。

SSD 上随机读和顺序读的差距小得多，应该设到 1.1。这一个改动能让优化器在大量场景下从 Seq Scan 转向 Index Scan，往往是单个收益最大的参数调整。

effective_io_concurrency 同理：机械盘设 2，SSD 设 200 左右，让 Bitmap Heap Scan 能并发预读多个页面。

为什么 Postgres 几乎必须配连接池

这是本书最后一个「从架构推导出实践」的例子。

Postgres 的每个连接是一个独立的操作系统进程，不是线程。这个设计带来：

后果	影响
每个连接约 5~10MB 的私有内存	1000 个连接就是 5~10GB，还没算 `work_mem`
建立连接要 fork 一个进程	每次连接约几毫秒~几十毫秒，比 MySQL 贵得多
所有进程共享 `shared_buffers`，需要锁协调	连接数越多，锁竞争越激烈
快照可见性判断要遍历活跃事务列表	活跃连接越多，每次取快照越慢

结果是一条反直觉的曲线：连接数超过某个点后，增加连接会让总吞吐下降。

连接数 ≈ CPU 核数 × 2 + 有效磁盘数

一台 16 核 + SSD 的机器，最优的活跃连接数通常在 30~50 之间，而不是几百。

超过这个数的请求应该排队等待，而不是各自开一个连接进去互相拖慢。这正是连接池要做的事。

pgbouncer 的三种模式

模式	连接归还时机	复用率	限制
`session`	客户端断开时	低	无限制
`transaction`	每个事务结束时	高	不能用会话级特性
`statement`	每条语句结束时	最高	不能用多语句事务

transaction 模式是绝大多数场景的正确选择，它能让几千个应用连接复用几十个数据库连接。

代价是这些东西会失效，因为它们依赖「同一个后端进程」：

预处理语句（多数驱动可以关掉或用协议级支持）
SET 会话变量（要改成 SET LOCAL）
会话级咨询锁（改用 pg_advisory_xact_lock）
LISTEN / NOTIFY
临时表
WITH HOLD 游标

一份起步配置

给一台 16 核 / 64GB 内存 / SSD 的通用 OLTP 服务器：

shared_buffers = 16GB                  # 内存的 25%
effective_cache_size = 48GB            # 内存的 75%，只是提示
work_mem = 32MB                        # 保守，按需 SET LOCAL 放宽
maintenance_work_mem = 2GB             # 建索引 / VACUUM 用

max_connections = 200                  # 配合连接池，实际活跃控制在 50 以内

random_page_cost = 1.1                 # SSD
effective_io_concurrency = 200         # SSD

max_wal_size = 8GB                     # 减少检查点频率
wal_compression = on                   # 压缩全页写，几乎白赚

idle_in_transaction_session_timeout = '5min'   # 防长事务
log_lock_waits = on                    # 记录锁等待
log_min_duration_statement = '1s'      # 记录慢查询

全书结束

回头看，这本书的每个阶段都在为下一个阶段做铺垫：

SQL 表达力决定你能否把问题一次说清楚；
类型与建模决定数据库能替你挡住多少错误；
MVCC 是理解 Postgres 一切行为的钥匙——UPDATE 的真相、VACUUM 的必要性、长事务的危害；
索引与执行计划建立在 MVCC 之上——Index-Only Scan 依赖可见性映射；
并发与锁只是「什么时候取快照」的不同选择；
存储与调优最终又回到最开始那个 8KB 的页面。

如果只记住一件事：Postgres 的绝大多数「怪行为」不是 bug，也不是孤立的知识点，而是 MVCC 这个架构选择的连锁后果。 遇到解释不了的现象时，回到阶段三重新推一遍，通常就能想通。

本节验收

先自己回答，再点开对照。

为什么 shared_buffers 不该设成内存的 80%？给出三个理由。

经验值是内存的 25%，上限一般不超过 40%。三个理由：

双重缓存——Postgres 有两层缓存，同一个页面在 shared_buffers 和 OS page cache 里各存一份，等于浪费一半内存；
检查点 I/O 尖峰——shared_buffers 越大，检查点要刷的脏页越多，尖峰越猛；
放弃了 OS 的算法——page cache 有更成熟的预读和淘汰策略，把内存全给 Postgres 就用不上了。

常见错误：查了一下命中率发现是 95%，就断定 shared_buffers 太小、该加。

SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0) FROM pg_stat_database;

这个数字里的「未命中」有相当一部分其实命中了 OS page cache——它只是没命中 Postgres 自己那层。真实的磁盘读远比数字看起来少。判据是低于 99% 才考虑加，95% 通常什么问题都没有。

常见错误二：对内存特别大的机器（512GB+）继续按比例往上堆。加不出收益——工作集要么已经装下了，要么大到怎么都装不下；继续加只是把双重缓存和检查点尖峰这两个代价同步放大。

（检查点尖峰详见 WAL、检查点与崩溃恢复）

work_mem 的真实内存上限该怎么估算？为什么按连接数估算会低估？

最大内存 ≈ 活跃连接数 × 每查询节点数 × 并行度 × work_mem

低估的原因是 work_mem 限的是单个执行节点，不是单个查询。一个查询里有 3 个 Sort 和 2 个 Hash，峰值就是 5 份；再叠上并行查询——每个 worker 各算各的，并行度 4 就是 20 份。按 max_connections × work_mem 估算漏掉了后两个乘数。

正确做法是全局保守 + 按需放宽：配置文件里留 16MB 左右，报表类的大排序单独 SET LOCAL work_mem = '512MB'。判断该不该调，看 EXPLAIN (ANALYZE, BUFFERS) 里有没有 temp read/written。

常见错误：算一笔看起来很稳的账——「max_connections 200 × 32MB = 6.4GB，机器有 64GB，绰绰有余」——于是放心把 work_mem 调到 512MB。漏掉的两个乘数各自就能带来 5 倍、4 倍的放大。更麻烦的是它只在特定条件下暴露：要几十个复杂查询在业务高峰同时展开才会打爆内存，测试环境永远复现不出来。这是 OOM 事故最常见的成因。

常见错误二：把 maintenance_work_mem 按同一套逻辑一起设得很保守。它是给 VACUUM、CREATE INDEX、ALTER TABLE 用的，同时跑的维护操作很少，可以设到 1~2GB。建索引慢的时候，调它比调什么都管用。

（怎么读 temp read/written 详见执行计划一节）

SSD 上必须改哪个参数？改它会让优化器的行为发生什么变化？

random_page_cost，从默认的 4.0 改到 1.1。 默认值的含义是「随机读一个页比顺序读贵 4 倍」，这是为机械硬盘设的；SSD 上两者差距小得多。

改小之后，优化器认为随机读不再昂贵，于是在大量场景下从 Seq Scan 转向 Index Scan。这往往是单个收益最大的参数调整。

配套还要改 effective_io_concurrency：机械盘 2，SSD 设到 200 左右，让 Bitmap Heap Scan 能并发预读多个页面。

常见错误：把它当成一个「调低就更快」的性能旋钮，索性设成和 seq_page_cost 相等甚至更低。它不改变任何实际 I/O 速度，只改变优化器的成本估算——设得过低会让优化器高估索引扫描的划算程度，在需要回表大量行的查询上选错计划，结果比 Seq Scan 还慢。1.1 这个值反映的是 SSD 上随机读仍然略贵这个事实。

常见错误二：以为 effective_cache_size 会占内存，所以设得保守。它一个字节都不分配，纯粹是给优化器的一句提示（「操作系统大概能缓存多少数据」）。设小了唯一的后果是优化器高估索引扫描的 I/O 成本、更不敢用索引。经验值是内存的 50~75%。

从进程模型出发，解释为什么 Postgres 的连接数超过某个点后吞吐会下降。

Postgres 的每个连接是一个独立的操作系统进程，不是线程。由此产生四条成本：

后果	影响
每个连接约 5~10MB 私有内存	1000 个连接就是 5~10GB，还没算 `work_mem`
建立连接要 fork 一个进程	每次几毫秒到几十毫秒，比 MySQL 贵得多
所有进程共享 `shared_buffers`，需要锁协调	连接越多，锁竞争越激烈
快照可见性判断要遍历活跃事务列表	活跃连接越多，每次取快照越慢

前两条是固定成本，多一个连接多一份；后两条随连接数超线性增长——这才是曲线掉头的原因。经验公式：连接数 ≈ CPU 核数 × 2 + 有效磁盘数。一台 16 核 + SSD 的机器，最优的活跃连接数通常在 30~50 之间。

常见错误：应用报 too many connections，就把 max_connections 从 200 调到 2000。报错确实消失了，但那些连接进来之后只会让锁竞争和快照开销更重，总吞吐反而下降——症状从「报错」变成「全体变慢」，后者更难查。正确做法是在前面放连接池让超出的请求排队等待，而不是各自开一个连接进去互相拖慢。起步配置里 max_connections = 200 配合连接池、实际活跃控制在 50 以内，就是这个思路。

常见错误二：以为连接池的主要价值是「省掉 fork 进程的开销」。省 fork 只是四条里的一条，而且是最小的一条。真正的收益是把并发活跃度压到 CPU 能消化的范围内，绕开后两条超线性的成本。这也解释了为什么把 max_connections 调大而不上连接池毫无帮助——fork 的成本本来就不是瓶颈。

pgbouncer 的 transaction 模式会让哪些特性失效？为什么？

因为在 transaction 模式下，连接每个事务结束就归还池子，下一条语句可能落到完全不同的后端进程上。所以一切依赖「同一个后端进程」的会话级状态都会失效：

预处理语句（多数驱动可以关掉，或用协议级支持）
SET 会话变量（要改成 SET LOCAL）
会话级咨询锁（改用 pg_advisory_xact_lock）
LISTEN / NOTIFY
临时表
WITH HOLD 游标

代价换来的是：几千个应用连接复用几十个数据库连接。绝大多数场景下 transaction 是正确选择。

常见错误：应用启动时执行一次 SET search_path = ... 或 SET timezone = ...，以为整条连接的生命周期内都有效。在 transaction 模式下这条 SET 只对当时那个事务所在的后端有效，之后的查询可能落到别的后端，拿到的是默认值。

这个 bug 恶劣在低并发时不出现：池子空闲、复用率低的时候你几乎总是拿到同一个后端，本地和测试环境永远是对的；上线一有并发，就变成随机的、无法复现的错误。

常见错误二：发现会话特性失效，就改用 session 模式「既要连接池又要会话语义」。session 模式下连接到客户端断开才归还，复用率极低——它基本退化成一个连接代理，拿不到连接池最核心的那个收益。正确的方向是改应用：SET 换成 SET LOCAL，会话锁换成事务锁。

（咨询锁的两种形式详见锁一节）