public inbox for [email protected]
help / color / mirror / Atom feedFrom: sud <[email protected]>
To: yudhi s <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Long running query causing XID limit breach
Date: Sun, 26 May 2024 02:29:48 +0530
Message-ID: <CAD=mzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
<[email protected]>
<CAD=mzVVvK8xk-9m8h3Xu27cGN7BW329HKYdO+0EMXfWvSD3AGA@mail.gmail.com>
<[email protected]>
<CAD=mzVVqR-mKUFHetsejFWSPQPbLjTVhCmBebJTFX5XmYp+nGg@mail.gmail.com>
<[email protected]>
<CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com>
<CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
On Sun, May 26, 2024 at 2:24 AM yudhi s <[email protected]> wrote:
>
>
> *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.
>
>
>
Thank you so much.
Does it mean that the last one we should go for i.e. (*hot_standby_feedback
OFF and max_standby_streaming_delay = 14 seconds), *as because high
availability is also a key requirement in any production environment, so
keeping 14 seconds lag is kind of okay and also at the same time
keeping hot_standby_feedback OFF will make sure the transaction id
wraparound around won't happen because of any long running query on standby
as it won't wait for the stand by feedback for vacuuming the tables.
But i have one question here , does max_standby_streaming_delay = 14 ,
means the queries on the standby will get cancelled after 14 seconds?
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Long running query causing XID limit breach
In-Reply-To: <CAD=mzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox