postgresql主从延迟的一则案例分析（大表大变动）

原创 Jerome Q 2025-10-20

496

1.集群背景：

一主两从，三台机器复用的两个集群。其中额一个主从中，有一个机器的延迟很大，但是观察到主机的io压力不大.

2.当前现象总结：

root执行，观察读写速度：
iotop -ao或者iotop -aoP | grep postgres

key	value
总 Disk Write	2.18 MB/s（吞吐不高）
`startup` 进程 IO Wait	51.28% （非常高）
`walreceiver` IO Wait	40.84%（也很高）
WAL replay 位置	`recovering 000000020000724300000079`
`checkpointer` 写入量	122MB+
WAL write 量	`walreceiver` 写了 128MB
`fsync = on` + `fdatasync`	每条写都阻塞在物理盘刷写，不能异步叠加

3.延迟猜测：

可能是 PostgreSQL 的 fsync() + 小 WAL flush + 顺序 replay 写造成的。

WAL replay 的写行为特征：PostgreSQL 的 startup 进程在 replay WAL 时会：
1.不断从 WAL 文件中解出指令（例如插入、更新、dirty pages）
2.触发针对数据页的写操作
3.写入数据后立即 fsync() 或调用 fdatasync()，刷盘确认

这个流程在 fsync=on 时非常阻塞：

每个小写操作都需要 fsync() 阻塞住等确认
如果底层存储（尤其是虚拟化、云硬盘、机械盘）单次 fsync 延迟达到 10ms+，则写 WAL 本身几乎是串行的

排查时注意是否存在回放锁：startup进程会需要获取AccessExclusive Lock
说明1.备库回放了DDL；
2.vacuum：回收的页处于存储数据的文件尾部，整个页都可以删除，会做truncate操作，这一步骤会获取AccessExclusive Lock，把尾部的这些页统一从文件中删除，文件大小和表所占空间随之减少。

4.一般性延迟的原因总结：

原因	说明
fsync 阻塞写	`fsync=on` 会让每个 flush 都必须等物理落盘
单线程 replay	`startup` 是单线程做 replay，无法并行刷盘
存储本身 fsync 延迟高	特别是机械硬盘/网络块存储/XFS/RAID5
checkpointer 配置不合理	刷盘行为在同步和 recovery 期间没有完全打散
非 WAL 分盘	`pg_wal` 与 `base/` 数据目录在同盘，导致 replay 和 wal write 互卡
宿主机 IO scheduler 不合适	比如使用 `cfq` 而不是 `deadline` / `none`（针对 SSD）

5.解决：

5.1 尝试关闭 fsync（非生产建议）

仅对非关键从库！不要对主库或 failover 热备这么做！

fsync = off
synchronous_commit = off

5.2加大 replay 并发缓冲：

wal_buffers = 16MB         -- 默认为 -1（自动），可以手动提升
maintenance_io_concurrency = 16  -- 或更高

5.3将pg_wal分盘（特别是 SSD）

因为可能 replay 和 wal write 互卡，把 $PGDATA/pg_wal 放到独立高性能盘（比如SSD），或者云盘的高速挂载点。

5.4降低 WAL `fsync` 强度（若你必须保留 fsync）

可以尝试：

wal_writer_delay = 20ms         -- 缓冲写入时间
wal_writer_flush_after = 256kB  -- 不要每个小 WAL 都刷盘

5.5检查文件系统调度器：

查看：

cat /sys/block/sdX/queue/scheduler

若使用 cfq（已废弃）或 bfq，建议：

对 SSD 用 none 或 mq-deadline
对机械盘建议 deadline
可更改：

echo mq-deadline > /sys/block/sdX/queue/scheduler

5.6使用 `pg_stat_replication` + `pg_stat_wal_receiver` 跟踪延迟

SELECT
  now() - pg_last_xact_replay_timestamp() AS replay_delay,
  slot_name, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

6.wal回放卡住的原因

连续执行postgres=# SELECT now(), pg_last_wal_replay_lsn();观察是否变化。
执行发现在变的，但是比较慢：

postgres=# SELECT now(), pg_last_wal_replay_lsn(); 
              now              | pg_last_wal_replay_lsn 
-------------------------------+------------------------
 2025-07-13 00:07:28.070297+08 | 7243/BB813D38
(1 row)

postgres=# SELECT now(), pg_last_wal_replay_lsn();
              now              | pg_last_wal_replay_lsn 
-------------------------------+------------------------
 2025-07-13 00:07:29.719128+08 | 7243/BC0F3678
(1 row)

postgres=# SELECT now(), pg_last_wal_replay_lsn();
              now              | pg_last_wal_replay_lsn 
-------------------------------+------------------------
 2025-07-13 00:07:31.045273+08 | 7243/BC0F3678
(1 row)

postgres=# SELECT now(), pg_last_wal_replay_lsn();
              now              | pg_last_wal_replay_lsn 
-------------------------------+------------------------
 2025-07-13 00:07:34.017014+08 | 7243/BCB0CCB0
(1 row)

postgres=# SELECT now(), pg_last_wal_replay_lsn();
              now              | pg_last_wal_replay_lsn 
-------------------------------+------------------------
 2025-07-13 00:07:42.673241+08 | 7243/BD3551C0
(1 row)

分析：

时间（秒）	LSN 变化（十六进制）	差值估算（近似）
00:07:28 → 00:07:29	`BB813D38` → `BC0F3678`	≈ 8MB（正常）
00:07:29 → 00:07:31	无变化	卡顿 2 秒
00:07:31 → 00:07:34	→ `BCB0CCB0`	≈ 8MB（恢复一点）
00:07:34 → 00:07:42	→ `BD3551C0`	≈ 8MB（耗时 8 秒❗）

6.1分析数据库当前行为

postgres=# SELECT pg_last_wal_replay_lsn();
 pg_last_wal_replay_lsn 
------------------------
 7244/3D08A0D8
(1 row)

(主执行？)

postgres=# SELECT pg_last_wal_replay_lsn(), pg_walfile_name(pg_last_wal_replay_lsn());
 pg_last_wal_replay_lsn |     pg_walfile_name      
------------------------+--------------------------
 79B/F5FFBC18           | 000000020000079B000000F5
(1 row)

pg_waldump 0000000200007243000000BD         
rmgr: Btree       len (rec/tot):     57/  2662, tx:          0, lsn: 7243/BD723780, prev 7243/BD723730, desc: VACUUM lastBlockVacuumed 189262, blkref #0: rel 1663/16645/932086 blk 189263 FPW
rmgr: Btree       len (rec/tot):     57/  2717, tx:          0, lsn: 7243/BD724200, prev 7243/BD723780, desc: VACUUM lastBlockVacuumed 189263, blkref #0: rel 1663/16645/932086 blk 189264 FPW
rmgr: Btree       len (rec/tot):     57/  2729, tx:          0, lsn: 7243/BD724CA0, prev 7243/BD724200, desc: VACUUM lastBlockVacuumed 189264, blkref #0: rel 1663/16645/932086 blk 189265 FPW

存在大量fpw

找到当前的这个对象932086是什么问题？

xx_db=# SELECT oid::regclass, relname, relkind FROM pg_class WHERE oid = 932086;
                 oid                 |         relname         | relkind 
-------------------------------------+-------------------------+---------
 sch1.idx_tab1_id | idx_tab1_id | i
(1 row)

找到表

xx_db=# SELECT
xx_db-#   ix.indexrelid::regclass AS index_name,
xx_db-#   t.oid::regclass AS full_table_name,
xx_db-#   t.relname AS table_name,
xx_db-#   t.relkind
xx_db-# FROM pg_index AS ix
xx_db-# JOIN pg_class AS i ON i.oid = ix.indexrelid
xx_db-# JOIN pg_class AS t ON t.oid = ix.indrelid
xx_db-# WHERE ix.indexrelid = 'sch1.idx_tab1_id'::regclass;
             index_name              |   full_table_name   | table_name | relkind 
-------------------------------------+---------------------+------------+---------
 sch1.idx_tab1_id | sch1.xxxx | xxxx    | r
(1 row)

表大小：

xx_db=# SELECT
  pg_size_pretty(pg_relation_size('sch1.xxxx')) AS table_size,                
  pg_size_pretty(pg_total_relation_size('sch1.xxxx')) AS total_size_with_index;                
 table_size | total_size_with_index 
------------+-----------------------
 22 GB      | 94 GB
(1 row)

22的数据，94的索引：所有索引排行是怎么样的？

xx_db=# SELECT
xx_db-#   indexrelname AS index_name,
xx_db-#   pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
xx_db-# FROM pg_stat_user_indexes
xx_db-# WHERE relid = 'sch1.xxxx'::regclass
xx_db-# ORDER BY pg_relation_size(indexrelid) DESC;
               index_name                | index_size 
-----------------------------------------+------------
 idx_xxxx_physic_device_id_up_down_id | 6865 MB
 idx_xxxx_old_id                      | 6100 MB
 xxxx_pkey                            | 5604 MB
 idx_xxxx_physic_device_id            | 5324 MB
 idx_xxxx_version                     | 4896 MB
 idx_xxxx_name                        | 4083 MB
 idx_xxxx_code                        | 3923 MB
 idx_xxxx_1                           | 3922 MB
 idx_xxxx_logic_device_id             | 3869 MB
 idx_YYYY46                              | 2964 MB
 idx_xxxx_nm_code                     | 2627 MB
 idx_xxxx_spec_id                     | 2423 MB
 idx_YYYY13                              | 2423 MB
 idx_YYYY14                              | 2417 MB
 idx_xxxx_create_date                 | 2413 MB
 idx_xxxx_region_id                   | 2221 MB
 idx_xxxx_leaf_region_id              | 2190 MB
 idx_xxxx_name_gin                    | 2177 MB
 idx_xxxx_code_gin                    | 2045 MB
 idx_xxxx_nm_id                       | 2030 MB
 idx_tab1_id                 | 2030 MB
 idx_xxxx_tml_id                      | 2030 MB

利用pgstattuple，观察膨胀率

 xx_db=# CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION
xx_db=# SELECT * FROM pgstattuple('sch1.xxxx');
  table_len  | tuple_count |  tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-------------+-------------+-------------+---------------+------------------+----------------+--------------------+------------+--------------
 23257366528 |    90814332 | 21459177880 |         92.27 |            72171 |       17641569 |               0.08 |  895667552 |         3.85
(1 row)

字段	含义	当前的值	解读
`table_len`	表文件总大小	23.25 GB	这是物理磁盘占用，不含索引
`tuple_count`	活跃元组数量	90,814,332	表中有 9000 多万行活跃数据
`tuple_len`	活跃数据占用空间	21.46 GB	活跃数据的实际字节数
`tuple_percent`	活跃数据占比	92.27%	非常健康
`dead_tuple_count`	死元组数量	72,171	很少
`dead_tuple_percent`	死元组占比	0.08%	几乎没有膨胀，极好
`free_space`	空闲空间（页内）	895 MB	有点空闲空间，完全正常
`free_percent`	空间利用率	96.15%	非常好

检查这些索引扫描的次数：

xx_db=# SELECT
xx_db-#   indexrelname AS index_name,
xx_db-#   idx_scan,
xx_db-#   pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
xx_db-# FROM pg_stat_user_indexes
xx_db-# WHERE relid = 'sch1.xxxx'::regclass
xx_db-# ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;
               index_name                |  idx_scan   | index_size 
-----------------------------------------+-------------+------------
 idx_xxxx_nm_id                       |           0 | 2030 MB
 idx_xxxx_old_id                      |           4 | 6100 MB
 idx_YYYY14                              |          16 | 2417 MB
 idx_xxxx_region_id                   |          23 | 2221 MB
 idx_xxxx_tml_id                      |          23 | 2030 MB
 idx_xxxx_create_date                 |         331 | 2413 MB
 idx_xxxx_leaf_region_id              |        3784 | 2190 MB
 idx_YYYY13                              |        5664 | 2423 MB
 idx_xxxx_code_gin                    |        8890 | 2045 MB
 idx_xxxx_name_gin                    |       10727 | 2177 MB
 idx_xxxx_name                        |       25961 | 4083 MB
 idx_xxxx_nm_code                     |       64325 | 2627 MB
 idx_YYYY46                              |      125414 | 2964 MB
 idx_tab1_id                 |     1376543 | 2030 MB
 idx_xxxx_code                        |     5327005 | 3923 MB
 idx_xxxx_version                     |    26086705 | 4896 MB
 idx_xxxx_logic_device_id             |   106492588 | 3869 MB
 idx_xxxx_spec_id                     |   213420828 | 2423 MB
 idx_xxxx_physic_device_id_up_down_id |   667464056 | 6865 MB
 idx_xxxx_1                           |  1996229766 | 3922 MB
 idx_xxxx_physic_device_id            |  2847977603 | 5324 MB
 xxxx_pkey                            | 27754139300 | 5604 MB
(22 rows)

随机检查一个索引的膨胀率

xx_db=# SELECT * FROM pgstattuple('sch1.idx_xxxx_physic_device_id_up_down_id');
 table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
 7198941184 |    90904033 | 2903617608 |         40.33 |              739 |          23648 |                  0 | 3807181468 |        52.89
(1 row)

字段名	含义	当前值	解读
`table_len`	索引物理大小（字节）	`7198941184` (≈ 6.7 GB)	索引实际占用空间，`du -sh` 可看到类似值
`tuple_count`	索引中有效条目数	`90,904,033`	说明有 9000 万个索引条目
`tuple_len`	所有有效条目的总字节数	`2903617608` (≈ 2.7 GB)	这些才是“真正有用”的空间
`tuple_percent`	有效条目占比	`40.33%`	表示仅 40% 的索引页有用
`dead_tuple_count`	死条目数量	`739`	非常低，说明不是删除造成的膨胀
`dead_tuple_len`	死条目总大小	`23648` 字节	忽略不计（≈ 23KB）
`dead_tuple_percent`	死条目占比	`0%`	可忽略，不是 DELETE 问题
`free_space`	页中空闲空间总量	`3807181468` (≈ 3.5 GB)	巨大的碎片空间！
`free_percent`	空页占比	`52.89%`	超过一半空间是浪费的！

建议reindex

REINDEX INDEX CONCURRENTLY sch1.idx_xxxx_physic_device_id_up_down_id;
不锁表、不阻塞读写

PS:从 WAL dump 中只要看到：
rmgr: Btree
desc: VACUUM
连续的 lastBlockVacuumed xxx
高频出现 + 快速递增 block
可以确认：主库正在跑一个“超大型索引的 VACUUM” 导致了大延迟。

可参考：xcc:《主从之间延迟过大如何优化》:https://mp.weixin.qq.com/s/ZbIqtANvRSxDlx9SL6pCKg

postgresql 墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者