Postgres Storytelling: What's going on with Synchronous Replication?
summary
Synchronous commits pile up because massive VACUUM activity on a 18 TB table produces huge volumes of WAL, trips max_wal_size and forces checkpoints so walreceiver stalls on filesystem work, producing about 400 SyncRep wait events. Investigation uses ss and perf to show walreceiver switching from fdatasync to heavy pwrite64 workload and slow WAL recycling/initialization. The practical fix is to disable wal_init_zero and wal_recycle, letting WAL files be created and acknowledged faster so SyncRep delays vanish.
AI-written summary · about
chapters (7)
- 0:00Intro
- 2:35The problem
- 5:05Initial investigation of the problem
- 5:54Synchronous replication explained
- 11:10Why is there accumulation of sychronous replication events at a certain point
- 15:54Why were there these wait events
- 22:55The solution
related talks
35mUnderstanding and Optimizing commit sychronization for maximum performance
29mLogical replication theory and concepts
31mPostgreSQL WAL and Commit Synchronization, Optimization Opportunities
26mMyths and Truths about Synchronous Replication in PostgreSQL
48mPostgreSQL Replication: 20 Pitfalls and Solutions
22mSecuring Postgres with streaming replication
48mReplication Refresh
42mPostgreSQL Replication Conflicts: Avoiding Pitfalls