MIME-Version: 1.0
References: <CAG-eXHLx3JGO+pGVBRDPvpUamc6Jbutj2egH7Y8+XTgZzy0V7A@mail.gmail.com>
In-Reply-To: <CAG-eXHLx3JGO+pGVBRDPvpUamc6Jbutj2egH7Y8+XTgZzy0V7A@mail.gmail.com>
From: Ramakrishna m <ram.pgdb@gmail.com>
Date: Wed, 24 Apr 2024 08:10:20 +0530
Message-ID: <CAG-eXH+PhDMavx3-XUyR=G8MyMDPdkqWsKkC5+sCrDPiUhJ8FA@mail.gmail.com>
Subject: Re: Logical Replication Delay on Remote Server
To: pgsql-general@lists.postgresql.org
Cc: ravisql09@gmail.com
Content-Type: multipart/alternative; boundary="0000000000002b6a630616ce9843"
Archived-At: <https://www.postgresql.org/message-id/CAG-eXH%2BPhDMavx3-XUyR%3DG8MyMDPdkqWsKkC5%2BsCrDPiUhJ8FA%40mail.gmail.com>
Precedence: bulk

--0000000000002b6a630616ce9843
Content-Type: text/plain; charset="UTF-8"

Hi Team,

adding one more point here,

 5. Pg_stat_replication_slot  total_txn count reduces when lag occurs .Does
decoding stop in case of logical replication delay?


On Tue, 23 Apr 2024 at 22:55, Ramakrishna m <ram.pgdb@gmail.com> wrote:

> Hi Team,
>
> I am facing replication lag in postgres16 at times,not able to find the
> reason.
>
> Please find the configuration:
> 1) Two replication slots for two servers (1 is the same data center,
> another remote)
> 2) weekly once/twice facing lag(around 2GB for 1k TPS environment) in the
> remote   replication slot and another same data center server slot with 0
> lag.
>
> My observation:
>
> 1) Noticed pg_stat_replication_slot total_txn reduced from 1k to 5 or 6
> but other  slot is same with 1k TPS
> 2)And noticed lag for remote slot but fine with same data center
> 3) Most importantly I have plenty of Bandwidth available in n/w ,2GB is
> still freely available out of 4GB network.
> 4)No IO issues on servers
> I am not able to prove if this is due to Network .Can you help me how to
> proceed on this?
> At Least how the logical decoding total_txn is counting the view
> pg_stat_replication_slot?
>
> --
> Thanks & Regards,
> Ram.
>


-- 
Thanks & Regards,
Ram.

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Team,</div><div dir=3D"ltr"><br><div>a=
dding one more point here,</div><div><br></div><div>=C2=A05. Pg_stat_replic=
ation_slot =C2=A0total_txn count reduces when lag occurs .Does decoding sto=
p in case of logical replication delay?</div><div><br></div></div><div dir=
=3D"ltr"><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Tue, 23 Apr 2024 at 22:55, Ramakrishna m &lt;<a href=3D"mailto:ram.pg=
db@gmail.com" target=3D"_blank">ram.pgdb@gmail.com</a>&gt; wrote:<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><di=
v><font face=3D"arial, sans-serif">Hi Team,<br></font></div><div><font face=
=3D"arial, sans-serif"><br></font></div><div><font face=3D"arial, sans-seri=
f">I am facing=C2=A0<span>replication</span>=C2=A0lag in postgres16 at time=
s,not able to find the reason.</font></div><div><font face=3D"arial, sans-s=
erif"><br>Please find the configuration:<br>1) Two=C2=A0<span>replication</=
span>=C2=A0slots for two servers (1 is the same data center, another=C2=A0<=
span>remote</span>)</font></div><div><font face=3D"arial, sans-serif">2) we=
ekly once/twice facing lag(around 2GB for 1k TPS environment) in the=C2=A0<=
span>remote</span>=C2=A0 =C2=A0<span>replication</span>=C2=A0slot and anoth=
er same data center=C2=A0<span>server</span>=C2=A0slot with 0 lag.<br><br><=
/font></div><div><font face=3D"arial, sans-serif">My observation:</font></d=
iv><div><font face=3D"arial, sans-serif"><br>1) Noticed pg_stat_replication=
_slot=C2=A0total_txn reduced from 1k to 5 or 6 but other=C2=A0 slot is same=
 with 1k TPS<br>2)And noticed lag for=C2=A0<span>remote</span>=C2=A0slot bu=
t fine with same data center<br>3) Most importantly=C2=A0I have plenty of B=
andwidth available in n/w ,2GB is still freely available=C2=A0out of 4GB ne=
twork.<br>4)No IO issues on servers<br>I am not able to prove if this is du=
e to Network .Can you help me how to proceed on this?=C2=A0<br>At Least=C2=
=A0how the=C2=A0<span>logical</span>=C2=A0decoding=C2=A0total_txn is counti=
ng the view pg_stat_replication_slot?<font color=3D"#888888"><br></font></f=
ont></div><font color=3D"#888888"><br></font></div><span class=3D"gmail_sig=
nature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><di=
v dir=3D"ltr">Thanks &amp; Regards,<div>Ram.<br></div></div></div></div>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr">Thanks &amp; Regards,<div>Ram.</div></div></div></div>
</div>

--0000000000002b6a630616ce9843--