| From: | OMPRAKASH SAHU <sahuop2121(at)gmail(dot)com> |
|---|---|
| To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | WAL replay is too slow on secondary server |
| Date: | 2025-10-30 04:36:56 |
| Message-ID: | CAOZWJqPc+s_vA-UfWWLR0s6Mt+DCffjXXVyLHJNJiuMrDLTYcA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
Hi Team,
Greetings!!
We have a postgresql cluster setup using patroni.
The DB is being used for heavy transactional application, now the
problem is that on replica server WAL replay is too slow.
We have increased the IOPS to 6k and Throughput to 600 on nvme EBS volume
of wal directory and 10k &800 on data directory.
but the WAL is being accumulated on the replica as usual and applying wal
is having no improvement.
changed the maintenance_io_concurrency on replica to 32.
CPU utilization max=20% , RAM utilization is also max 20.
see the below postgres logs that shows around 2hrs lag
*tail -f /var/log/postgresql/postgresql.log2025-10-30 09:02:08 IST [27125]:
user=,db=,app=,client=LOG: recovery restart point at
5B65/F1DAFA202025-10-30 09:02:08 IST [27125]: user=,db=,app=,client=DETAIL:
Last completed transaction was at log time 2025-10-30
07:16:40.115131+05:30.2025-10-30 09:08:23 IST [27125]:
user=,db=,app=,client=LOG: restartpoint starting: time2025-10-30 09:12:53
IST [27125]: user=,db=,app=,client=LOG: restartpoint complete: wrote 44067
buffers (2.1%); 1 WAL file(s) added, 73 removed, 0 recycled; write=269.362
s, sync=0.042 s, total=269.633 s; sync files=142, longest=0.005 s,
average=0.001 s; distance=1197052 kB, estimate=1587336 kB;
lsn=5B66/6C3082F8, redo lsn=5B66/3AEAEA602025-10-30 09:12:53 IST [27125]:
user=,db=,app=,client=LOG: recovery restart point at
5B66/3AEAEA602025-10-30 09:12:53 IST [27125]: user=,db=,app=,client=DETAIL:
Last completed transaction was at log time 2025-10-30
07:21:47.56674+05:30.*
recovery_prefetch output:
postgres=# select * from pg_stat_recovery_prefetch;
stats_reset | prefetch | hit | skip_init |
skip_new | skip_fpw | skip_rep | wal_distance | block_distance | io_depth
----------------------------------+----------+-----------+-----------+----------+----------+-----------+--------------+----------------+----------
2025-10-29 23:02:21.396179+05:30 | 182762 | 251000856 | 3841721 |
1100099 | 3520777 | 137392573 | 8984 | 80 | 0
(1 row)
I would request your thoughts and suggestions if we can get rid of this
slowness and get some speed.
Regards,
OM
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Edwin UY | 2025-10-30 09:30:00 | Replication Question / Issue - PRIMARY with SYNC and ASYNC Replication |
| Previous Message | Sam Stearns | 2025-10-29 19:19:25 | Re: pg_basebackup --incremental |