PostgreSQL 18 异步 I/O 调优实战:从 io_method 到 pg_stat_io 的可验证诊断与回退 原创
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) 对齐求差值。回答三个问题:
- 读放大:返回相同业务行数时,
read_bytes是否下降? - 写放大:
normal、vacuum、bulkwrite的写入是否出现异常增长? - 扩展成本:
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、尾延迟或设备队列没有改善,就没有足够理由上线。
六、上线与回退清单
- 保存实际值:导出
pg_settings,同时保存SHOW config_file、启动参数和云盘/IOPS 配置;不要只复制一段 postgresql.conf。 - 隔离验证:在同版本的小实例中测试
io_method;它是 postmaster 参数,不能靠SELECT pg_reload_conf()生效。 - 监控就绪:先开启一小段
track_io_timing评估观测开销,再决定生产是否常开;WAL I/O 计时由独立的track_wal_io_timing控制。 - 灰度一次一变量:不要同时改
shared_buffers、并发、io_combine_limit和存储队列。 - 验收:固定窗口比较计划、TPS/延迟、
pg_stat_io增量与iostat,并检查错误日志。 - 回退:恢复已记录的
io_method,在既定维护窗口重启,重新执行同一基线,确认指标恢复。
结论很简单:PostgreSQL 18 的异步 I/O 是值得测试的新执行路径,而不是一条通用的加速开关。真正可靠的调优结果,必须能同时回答“计划是否相同、读了哪些对象、设备是否排队、尾延迟是否改善、如何回退”这五个问题。
参考:PostgreSQL 18 官方文档的 Resource Consumption、EXPLAIN、Cumulative Statistics System 与 WAL 配置章节。