MIME-Version: 1.0
From: OMPRAKASH SAHU <sahuop2121@gmail.com>
Date: Thu, 30 Oct 2025 10:06:56 +0530
Message-ID: 
 <CAOZWJqPc+s_vA-UfWWLR0s6Mt+DCffjXXVyLHJNJiuMrDLTYcA@mail.gmail.com>
Subject: WAL replay is too slow on secondary server
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000084e8a6064258cd8a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOZWJqPc%2Bs_vA-UfWWLR0s6Mt%2BDCffjXXVyLHJNJiuMrDLTYcA%40mail.gmail.com>
Precedence: bulk

--00000000000084e8a6064258cd8a
Content-Type: text/plain; charset="UTF-8"

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

--00000000000084e8a6064258cd8a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Team,<div><br><div>Greetings!!<br><div dir=3D"auto"></d=
iv>
<div><br></div><div>We have a postgresql cluster setup using patroni.</div>=
<div>The DB is being used for heavy transactional application, now the prob=
lem=C2=A0is that on replica server WAL replay is too slow.</div><div>We hav=
e increased the IOPS to 6k and Throughput to 600 on nvme EBS volume of wal =
directory and 10k &amp;800 on data directory.</div><div><br></div><div>but =
the WAL is being accumulated on the replica as usual and applying wal is ha=
ving no improvement.</div><div>changed=C2=A0the maintenance_io_concurrency =
on replica to 32.</div><div>CPU utilization max=3D20% , RAM utilization is =
also max 20.</div><div><br></div><div>see the below postgres logs that show=
s around 2hrs lag</div><div><br></div><div><b>tail -f /var/log/postgresql/p=
ostgresql.log<br>2025-10-30 09:02:08 IST [27125]: user=3D,db=3D,app=3D,clie=
nt=3DLOG: =C2=A0recovery restart point at 5B65/F1DAFA20<br>2025-10-30 09:02=
:08 IST [27125]: user=3D,db=3D,app=3D,client=3DDETAIL: =C2=A0Last completed=
 transaction was at log time 2025-10-30 07:16:40.115131+05:30.<br>2025-10-3=
0 09:08:23 IST [27125]: user=3D,db=3D,app=3D,client=3DLOG: =C2=A0restartpoi=
nt starting: time<br>2025-10-30 09:12:53 IST [27125]: user=3D,db=3D,app=3D,=
client=3DLOG: =C2=A0restartpoint complete: wrote 44067 buffers (2.1%); 1 WA=
L file(s) added, 73 removed, 0 recycled; write=3D269.362 s, sync=3D0.042 s,=
 total=3D269.633 s; sync files=3D142, longest=3D0.005 s, average=3D0.001 s;=
 distance=3D1197052 kB, estimate=3D1587336 kB; lsn=3D5B66/6C3082F8, redo ls=
n=3D5B66/3AEAEA60<br>2025-10-30 09:12:53 IST [27125]: user=3D,db=3D,app=3D,=
client=3DLOG: =C2=A0recovery restart point at 5B66/3AEAEA60<br>2025-10-30 0=
9:12:53 IST [27125]: user=3D,db=3D,app=3D,client=3DDETAIL: =C2=A0Last compl=
eted transaction was at log time 2025-10-30 07:21:47.56674+05:30.</b><br></=
div><div><b><br></b></div><div>recovery_prefetch output:</div><div><br></di=
v><div>postgres=3D# select * from pg_stat_recovery_prefetch;<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stats_reset =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0| prefetch | =C2=A0 =C2=A0hit =C2=A0 =C2=A0| skip_init | skip_new=
 | skip_fpw | skip_rep =C2=A0| wal_distance | block_distance | io_depth<br>=
----------------------------------+----------+-----------+-----------+-----=
-----+----------+-----------+--------------+----------------+----------<br>=
=C2=A02025-10-29 23:02:21.396179+05:30 | =C2=A0 182762 | 251000856 | =C2=A0=
 3841721 | =C2=A01100099 | =C2=A03520777 | 137392573 | =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 8984 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 80 | =C2=A0 =C2=A0=
 =C2=A0 =C2=A00<br>(1 row)</div><div><br></div><div>I would request your th=
oughts and suggestions if we can get rid of this slowness and get some spee=
d.</div><div><br></div><div>Regards,</div><div>OM</div></div></div></div>

--00000000000084e8a6064258cd8a--