1. 追问题根PostgreSQL 的 I/O 路径到底长什么样先说个我早年间踩过的坑。刚接手第一套生产 PostgreSQL 时我做的事情跟大多数同学一样——拿着网上的调优脚本照抄参数shared_buffers 给了 8GBwork_mem 拉到 64MB以为业务查询就能起飞。结果呢查询没变快多少反而有一段时间因为检查点刷盘太猛直接把磁盘 IOPS 打满主库 WAL 堆积从库延迟飙到一小时差点闹出事故。从那以后我才明白PostgreSQL 的 I/O 调优不懂底下的数据流是怎么走的靠堆参数纯粹是撞运气。要理解 PostgreSQL 的高性能 I/O绕不开一条完整的数据路径从磁盘把数据块读进内存、在内存中完成修改、由 WAL 机制担保崩溃安全、最后再由后台进程定期刷回磁盘。这条路径上的每一个环节都可能成为瓶颈也都对应着不同的参数管着。PostgreSQL 的 I/O 架构里最核心的一个组件是shared_buffers。你可以把它理解成整个数据库实例的“公共缓存区”所有表数据、索引页在进入工作内存之前都得先经过这里。它的大小直接决定了有多少数据能常驻内存少一次磁盘读就少一次 I/O。数据读入 shared_buffers 之后还需要一个进程去管理这些页面和磁盘文件的对应关系。PostgreSQL 用的是经典的缓冲池管理方式页面的替换策略是时钟扫描clock sweep不是严格的 LRU。这个设计上的差异会造成一个实际影响某些不怎么热的大表全表扫描可能会把真正热的数据挤出缓存——这个后面调优的时候会讲到实际解法。写路径就更复杂了。客户端发起一条 UPDATE 或 INSERT数据并不会直接落盘而是先把变化记进内存里的 WALWrite-Ahead Log缓冲区然后把 WAL 记录刷进磁盘上的 wal 日志文件最后再把修改过的数据页留在 shared_buffers 里变成脏页等检查点或者后台写进程决定什么时候刷回数据文件。这中间每多一次“同步等待”都会变成你 SQL 里肉眼可见的延迟。这个架构最精妙的地方在于任何数据修改先写日志再写数据文件。日志是顺序追加写的数据文件是随机写的顺序写远快于随机写所以 PostgreSQL 把“保证崩溃后不丢数据”的重担放在了顺序写的 WAL 上数据文件的刷盘可以攒到一定批量后再做。这就是 PostgreSQL 写入性能不至于被随机 I/O 拖垮的根本原因。而 I/O 调优本质上是控制好这条数据流水线上每一个阀门缓存够不够大、WAL 刷盘的频率要不要降低、检查点触发和刷盘节奏怎么跟磁盘能力匹配、文件系统怎么组织才不会让随机读雪上加霜。下面我把每一处阀门的调法结合我自己的实测经验分别拆开讲。2. 内存侧的胜负手shared_buffers 与缓存命中率2.1 shared_buffers 到底该设多大这是所有 PostgreSQL 调优的第一个问题也是被误解最多的一个问题。官方的默认值只有 128MB对绝大多数生产库来说这个值小得可怜。但你要是以为“内存大就无限往上加”那就错了。shared_buffers 太大会导致两个问题一是 PostgreSQL 在启动和检查点刷盘时清理大量脏页的负担变重反而拖慢恢复速度二是共享内存太大操作系统页缓存能缓存的量就变少二者之间没有合理分工的话实际收益并不高。我个人常用的估算方式很简单总内存的 25% 作为起点。比如 32GB 内存的机器shared_buffers 设 8GB64GB 内存可以给到 16GB。超过 32GB 的情况后续的收益曲线会明显变平。除非你的库是纯 OLAP、且所有数据都期望常驻内存否则不建议超过总内存的 40%。这里面有个多数人忽略的细节shared_buffers 里的数据页操作系统同样会把它留在页缓存里。也就是说读 shared_buffers 里的数据有可能命中两次缓存一次操作系统缓存、一次 PostgreSQL 缓冲池。这就导致一个反直觉的结论shared_buffers 设得太大不一定能减少物理 I/O因为数据仍然会以“双缓存”的方式占着操作系统内存。Linux 下我们可以通过posix_fadvise来跳过 OS 缓存但 PostgreSQL 默认没有启用这种方式所以你务必记住shared_buffers 不是越大越好而是要结合热数据总量来判断。实操上判断当前设置是否合理先看一段时间的pg_stat_databaseSELECT datname, blks_read, blks_hit, blks_hit::float / (blks_hit blks_read) AS hit_ratio FROM pg_stat_database WHERE datname 你的库名;如果命中率低于 95%说明缓存可能不够如果你的命中率有 99% 以上那么 shared_buffers 先不用急着加瓶颈大概率在别的地方比如 CPU、锁等待或者 WAL 刷盘节奏。2.2 高效读取的辅助参数effective_io_concurrency 和 effective_cache_size很多 DBA 调参只盯着 shared_buffers却把两个极其关键的参数忘了。一个是effective_io_concurrency另一个是effective_cache_size。effective_io_concurrency控制的是 PostgreSQL 在需要顺序读取多个块时同时发起多少个异步 I/O 请求。默认值是 1基本等于没有并发预读。如果你用的是 SSD 或者 NVMe 盘这个值完全可以开到 200 甚至更高如果还是机械硬盘那保持 1-2 即可因为机械硬盘的寻道时间是硬伤并发太大反而让磁头来回跑。我自己实测过一个典型场景一张 2000 万行的聚合表全表扫描的时候把effective_io_concurrency从 1 调到 64查询时间从 38 秒降到 22 秒接近一倍差距。这个参数对利用操作系统预读和多通道并发读非常有用属于“动一个数字就能看到收益”的类型。effective_cache_size则是告诉优化器“操作系统页面缓存大概有多大”的估计值。它不分配任何内存只影响规划器对索引扫描和全表扫描成本的判断。如果你把它设得太小优化器会高估索引扫描的代价倾向于用全表扫描设得太大又可能导致优化器过度自信地选择索引扫描碰上大量回表时反而变慢。正常的做法是把它设为操作系统可用内存的一半以上比如 32GB 内存的机器设成 24GB。这个参数不必设得比真实可用内存还大除非你的机器上除了 PostgreSQL 几乎不运行其他进程。2.3 从等待事件看内存够不够光看命中率还不够因为命中率高不代表没有 I/O 问题。更准确的做法是翻 PostgreSQL 的等待事件视图。从 9.6 开始PostgreSQL 提供pg_stat_activity里的wait_event_type和wait_event这是排查 I/O 瓶颈最有力的工具。当查询等待在DataFileRead或者DataFileWrite说明实际发生了数据文件层面的物理 I/O。出现大量BufferIO等待说明多个前端进程在争抢同一个缓冲页的 I/O 完成。还有WALWrite、WALBufferFull这些直接指示了日志写入路径的拥塞程度。我排查时习惯写这样一个查询SELECT wait_event_type, wait_event, COUNT(*) FROM pg_stat_activity WHERE state active AND wait_event IS NOT NULL GROUP BY wait_event_type, wait_event ORDER BY COUNT(*) DESC;如果里面DataFileRead占大头第一反应不是加内存而是先审视 SQL 有没有索引有没有低效的嵌套循环。等 SQL 本身没问题再考虑调节点共享缓冲。要把 I/O 问题从 SQL 问题中剥离开否则调内存参数很可能只是给一个糟糕的 SQL 擦了屁股问题换个马甲还会回来。3. WAL 写日志性能与安全之间的钢丝行走3.1 synchronous_commit 三种取值的性能账WAL是 PostgreSQL 保证数据不丢的核心机制但“保证不丢”需要付出同步刷盘的代价。这里最关键的参数是synchronous_commit它有四个取值on、off、remote_apply、local。对绝大多数场景你只需要在on和off之间做选择。on是默认值事务提交时必须等 WAL 日志真正刷到磁盘才会向客户端返回提交成功。每笔事务至少要等一次磁盘 fsync这个延迟在机械硬盘上可能就是 5-15ms在高并发下非常可观。off则把这个等待去掉事务提交时WAL 记录只要进了操作系统缓冲区就返回真正的刷盘由后台进程稍后完成。这个模式下单事务的提交延迟可能低到 0.1ms 级别吞吐量提升常常是 3-5 倍起步。代价是什么如果数据库在 WAL 刷盘之前崩溃已提交的事务可能丢失。注意这里不是“丢数据”这么一句简单的话而是“已向客户端确认提交成功但事务实际没落到磁盘上”这是最让业务方头疼的丢数据类型。我的个人习惯是核心业务系统老老实实用 on。那些对延迟敏感、能容忍一部分丢数据的场景比如朋友圈的阅读计数、埋点日志的中间缓冲可以用off。如果业务是主从架构只有一台从库做同步复制可以考虑remote_apply保证从库上也能读到最新已提交数据。3.2 wal_buffers 与 wal_writer_delay 的搭配wal_buffers是 WAL 记录在内存里的缓冲区大小默认根据 shared_buffers 自动推导通常是 1/32 的 shared_buffers但有上下限。这个参数默认就够用大多数场景不用动。但高并发写入下如果它太小事务多到把 WAL 缓冲区塞满就会触发 WAL 缓冲区强制刷盘性能会剧烈抖动。判断 wal_buffers 是否够用还是看等待事件如果你看到大量WALBufferFull等待那就需要把 wal_buffers 调大一点。常见做法是直接设成 16MB 或 32MB对现代服务器来说成本很低却能缓解峰值写入压力。还有一个容易被忽略的是wal_writer_delay它控制 WAL 后台写进程每隔多久把缓冲区的 WAL 刷一次盘。默认 200ms意味着如果事务本身不触发同步提交WAL 最多可能积压 200ms 再刷盘。降低这个值可以降低崩溃时丢失的数据量但会增加 WAL 刷盘频率带来多一点 I/O 开销。反过来纯 OLAP 场景、偶尔批量导数据可以适当调到 500ms 甚至 1s减少低频刷盘时的 I/O 压力。需要特别提醒的陷阱不要为了“提升性能”而直接暴力调高wal_buffers。WAL 缓冲区过大的副作用是崩溃恢复时要重放更多的 WAL 记录恢复时间变长。你追求的是“刚好够用略有富余”不是越大越好。3.3 max_wal_size 与 WAL 文件数量的平衡很多旧文档还会让你调checkpoint_segments那是 9.5 以前的玩法。9.5 之后PostgreSQL 把管理方式换成了基于max_wal_size和min_wal_size的目标导向模式。max_wal_size默认是 1GB意思是当 WAL 累积到这个量就会触发一次检查点来推进 LSN同时回收旧的 WAL 段。这个值设得太小检查点触发过于频繁每次检查点都要刷大量脏页I/O 峰值密集出现设得太大检查点间隔变长但一旦触发一次性刷脏页的量会非常大反而可能瞬间吃光 I/O。我实测过一个典型场景机械磁盘环境默认max_wal_size 1GB每次检查点大约产生 15GB 的脏页刷盘流量耗时接近 40 秒期间查询延迟从 5ms 飙到 500ms。把max_wal_size调到 4GB 后检查点间隔拉长到原来的 3 倍多单次刷盘流量没减少多少但频次低了观察到的 I/O 峰值明显平缓。由此我做了一个关键决策把检查点刷盘流量打散比单纯控制检查点频率更重要。这一步主要靠接下来要说到的checkpoint_completion_target。4. 检查点与后台写进程刷盘节奏的艺术4.1 检查点是怎么触发和执行的PostgreSQL 的检查点有两个触发维度时间checkpoint_timeout和量max_wal_size。任一达到都会触发一次检查点。检查点执行时要把 shared_buffers 里所有脏页找出来统一刷到磁盘。在刷盘过程中系统会记录一个 checkpoint 开始时的 LSN之后新产生的 WAL 不会被这次检查点覆盖。这样崩溃恢复时只需要从最近一个完整检查点开始重放 WAL 即可恢复速度才有保障。但这里有个容易被误解的点检查点刷盘并不是一次性把全部脏页瞬间刷完。PostgreSQL 会按checkpoint_completion_target来控制刷盘速率目标是把刷盘动作平摊到整个检查点周期里完成。checkpoint_completion_target表示检查点刷盘应当在检查点时间目标的百分之多少之内完成。默认是 0.5意思是当一次检查点被触发后PostgreSQL 努力让刷盘动作在前 50% 的检查点周期里完成留下另一半时间作缓冲避免下一次检查点触发时上一次还没刷完。这个参数上我推荐一个非常实用的经验值0.9。理由很简单检查点刷盘本来就是要发生的工作量最重要的是别让它在短时间内集中爆发。设置完成目标为 0.9意味着刷盘被尽量均匀地分散到接近整个周期I/O 波动可以最小化。但注意如果你把 completion_target 设得过高可能导致系统在检查点周期末尾还有大量脏页没有刷完而此时下一次检查点已经触发两波刷盘叠在一起I/O 风暴反而更严重。所以在高负载写库上0.9 是一个比较稳的折中。4.2 后台写进程 bgwriter 的微调除了检查点强制刷盘PostgreSQL 还有一个bgwriter进程负责定期把一部分脏页提前刷出去让检查点触发时需要刷的脏页量减少。bgwriter 的行为由三个参数控制bgwriter_delay默认 200ms扫描频率、bgwriter_lru_maxpages每次最多写多少页、bgwriter_lru_multiplier根据最近客户端请求的缓冲区数量决定每次该写多少脏页的系数。在默认配置下bgwriter 对大量脏页场景的作用很有限所以很多 DBA 干脆不调它靠检查点兜底。我的经验是如果业务是持续稳定的写入比如大量 INSERT 或 UPSERT值得把bgwriter_lru_maxpages从默认 100 提到 1000把bgwriter_delay降到 100ms。这样能让 bgwriter 分担一部分脏页刷盘降低检查点触发时的瞬时 I/O 压力。但要注意bgwriter_lru_multiplier建议保持默认或略微调高原因在于如果这个系数太大bgwriter 可能把太多冷页面提前刷盘导致原本可以留在内存里服务查询的热数据被过早挤出缓冲池。我在一遍遍实测中发现multiplier在 2-4 之间比较稳定高于 4 可能反而增加不必要的写放大。4.3 用 pg_stat_bgwriter 观察你的刷盘健康度配置参数调来调去最终要看效果。PostgreSQL 提供了pg_stat_bgwriter视图记录了 bgwriter 和检查点的行为统计。关键字段有checkpoints_timed按时间触发的检查点次数、checkpoints_req按请求触发的检查点次数、buffers_checkpoint检查点期间刷的脏页数、buffers_cleanbgwriter 刷的脏页数、maxwritten_cleanbgwriter 因达到 maxpages 上限而提前结束扫描的次数。判断逻辑很简单如果checkpoints_req占比高说明很多检查点是因为 WAL 量达到了max_wal_size而触发而不是因为时间到期这时候系统写入压力大可能需要增大max_wal_size。而maxwritten_clean增长飞快说明 bgwriter 每次干活都撞上maxpages上限可以考虑调大bgwriter_lru_maxpages。我习惯每周看一次这个视图的趋势尤其是在发版或者上线新业务之后能及时捕捉到写入模型的变化。5. 物理层布局磁盘、文件系统和表空间5.1 WAL 单独放磁盘是性价比最高的操作很多人会忽略物理磁盘布局对 PostgreSQL I/O 的影响这是我最想分享的一个重要经验——把 WAL 目录迁移到独立的物理磁盘上是性价比最高的硬件级调优。原因不复杂WAL 是顺序写、低并发、对延迟敏感数据文件是随机读、并发高、对吞吐敏感。两种访问模式混在一张盘上时WAL 的顺序写入会被数据文件的随机读写打断导致最关键的 commit 延迟时间被拉长。物理隔离后WAL 写不受数据文件干扰单事务提交延迟可以明显降低。我在自己的项目里做过对照原先 WAL 和数据文件共用一块 SATA SSDsync 提交的 commit 时延平均 4.2ms把 WAL 挪到另一块 NVMe 后时延降到 1.1ms降幅接近 75%。如果你的机器上有闲置的 SSD哪怕容量很小WAL 本身通常只有几百 MB 到几 GB 的循环占用这一步的收益也立竿见影。迁移方法很简单停库把pg_wal目录旧版本是pg_xlog整体移动到新盘然后在原位置建立软链接。比如mv /data/pgsql/pg_wal /wal/pg_wal ln -s /wal/pg_wal /data/pgsql/pg_wal注意必须先停库操作否则文件不一致性的风险非常大。启动之后用pg_controldata检查下 WAL 位置确认没问题再继续。5.2 文件系统选型XFS 依然是 PostgreSQL 的最优解从 Linux 的角度看ext4 和 XFS 是 PostgreSQL 最常见的两个文件系统。我自己的生产环境里从 ext4 换到 XFS 之后高并发随机写场景的延迟 IO 有明显改善。原因有几个XFS 的延迟分配机制Delayed Allocation能更好地将多个写入请求合并成较大的连续 I/O减少碎片它在多 CPU 大型机上扩展性更好对大文件的顺序 I/O 支持也很成熟。ext4 在文件系统崩溃后的恢复速度有时反而快些但 PostgreSQL 自己有 WAL 兜底文件系统层的崩溃一致性并没有那么关键。挂载参数上有两个值得关注nobarrier和noatime。noatime很安全避免每次读文件都更新访问时间减少了不必要的写 I/O基本没有副作用。nobarrier可以提升写性能但它会让文件系统在断电时面临更大的元数据损坏风险因为 PostgreSQL 不能替代文件系统层的 barrier 语义。我的个人建议是保留 barrier在数据库层面已经有 WAL 保障的前提下文件系统层的完整性仍然重要别为了那百分之几的写入提升去赌断电安全。如果条件允许用fdatasync替代fsync也可以减少写入量。PostgreSQL 在fsync参数开启时默认使用fsync只有在某些操作上使用fdatasync。对要追求极致 I/O 的场景你可以通过改wal_sync_method参数来调整实际的同步方式比如open_datasync或fsync_writethrough但这通常属于高级运维操作普通项目没必要深挖。5.3 表空间分层冷热数据分离数据表空间是 PostgreSQL 的特色能力之一允许把不同的表、索引放在不同的物理目录。我对它的使用建议很简单把热表和高频索引放在 SSD 表空间把历史归档表、大日志表放到 HDD 或低频存储。比如说订单表orders_p2023这种访问频率低、数据量大的分区就放到慢盘表空间实时在用的orders_current放在快速盘。这比单纯加缓存内存更省钱也不影响逻辑结构表分区切割后对应用透明。操作示例CREATE TABLESPACE fast_disk LOCATION /data/fastdisk; CREATE TABLESPACE slow_disk LOCATION /data/slowdisk; ALTER TABLE orders_current SET TABLESPACE fast_disk; ALTER TABLE orders_p2023 SET TABLESPACE slow_disk;我个人经验中这个策略在数据仓库类场景收益明显因为历史分区几乎只在月末统计时才访问放慢盘能显著节省 SSD 的成本。不过要记得表空间的迁移会重写整个表的数据文件会产生大量 I/O务必在业务低峰期执行或者用pg_repack类的工具来控制锁和迁移节奏。6. 调优效果验证与问题排查实录6.1 调优之后如何确认真的变快了很多同学调完参数重启实例发现查询快了就说“调优成功”。我负责任地告诉你这种验证方式不靠谱。性能提升可能是缓存带来的假象也可能是系统本来就处于低峰。我实际使用的验证方法有两套。第一是压测用pgbench在调整前后的同一负载条件下跑固定时长的 TPC-B 模型对比 TPS 和平均延迟。比如pgbench -i -s 100 mydb pgbench -c 32 -j 8 -T 300 mydb第二套是直接观察等待事件对比调优前后DataFileRead和WALWrite等待占比是否下降。这个方法比只看 TPS 更能指出 I/O 瓶颈是否真的被解除。还有两个容易偷走性能的地方一定要记得复查。第一autovacuum 的运行时 I/O 压力autovacuum 默认的autovacuum_work_mem较小大批量更新后触发的 vacuum 可能跑很久持续消耗 I/O。我通常会给autovacuum_max_workers一个合理限制比如 3并开启autovacuum_vacuum_cost_delay来限制它抢占前台查询的 I/O。第二提前检查是否有多套配置没有生效比如你改了shared_buffers但没重启或者改的是postgresql.auto.conf而postgresql.conf里的值把它覆盖了。6.2 高频 I/O 问题排查速查表这里整理一张典型的 I/O 问题排查表都是我在日常运维中反复用到的判断逻辑放在下面方便参考。观察到的现象可能的瓶颈优先排查思路commit延迟高应用响应慢WAL刷盘路径检查pg_stat_activity等待事件是否为WALWrite试试 WAL 独立磁盘或开synchronous_commitoff业务允许丢数据前提下全表扫描频繁命中率低索引缺失或缓存不足看执行计划确认是否缺索引再结合blks_hit判断是否要加大 shared_buffers检查点触发时I/O尖峰刷盘集中调大max_wal_size或checkpoint_completion_target到 0.8-0.9同时加大 bgwriter 参与度出现大量BufferIO等待缓冲页竞争减少对该表的并发大事务数量拆事务为更小的批次检查是否有锁等待导致的长事务归档模式下恢复慢WAL量过大适当减小max_wal_size缩短恢复时的WAL重放范围同时保持检查点不至于过密pass-through 型磁盘满表空间写满检查pg_stat_database各库的pg_database_size和pg_tablespace_size迁移或扩容这张表只能作为起点。真正的排查一定要从实际wait_event出发顺着 I/O 路径一步步往回找而不是一上来就调参。我见过太多“一顿操作猛如虎瓶颈在 SQL 上”的情况——参数改了半天其实一条倒排索引就解决问题了。6.3 一个小技巧如何快速定位“谁在产生大量脏页”最后分享一个我经常用的快速定位技巧。当实例的写 I/O 很高但你不知道是什么业务在刷脏页时可以结合pg_stat_statements和pg_stat_bgwriter看SELECT query, calls, rows, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;如果你发现某个高频 UPDATE 语句有大量的行修改那么它就是脏页的主要生产者。修复方式有很多种降低每条事务的修改量批量提交、增加检查点间隔或者从业务层面改成更平滑的写入方式比如把 UPDATE 改成 INSERT 异步聚合。还有一个小技巧用pg_stat_progress_vacuum查看 autovacuum 在某个表上的实时进度SELECT a.pid, a.datname, a.relation::regclass, p.phase, p.heap_blks_scanned, p.heap_blks_total, ROUND(100 * p.heap_blks_scanned::numeric / p.heap_blks_total, 1) AS progress_pct FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a ON a.pid p.pid;如果 autovacuum 持续跑在同一个大表上且进度百分比长时间不增长说明 I/O 已经被挤占了这时候应该停下来调整autovacuum_vacuum_cost_delay或者人工介入。碰到这种情况我最直接的做法是把autovacuum_vacuum_cost_delay从默认的 20ms 临时调到 50ms给前台查询让路等业务低峰期再重新执行一次干净的 vacuum 或 analyze。实际运维中PostgreSQL 的 I/O 调优远不是“抄一组参数”就能一劳永逸的事。它需要你理解 WAL、shared_buffers、检查点、bgwriter 这几条主要路径的交互关系再结合业务特征去做针对性取舍。我自己每次做调优碰上拿不准的时候都会先定位瓶颈再造一个可控的压测场景验证最后才改动生产配置。这套方法的容错率远比“看网上推荐就照抄”要稳得多。如果你的库也经常遇到高写入负载下延迟抖动的问题希望这些排障思路能帮你少踩几个坑。
