public inbox for [email protected]
help / color / mirror / Atom feedFrom: sud <[email protected]>
To: Torsten Förtsch <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Long running query causing XID limit breach
Date: Sun, 26 May 2024 14:45:50 +0530
Message-ID: <CAD=mzVUzDD_XDa+BJ8fH96pEwgTG1NHUvaoyVePLhp3xEN=J9A@mail.gmail.com> (raw)
In-Reply-To: <CAKkG4_nLNSDSDd7emC+p4rEicmeCBCBJozzy3QRFvO7BWh3SRA@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>
<CAD=mzVX1HuqQuMZx2QCy8ybJCG43zzxY4mqY4pM_gpxKscadBw@mail.gmail.com>
<CAKkG4_nLNSDSDd7emC+p4rEicmeCBCBJozzy3QRFvO7BWh3SRA@mail.gmail.com>
On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch <[email protected]>
wrote:
> On Sat, May 25, 2024 at 11:00 PM sud <[email protected]> wrote:
>
>>
>> But i have one question here , does max_standby_streaming_delay = 14 ,
>> means the queries on the standby will get cancelled after 14 seconds?
>>
>
> No, your query gets cancelled when it stalls replication for >14 sec. If
> your master is idle and does not send any WAL and the replica has
> caught up, the query can take as long as it wants.
>
Thank you so much.
For example , in below scenario,
if i have insert query going on on primary instance on table 25th may
partition of TABLE1, and at same time we are selecting data from 24th May
partition , then with "max_standby_streaming_delay = 14" setup , it just
allows the select query to run for any duration without any restriction
even if the WAL gets applied on the standby regularly. Also INSERT query in
primary won't make the standby SELECT queries to cancel as because the WAL
record of INSERT queries on the primary instance is not conflicting to the
exact rows those were being read by the standby. Is my understanding
correct here?
However, if i have Update/Delete query going on on primary instance on
table 25th may partition of TABLE1 and on the exact same set of rows which
were being read by the standby instance by the SELECT query, then the
application of such WAL record to standby can max wait for 14 seconds and
thus those select query are prone to be cancelled after 14 seconds. Is this
understanding correct?
If the above is true then it doesn't look good, as because in an OLTP
system there will be a lot of DMLS happening on the writer instances and
there may be many queries running on the reader/standby instances which are
meant to run for hours. And if we say making those SELECT queries run for
hours means compromising an hour of "high availability"/RPO or a lag of an
hour between primary and standby , that doesn't look good. Please
correct me if I am missing something here.
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]
Subject: Re: Long running query causing XID limit breach
In-Reply-To: <CAD=mzVUzDD_XDa+BJ8fH96pEwgTG1NHUvaoyVePLhp3xEN=J9A@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