Re: Long running query causing XID limit breach - Mailing list pgsql-general
| From | sud |
|---|---|
| Subject | Re: Long running query causing XID limit breach |
| Date | |
| Msg-id | CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com Whole thread Raw |
| In response to | Re: Long running query causing XID limit breach (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Responses |
Re: Long running query causing XID limit breach
|
| List | pgsql-general |
On Thu, May 23, 2024 at 8:11 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-05-23 at 18:15 +0530, sud wrote:
> On Thu, May 23, 2024 at 1:45 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > If a long running query on the standby influences the primary, that means that
> > you have "hot_standby_feedback" set to "on". Set it to "off".
>
> Will the setting up of "hot_standby_feedback" value to OFF will cause the
> reader instance to give incorrect query results or unexpected query failure
> which will be potential inconsistency between the writer and reader instance,
> as because those XID's can be removed/cleaned by the writer node even if its
> being read by the reader instance query. And it can have more replication lag.
There will never be incorrect query results.
It can happen that a query on the standby gets canceled if you don't set
"max_standby_streaming_delay" to -1, but that can happen even if
"hot_standby_feedback" is "on". It just happens less often.
The effect of setting "max_standby_streaming_delay" to -1 will often be a
replication delay if you run a long query. That's what you have to
accept if you want to execute long-running queries.
You will never be able to have both of the following:
- queries never get canceled
- there is no replication delay
I am trying to understand these two parameters and each time it looks a bit confusing to me. If These two parameters complement or conflict with each other.
Say for example, If we set hot_feedback_standby to ON (which is currently set as default ON by the way), it will make the primary wait till the query completion at standby and can cause such a high bump in XID in scenarios where the query on standby runs for days(like in our current scenario which happens). So we were thinking of setting it as OFF, to avoid the transaction ID wrap around issue..
But as you also mentioned to set the "max_standby_streaming_delay" to -1 (which is currently set as 14 second in our case) ,it will wait infinitely , till the query completes on the standby and wont apply the WAL which can cause override of the XID which the standby query is reading from. But wont this same behaviour be happening while we have hot_feedback_standby set as "ON"?
But again for HA , in case primary down we should not be in big lag for the standby and thus we want the standby also with minimal lag. And as you mentioned there will never be incorrect results but at amx it will be query cancellation, so I was thinking , if it's fine to just keep the "hot_feedback_standby" as OFF and let the max_standby_streaming_delay set as it is like 14 sec. Let me know your thoughts.
Basically below are the combinations, i am confused between..
hot_feedback_stanby ON and max_standby_streaming_delay=-1
or
hot_feedback_stanby OFF and max_standby_streaming_delay=-1
Or
hot_feedback_stanby ON and max_standby_streaming_delay=14 sec
Or
hot_feedback_stanby OFF and max_standby_streaming_delay=14 sec
pgsql-general by date: