PostgreSQL 18 异步 I/O 调优实战:从 io_method 到 pg_stat_io 的可验证诊断与回退 原创

温馨提示:
本文最后更新于 2026-11-20,已超过 1 天没有更新。 若文章内的图片失效(无法正常加载),请留言反馈或直接 联系我。

PostgreSQL 18 引入异步 I/O 后,运维中最容易犯的错误是:看到新版本就立刻把 io_method 改成 io_uring,再凭一条冷缓存 SQL 的耗时宣布“性能提升多少”。这两个动作都不构成验证。前者忽略了当前读取路径是否真的能并发,后者把 Linux page cache、计划变化和采样误差混在了一起。

本文在 PostgreSQL 18.6 / Linux 的独立测试实例中验证了文中的参数查询、执行计划与统计 SQL,给出一套可重复的流程:先确认 I/O 路径,再建立基线,最后只调整一个变量。文中的毫秒数仅用于解释输出,不是跨硬件可复用的性能承诺。

一、先判断:你的瓶颈真的是同步读吗

异步 I/O 的价值是让一个后端在等待存储时还能推进其他工作,或让可以批量提交的读取不再逐块等待。它不会自动优化错误的执行计划,也不会消除锁等待、WAL flush、网络传输或序列化成本。

先按下面的决策表缩小范围,避免拿 I/O 参数解决所有慢查询。

观测证据 优先排查方向 是否优先试异步 I/O
DataFileRead、执行计划有大量 shared read,存储延迟高 Bitmap Heap Scan、顺序扫描、批量维护读取 是,可以做受控 A/B
锁等待、长事务、连接堆积 pg_stat_activity 与锁链 否
临时文件读写、Hash/Sort spill work_mem、行数估算、查询改写 通常否
提交时等待 WAL 持久化 WAL、存储 fsync、同步复制级别 否,不能混同数据页异步读
数据全部命中 shared buffers,CPU 时间占主导 计划、函数、JIT、结果处理 通常没有明显收益

会话级等待快照可以这样取:

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS elapsed,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND state = 'active'
ORDER BY query_start;

wait_event_type = 'IO' 只是入口;要进一步区分读取数据页、WAL、临时文件和扩展数据文件,必须结合 EXPLAIN 与 pg_stat_io。

二、三个参数分别控制什么

在本文验证实例上,默认值如下:io_method = worker、io_workers = 3、io_combine_limit = 128kB。你的发行版、启动配置或云平台可能不同,不要直接照抄数值。

SELECT name, setting, unit, context, source, boot_val
FROM pg_settings
WHERE name IN (
  'io_method', 'io_workers', 'io_combine_limit',
  'effective_io_concurrency', 'maintenance_io_concurrency',
  'track_io_timing', 'track_wal_io_timing'
)
ORDER BY name;
参数 作用与变更边界 验证重点
io_method 选择异步 I/O 执行方式;支持情况取决于构建和操作系统。它属于 postmaster 上下文,改动需要重启实例。 确认新值实际生效,而非仅写进配置文件。
io_workers io_method = worker 时的 I/O worker 数;默认 3,配置重载可生效。 worker 数不是越大越好,要结合存储队列与 CPU。
io_combine_limit 限制一次读写的合并大小;验证实例为 16 × 8kB,即 128kB。 观察读取字节与次数,避免只看 SQL 总耗时。
effective_io_concurrency 向优化器/预取路径描述存储可承担的并发能力;它不是磁盘队列深度的简单复制。 先保持默认,单独验证,不与 io_method 同时调。

Linux 上考虑 io_uring 前,先检查内核接口和 PostgreSQL 构建支持:

# 宿主机/容器宿主内核均需满足;容器内还可能受 seccomp 限制
uname -r
grep -w io_uring_setup /proc/kallsyms

# PostgreSQL 18 自带的配置检查工具
pg_config --configure | grep -E 'liburing|io_uring' || true

注意:/proc/kallsyms 中存在符号不等于容器一定可调用;有些环境会通过 seccomp 禁止 io_uring。最终应以隔离实例启动日志和压测结果为准,不要在生产库上边重启边试错。

三、把 EXPLAIN 从“看执行计划”升级为 I/O 证据

对 SELECT 做基线时,使用同一组选项,保存完整输出:

SET track_io_timing = on;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, FORMAT JSON)
SELECT customer_id, count(*), sum(sku)
FROM bench_orders
GROUP BY customer_id
ORDER BY count(*) DESC
LIMIT 20;

如果担心逐节点取时钟的开销,可以用 TIMING OFF;语句总时间仍会记录。对于 INSERT/UPDATE/DELETE,ANALYZE 会真的执行语句。无副作用地测量写入计划时必须放在事务里:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL, COSTS OFF)
UPDATE bench_orders SET sku = sku + 1 WHERE id = 1;
ROLLBACK;

测试输出会出现类似信息:

Update on bench_orders
  Buffers: shared hit=14 read=1 dirtied=3
  WAL: records=4 fpi=2 bytes=15079
  -> Index Scan using bench_orders_pkey on bench_orders

这里的 fpi 是 full-page image。它解释了为什么一次看似很小的 UPDATE 也可能产生较多 WAL;这类问题不能靠数据页异步读取解决。

读取案例则重点看下面这些字段:

Aggregate
  Buffers: shared hit=62 read=52
  I/O Timings: shared read=0.323
  -> Bitmap Heap Scan on bench_orders
       Recheck Cond: (customer_id >= 100 AND customer_id <= 199)
       Heap Blocks: exact=97
  • shared hit:在 PostgreSQL shared buffers 中命中,避免一次读取。
  • shared read:向操作系统发起读取,不代表一定穿透到物理磁盘,数据仍可能在 Linux page cache。
  • read=...ms:需开启 track_io_timing;它是观测数据,不是纯设备延迟。
  • SETTINGS:把影响规划的非默认参数带进结果,避免两次测试实际上用了不同配置。

PostgreSQL 18 的 EXPLAIN ANALYZE 已默认包含 buffers 信息,但显式写出 BUFFERS 能让运维脚本在升级前后保持明确、可读。

四、用 pg_stat_io 找出“谁在读写什么”

单条 SQL 快并不代表实例整体改善。PostgreSQL 18 的 pg_stat_io 同时保留操作次数与字节数,可将后端类型、对象和访问上下文玩整拆开:

SELECT backend_type, object, context,
       reads, read_bytes, read_time,
       writes, write_bytes, write_time,
       extends, extend_bytes
FROM pg_stat_io
WHERE coalesce(reads, 0) + coalesce(writes, 0)
    + coalesce(extends, 0) > 0
ORDER BY coalesce(read_bytes, 0)
       + coalesce(write_bytes, 0) DESC;

建议用“固定窗口增量”而不是直接解读累计值。以下函数返回一行 JSON 快照,便于 Prometheus exporter 或巡检脚本保存:

SELECT jsonb_agg(to_jsonb(s)) AS snapshot
FROM (
  SELECT backend_type, object, context,
         reads, read_bytes, writes, write_bytes, extends, extend_bytes
  FROM pg_stat_io
) s;

间隔 10 分钟再取一次,按 (backend_type, object, context) 对齐求差值。回答三个问题:

  1. 读放大:返回相同业务行数时,read_bytes 是否下降?
  2. 写放大:normal、vacuum、bulkwrite 的写入是否出现异常增长?
  3. 扩展成本:extends 很高时,是否该检查批量插入、表膨胀或文件扩展,而不是只调读参数?

定位单个后端时,PostgreSQL 18 可以使用 pg_stat_get_backend_io()。注意它是函数,不要自行添加不存在的 pid 输出列;连接 PID 在 pg_stat_activity 中关联:

SELECT a.pid, a.usename, a.application_name,
       i.object, i.context, i.reads, i.read_bytes,
       i.writes, i.write_bytes, i.extends, i.extend_bytes
FROM pg_stat_activity a
CROSS JOIN LATERAL pg_stat_get_backend_io(a.pid) i
WHERE a.datname = current_database()
  AND (
    coalesce(i.reads, 0) + coalesce(i.writes, 0)
    + coalesce(i.extends, 0)
  ) > 0
ORDER BY a.pid, i.object, i.context;

五、可复现 A/B:一次只改一个变量

先建立可重复的数据集。以下 SQL 已在 18.6 实例执行;50 万行只是功能演示,不足以证明 NVMe 或云盘上的性能趋势:

CREATE TABLE bench_orders AS
SELECT g AS id,
       (g % 10000) AS customer_id,
       (g % 1000) AS sku,
       md5(g::text) AS payload
FROM generate_series(1, 500000) g;

ALTER TABLE bench_orders ADD PRIMARY KEY (id);
CREATE INDEX bench_orders_customer_idx
  ON bench_orders(customer_id, id);
ANALYZE bench_orders;

用 pgbench 执行固定查询。脚本 read.sql 使用确定性区间,保证 A/B 两轮访问同一逻辑数据:

-- read.sql
SELECT count(*)
FROM bench_orders
WHERE customer_id BETWEEN 100 AND 199;

-- 先预热,再测试;连接数、时长、数据目录、存储策略必须一致
pgbench -h /var/run/postgresql -p 5432 -U bench -d benchdb \
  -c 8 -j 4 -T 120 -r -f read.sql

正式比较时至少做三组:当前配置、仅改变 io_method、回到当前配置。每组先预热,再测多轮并报告中位数与尾延迟。以下表格用于记录,不要预填“提升百分比”:

检查项 A:原配置 B:仅改 io_method 判定
SQL / 数据 / 连接数 / 时长 完全一致 否则结果不可比
执行计划指纹 保存 JSON 保存 JSON 计划变了需单独解释
pgbench TPS、平均与尾延迟 多轮中位数 多轮中位数 不以单次最好值下结论
pg_stat_io 窗口字节/次数 同窗口差值 同窗口差值 看读取路径是否真正变化
iostat 的 await、aqu-sz、%util 同步采样 同步采样 排除设备排队恶化

与基准测试并行采样 Linux 块设备:

iostat -xmt 1 120 > io-baseline.txt
# 第二次测试写入独立文件,不覆盖基线
iostat -xmt 1 120 > io-aio.txt

若新配置只提高单线程冷启动第一轮的分数,而稳定并发下 TPS、尾延迟或设备队列没有改善,就没有足够理由上线。

六、上线与回退清单

  1. 保存实际值:导出 pg_settings,同时保存 SHOW config_file、启动参数和云盘/IOPS 配置;不要只复制一段 postgresql.conf。
  2. 隔离验证:在同版本的小实例中测试 io_method;它是 postmaster 参数,不能靠 SELECT pg_reload_conf() 生效。
  3. 监控就绪:先开启一小段 track_io_timing 评估观测开销,再决定生产是否常开;WAL I/O 计时由独立的 track_wal_io_timing 控制。
  4. 灰度一次一变量:不要同时改 shared_buffers、并发、io_combine_limit 和存储队列。
  5. 验收:固定窗口比较计划、TPS/延迟、pg_stat_io 增量与 iostat,并检查错误日志。
  6. 回退:恢复已记录的 io_method,在既定维护窗口重启,重新执行同一基线,确认指标恢复。

结论很简单:PostgreSQL 18 的异步 I/O 是值得测试的新执行路径,而不是一条通用的加速开关。真正可靠的调优结果,必须能同时回答“计划是否相同、读了哪些对象、设备是否排队、尾延迟是否改善、如何回退”这五个问题。

参考:PostgreSQL 18 官方文档的 Resource Consumption、EXPLAIN、Cumulative Statistics System 与 WAL 配置章节。