# 真正重要的配置 > Postgres 的 400 项配置中，哪些值得你关注，如何安全地逐一修改，以及一份适用于 4 vCPU、16 GB 服务器、带有注释的起步配置。 --- LLMS 索引： [llms.txt](/llms.txt) --- 在一台 64 GB 内存的机器上，采用默认配置的 PostgreSQL 18 服务器，会拿出 128 MB 作为自己的缓存。它假定随机读取的代价是顺序读取的四倍，仿佛背后是旋转磁盘。它每产生大约半个 GB 的 WAL 就启动一次 [检查点](/43-glossary/#checkpoint)，默认不记录慢查询、锁等待或临时文件。 这些默认值全都很安全，目的是让 Postgres 能在任何人可能运行它的最小机器上启动。作为默认值，这样做是对的；对于你的生产服务器，却不对。 大约 400 项配置中，真正重要的只有二十来项。下面说明它们是什么、每项怎么选，以及要检查 Postgres 底层环境的哪些方面。在有人要求更大的服务器之前就设好它们：默认配置会浪费你给它的任何机器（[先用数据证明，该换更大的机器了](/37-scale/#earn-the-bigger-box)）。 > [!DETAILS]- 深入阅读：对照服务器检查默认值 > > 每个配置项编译时内置的默认值都在 `pg_settings.boot_val` 中，旁边还有当前值以及这个值的来源。要查看服务器偏离原始默认值的所有设置，这也是接手一台服务器后首先要读的内容： > > ```sql > select name, setting, unit, boot_val, source > from pg_settings > where setting is distinct from boot_val > order by source, name; > ``` > > `postgresql.conf` 中有少数几行是由 `initdb` 写入的（`TimeZone`、`log_timezone`、`lc_*` 配置，以及小型机器上有时会写入的 `max_connections` 或 `shared_buffers`），所以 `source` 会显示为 configuration file，尽管并没有人主动选择它们。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > -- boot_val is the compiled-in default, whatever this server's config says > > select lab.prove('shared_buffers defaults to 128 MB (16384 pages of 8 kB)', > > boot('shared_buffers') = '16384' > > and (select unit from pg_settings where name = 'shared_buffers') = '8kB'); > > select lab.prove('random_page_cost defaults to 4, seq_page_cost to 1', > > boot('random_page_cost') = '4' and boot('seq_page_cost') = '1'); > > select lab.prove('a checkpoint starts after about max_wal_size / (1 + checkpoint_completion_target) of WAL: ~539 MB', > > round(1024 / (1 + boot('checkpoint_completion_target')::numeric)) = 539); > > select lab.prove('no slow-query, lock-wait, or temp-file logging by default', > > boot('log_min_duration_statement') = '-1' > > and boot('log_lock_waits') = 'off' > > and boot('log_temp_files') = '-1'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 知道配置在哪里设置，以及什么会使它生效 {#know-where-a-setting-is-set-and-what-applies-it} 修改值之前，先知道它能在哪里设置，以及要经过什么才能应用。大多数“配置没有生效”的谜团都源于这两点之一。 ### 最具体的设置胜出 {#the-most-specific-setting-wins} ```text postgresql.conf server-wide, edited by hand postgresql.auto.conf server-wide, written by ALTER SYSTEM ALTER DATABASE ... SET per database, at connection start ALTER ROLE ... SET per role (and per role in a database) SET per session SET LOCAL until the end of the transaction ``` 每一行都会覆盖上面的设置。按角色设置常被低估：每种负载都可以有自己的上限。应用角色的超时配置在 [每个超时默认都是关闭的](/28-connections/#every-timeout-defaults-to-off)；报表角色则相反：给更多内存，也更有耐心： ```sql alter role reporting set work_mem = '256MB'; alter role reporting set statement_timeout = '5min'; ``` 它们在会话启动时应用，所以连接池中的连接在重新连接前会保留旧值。某个配置看起来没有生效时，`pg_settings.source` 会告诉你这个值来自哪里。 > [!DETAILS]- 深入阅读：先设数据库，再叠加角色 > > 给数据库设置 `work_mem`：新会话会以 `source = database` 报告这个值，而已经连接的会话会保留旧值。再添加角色设置后，角色设置会胜出，`source = user`。数据库设置和角色设置都存放在 `pg_db_role_setting` 中。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > alter database :"DBNAME" set work_mem = '16MB'; > > select lab.prove('a new session picks up the database setting, with source = database', > > (select s from dblink('fresh', > > $$select setting || ' ' || unit || ' ' || source from pg_settings where name = 'work_mem'$$) t(s text)) > > = '16384 kB database'); > > select lab.prove('this session, connected before the change, keeps its old value', > > current_setting('work_mem') = '4MB'); > > alter role lab_cfg_reporting set work_mem = '256MB'; > > select lab.prove('a role setting overrides the database setting (source = user)', > > (select s from dblink('reporting', > > $$select current_setting('work_mem') || ' ' || source from pg_settings where name = 'work_mem'$$) t(s text)) > > = '256MB user'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ### 上下文决定重载还是重启 {#context-decides-reload-or-restart} `context` 列位于 `pg_settings` 中，说明修改需要什么操作： | 上下文 | 修改方式 | 示例 | |--------------|----------------------------------------------------|---------------------------------------| | `postmaster` | 配置文件或 `ALTER SYSTEM`，然后**重启** | `shared_buffers`、`max_connections`、`io_method` | | `sighup` | 配置文件或 `ALTER SYSTEM`，然后**重载** | `max_wal_size`、大多数 `autovacuum_*` | | `superuser` | 重载，或由超级用户执行 `SET` | `log_min_duration_statement`、`wal_compression` | | `user` | 重载，或由任何人执行 `SET`；也可按角色、按数据库设置 | `work_mem`、`random_page_cost`、`statement_timeout` | **Table 30-1.** 上下文决定重载还是重启 对于服务器级配置，先执行 `alter system set ...`，再执行 `select pg_reload_conf()`；重载是安全的，正在运行的查询会继续。`postmaster` 配置虽然会被记录下来，却要等重启才会应用，所以每次改完配置都运行： ```sql select name, setting, pending_restart from pg_settings where pending_restart; ``` > [!TIP] 服务器配置只选一个地方 > > `postgresql.auto.conf` 会覆盖 `postgresql.conf`。把两者混用，迟早会出现这样的情况：`postgresql.conf` 的修改没有效果，因为旧的 `ALTER SYSTEM` 仍然胜出。选定一个地方（配置管理通常意味着使用配置文件），再用 `alter system reset all` 清空另一个地方。 > [!DETAILS]- 深入阅读：不可修改的设置，以及已核对的上下文 > > `internal` 设置完全不能修改（`block_size`、`data_checksums`）。`pg_file_settings` 会显示每个配置文件行以及它是否已应用；它的 `error` 列可以捕获错误值，而重载报告这类错误时只会写入服务器日志。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('postmaster (restart): shared_buffers, max_connections, io_method', > > ctx('shared_buffers') = 'postmaster' and ctx('max_connections') = 'postmaster' > > and ctx('io_method') = 'postmaster'); > > select lab.prove('user: work_mem, random_page_cost, statement_timeout', > > ctx('work_mem') = 'user' and ctx('random_page_cost') = 'user' and ctx('statement_timeout') = 'user'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 内存：先做预算，再调优 {#memory-budget-it-before-you-tune-it} Postgres 在启动时分配一个共享池，此外每个连接运行时还会分配私有内存（[后端内存是私有的](/05-buffers/#backend-memory-is-private)）。后者是服务器耗尽内存的原因，所以提高任何配置之前，先把各项上限加起来。本章起步配置的预算如下： ```text 16 GB server, starter config ------------------------------------------------------------ allocated at startup shared_buffers 4 GB ceilings, used only when busy work_mem 32MB x 3 nodes x 100 connections 9.4 GB (x2 for hash nodes, x3 with 2 parallel workers) autovacuum_work_mem 512MB x 4 workers 2 GB maintenance_work_mem 1GB x 1 index build 1 GB everything left over OS page cache, the second cache tier the rest ------------------------------------------------------------ ceilings alone: 16.4 GB. Nothing left for the OS cache. ``` 这些上限加起来超过了机器的内存容量，这很正常：大多数查询只会用到一小部分 `work_mem`。但要知道哪一项会随流量增长：`work_mem` 乘以连接数。 ### shared_buffers 从内存的 25% 起步，而不是 80% {#shared_buffers-starts-at-25-of-ram-not-80} `shared_buffers` 是 Postgres 自己的页缓存（[shared_buffers 是一个页数组](/05-buffers/#shared_buffers-is-an-array-of-pages)），默认值为 128 MB。在专用服务器上，从内存的 **25%** 起步。不要设成 80%：Postgres 还会经过 [操作系统页缓存](/43-glossary/#os-page-cache)读取数据；那是第二层、更大的缓存，而过大的 `shared_buffers` 会挤压它，同时又重复缓存相同的页。超过 40% 后要测量。修改它需要重启。 ### 大的 shared_buffers 需要大页 {#a-big-shared_buffers-wants-huge-pages} 当 `shared_buffers` 很大时，在 Linux 上使用 2 MB 的**大页（huge pages）**：如果使用普通的 4 KB 页面，数百个进程映射 16 GB 共享内存时，其页表可能占掉数 GB。先在内核中预留大页（`vm.nr_hugepages`，数量由 `postgres -C shared_memory_size_in_huge_pages` 计算），然后设置 `huge_pages = on`；默认值 `try` 在没有预留大页时会静默回退。 ### effective_cache_size 是提示：设为内存的 50% 到 75% {#effective_cache_size-is-a-hint-set-it-to-50-to-75-of-ram} 它不会分配任何内存。规划器把它当作关于可以缓存多少数据的提示（[effective_cache_size 是提示，不是内存](/05-buffers/#effective_cache_size-is-a-hint-not-memory)）。把它设为内存的 50% 到 75%；默认值是 4 GB。 ### work_mem：先乘起来，再提高 {#work_mem-multiply-before-you-raise-it} [`work_mem`](/43-glossary/#work_mem)（默认 4 MB）是一个查询在溢写到临时文件之前，可供**每个**排序或哈希节点使用的内存。下面是以默认值和 256 MB 对整个 `events` 排序的结果： ```text Sort Method: external merge Disk: 50896kB ... Sort Method: quicksort Memory: 111653kB ``` 在 4 MB 时，排序向临时文件写入了 50 MB；在 256 MB 时，它用 109 MB 内存完成了排序。阈值很陡：64 MB 仍然会溢写同样的 50 MB，而 128 MB 就能装下。在我们的机器上，内存排序甚至没有更快。当溢写量大、发生频繁或存储很慢时，就需要重视溢写。 那为什么不在全局设置 `work_mem = 256MB`？关键在于“**每个**”。一个计划可能有多个排序和哈希节点（[work_mem 是按节点计算的，而且可以看到](/05-buffers/#work_mem-is-per-node-and-you-can-see-it)），哈希节点会得到两倍的 `work_mem`（`hash_mem_multiplier` 自 PostgreSQL 15 起默认为 2.0），每个并行工作进程都有自己的额度，而且每个连接都可能同时这样做： ```text max memory ~ connections x nodes per query x work_mem (x2 for hashes) x (1 + parallel workers) 200 connections x 3 nodes x 64MB = 38 GB on a 16 GB server ``` 真正会唤醒 Linux OOM killer 的，是内存使用的尾部峰值；它杀掉一个后端进程时，整个服务器都会重启（见下文的[不要让 OOM killer 选择 Postmaster](#dont-let-the-oom-killer-choose-the-postmaster)）。所以： 1. 在连接池代理后面保持全局 `work_mem` 适中：16 到 64 MB；连接数很多时还要更低。 2. 在明确知道需要的地方，按角色（如上面的 `reporting` 角色）或按事务（`set local work_mem = '512MB'`）提高它。 3. 打开 `log_temp_files`（见下文），找出实际发生溢写的地方。 > [!DETAILS]- 深入阅读：溢写阈值，以及一次聚合中的九份内存额度 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > -- each row: one EXPLAIN ANALYZE of the sort at that work_mem > > select lab.prove('at the default 4 MB the sort spills: external merge, about 50 MB on disk', > > (select method = 'external merge' and space_type = 'Disk' > > and space_kb between 45000 and 56000 and temp_written > 10000 > > from sorts where work_mem = '4MB')); > > select lab.prove('at 256 MB it sorts in memory, using about 109 MB', > > (select method = 'quicksort' and space_type = 'Memory' and space_kb between 100000 and 120000 > > from sorts where work_mem = '256MB')); > > select lab.prove('the threshold: 64 MB (less than the sort needs) still spills, 128 MB (more) does not', > > (select method from sorts where work_mem = '64MB') = 'external merge' > > and (select method from sorts where work_mem = '128MB') = 'quicksort'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) > > 排序是在关闭并行的情况下运行的；它溢写了 `temp written=12751` 个块。普通的 `select kind, count(*) from events group by kind` 会在三个进程中运行一个位于 Partial HashAggregate 之上的 Sort（一个领导进程和两个工作进程）。它实际用量只在 KB 量级（每个排序 25 KB、每个哈希 32 KB），但它的**上限**是：每个进程中排序的内存额度是 `work_mem`，哈希的额度是它的两倍；合计就是 `work_mem` 的九倍。 ### 每个自动清理工作进程也会占用 maintenance_work_mem {#each-autovacuum-worker-takes-maintenance_work_mem-too} `maintenance_work_mem`（默认 64 MB）供 `CREATE INDEX`、`VACUUM` 和恢复操作使用；同时运行的这类操作通常不多，所以在 16 GB 机器上设为 1 GB 是合理的。除非显式设置 `autovacuum_work_mem`，否则每个自动清理工作进程也会占用同样多的内存。（内存不足时，清理[会重新扫描每个索引](/10-vacuum/#basics-10-1-remove-dead-row-versions)。） ## 告诉 Postgres 你的存储能做什么 {#tell-postgres-what-your-storage-can-do} 有两组配置用于向 Postgres 描述磁盘：一组为规划器设定读取代价，另一组决定同时保持多少次读取处于在途状态。 ### random_page_cost 假定旋转磁盘 {#random_page_cost-assumes-spinning-disks} 规划器把顺序读取一页的代价定为 1，把随机读取一页的代价定为 `random_page_cost = 4`，这个比值模拟的是磁盘磁头寻道。在 SSD 上，随机读取与顺序读取的代价几乎相同，所以默认值会低估索引扫描的价值。下面是在 `project_id` 上有索引时，按项目顺序读取前 20 个项目的事件的结果： ```text -- random_page_cost = 4 Buffers: shared hit=135 read=1784 -> Sort ... -> Bitmap Heap Scan on events ... -- random_page_cost = 1.1 Buffers: shared hit=1970 -> Index Scan using events_project_id_idx on events ... ``` 两种方式触碰的页面数大致相同（1,919 对 1,970）。在 `4` 下，规划器认为按顺序读取索引的代价高于位图扫描加排序，于是用位图收集页面后再排序；在 `1.1` 下，按顺序的索引扫描胜出，排序消失。对于 SSD 存储的服务器（包括云块存储），设置 `random_page_cost = 1.1`。它是 `user` 配置，所以先按会话试用，看看哪些计划会变化（[规划器也在估算](/20-planner/#basics-chapter-20)）。 > [!DETAILS]- 深入阅读：两个计划，以及背后的查询 > > ```sql > create index events_project_id_idx on events (project_id); > set max_parallel_workers_per_gather = 0; > explain (analyze, timing off) > select * from events where project_id between 1 and 20 > order by project_id limit 5000; > ``` > > `limit` 设置的上限不会触及，因为查询只返回 1,967 行。估算代价：在 `4` 下为 6,748，在 `1.1` 下为 2,244。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > \set q 'select * from events where project_id between 1 and 20 order by project_id limit 5000' > > select lab.prove('at random_page_cost = 4: a bitmap heap scan, then a sort', > > (:'nodes_at_4')::text[] @> array['Sort', 'Bitmap Heap Scan']); > > select lab.prove('at 1.1: an index scan in order, no sort', > > (:'nodes_at_11')::text[] @> array['Index Scan'] and not (:'nodes_at_11')::text[] @> array['Sort']); > > select lab.prove('both touch about the same number of pages', > > :buffers_at_11::numeric / :buffers_at_4 between 0.8 and 1.25); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ### 快速存储需要更多在途读取 {#fast-storage-wants-more-reads-in-flight} `effective_io_concurrency` 设置的是：当 Postgres 能预判所需页面时，允许同时在途的读取数量。PostgreSQL 18 把默认值从 1 提高到 16（`maintenance_io_concurrency` 也从 10 提高到 16）；在快速 NVMe 或高 IOPS 的云卷上，把它设为 64 到 200，可能有助于从磁盘读取的扫描。 PostgreSQL 18 还为读取加入了**异步 I/O**，由 `io_method` 选择，而修改它需要重启。默认的 `worker` 会把读取交给 `io_workers` 个进程；`io_uring` 使用 Linux 内核的接口；`sync` 是旧行为。保持 `worker`。如果扫描受 I/O 限制，先提高 `io_workers`，这只需要重载。 ## 拉长检查点间隔，并按 WAL 速率设置 max_wal_size {#space-out-checkpoints-and-size-max_wal_size-to-your-wal-rate} [先记日志，再写数据](/08-wal/#basics-chapter-8)解释了这些配置的作用；下面说明如何设置它们： - **`checkpoint_timeout`**（5 分钟）：在写入繁重的系统上提高到 15 到 30 分钟。[整页镜像](/43-glossary/#full-page-write)会更少，但崩溃恢复会更久。 - **`max_wal_size`**（1 GB）：检查点之间 WAL 的软上限；Postgres 会在 WAL 达到约 `max_wal_size / (1 + checkpoint_completion_target)` 时请求检查点，按默认值大约是 540 MB。测量 WAL 速率（在繁忙的一小时内计算 [LSN](/43-glossary/#lsn) 的差值），把它设得明显高于一个间隔产生的 WAL 量：在 16 GB 服务器上，4 到 16 GB 很常见，同时要在 `pg_wal` 所在磁盘上留出同样多的空闲空间。 - **`wal_compression`**（off）：设为 `lz4` 或 `zstd`。WAL 章节的测试中，它把以镜像为主的 WAL 降到了 59% 和 35%。 - **`min_wal_size`**（80 MB）：1 GB 没问题。其余配置已经合适。 然后确认大多数检查点是按时间触发，而不是被请求触发的（[检查点为恢复设定上限](/08-wal/#checkpoints-put-a-limit-on-recovery)说明了如何读取 `pg_stat_checkpointer`）。 > [!DETAILS]- 深入阅读：核对 WAL 默认值 > > `wal_buffers` 的默认值为 -1：取 `shared_buffers` 的 1/32，但上限是一个 16 MB 的 WAL 段；这对几乎所有服务器都合适。有两个 WAL 配置可以保持不动：`wal_level` 的默认值是 `replica`，足以支持备库和时间点恢复（只有逻辑解码需要 `logical`）；`full_page_writes` 保持开启，因为关闭它后，如果 8 KB 页写入到一半时发生崩溃，可能留下恢复无法修复的撕裂页。“其余配置已经合适”指的就是这些配置。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('checkpoint_timeout 5 min, checkpoint_completion_target 0.9, min_wal_size 80 MB', > > boot('checkpoint_timeout') = '300' and boot('checkpoint_completion_target') = '0.9' > > and boot('min_wal_size') = '80'); > > select lab.prove('wal_compression off (superuser), wal_buffers -1 (automatic, restart)', > > boot('wal_compression') = 'off' and ctx('wal_compression') = 'superuser' > > and boot('wal_buffers') = '-1' and ctx('wal_buffers') = 'postmaster'); > > select lab.prove('18 defaults: effective_io_concurrency and maintenance_io_concurrency 16', > > boot('effective_io_concurrency') = '16' and boot('maintenance_io_concurrency') = '16'); > > select lab.prove('io_workers defaults to 3 and changes with a reload', > > boot('io_workers') = '3' and ctx('io_workers') = 'sighup'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 自动清理的默认值适合小表和慢磁盘 {#autovacuums-defaults-suit-small-tables-and-slow-disks} [清理不可省](/10-vacuum/#basics-chapter-10)解释了为什么这些默认值在大表和快速存储上会落后。值得做的服务器级修改如下： | 配置项 | 默认值 | 起始值 | 原因 | |-------------------------------------|--------|--------------|------| | `autovacuum_vacuum_cost_limit` | -1 (200) | 1000 到 2000 | 在 SSD 上太慢，而且由所有工作进程共享 | | `autovacuum_vacuum_scale_factor` | 0.2 | 0.05 | 死行达到 5% 就清理，而不是 20% | | `autovacuum_vacuum_insert_scale_factor` | 0.2 | 0.05 | 更早冻结并设置可见性位 | | `autovacuum_analyze_scale_factor` | 0.1 | 0.02 到 0.05 | 让增长中的表拥有更新的统计信息 | | `autovacuum_max_workers` | 3 | 3 到 6 | 并行处理更多表；同时提高代价上限 | | `log_autovacuum_min_duration` | 10min | 1s | 看到自动清理在做什么 | **Table 30-2.** 自动清理的默认值适合小表和慢磁盘 然后为最大的表添加按表设置。PostgreSQL 18 还把死行触发条件限制在 `autovacuum_vacuum_max_threshold`（默认 1 亿行），所以一个有十亿行的表不再需要等到出现 2 亿个死行才触发（[清理不可省](/10-vacuum/#basics-chapter-10)中有公式）。 > [!DETAILS]- 深入阅读：核对自动清理默认值 > > 在 PostgreSQL 18 中，`autovacuum_max_workers` 可以通过重载修改，最高不超过 `autovacuum_worker_slots`（默认 16；修改该值需要重启）。 > > 按表设置属于存储参数，只对那张表覆盖服务器级值。对于像 `events` 这样规模大、很繁忙的表： > > ```sql > alter table events set (autovacuum_vacuum_scale_factor = 0.01, > autovacuum_analyze_scale_factor = 0.01); > ``` > > 除非表拥有自己的 `autovacuum_vacuum_cost_limit`，否则一个工作进程的代价预算会在同时运行的工作进程之间分摊；如果表有自己的设置，该工作进程就使用自己的预算。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('autovacuum_vacuum_cost_limit -1, meaning vacuum_cost_limit (200)', > > boot('autovacuum_vacuum_cost_limit') = '-1' and boot('vacuum_cost_limit') = '200'); > > select lab.prove('scale factors 0.2, 0.2 (insert), 0.1 (analyze)', > > boot('autovacuum_vacuum_scale_factor') = '0.2' > > and boot('autovacuum_vacuum_insert_scale_factor') = '0.2' > > and boot('autovacuum_analyze_scale_factor') = '0.1'); > > select lab.prove('autovacuum_max_workers 3, a reload in 18, up to autovacuum_worker_slots (16, restart)', > > boot('autovacuum_max_workers') = '3' and ctx('autovacuum_max_workers') = 'sighup' > > and boot('autovacuum_worker_slots') = '16' and ctx('autovacuum_worker_slots') = 'postmaster'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 并行查询加速一个查询，不是整个服务器 {#parallel-query-speeds-up-one-query-not-the-server} Postgres 可以把大型扫描、连接或聚合拆分到多个工作进程中。这样会让一个大型查询更快，但不会提高每秒查询数；在繁忙的 OLTP 服务器上，查询的工作进程占用的每个核心，都是其他连接无法使用的核心。 对于 4 个 vCPU：设置 `max_parallel_workers = 4`、`max_parallel_workers_per_gather = 2`。分析型服务器可以设得更高；对延迟敏感的 OLTP，可以为应用角色将每个 Gather 的并行工作进程上限设为 0，只让报表查询并行执行。 > [!DETAILS]- 深入阅读：规划器如何决定工作进程数量 > > `max_worker_processes`（8，修改需要重启）限制所有后台工作进程，而 `max_parallel_workers`（8）是其中分给并行查询的份额。规划器根据表大小调整工作进程数量：超过 `min_parallel_table_scan_size`（8 MB）后可以使用一个工作进程；每增加一个工作进程，所需大小再乘以三，因此 `events`（275 MB）会得到四个工作进程。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('events (275 MB) is past 8 x 3^3 = 216 MB but short of 648 MB: four workers', > > pg_relation_size('events') between 216 * 1024 * 1024 and 648 * 1024 * 1024); > > set max_parallel_workers_per_gather = 8; > > select lab.prove('with the cap raised, the planner plans 4 workers for events', > > (select (p -> 'Plan' -> 'Plans' -> 0 ->> 'Workers Planned')::int > > from lab.plan('select kind, count(*) from events group by kind') p) = 4); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 低开销日志能回答大多数凌晨 3 点的问题 {#cheap-logging-answers-most-3-am-questions} Postgres 开箱只记录错误和检查点（从 PostgreSQL 15 起，`log_checkpoints` 默认开启），除此之外几乎什么都不记。这些配置代价很低，却能回答大多数“凌晨 3 点发生了什么”的问题： | 配置项 | 默认值 | 建议值 | 它提供的信息 | |------------------------------|--------|--------------|--------------------------------------| | `log_min_duration_statement` | -1 | 250ms 到 1s | 所有慢于该值的语句 | | `log_lock_waits` | off | on | 每当锁等待超过 `deadlock_timeout`（1s）时记录一行 | | `log_temp_files` | -1 | 0 或某个大小 | 把数据溢写出 `work_mem` 的查询 | | `log_autovacuum_min_duration` | 10min | 1s | 带有完整统计信息的自动清理运行记录 | **Table 30-3.** 低开销日志能回答大多数凌晨 3 点的问题 把 `log_line_prefix` 设为 `'%m [%p] %q%u@%d %a '`，这样每一行都会标出用户、数据库和 `application_name`。 有两点需要注意：`log_min_duration_statement` 会记录查询参数，因此个人数据可能进入日志；`log_statement = 'all'` 产生的 I/O 可能比数据库本身还多。日志能显示哪些查询很慢；`pg_stat_statements` 能显示哪些查询的总开销最大，而这通常才是更好的问题（[观察数据库运行](/31-observability/#basics-chapter-31)）。 ### auto_explain 记录你之后无法复现的计划 {#auto_explain-logs-the-plan-you-cant-reproduce-later} `auto_explain` 会记录慢查询实际运行时的*计划*，而这个计划往往无法在事后复现。把它预加载到 `shared_preload_libraries` 中，将 `auto_explain.log_min_duration` 设为两秒左右，打开 `log_analyze` 和 `log_buffers`，关闭 `log_timing`：逐节点计时才是昂贵的部分。下面的起步配置已经包含这些行。 > [!DETAILS]- 深入阅读：auto_explain 在一个会话中捕获了什么 > > 在一个会话中加载它，并把阈值设为 50 ms 后，统计状态为失败的告警数量时，记录下了如下日志： > > ```text > NOTICE: duration: 64.819 ms plan: > Query Text: select count(*) from events > where kind = 'alert' and payload->>'status' = 'failed'; > Finalize Aggregate (... rows=1 width=8) (actual rows=1.00 loops=1) > Buffers: shared hit=4207 read=30966 > ... > -> Parallel Seq Scan on events (... rows=511 width=0) > (actual rows=20820.33 loops=3) > Rows Removed by Filter: 645846 > ``` > > 这个计划自己就讲清了一个故事：一次计数扫描了 35,000 个页面；估算每个进程处理 511 行（合计约 1,200 行：两个工作进程加领导进程，规划器预计领导进程的工作量会少于工作进程），而实际有 62,461 行（[规划器也在估算](/20-planner/#basics-chapter-20)解释了这个误估）。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > load 'auto_explain'; > > select lab.prove('the slow count reads about 35,000 pages', > > lab.buffers($$select count(*) from events where kind = 'alert' and payload->>'status' = 'failed'$$) > > between 33000 and 37000); > > -- est: the scan's estimate across all processes; actual: rows x loops > > select lab.prove('the planner estimates under 5,000 matching rows; there are over 60,000', > > (select est < 5000 and actual > 60000 from est)); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 内核可以拖垮一台调优良好的服务器 {#the-kernel-can-take-down-a-well-tuned-server} 上面的每项配置都可能是正确的，但服务器仍然会崩溃：Linux 杀掉 Postmaster，检查点被数 GB 的脏页阻塞，容器只给并行查询 64 MB 共享内存。内核有自己的规则。（在托管服务中，内核由服务商负责，但内存不足导致的进程终止仍会影响你，表现为所有连接同时断开。） ### 别让 OOM killer 选择 Postmaster {#dont-let-the-oom-killer-choose-the-postmaster} 默认情况下，Linux 会承诺分配它可能无法实际兑现的内存；当 RAM 和交换空间都耗尽时，**OOM killer** 会杀掉一个进程。如果它选中的是后端进程，Postgres 无法判断共享内存是否只改了一半，于是会终止其他所有进程并执行崩溃恢复（[检查点为恢复设定上限](/08-wal/#checkpoints-put-a-limit-on-recovery)）。所有连接都会断开。 文档给出了两个可以一起使用的修复办法。**严格超额承诺**（`vm.overcommit_memory = 2`）会让内核拒绝超过上限的分配，因此想要过多内存的查询只会单独失败并报 `out of memory`。用 `oom_score_adj = -1000` **保护 Postmaster**，并设置 `PG_OOM_ADJUST_FILE`，让它的子进程仍然可以被杀掉。它们都不能修复根因；根因要靠[内存预算](#memory-budget-it-before-you-tune-it)解决。 > [!DETAILS]- 深入阅读：严格超额承诺与 oom_score_adj，逐步操作 > > 在严格超额承诺下，提交上限为 `(RAM - huge pages) x vm.overcommit_ratio / 100 + swap`；该比值默认是 50，这会在还有一半 RAM 空闲时就拒绝分配，所以只能在专用服务器上调高它；我们从 80 到 90 起步。在 systemd 下，保护配置是 `OOMScoreAdjust=-1000` 加上 `Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj`；没有这个变量时，每个后端进程都会继承豁免。如果 Postmaster 自己被杀掉，在重启前不会接受新连接（[PostgreSQL 文档](https://www.postgresql.org/docs/18/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT)）。 > > ```bash > grep -E '^(CommitLimit|Committed_AS)' /proc/meminfo # Committed_AS must stay below > cat /proc/$(head -1 "$PGDATA/postmaster.pid")/oom_score_adj # -1000: protected > journalctl -k | grep -iE 'out of memory|killed process' # past kills > ``` > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('without PG_OOM_ADJUST_FILE, a backend inherits the postmaster''s oom_score_adj', > > position('PG_OOM_ADJUST_FILE'::bytea in pg_read_binary_file('/proc/' || :pm_pid || '/environ')) = 0 > > and host_file('/proc/' || :pm_pid || '/oom_score_adj') > > = host_file('/proc/' || pg_backend_pid() || '/oom_score_adj')); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ### 为每种机器类型一次性决定操作系统配置 {#decide-the-os-settings-once-per-machine-type} 每一项都是 Postgres 无法替你决定的内核或文件系统选择： - **预留大页（`vm.nr_hugepages`），并关闭透明大页（transparent huge pages）。** 文档不建议使用透明大页。 - **保留交换空间，但让它很少被使用**（`vm.swappiness` 为 1 到 10）。被换出的后端进程会很慢；被杀掉的后端会让服务器重启。 - **用字节数限制脏页**（`vm.dirty_background_bytes` 和 `vm.dirty_bytes`，约为存储每秒可写入量）。默认情况下，内核允许 10% 的内存处于未刷盘状态；而检查点的 `fsync` 会让每次提交都在它后面停住：每隔几分钟准时出现一次延迟尖峰。 - **使用 ext4 或 XFS、设置 `noatime`，并且绝不要禁用写屏障**，否则断电可能丢失 Postgres 已报告为持久化的提交。 - **保持预读和 I/O 调度器的默认值，并保持 `vm.zone_reclaim_mode = 0`。** 对 OLTP，优先选择更少但更快的核心；在 x86 云环境中，一个 vCPU 通常只是半个物理核心。 - **在容器中按 cgroup 限额做预算**，因为该限额包含页缓存；同时给 `/dev/shm` 多于 Docker 默认的 64 MB，否则大型并行查询可能失败。 > [!DETAILS]- 深入阅读：用数字看大页和脏页 > > `postgres -D "$PGDATA" -C shared_memory_size_in_huge_pages` 即使在服务器启动之前也能给出答案（实验服务器上是 76 个，即 152 MB）。把 `vm.nr_hugepages` 设为这个数，再设置 `huge_pages = on`。预留的大页会从其他用途可用的内存中消失，所以只预留 Postgres 需要的数量（[文档中的操作步骤](https://www.postgresql.org/docs/18/kernel-resources.html#LINUX-HUGE-PAGES)）。透明大页“已知会在某些 Linux 版本上让某些用户的 PostgreSQL 性能下降”（[文档](https://www.postgresql.org/docs/18/runtime-config-resource.html#GUC-HUGE-PAGES)）；这些报告之后内核已经变化，但没人证明 THP 有帮助。 > > 超过 `vm.dirty_background_ratio`（10）后，内核开始刷盘；超过 `vm.dirty_ratio`（20）后，写入者必须自己刷盘：在一台 256 GB 的机器上，开始刷盘前大约会积累 25 GB。Postgres 写入时会推动内核（`checkpoint_flush_after` 为 256 KB，`bgwriter_flush_after` 为 512 KB），但批量写入的后端进程不会这样做（`backend_flush_after = 0`）。把字节数上限设为存储约一秒内可写入的数据量，并比较修改前后 `sync` 在 `log_checkpoints` 行中的时间。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('huge_pages defaults to try, and changing it needs a restart', > > boot('huge_pages') = 'try' and ctx('huge_pages') = 'postmaster'); > > select lab.prove('on Linux, checkpoint_flush_after is 256 kB and bgwriter_flush_after 512 kB; backends: 0', > > boot('checkpoint_flush_after') = '32' and boot('bgwriter_flush_after') = '64' > > and boot('backend_flush_after') = '0' > > and (select unit from pg_settings where name = 'checkpoint_flush_after') = '8kB'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) > [!DETAILS]- 深入阅读：容器会把页缓存计入内存，而 /dev/shm 只有 64 MB > > cgroup 的内存包括页缓存和共享内存（[内核 cgroup 文档](https://docs.kernel.org/admin-guide/cgroup-v2.html)）；达到 `memory.max` 时，OOM killer 会在 cgroup 内运行。根据这个限额设置 `shared_buffers`，限额见 `/sys/fs/cgroup/memory.max`；容器内的 `free` 通常报告的是宿主机的内存。 > > 并行查询会在 `/dev/shm` 中共享状态，例如并行哈希连接的哈希表；Docker 给容器的这一空间只有 64 MB。几次大型并行哈希连接同时运行时，会失败并报 `could not resize shared memory segment ... No space left on device`。实验套件采用 `shm_size: 512m` 这一设置；在 Kubernetes 中，把一个以内存为后端的 `emptyDir` 挂载到那里（[Kubernetes 文档](https://kubernetes.io/docs/concepts/storage/volumes/#emptydir)）。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > select lab.prove('shared_buffers lives in anonymous mmap memory; dynamic shared memory is POSIX shm', > > current_setting('shared_memory_type') = 'mmap' > > and current_setting('dynamic_shared_memory_type') = 'posix'); > > select lab.prove('the lab kit''s compose file gives /dev/shm 512 MB, not Docker''s 64 MB', > > host_file('/proc/self/mounts') ~ '/dev/shm \S+ \S*size=524288k'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) > [!DETAILS]- 深入阅读：完整的 sysctl 文件，以及检查一台机器的命令 > > 下面这份 16 GB 起步配置面向一台专用服务器，存储可以持续提供约 500 MB/s： > > ```ini > # /etc/sysctl.d/30-postgresql.conf, then: sysctl --system > vm.overcommit_memory = 2 # refuse, don't kill > vm.overcommit_ratio = 85 # then watch Committed_AS vs CommitLimit > vm.swappiness = 5 # keep swap for emergencies > vm.dirty_background_bytes = 268435456 # 256 MB; size to about 1 s of writes > vm.dirty_bytes = 1073741824 # 1 GB > vm.zone_reclaim_mode = 0 # the default; multi-socket boxes > vm.nr_hugepages = N # from shared_memory_size_in_huge_pages > # and on the kernel command line: transparent_hugepage=never > ``` > > ```bash > cat /sys/kernel/mm/transparent_hugepage/enabled # want [never] (or [madvise]) > grep -E '^(HugePages_(Total|Free|Rsvd)|Dirty|Writeback):' /proc/meminfo > vmstat 5 # nonzero si/so: swapping now > findmnt -no SOURCE,FSTYPE,OPTIONS -T "$PGDATA" # noatime; never nobarrier > ``` ## 从这份 4 vCPU、16 GB 配置开始，然后测量 {#start-from-this-config-for-4-vcpu-and-16-gb-then-measure} 大多数值都在前文相应小节中说明过；少数没有沿用上文的值，会在注释中说明原因。它假定这是使用 SSD、4 个 vCPU、16 GB 内存的专用服务器，应用位于连接池代理（pooler）之后，并且已按 PostgreSQL 18.6 核对。复制之前先注意：`work_mem = 32MB` 假定服务器连接数约为 100；`max_wal_size = 8GB` 需要 WAL 卷有同样多的余量；所有值都应随环境缩放：内存随 RAM、并行度随核心数、WAL 随写入速率变化。 把它放入由 `postgresql.conf` 引入的文件中，或者用 `ALTER SYSTEM` 设置，但不要两者都用。 ```ini # Connections (restart). Keep this low and use a pooler. max_connections = 100 # Memory shared_buffers = 4GB # 25% of RAM (restart) huge_pages = try # 'on' once vm.nr_hugepages is set effective_cache_size = 12GB # per sort/hash node, per process; raise per role work_mem = 32MB hash_mem_multiplier = 2.0 maintenance_work_mem = 1GB autovacuum_work_mem = 512MB # Storage and I/O (SSD / NVMe / cloud block storage) random_page_cost = 1.1 # random reads are nearly sequential cost effective_io_concurrency = 64 # fast SSDs; 16 is the default maintenance_io_concurrency = 64 io_method = worker # the default, made explicit io_workers = 4 # one per vCPU to start; a reload # WAL and checkpoints wal_compression = lz4 max_wal_size = 8GB # make checkpoints time-driven min_wal_size = 1GB checkpoint_timeout = 15min # fewer full-page images, longer recovery checkpoint_completion_target = 0.9 # Autovacuum autovacuum_max_workers = 4 autovacuum_vacuum_cost_limit = 2000 # shared by all workers autovacuum_vacuum_scale_factor = 0.05 autovacuum_vacuum_insert_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.02 # then per-table settings for the biggest tables # Parallel query (4 cores, mixed workload) max_worker_processes = 8 # the default; all background workers max_parallel_workers = 4 max_parallel_workers_per_gather = 2 max_parallel_maintenance_workers = 2 # the default; parallel index builds # Server-wide backstop only. The app role's timeouts (statement 5s, # lock 3s, idle in transaction 30s, transaction 5min) are set per role. idle_in_transaction_session_timeout = 5min # Logging log_line_prefix = '%m [%p] %q%u@%d %a ' log_min_duration_statement = 500ms log_lock_waits = on log_temp_files = 0 # every spill; raise to e.g. 64MB if noisy log_autovacuum_min_duration = 1s log_checkpoints = on # Extensions (restart) shared_preload_libraries = 'pg_stat_statements,auto_explain' pg_stat_statements.max = 10000 # default 5000; room for ORM-heavy apps pg_stat_statements.track = top # the default: top-level statements auto_explain.log_min_duration = 2s auto_explain.log_analyze = on auto_explain.log_buffers = on auto_explain.log_timing = off # Monitoring. track_io_timing reads the clock on every I/O: cheap on # modern hardware; check new machine types with pg_test_timing. track_io_timing = on ``` > [!TIP] 一次只改一项，并进行测量 > > 修改之前，先记录它应该改变的数值：`pg_stat_statements` 总量、检查点次数、临时文件量和自动清理频率。一次只改一个配置，等待一个有代表性的时间段，然后进行比较（[只改一件事，并端到端测量](/29-benchmarking/#change-one-thing-and-measure-it-end-to-end)）。把配置放进版本控制，这样“改了什么？”永远都有答案。 > [!DETAILS]- 深入阅读：实验检查起步配置的每一行 > > 实验会用检查自己的配置同样的方式检查这份配置：每个名称都存在于 `pg_settings` 中，每个会话可以设置的值都能被接受。在你自己的服务器上，再为本章前面的 `pending_restart` 检查加一条查询。它会列出服务器无法应用的配置文件行： > > ```sql > select sourcefile, sourceline, name, setting, error > from pg_file_settings where error is not null; > ``` > > `postgresql.conf` 中拼错的名称会出现在这里，而 `postmaster` 配置的错误值会让服务器根本无法启动；所以要在重启之前检查，而不是之后。 > > > [!EXAMPLE]- 动手验证 · lab `29-configuration` > > > > ```sql > > -- the lab also checks that every name in the config exists in pg_settings, > > -- and SETs every value a session can set, inside a transaction > > select lab.prove('the session-settable starter values are all accepted (wal_compression = lz4 included)', > > current_setting('wal_compression') = 'lz4' and current_setting('random_page_cost') = '1.1'); > > ``` > > > > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/29-configuration.sql) ## 在托管服务上，真正重要的配置仍可调整 {#on-managed-services-the-settings-that-matter-still-move} 在托管服务上，配置位于服务商的参数组中，需要重启的配置要重启实例才生效（往往意味着故障切换），有些配置则被锁定（[设置位于参数组中](/34-managed/#settings-live-in-a-parameter-group)）。替换存储层的服务商会改变 WAL 配置的行为（[分离式存储改变了部分规则](/34-managed/#disaggregated-storage-changes-some-rules)）。最重要的配置在哪里都可以调整，`ALTER ROLE ... SET` 即使在不能使用 `ALTER SYSTEM` 的地方也能工作。 ## 本章要点 {#the-short-version} - 在修改任何内容之前检查 `pg_settings`（`source`、`context`）；凡是因负载而异的配置，都按角色设置。 - 先做内存预算：`shared_buffers` 设为 RAM 的 25%，再加上 `work_mem` 乘以节点数和连接数。全局保持 `work_mem` 适中，在明确需要的地方再提高。 - 在 SSD 上设置 `random_page_cost = 1.1`；让异步 I/O（`io_method = worker`）保持默认值。 - 用更大的 `max_wal_size` 和更长的 `checkpoint_timeout` 让检查点由时间驱动，并打开 `wal_compression`。 - 给自动清理更大的代价上限和更小的比例因子。 - 在 Postgres 之下的系统层：让 OOM killer 不要杀掉 Postmaster，关闭透明大页，用字节数限制脏页，绝不要禁用写屏障；在容器中按 cgroup 限额做预算。 - 打开慢查询、锁等待、临时文件和自动清理日志，以及 `auto_explain`。 ## 延伸阅读 {#further-reading} - [Setting parameters](https://www.postgresql.org/docs/18/config-setting.html)，PostgreSQL 文档。设置值的所有方式，包括 include、`ALTER SYSTEM` 和按角色设置。 - [Resource consumption](https://www.postgresql.org/docs/18/runtime-config-resource.html)，PostgreSQL 文档。内存、I/O 和异步 I/O 配置，以及它们的确切默认值和限制。 - [Write Ahead Log settings](https://www.postgresql.org/docs/18/runtime-config-wal.html)，PostgreSQL 文档。检查点、`max_wal_size` 和 `wal_compression`。 - [Error reporting and logging](https://www.postgresql.org/docs/18/runtime-config-logging.html)，PostgreSQL 文档。所有 `log_*` 配置以及 `log_line_prefix` 的转义符。 - [Managing kernel resources](https://www.postgresql.org/docs/18/kernel-resources.html)，PostgreSQL 文档。大页、Linux 内存超额承诺，以及保护 Postmaster 不被 OOM killer 杀掉的 `oom_score_adj` 配置方法。 - [Documentation for /proc/sys/vm](https://docs.kernel.org/admin-guide/sysctl/vm.html)，Linux 内核文档。超额承诺、交换倾向、脏页阈值和区域回收的确切含义与默认值。