MIME-Version: 1.0
References: 
 <CAOZWJqPc+s_vA-UfWWLR0s6Mt+DCffjXXVyLHJNJiuMrDLTYcA@mail.gmail.com>
In-Reply-To: 
 <CAOZWJqPc+s_vA-UfWWLR0s6Mt+DCffjXXVyLHJNJiuMrDLTYcA@mail.gmail.com>
From: Shubhang Joshi <shubhangjoshi2405@gmail.com>
Date: Thu, 30 Oct 2025 17:08:06 +0530
Message-ID: 
 <CAOJCrX91Xf3HU5J0Vn_FdrRDpMevNiZUEN3oAWwk4J1H0ibo-Q@mail.gmail.com>
Subject: Re: WAL replay is too slow on secondary server
To: OMPRAKASH SAHU <sahuop2121@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000094d41a06425eaf7b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOJCrX91Xf3HU5J0Vn_FdrRDpMevNiZUEN3oAWwk4J1H0ibo-Q%40mail.gmail.com>
Precedence: bulk

--00000000000094d41a06425eaf7b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi OM,

Please check the network speed =E2=80=94 we faced a similar issue earlier, =
and it
turned out to be related to network performance.
Kindly verify the network latency with your network team as well.

Regards,
Shubhang

On Thu, 30 Oct, 2025, 10:07=E2=80=AFam OMPRAKASH SAHU, <sahuop2121@gmail.co=
m> wrote:

> 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=3D20% , 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=3D,db=3D,app=3D,client=3DLOG:  recovery restart point at
> 5B65/F1DAFA202025-10-30 09:02:08 IST [27125]: user=3D,db=3D,app=3D,client=
=3DDETAIL:
>  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=3D,db=3D,app=3D,client=3DLOG:  restartpoint starting: time2025-10-30=
 09:12:53
> IST [27125]: user=3D,db=3D,app=3D,client=3DLOG:  restartpoint complete: w=
rote 44067
> buffers (2.1%); 1 WAL 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 lsn=3D5B66/3AEAEA602025-10-30 09:12:53 IST [271=
25]:
> user=3D,db=3D,app=3D,client=3DLOG:  recovery restart point at
> 5B66/3AEAEA602025-10-30 09:12:53 IST [27125]: user=3D,db=3D,app=3D,client=
=3DDETAIL:
>  Last completed transaction was at log time 2025-10-30
> 07:21:47.56674+05:30.*
>
> recovery_prefetch output:
>
> postgres=3D# select * from pg_stat_recovery_prefetch;
>            stats_reset            | prefetch |    hit    | skip_init |
> skip_new | skip_fpw | skip_rep  | wal_distance | block_distance | io_dept=
h
>
> ----------------------------------+----------+-----------+-----------+---=
-------+----------+-----------+--------------+----------------+----------
>  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
>

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

<div dir=3D"auto"><div dir=3D"auto">Hi OM,</div><div dir=3D"auto"><br></div=
><div dir=3D"auto">Please check the network speed =E2=80=94 we faced a simi=
lar issue earlier, and it turned out to be related to network performance.<=
/div><div dir=3D"auto">Kindly verify the network latency with your network =
team as well.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Regards,</=
div><div dir=3D"auto">Shubhang</div></div><br><div class=3D"gmail_quote gma=
il_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 30 Oct, 2=
025, 10:07=E2=80=AFam OMPRAKASH SAHU, &lt;<a href=3D"mailto:sahuop2121@gmai=
l.com">sahuop2121@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex"><div dir=3D"ltr">Hi Team,<div><br><div>Greetings!!<br><div dir=3D"a=
uto"></div>
<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>
</blockquote></div>

--00000000000094d41a06425eaf7b--