MIME-Version: 1.0
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
 <abd3bc064d16bc93a2d8661a692903da97d2c154.camel@cybertec.at>
 <CAD=mzVVvK8xk-9m8h3Xu27cGN7BW329HKYdO+0EMXfWvSD3AGA@mail.gmail.com>
 <bed28c629a839b1f354e18f416a87fd5f4f78ba7.camel@cybertec.at>
 <CAD=mzVVqR-mKUFHetsejFWSPQPbLjTVhCmBebJTFX5XmYp+nGg@mail.gmail.com>
 <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at>
 <CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com>
 <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
 <d18f56f9e8aec98b981ade94f300ec7473ec0cce.camel@cybertec.at>
 <CAEzWdqdPnErdeg6xe=zf7aF-fGy0Z42vXEm6zE6Ok25o=f6a7Q@mail.gmail.com> <56ad97911d83f721dd872e8ee68cd77d50d3eef6.camel@cybertec.at>
In-Reply-To: <56ad97911d83f721dd872e8ee68cd77d50d3eef6.camel@cybertec.at>
From: sud <suds1434@gmail.com>
Date: Wed, 5 Jun 2024 11:55:32 +0530
Message-ID: <CAD=mzVU7Ry7xhZ=Kra4N87ugvAUubwGFqnLtXbcvy8yJasOVPQ@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: yudhi s <learnerdatabase99@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dcbfcb061a1ea298"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVU7Ry7xhZ%3DKra4N87ugvAUubwGFqnLtXbcvy8yJasOVPQ%40mail.gmail.com>
Precedence: bulk

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

Hello Laurenz,

Thank you so much.This information was really helpful for us
understanding the working of these parameters.

One follow up question i have , as we are setting one of the
standby/replica with value idle_in_transaction_session_timeout=3D-1 which c=
an
cause the WAL's to be heavily backlogged in a scenario where we have a
query running for very long time on that instance. So in that case will
there be chances of instance restart and if that can be avoided anyway?

And the plan is to set these system parameters with different values in
writer/read replica , so in that case if we apply the "alter system"
command on the primary , won't the WAL going to apply those same commands
forcibly on reader instance making those same as the writer instance
configuration( but we want the reader replica configuration to be different
from writer)?

Appreciate your guidance.

On Wed, May 29, 2024 at 1:38=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Wed, 2024-05-29 at 01:34 +0530, yudhi s wrote:
> > > The only way you can have no delay in replication AND no canceled
> queries is
> > > if you use two different standby servers with different settings for
> > > "max_standby_streaming_delay".  One of the server is for HA, the othe=
r
> for
> > > your long-running queries.
> >
> > When you suggest having different max_standby_streaming_delay for first
> replica
> > (say 10 sec for High availability) and second replica(say -1 for long
> running queries).
> > Do you also suggest  keeping "hot_feedback_standby" as "OFF" for all th=
e
> three
> > instances i.e. master and both the replicas?
>
> The parameter is ignored on the master.
> It needs to be off on the standby that is running long queries.
> For the other standby it probably doesn't matter if you are not running a=
ny
> queries on it.  I would leave "hot_standby_feedback =3D off" there as wel=
l.
>
> Actually, I would set "hot_standby =3D off" on the standby that is only u=
sed
> for HA.
>
>
> - I would leave "hot_standby_feedback" off everywhere.
> - "max_standby_streaming_delay" should be -1 on the reporting standby and
> very
>   low or 0 on the HA standby. It doesn't matter on the primary.
> - "statement_timeout" should be way lower on the first two nodes.
> - "idle_in_transaction_session_timeout" is good.
> - I would leave "autovacuum_freeze_max_age" at the default setting but 10=
0
> million
>   is ok too.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr"><div>Hello Laurenz,</div><div><br></div><div>Thank you so =
much.This information was really helpful=C2=A0for us understanding=C2=A0the=
 working of these parameters.</div><div><br></div><div>One follow up questi=
on i have , as we are setting one of the standby/replica with value idle_in=
_transaction_session_timeout=3D-1 which can cause=C2=A0the WAL&#39;s to be =
heavily backlogged in a scenario=C2=A0where we have a query running for ver=
y long time on that instance. So in that case will there be chances of inst=
ance restart and if that can be avoided anyway?</div><div><br></div><div>An=
d the plan is to set these system=C2=A0parameters=C2=A0with different value=
s in writer/read replica , so in that case if we apply the &quot;alter syst=
em&quot; command on the primary , won&#39;t the WAL=C2=A0going to apply tho=
se same commands forcibly on reader instance making those same as the write=
r instance configuration( but we want the=C2=A0reader replica configuration=
 to be different from writer)?=C2=A0</div><div><br></div><div>Appreciate=C2=
=A0your guidance.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" clas=
s=3D"gmail_attr">On Wed, May 29, 2024 at 1:38=E2=80=AFPM Laurenz Albe &lt;<=
a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</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">On Wed, =
2024-05-29 at 01:34 +0530, yudhi s wrote:<br>
&gt; &gt; The only way you can have no delay in replication AND no canceled=
 queries is<br>
&gt; &gt; if you use two different standby servers with different settings =
for<br>
&gt; &gt; &quot;max_standby_streaming_delay&quot;.=C2=A0 One of the server =
is for HA, the other for<br>
&gt; &gt; your long-running queries.<br>
&gt;<br>
&gt; When you suggest having different max_standby_streaming_delay for firs=
t replica<br>
&gt; (say 10 sec for High availability) and second replica(say -1 for long =
running queries).<br>
&gt; Do you also suggest=C2=A0 keeping &quot;hot_feedback_standby&quot; as =
&quot;OFF&quot; for all the three<br>
&gt; instances i.e. master and both the replicas?<br>
<br>
The parameter is ignored on the master.<br>
It needs to be off on the standby that is running long queries.<br>
For the other standby it probably doesn&#39;t matter if you are not running=
 any<br>
queries on it.=C2=A0 I would leave &quot;hot_standby_feedback =3D off&quot;=
 there as well.<br>
<br>
Actually, I would set &quot;hot_standby =3D off&quot; on the standby that i=
s only used<br>
for HA.<br>
<br><br>
- I would leave &quot;hot_standby_feedback&quot; off everywhere.<br>
- &quot;max_standby_streaming_delay&quot; should be -1 on the reporting sta=
ndby and very<br>
=C2=A0 low or 0 on the HA standby. It doesn&#39;t matter on the primary.<br=
>
- &quot;statement_timeout&quot; should be way lower on the first two nodes.=
<br>
- &quot;idle_in_transaction_session_timeout&quot; is good.<br>
- I would leave &quot;autovacuum_freeze_max_age&quot; at the default settin=
g but 100 million<br>
=C2=A0 is ok too.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div></div>

--000000000000dcbfcb061a1ea298--