public inbox for [email protected]
help / color / mirror / Atom feedFrom: yudhi s <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Long running query causing XID limit breach
Date: Wed, 29 May 2024 01:34:28 +0530
Message-ID: <CAEzWdqdPnErdeg6xe=zf7aF-fGy0Z42vXEm6zE6Ok25o=f6a7Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
On Mon, May 27, 2024 at 2:50 PM Laurenz Albe <[email protected]>
wrote:
>
> > > 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.
> >
> You cannot have it.
> Let me repeat: you cannot have it.
>
> 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 other 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 the three instances i.e. master and both the replicas?
Also OP has added a few other parameters as below, do you think these
should be needed?
I think the master and first replica should have the same set up because
in case of any disaster to master the first replica should be able to take
the place of master.
Master/Primary First Replica/Standby for High Availability Second Replica
for Reporting
hot_standby_feedback=ON hot_standby_feedback=ON hot_standby_feedback=OFF
max_standby_streaming_delay=10 sec max_standby_streaming_delay=10 sec
max_standby_streaming_delay=-1
(Infinite)
statement_timeout = "2hrs" statement_timeout="2hrs" No statement_timeout
i.e. infinite
idle_in_transaction_session_timeout=10minutes
idle_in_transaction_session_timeout=10minutes No
idle_in_transaction_session_timeout i.e. infinite
autovacuum_freeze_max_age=100M autovacuum_freeze_max_age=100M
autovacuum_freeze_max_age=100M
Log_autovacuum_min_duration=0 Log_autovacuum_min_duration=0
Log_autovacuum_min_duration=0
view thread (24+ messages)
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: <CAEzWdqdPnErdeg6xe=zf7aF-fGy0Z42vXEm6zE6Ok25o=f6a7Q@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