Re: Long running query causing XID limit breach
| От | yudhi s |
|---|---|
| Тема | Re: Long running query causing XID limit breach |
| Дата | |
| Msg-id | CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Long running query causing XID limit breach (sud <suds1434@gmail.com>) |
| Ответы |
Re: Long running query causing XID limit breach
Re: Long running query causing XID limit breach |
| Список | pgsql-general |
On Fri, May 24, 2024 at 10:34 AM sud <suds1434@gmail.com> wrote:
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=-1orhot_feedback_stanby OFF and max_standby_streaming_delay=-1Orhot_feedback_stanby ON and max_standby_streaming_delay=14 secOrhot_feedback_stanby OFF and max_standby_streaming_delay=14 sec
As per my understanding here, this would be the behaviour. Others may comment..
hot_standby_feedback ON and max_standby_streaming_delay = -1:
Ensures that long-running queries on the standby are not interrupted. The primary waits indefinitely to avoid vacuuming rows needed by standby queries.
But Can lead to significant replication lag and increased XID consumption on the primary, potentially causing transaction ID wraparound issues.
hot_standby_feedback OFF and max_standby_streaming_delay = -1:
Ensures long-running queries on the standby are not interrupted. No feedback is sent to the primary, reducing the risk of XID wraparound.
But The standby may fall significantly behind the primary, resulting in high replication lag.
hot_standby_feedback ON and max_standby_streaming_delay = 14 seconds:
The primary prevents vacuuming rows needed by standby queries, reducing query cancellations on the standby. The replication lag is limited to 14 seconds.
But Long-running queries on the standby that exceed 14 seconds may be canceled, and the primary can still experience increased XID consumption.
hot_standby_feedback OFF and max_standby_streaming_delay = 14 seconds:
Limits replication lag to 14 seconds and reduces XID consumption on the primary. Queries on the standby exceeding 14 seconds are canceled.
but Long-running queries on the standby are more likely to be canceled due to the lack of feedback to the primary.
В списке pgsql-general по дате отправления: