PHP小码农

Appearance

磁盘 I/O 优化 ​

PostgreSQL 的性能问题追到最后，绝大多数都会落在磁盘 I/O 上：缓存命中率悄悄下降、检查点周期性抖动、VACUUM 拖慢在线查询、iowait 常年高位——这些现象的根因都是「数据库发起的 I/O 请求量大、访问模式差、或者存储本身扛不住」。

本节前半部分讲清 PostgreSQL 的 I/O 是怎么产生的、随机 I/O 与顺序 I/O 的代价差多少、PG18 的异步 I/O（AIO）子系统带来了什么；后半部分逐个参数给出推荐值与验证方法，最后给出一套完整的 I/O 瓶颈排查流程。写入路径上的 WAL 与检查点参数见 WAL 与检查点优化。

前置知识

阅读本节前，建议先了解：WAL 与检查点优化

基础概念 ​

数据库的 I/O 从哪里来 ​

讨论 I/O 优化之前，先要搞清楚「谁在发 I/O」。PostgreSQL 的磁盘访问大致来自以下几处：

来源访问模式触发者典型特征
顺序扫描（sequential scan）顺序读后端进程大表全扫，读放大明显
索引扫描回表随机读后端进程小块、离散，对 IOPS 敏感
位图堆扫描（bitmap heap scan）半顺序读后端进程按块号排序后批量读，最适合预读
脏页刷盘随机写Checkpointer / Background Writer / 后端进程8kB 粒度，量大
WAL 写入顺序写后端进程 / WAL Writer提交时 fsync，对延迟最敏感
VACUUM / ANALYZE顺序读 + 随机写Autovacuum Worker长事务，会与在线查询抢 I/O
临时文件顺序写 + 顺序读后端进程排序/哈希溢出，work_mem 不够时暴增
备份与归档顺序读pg_basebackup / 归档进程带宽敏感，容易打满磁盘

一次普通查询的读取路径是这样的：

text
SELECT ... WHERE id = 42

  后端进程
     │ ① 查 shared_buffers（内存，纳秒级）
     ├─────────────► 命中 → 直接返回（不产生 I/O）
     │ 未命中
     ▼ ② read() 系统调用
  ┌──────────────────┐
  │ 内核页缓存（page cache）│  ③ 命中 → 从内存返回（产生 "read" 统计但不产生物理 I/O）
  └──────────────────┘
     │ 未命中
     ▼ ④ 真正落到存储设备（物理 I/O，微秒~毫秒级）
  ┌──────────────────┐
  │  SATA SSD / NVMe  │
  └──────────────────┘

关键点：数据库看到的「读」和存储设备真正执行的读，中间隔着两层缓存（shared_buffers 与内核页缓存）。这也是后文反复强调「pg_stat_io 的 reads 不等于物理读」的原因。

随机 I/O 与顺序 I/O 的代价差异 ​

不同存储介质上，随机访问与顺序访问的差距是数量级的：

介质顺序吞吐随机 IOPS随机读延迟适合的负载
机械硬盘（HDD 7200rpm）150~200 MB/s75~150约 8~15 ms归档、冷数据、顺序扫描
SATA SSD500~550 MB/s50k~100k约 100~200 µs通用 OLTP
NVMe SSD（消费级）2~3.5 GB/s300k~500k约 60~120 µs中小型 OLTP
NVMe SSD（企业级）6~7 GB/s800k~1.5M约 30~80 µs高并发 OLTP

把这张表换算成「一次 8kB 页读取的耗时」：

text
读取 1000 个随机分布的 8kB 页（约 8MB 数据）

HDD         ：1000 / 100 IOPS   ≈ 10 秒
SATA SSD    ：1000 / 50000 IOPS ≈ 20 毫秒
NVMe SSD    ：1000 / 500000 IOPS ≈ 2 毫秒

同样的逻辑数据量，硬件差异带来 5000 倍 的差距。由此得到两条重要推论：

  1. 机械盘不适合随机读密集型负载。索引扫描在 HDD 上几乎是灾难，优化器也会因此极度偏好顺序扫描。
  2. SSD 上「顺序 vs 随机」的差距大幅缩小，所以固态存储时代要主动调低 random_page_cost，否则优化器会错误地放弃索引。

shared_buffers 与内核页缓存的分工 ​

PostgreSQL 有自己的一块共享内存缓存（shared_buffers），操作系统也有一份页缓存。两次读同一个页，理想情况下应该只发生一次物理 I/O：

text
第一次读页 P：
  shared_buffers 未命中 → 内核页缓存未命中 → 物理读 → 页同时进入两层缓存

第二次读页 P：
  shared_buffers 命中 → 直接返回，零 I/O

内存压力大、shared_buffers 淘汰了页 P，但内核缓存还留着：
  内核页缓存命中 → 无物理 I/O（但仍会被统计为一次 read）

两层都淘汰了：
  再次物理读

由此产生几个实践要点：

  • shared_buffers 不是越大越好。官方明确说明：在专用数据库服务器上，shared_buffers 的合理起点是内存的 25%；由于 PostgreSQL 同时依赖操作系统缓存，把超过 40% 的内存分配给 shared_buffers 不太可能比更小的值表现更好。
  • 不要试图「关掉」内核页缓存。PG 默认不做直接 I/O（不绕过页缓存），这既是双缓存开销的来源，也是顺序扫描预读、写合并能生效的前提。
  • shared_buffers 调大需要同步调大 max_wal_size。官方文档指出：更大的 shared_buffers 通常需要相应增大 max_wal_size，以便把大量新增或修改数据的写出过程摊到更长的时间窗口里。

PG18 的异步 I/O（AIO）子系统 ​

PostgreSQL 18 引入了异步 I/O 子系统，这是本节最重要的版本特性。发布说明的原话是：

An asynchronous I/O (AIO) subsystem that can improve performance of sequential scans, bitmap heap scans, vacuums, and other operations.

其工作机制是：后端进程不再一次只发一个读请求然后干等，而是把多个读请求排队，由 I/O 工作进程（或 io_uring）并行完成，从而提升存储设备的队列深度利用率。官方原文的描述是：

This feature allows backends to queue multiple read requests ... This is enabled by server variable io_method, with server variables io_combine_limit and io_max_combine_limit added to control it. This also enables effective_io_concurrency and maintenance_io_concurrency values greater than zero for systems without fadvise() support.

这段话有两个关键含义，值得反复读：

  1. AIO 的开关是 io_method，配套还有 io_combine_limit、io_max_combine_limit 两个控制参数；
  2. 在 PG18 之前，effective_io_concurrency 只在支持 fadvise() 预读建议的系统上才真正生效。PG18 之后，即使系统不支持 fadvise()，该参数也能起作用（改由 AIO 实现预读）。

同时 PG18 还提高了两个默认值：

Increase server variables effective_io_concurrency's and maintenance_io_concurrency's default values to 16 (Melanie Plageman) — This more accurately reflects modern hardware.

也就是说，PG18 中 effective_io_concurrency 与 maintenance_io_concurrency 的默认值都是 16（此前分别是 1 和 10）。这直接改变了「什么都不调」时的默认行为——如果你从 PG17 及更早版本升级上来，这一点必须留意。

AIO 影响哪些操作 ​

AIO 并不是「所有 I/O 都变成异步」。它作用在那些可以提前知道要读哪些块的场景：

操作是否受益于 AIO原因
顺序扫描是块号连续，可批量提交
位图堆扫描是已按块号排序，天然适合批量
VACUUM / ANALYZE是全表顺序扫描
索引扫描部分需要先读索引页才能知道要读哪个堆页，依赖受限
单行主键查询否只有一两个块可读，没有并发空间
WAL 写入否顺序写，本身已经很快

结论：AIO 对「大范围扫描类」操作收益最大，对点查几乎没有影响。OLAP / 报表 / 批量任务为主的实例，收益会比纯点查的 OLTP 实例明显。

I/O 并发度到底控制什么 ​

effective_io_concurrency 的字面含义容易误解：它不是「同时开多少个连接去读」，而是单个后端进程可以同时挂起的 I/O 请求数量上限——用官方说法，它是一个「磁盘 I/O 并发度」的估计值。

官方对 PG18 中该参数的描述（英文原文片段）为：

default is 16. Higher values will have the most impact on higher latency storage where queries otherwise experience noticeable I/O stalls and on devices with high IOPs. Unnecessarily high values may increase I/O latency for all queries on the system. On systems with prefetch advice support, effective_io_concurrency also controls the prefetch distance.

这段话给出三条调优准则：

  • 延迟高、IOPS 高的存储收益最大（即 NVMe、云上高 IOPS 盘）；
  • 盲目调高会拖慢所有查询（对所有查询都抬高 I/O 延迟）；
  • 在支持预读建议的系统上，它还控制预读距离。

为什么 pg_stat_io 的 reads 不等于物理读 ​

这是最容易踩的坑之一。官方文档在统计信息一章里明确写了保留意见：

Note that while PostgreSQL's I/O statistics capture most instances in which the kernel was invoked in order to perform I/O, they do not differentiate between data which had to be fetched from disk and that which already resided in the kernel page cache. Users are advised to use the PostgreSQL statistics views in combination with operating system utilities for a more complete picture of their database's I/O performance.

翻译过来就是：统计视图记录的是「内核被调用了多少次」，而不是「磁盘真的被读了多少次」；数据可能来自内核页缓存。因此官方建议：把 PostgreSQL 的统计视图与操作系统工具结合起来看。

这条结论直接决定了后文的排查方法：任何「I/O 到底有没有真的打到盘上」的判断，都必须用 iostat / iotop / vmstat 等 OS 层工具交叉验证。

写放大与读放大 ​

「放大」指的是实际 I/O 量远超逻辑数据量。常见来源：

放大类型来源缓解手段
读放大顺序扫描读整表只为取少量行建索引、分区裁剪、覆盖索引
读放大索引扫描回表（每行一次随机读）位图扫描、覆盖索引（INCLUDE）
写放大更新产生新版本行 + 索引新增条目HOT 更新、fillfactor 预留空间
写放大检查点后首次改页写整页进 WAL控制检查点频率（见 WAL 一节）
写放大存储层（SSD 内部 GC、RAID 校验）企业级 SSD、合理预留空间
写放大临时文件反复读写调大 work_mem，减少排序/哈希溢出

只有理解了放大来源，才能判断「该加内存、该加索引，还是该换盘」。加硬件永远排在「消除放大」之后。

I/O 相关参数总览 ​

下表汇总本节涉及的核心参数（默认值以 PostgreSQL 18 为准）：

参数默认值是否需重启作用
shared_buffers128MB是数据库自己的页缓存大小
effective_io_concurrency16（PG18）否单个后端可并发的 I/O 请求数（读路径、位图扫描）
maintenance_io_concurrency16（PG18）否维护类操作（VACUUM、ANALYZE、索引构建）的并发度
io_methodworker是PG18 异步 I/O 的实现方式
io_workers3特殊（见下）io_method = worker 时使用的 I/O 工作进程数
io_combine_limit16（块，即 128kB）否合并 I/O 操作的最大块数
io_max_combine_limit取决于 OS是平台允许的合并上限，静默限制 io_combine_limit
io_max_concurrency-1（自动）否AIO 最大并发数上限
bgwriter_delay200ms否后台写入器两轮活动之间的间隔
bgwriter_lru_maxpages100否每轮最多写出多少缓冲区
bgwriter_lru_multiplier2.0否每轮目标写出量的放大系数
bgwriter_flush_afterLinux 512kB，其他 0否后台写入器强制回写阈值
backend_flush_afterLinux 512kB，其他 0否后端进程强制回写阈值
wal_writer_flush_after1MB否WAL 写入器强制刷盘阈值
track_io_timingoff否是否采集块读写耗时
vacuum_buffer_usage_limit2MB否VACUUM 使用的缓冲区上限
random_page_cost4.0否规划器对随机页读取的代价估计
seq_page_cost1.0否规划器对顺序页读取的代价估计

注意「是否需重启」这一列：io_method 只能在服务器启动时设置；io_workers 只能在 postgresql.conf 文件或服务器命令行中设置（即需要重载/重启才能生效，不能通过 SET 在会话内修改）；io_max_combine_limit 同样只允许在服务器启动时设置。

语法与用法 ​

查看当前参数值 ​

参数分为「按单位计算的整数」与「浮点数」两类，查看方式相同：

sql
-- 一次性查看本节涉及的全部参数
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'effective_io_concurrency',
    'maintenance_io_concurrency',
    'io_method',
    'io_workers',
    'io_combine_limit',
    'io_max_combine_limit',
    'io_max_concurrency',
    'bgwriter_delay',
    'bgwriter_lru_maxpages',
    'bgwriter_lru_multiplier',
    'bgwriter_flush_after',
    'backend_flush_after',
    'wal_writer_flush_after',
    'track_io_timing',
    'vacuum_buffer_usage_limit',
    'random_page_cost',
    'seq_page_cost'
)
ORDER BY name;

pg_settings 的几列含义值得记住：

列含义
setting当前生效值
unit单位（如 8kB、ms、kB），若无单位则为空
context值何时能改：internal / postmaster / sighup / superuser / user
pending_restart是否已改但需重启才生效（context = postmaster 时常见）
boot_val编译/启动时的默认值（可与 reset_val 对比）

用 context 就能快速判断某个参数能不能在线改：

text
context = internal      → 不可修改
context = postmaster    → 改后需重启
context = sighup        → 改后需 pg_reload_conf() 或 SIGHUP
context = superuser     → 超级用户可 SET（会话级）
context = user          → 普通用户也可 SET（会话级）

修改参数 ​

临时验证（只影响当前会话，断开即失效）：

sql
-- 在会话内临时降低随机读代价，观察执行计划变化
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
RESET random_page_cost;

持久化修改（写入 postgresql.auto.conf，重启或重载后生效）：

sql
-- 持久化推荐值（注意 io_method 需要重启）
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET maintenance_io_concurrency = 100;
SELECT pg_reload_conf();

直接改配置文件（推荐与 ALTER SYSTEM 二选一，不要混用同一参数）：

ini
# postgresql.conf 片段：SSD/NVMe 场景的 I/O 相关配置
shared_buffers = 4GB                 # 约物理内存的 25%，仅启动时生效
effective_io_concurrency = 200       # NVMe 可给 200；SATA SSD 给 64 左右
maintenance_io_concurrency = 100     # 维护类操作并发度
random_page_cost = 1.1               # SSD 上拉近与 seq_page_cost 的差距
bgwriter_lru_maxpages = 200          # 允许后台写入器每轮多写一些
bgwriter_flush_after = 512kB         # Linux 默认值，通常无需改
track_io_timing = on                 # 打开 I/O 计时，便于定位慢 I/O

采集 I/O 统计 ​

PG18 中与 I/O 相关的统计视图主要有四个，职责不同：

视图覆盖范围关键列
pg_stat_io集群级 I/O，按后端类型 × 对象 × 上下文分组reads、writes、hits、evictions、fsyncs、read_time、read_bytes 等
pg_stat_bgwriter后台写入器buffers_clean、maxwritten_clean、buffers_alloc、stats_reset
pg_stat_checkpointer检查点进程num_timed、num_requested、num_done 等
pg_stat_walWAL 活动WAL 记录数、写入量、同步/异步提交次数

查询 pg_stat_io：

sql
-- 按后端类型汇总 I/O 概览
SELECT backend_type,
       object,
       sum(reads)  AS reads,
       sum(writes) AS writes,
       sum(hits)   AS hits,
       sum(evictions) AS evictions
FROM pg_stat_io
GROUP BY backend_type, object
ORDER BY reads + writes DESC;
text
     backend_type     |  object   |  reads  | writes |   hits   | evictions
----------------------+-----------+---------+--------+----------+-----------
 client backend       | relation  | 4821093 | 318204 | 91284773 |    214587
 autovacuum worker    | relation  |  882140 |  41220 | 12841022 |     88214
 checkpointer         | relation  |       0 | 218440 |        0 |      1402
 background writer    | relation  |       0 |  64218 |        0 |       318
 walwriter            | wal       |       0 |  98142 |        0 |         0
 client backend       | temp relation | 12840 |  12840 |   420118 |         0
(6 rows)

这份输出的读法是：

  • client backend 的 relation 行读多写少 → 典型的读密集负载；
  • autovacuum worker 的 evictions 偏高 → VACUUM 在挤占共享缓冲区（可考虑调 vacuum_buffer_usage_limit）；
  • checkpointer 的 writes 就是检查点刷出的脏页量；
  • walwriter 的 object = wal 是 PG18 新增的 WAL I/O 跟踪。

查看各参数对当前实例的实际取值：

sql
-- 当前生效值与来源文件，便于确认是否被 include 文件覆盖
SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('effective_io_concurrency', 'io_method', 'random_page_cost');

操作系统层工具 ​

数据库侧的统计必须与 OS 侧交叉验证：

bash
# 每 2 秒采样一次磁盘扩展统计：关注 %util 与 await
iostat -x 2

# 只看关键列：设备、利用率、平均等待、每秒读写次数
iostat -dx 2 | awk 'NR==1 || /^sd|^nvme/'

# 按进程观察 I/O（需 root）
iotop -oPa

# 查看 I/O 等待占比与换页情况
vmstat 2

# 查看块设备调度器与队列深度参数
cat /sys/block/nvme0n1/queue/scheduler
cat /sys/block/nvme0n1/queue/nr_requests

iostat -x 中最需要看的三个指标：

指标含义健康区间（经验值）
%util设备忙碌时间占比持续 > 80% 说明接近饱和
await平均 I/O 完成时间（含排队）SSD 应 < 1ms；明显偏离基线即为瓶颈
aqu-sz / avgqu-sz平均队列长度与硬件并发能力对比，过低说明并发不足
bash
# 用 pg_test_fsync 测存储的 fsync 能力（离线评估，不要在高峰期跑）
pg_test_fsync -s 5

# 用 pg_test_timing 评估系统时钟精度，判断 track_io_timing 开销是否可接受
pg_test_timing

详细说明 ​

参数分档：按存储介质给推荐值 ​

磁盘 I/O 参数没有「一套通吃」的值，必须先确定介质类型。下面这张表按介质给出起始推荐值，落地时再根据实测微调：

参数HDD / 低速 SANSATA SSD本地 NVMe云盘（高 IOPS）
effective_io_concurrency264200200~300
maintenance_io_concurrency232100100
random_page_cost4.0（默认）1.11.11.1~1.5
shared_buffers内存 25%内存 25%内存 25%内存 25%
bgwriter_lru_maxpages100（默认）200200200
track_io_timing谨慎开启ononon

几条解读：

  • HDD 上不要把 effective_io_concurrency 调高。机械盘无法并行寻道，请求排队只会让单次延迟更大。官方也明确说：过高的值会抬高系统上所有查询的 I/O 延迟。
  • random_page_cost 是 HDD → SSD 迁移时最该改的参数。默认 4.0 是在「随机读比顺序读贵 4 倍」的假设下设定的；SSD 上这个差距大幅缩小，保持 4.0 会让优化器错误地放弃索引扫描。
  • seq_page_cost 保持 1.0 即可。它是基准单位，改它等于把所有代价估计整体缩放，收益不明确。

effective_io_concurrency 与 maintenance_io_concurrency ​

两个参数名字很像，但作用域完全不同：

维度effective_io_concurrencymaintenance_io_concurrency
作用对象普通查询的读路径维护类操作（VACUUM、索引构建、ANALYZE 等）
典型受益者位图堆扫描、顺序扫描CREATE INDEX、VACUUM
影响规划器是（参与代价估算）否
PG18 默认值1616
可被覆盖表空间级同名参数表空间级同名参数

两个参数都可以按表空间覆盖，这对「热数据放 NVMe、冷数据放 HDD」的分层存储特别有用：

sql
-- 把归档表空间的数据告诉优化器：这块盘很慢，别用高并发读
ALTER TABLESPACE archive_ts SET (effective_io_concurrency = 2);

-- 维护操作针对冷盘也降低并发
ALTER TABLESPACE archive_ts SET (maintenance_io_concurrency = 2);

-- 查看某个表空间上的覆盖值
SELECT spcname, spcoptions
FROM pg_tablespace
WHERE spcname = 'archive_ts';

请记住官方对 effective_io_concurrency 的定义：它是一个估计值，代表「磁盘子系统的并发能力」。它影响的是发出请求的激进程度和规划器对并行/位图扫描的偏好，不是「给每个查询分配多少带宽」。

PG18 AIO 的三种实现（io_method） ​

PG18 的异步 I/O 通过单一参数 io_method 选择实现方式：

取值机制平台要求适用场景
sync不使用异步，退回 PG17 的同步行为无排查问题时作对照基线
worker由 I/O 工作进程执行异步读（默认）无（可移植）通用场景，开箱即用
io_uring使用 Linux 的 io_uring 接口Linux，且需 --with-liburing / -Dliburing 构建追求极致读吞吐

关键约束有三条，都是官方明确写死的：

  1. io_method 只能在服务器启动时设置（context = postmaster），改完必须重启；
  2. io_uring 要求编译时带上 liburing 支持，普通发行版二进制包未必包含；
  3. io_workers 仅在 io_method = worker 时有效。

因此升级到 PG18 后的实际路径是：先什么都不改，保持默认的 worker 观察效果；确认 AIO 生效且平稳后，再评估是否值得换 io_uring。

sql
-- 确认 AIO 是否生效、实际有多少 I/O 工作进程
SELECT name, setting, context
FROM pg_settings
WHERE name IN ('io_method', 'io_workers', 'io_combine_limit', 'io_max_concurrency');

-- PG18 新增视图：查看正在进行的异步 I/O 句柄
SELECT * FROM pg_aios LIMIT 20;
ini
# postgresql.conf：仅在确认默认 worker 模式不够用时才考虑
io_method = 'io_uring'          # 需要 liburing 构建，且只能在启动时设置
io_workers = 3                  # 默认值；io_method = worker 时有效
io_combine_limit = 16           # 16 块 = 128kB，默认值
# io_max_concurrency = -1       # 默认自动推导，上限 64，通常无需修改

后台写入器与检查点的分工 ​

两者都负责把脏页刷到磁盘，但目标完全不同，理解这点才能正确调参：

text
Background Writer（后台写入器，持续运行）
  目标：让共享缓冲区里随时有空闲的干净页可用
  节奏：bgwriter_delay（默认 200ms）一轮
  每轮写出：bgwriter_lru_maxpages（默认 100）个缓冲区

Checkpointer（检查点进程，周期性运行）
  目标：把某个时间点之前的全部脏页落盘，使 WAL 可以回收
  节奏：checkpoint_timeout（默认 5 分钟）或 max_wal_size 触发
  每轮写出：全部脏页（可通过 checkpoint_completion_target 摊平）

调参逻辑是这样的：

  • 后台写入器「不够勤」的表现：pg_stat_bgwriter 的 maxwritten_clean 持续增长 —— 官方解释是「后台写入器因为写入过多缓冲区而停止清理扫描的次数」。这说明它每轮被 bgwriter_lru_maxpages 限制住了，可以适当调大。
  • 后台写入器「够勤」的收益：后端进程不需要自己去刷脏页，pg_stat_io 中 backend_type = client backend 的 writes 会下降，查询延迟更稳定。
  • 检查点仍然会造成抖动，因为它是「必须完成」的批量落盘。把检查点摊平靠的是 checkpoint_completion_target（默认 0.9）与足够的 max_wal_size，这部分细节见 WAL 与检查点优化。

几个强制回写阈值（*_flush_after）的作用是限制单个进程一次连续写出的脏页数量，避免一次性写入过多数据把内核页缓存刷爆、导致后续读全变物理读：

参数默认值作用对象
bgwriter_flush_afterLinux 512kB，其他平台 0后台写入器
backend_flush_afterLinux 512kB，其他平台 0后端进程
wal_writer_flush_after1MBWAL 写入器

这几个参数通常不需要动。它们的默认值是上游在多种负载下折中的结果，只有在明确观察到「写突发导致读延迟尖峰」时才尝试调整。

shared_buffers 该给多大 ​

shared_buffers 是 I/O 优化的第一道防线，但它的收益是递减的。官方给出的两条指导意见必须记牢：

  1. 专用数据库服务器（1GB 以上内存）的合理起始值是系统内存的 25%；
  2. 由于 PostgreSQL 同时依赖操作系统缓存，把超过 40% 的 RAM 分配给 shared_buffers 不太可能比更小的值表现更好。

还有一条容易被忽视的联动关系：更大的 shared_buffers 通常需要相应增大 max_wal_size，以便把大量新增或修改数据的写出过程分散到更长的时间窗口内。否则缓冲区越大，检查点要刷的脏页越多，抖动越明显。

shared_buffers 只能在服务器启动时设置，调大之前先确认两件事：

sql
-- 1. 检查大页状态：huge_pages = try 时可能悄悄退回普通页
SHOW huge_pages;         -- 期望值 try（默认）
SHOW huge_pages_status;  -- 实际状态，用于确认是否真的用上了大页

-- 2. 检查共享内存类型（Linux 上大页仅与 mmap 配合生效）
SHOW shared_memory_type; -- Linux 默认 mmap

大页（huge pages）能减少页表项数量、降低内存管理的 CPU 开销。huge_pages 默认是 try：能申请到就用，申请不到就退回普通页，不会导致启动失败。如果希望确定性地使用大页，需要先在操作系统层预留，这块的配置方法在硬件选型一节展开。

临时文件 I/O：work_mem 与 temp_file_limit ​

排序、哈希连接、哈希聚合在内存不够时会把中间结果落到临时文件。这些临时文件产生的 I/O 有两个特点：突发性强（一个大查询就能写出几十 GB）和顺序性差（哈希分区是交替读写）。

控制手段有两层：

sql
-- 每个排序/哈希操作可用的内存（注意：不是每个查询，是每个操作节点）
SHOW work_mem;               -- 默认 4MB

-- 单个进程可用于临时文件的磁盘空间上限
SHOW temp_file_limit;        -- 默认 -1（无限制）

-- 记录超过阈值的临时文件（-1 禁用，0 记录全部）
SHOW log_temp_files;

work_mem 的正确理解方式是：一个查询可能有多个排序/哈希节点，每个节点都能用到完整的 work_mem，所以实际占用可能是 work_mem × 节点数 × 并发数。把它调得过大是内存耗尽事故的常见原因。

实用的排查手法是先靠日志定位，再针对性调参：

sql
-- 会话级临时抬高，验证「调大 work_mem 能否消除临时文件」
SET work_mem = '64MB';
SET log_temp_files = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, count(*) FROM orders GROUP BY user_id;
sql
-- 找出当前正在消耗临时文件的查询（PG18 中 temp_blks_written 类型为 numeric）
SELECT pid,
       temp_blks_read,
       temp_blks_written,
       round(100.0 * temp_blks_written / NULLIF(temp_blks_read + temp_blks_written, 0), 2) AS pct_temp_write,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

需要先安装 pg_stat_statements 扩展才能使用上面的视图。

temp_file_limit 则是一道安全阀：它限制单个进程使用的临时文件总空间，超出的事务会被取消。生产环境通常设一个足够大的值（例如单表最大排序量），用来防止某个失控查询把磁盘写满：

sql
-- 限制单个进程临时文件不超过 100GB（超限事务会被取消）
ALTER SYSTEM SET temp_file_limit = '100GB';

读放大：用 pg_stat_io 对照索引设计 ​

「读放大」是 I/O 优化中最值得投入的方向，因为它不花钱。用 pg_stat_io 的 hits 与 reads 比值就能定性判断缓存效率：

sql
-- 按对象类型看缓存命中率（关系数据的命中率应长期高于 99%）
SELECT object,
       sum(hits)   AS hits,
       sum(reads)  AS reads,
       round(100.0 * sum(hits) / NULLIF(sum(hits) + sum(reads), 0), 3) AS hit_pct
FROM pg_stat_io
WHERE object IN ('relation', 'temp relation')
GROUP BY object;
text
  object  |    hits    |  reads  | hit_pct
----------+------------+---------+---------
 relation | 9128477310 | 4821093 |  99.947
(1 row)

命中率低通常意味着两件事之一：工作集大于可用缓存（该加内存或做分区/归档），或者访问模式差（缺索引导致全表扫描）。区分方法是结合 pg_stat_user_tables 看扫描方式：

sql
-- 顺序扫描次数远多于索引扫描，且表并不小时，优先怀疑缺索引
SELECT relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       n_live_tup,
       round(seq_tup_read::numeric / NULLIF(seq_scan, 0), 0) AS avg_rows_per_seqscan
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY seq_tup_read DESC
LIMIT 10;

avg_rows_per_seqscan 很大说明每次全扫确实读了很多行（值得）；很小说明为了取少量行却扫了整表 —— 这就是典型的读放大，应当补索引。

另一个常被忽略的读放大来源是 TOAST 表。宽列（text、jsonb、bytea）被压缩切分后单独存放在 TOAST 表中，读取时可能需要额外的一次随机 I/O。如果表上存在大量 SELECT * 却只用少数列，应按需选择列。

写放大：HOT 更新与 fillfactor ​

PostgreSQL 的 UPDATE 本质是「插入新版本 + 标记旧版本」。这会带来两重写放大：

text
一次 UPDATE 的物理写入

  堆表：写入新版本行（1 次）
  索引：如果被更新的列上有索引 → 每个索引都插入一条新条目
        （HOT 更新可避免这一步，条件是：更新的列未被索引引用，
          且新版本能放进同一个数据页）

HOT（Heap-Only Tuple）更新是避免索引写放大的关键机制。触发条件是「更新的列没有被任何索引引用」且「当前页有足够空闲空间」。第二点直接由 fillfactor 控制：

sql
-- 高频更新、少增长的表：预留 20%~30% 空间给 HOT 更新
ALTER TABLE orders SET (fillfactor = 80);

-- 查看当前设置
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

判断 HOT 效果要看 pg_stat_user_tables 的 n_tup_hot_upd 与 n_tup_upd 之比：

sql
-- HOT 更新占比（偏低时应考虑调低 fillfactor 或减少被索引的列）
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC
LIMIT 10;

fillfactor 对只读表或只追加表是有害的：它浪费空间、降低顺序扫描密度，却换不来任何 HOT 收益。默认值 100（完全填满）对这类表是正确的。

不要把参数调优当成第一步 ​

I/O 问题的处理优先级应当是固定的：

text
① 消除读放大 / 写放大   → 索引、分区裁剪、fillfactor、HOT、work_mem
② 提升缓存命中率        → shared_buffers、归档冷数据、减少工作集
③ 调数据库 I/O 参数      → 并发度、random_page_cost、bgwriter
④ 存储层与硬件          → RAID、SSD 预留空间、条带化、换盘

越靠前的步骤收益越高、代价越低。直接从 ③④ 入手（例如盲目加大内存、换成更贵的盘）往往掩盖了真正的问题：一个缺失的索引比一块 NVMe 便宜几个数量级，效果却常常更好。存储层的选型与配置细节留给下一节 硬件选型建议。

实战示例 ​

示例 1：定位周期性 I/O 抖动 ​

现象：业务方反馈「每隔几分钟，接口响应时间出现一次尖峰」。

排查步骤：

sql
-- 第一步：看检查点是否有大量「请求式」检查点（请求式过多说明 max_wal_size 偏小）
SELECT num_timed,
       num_requested,
       num_done,
       round(num_requested::numeric / NULLIF(num_timed + num_requested, 0) * 100, 2) AS pct_requested
FROM pg_stat_checkpointer;
text
 num_timed | num_requested | num_done | pct_requested
-----------+---------------+----------+---------------
        12 |            47 |       59 |         79.66
(1 row)

pct_requested 高达 79.66% —— 绝大多数检查点是被 max_wal_size 提前逼出来的，这就是抖动的根源。

bash
# 第二步：用 OS 工具确认抖动确实来自刷盘（观察 %util 与 await 的周期性尖峰）
iostat -dx 2 60

结论与处理：调大 max_wal_size 让检查点回归时间驱动，同时确认 checkpoint_completion_target 为默认的 0.9；具体取值方法见 WAL 与检查点优化。

示例 2：报表查询拖垮在线业务 ​

现象：每晚上跑报表时，在线接口的 P99 延迟翻倍。

排查步骤：

sql
-- 找出当前正在跑的重 I/O 查询（关注 temp_blks_written 与 shared_blks_read）
SELECT pid, state, wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'active'
ORDER BY query_start;
text
  pid  | state  | wait_event_type |  wait_event   |                    query
-------+--------+-----------------+---------------+----------------------------------------------
 48213 | active | IO              | DataFileRead  | SELECT region, sum(amount) FROM orders GRO...
(1 row)

wait_event_type = IO 且 wait_event = DataFileRead 说明查询正等物理读。

处理组合拳：

sql
-- 1. 该报表需要的列上补索引，消除全表扫描
CREATE INDEX CONCURRENTLY idx_orders_report
    ON orders (created_at, region)
    INCLUDE (amount);

-- 2. 报表会话单独降低 I/O 激进程度，避免与在线业务抢盘
--    （会话级设置，仅影响当前连接）
SET effective_io_concurrency = 2;

-- 3. 给它单独的时间窗与连接池额度，避免占用在线业务的连接

根本解法是让报表走只读副本或在业务低峰执行，而不是继续在同实例上调参。

示例 3：升级到 PG18 后启用并验证 AIO ​

背景：从 PG17 升级到 PG18，希望用上异步 I/O。

步骤：

sql
-- 第一步：确认版本与默认实现（PG18 默认 io_method = worker）
SELECT version();
SHOW io_method;
SHOW effective_io_concurrency;
SHOW maintenance_io_concurrency;

期望看到 io_method = worker、两个并发度参数均为 16（PG18 的新默认值）。

sql
-- 第二步：记录基线（重置统计后跑代表性负载）
SELECT pg_stat_reset_shared('io');
-- ... 执行一轮典型业务负载 ...
SELECT object, context,
       sum(reads) AS reads, sum(writes) AS writes,
       sum(read_time) AS read_time_ms
FROM pg_stat_io
GROUP BY object, context
ORDER BY reads DESC;
bash
# 第三步：OS 层记录基线，与数据库统计交叉验证
iostat -dx 1 30 > baseline.txt

调整前后各跑一轮同样的负载，对比 read_time 与 iostat 的 await。如果存储是本地高速 NVMe，收益可能不明显；如果是云盘，AIO 带来的提升通常更可观。

示例 4：临时文件暴增差点写满磁盘 ​

现象：磁盘使用率突然飙升到 95%，df 显示是 base/pgsql_tmp 目录。

排查：

sql
-- 找出当前产生了最多临时文件的查询
SELECT pid,
       temp_blks_written * 8192 / 1024 / 1024 AS temp_mb,
       left(query, 70) AS query
FROM pg_stat_activity
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC;
text
  pid  | temp_mb |                     query
-------+---------+------------------------------------------------
 51902 |  182640 | SELECT o.id, l.name FROM orders o JOIN logs l O...
(1 row)

182GB 临时文件 —— 这是一个典型的哈希连接溢出。原因通常是统计信息缺失导致规划器低估行数，选错了连接算法。

处理：

sql
-- 1. 立刻终止失控查询，保住磁盘
SELECT pg_cancel_backend(51902);

-- 2. 补统计信息（比例与采样方法见统计信息一节）
ANALYZE orders;
ANALYZE logs;

-- 3. 加安全阀，防止同类事故再次写满磁盘
ALTER SYSTEM SET temp_file_limit = '100GB';
sql
-- 4. 复盘：确认连接算法是否已回归合理选择
EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, l.name FROM orders o JOIN logs l ON l.order_id = o.id;

注意事项 ​

  • 不要在生产库上直接跑 pg_test_fsync。它会持续写入并 fsync 数据文件，是纯离线评估工具：

    bash
    # 错误！不要在高峰期对着正在提供服务的实例跑
pg_test_fsync -s 30

# 正确做法：在独立的测试环境、用相同的存储规格测量
# 目标是把结果作为「存储 fsync 能力」的参考值记录下来

  • 不要把 effective_io_concurrency 当成「带宽配额」无限调高。官方明确警告过高的值会抬高系统上所有查询的 I/O 延迟：

    ini
    # 不推荐：HDD 上给出四位数的并发度
effective_io_concurrency = 1000

# 推荐：HDD 给 2；SATA SSD 给 64；NVMe/高 IOPS 云盘给 200 左右
effective_io_concurrency = 200

  • io_uring 不是「改了就有」的。它要求 Linux 且实例的二进制带 liburing 构建；不满足条件时服务器可能无法以该值启动：

    ini
    # 错误！发行版二进制未启用 liburing 时直接改为 io_uring
io_method = 'io_uring'

# 正确做法：先确认构建选项，再小步切换，且记得需要重启
# 不确定时保持默认
io_method = 'worker'

  • 不要为了「省内存」把 shared_buffers 设到很小。这会让所有读都依赖内核页缓存，反而丧失 PostgreSQL 自身的缓冲管理能力（FSM、VM、HOT 都依赖共享缓冲区）：

    ini
    # 不推荐：32GB 内存的专用库，只给 256MB
shared_buffers = 256MB

# 推荐起点：内存的 25%；上限不必超过 40%
shared_buffers = 8GB

  • SSD 上不要保留 random_page_cost = 4.0。这个值会让优化器系统性偏好顺序扫描：

    sql
    -- 错误！SSD 上沿用 HDD 时代的默认值，导致该走索引却全表扫
SET random_page_cost = 4.0;

-- 正确做法：SSD/NVMe 上降到 1.1 左右
SET random_page_cost = 1.1;

  • 不要对所有表都用低 fillfactor。只有高频更新且被更新列未被索引引用的表才受益：

    sql
    -- 错误！只追加的日志表设低 fillfactor 纯粹浪费空间
ALTER TABLE logs SET (fillfactor = 70);   -- 不推荐

-- 正确做法：只对高频 HOT 更新的表设置
ALTER TABLE orders SET (fillfactor = 80);

  • track_io_timing 开启前先测开销。它在每次 I/O 前后读系统时钟，某些平台上开销显著；官方提供了专门的评估工具：

    bash
    # 先评估：若单次调用开销很大，则不要在生产库长期开启
pg_test_timing
    sql
    -- 仅在排查 I/O 慢查询的窗口期内开启，用完关闭
SET track_io_timing = on;
-- ... 采集数据 ...
SET track_io_timing = off;

  • 不要把 pg_stat_io 的 reads 直接当物理读。统计记的是「内核被调用了多少次」，数据可能来自内核页缓存，必须与 OS 工具交叉验证：

    sql
    -- 不推荐：只看数据库视图就断定「磁盘被打满了」
SELECT sum(reads) FROM pg_stat_io;

-- 正确做法：视图 + OS 工具一起看，并启用 read_time 量化实际等待
SELECT object, context, sum(reads), sum(read_time)
FROM pg_stat_io GROUP BY object, context ORDER BY 3 DESC;

  • 不要把 work_mem 当成 shared_buffers 那样一次加到位。它是「每个操作节点」的内存，会随并发与节点数相乘：

    sql
    -- 错误！全局把 work_mem 设成 1GB，几十个并发就能打爆内存
-- ALTER SYSTEM SET work_mem = '1GB';

-- 正确做法：全局保持较小值，对个别报表查询会话级上调
ALTER SYSTEM SET work_mem = '8MB';
-- 报表连接里：
SET work_mem = '256MB';

最佳实践 ​

  • 先测量，再调参。所有 I/O 参数调整都要有前后对比数据，基线采集方法固定下来：

    sql
    -- 建立基线快照
SELECT pg_stat_reset_shared('io');
SELECT pg_stat_reset_shared('bgwriter');
-- 跑一轮代表性负载后对比
SELECT * FROM pg_stat_io WHERE reads > 0 OR writes > 0;

  • 按存储介质设置 effective_io_concurrency，并把值写进配置管理，而不是靠口头约定：

    ini
    # NVMe / 高 IOPS 云盘
effective_io_concurrency = 200
maintenance_io_concurrency = 100

# SATA SSD
# effective_io_concurrency = 64
# maintenance_io_concurrency = 32

# HDD
# effective_io_concurrency = 2
# maintenance_io_concurrency = 2

  • SSD 上把 random_page_cost 降到 1.1，seq_page_cost 保持 1.0：

    sql
    ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

  • 分层存储用表空间级参数覆盖，让热数据激进读、冷数据保守读：

    sql
    ALTER TABLESPACE hot_ts SET (effective_io_concurrency = 200);
ALTER TABLESPACE archive_ts SET (effective_io_concurrency = 2);

  • 长期开启 track_io_timing（若 pg_test_timing 显示开销可接受），让 read_time 可用于量化定位：

    ini
    track_io_timing = on
    sql
    -- 按平均单次读耗时排序，找出最慢的 I/O 来源
SELECT backend_type, object,
       reads,
       round(read_time / NULLIF(reads, 0), 4) AS avg_read_ms
FROM pg_stat_io
WHERE reads > 1000
ORDER BY avg_read_ms DESC NULLS LAST;

  • 把 pg_stat_bgwriter 与 pg_stat_io 一起监控，判断后台写入器是否够勤：

    sql
    -- maxwritten_clean 持续增长 → 调大 bgwriter_lru_maxpages
SELECT buffers_clean, maxwritten_clean, buffers_alloc, stats_reset
FROM pg_stat_bgwriter;
    ini
    # 观察到 maxwritten_clean 持续增长时
bgwriter_lru_maxpages = 200     # 默认 100，适度上调

  • 给失控查询加安全阀，temp_file_limit 与语句超时双保险：

    sql
    ALTER SYSTEM SET temp_file_limit = '100GB';
ALTER SYSTEM SET statement_timeout = '5min';
SELECT pg_reload_conf();

  • 用 HOT 更新降低索引写放大，对高频更新的表评估 fillfactor：

    sql
    -- 高频更新列上不要建索引，让更新能走 HOT
-- 并为 HOT 预留页内空间
ALTER TABLE orders SET (fillfactor = 80);

  • 读放大优先靠索引与分区解决，而不是靠加内存掩盖：

    sql
    -- 为报表/范围查询建立合适的复合索引，并用 INCLUDE 做覆盖索引
CREATE INDEX CONCURRENTLY idx_orders_report
    ON orders (created_at, region) INCLUDE (amount);

  • 把大表按时间分区，让「热分区留在缓存、冷分区不必频繁访问」：

    sql
    -- 分区后，查询能通过分区裁剪跳过大量冷数据，从根本上减少 I/O
SELECT tableoid::regclass AS partition, count(*)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1 ORDER BY 1;

  • 升级到 PG18 后重新审视 I/O 默认值。两个并发度参数的默认值已提升，pg_stat_io 新增了字节数统计，AIO 也可能改变了原有瓶颈位置：

    sql
    -- 对比升级前后的实际取值，确认没有被旧配置覆盖
SELECT name, setting, source
FROM pg_settings
WHERE name IN ('effective_io_concurrency',
               'maintenance_io_concurrency',
               'io_method',
               'io_combine_limit');

  • 建立 I/O 健康度例行巡检，把本节的关键查询固化成脚本：

    sql
    -- 一次查出四个视图的关键指标，纳入日常巡检
SELECT 'io_hit_pct' AS metric,
       round(100.0 * sum(hits) / NULLIF(sum(hits) + sum(reads), 0), 3)::text AS value
FROM pg_stat_io WHERE object = 'relation'
UNION ALL
SELECT 'checkpoint_pct_requested',
       round(num_requested::numeric / NULLIF(num_timed + num_requested, 0) * 100, 2)::text
FROM pg_stat_checkpointer
UNION ALL
SELECT 'bgwriter_maxwritten', maxwritten_clean::text
FROM pg_stat_bgwriter;

参考链接 ​

下一节

继续学习：硬件选型建议