public inbox for [email protected]  

help / color / mirror / Atom feed

From: shveta malik <[email protected]>
To: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: shveta malik <[email protected]>
Subject: Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0
Date: Mon, 27 Apr 2026 15:45:51 +0530
Message-ID: <CAJpy0uCxN3t==fHuB6oPsUqJ-MHk7OTK7uJETyjSa90noe4A9w@mail.gmail.com> (raw)
In-Reply-To: <CAJpy0uDGM8eVqMORHKw61mm24AjH7jPZAhUaN3YeJrZ1bdpQFQ@mail.gmail.com>
References: <CAHg+QDdKVnCLHot=AcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ@mail.gmail.com>
	<CAJpy0uDGM8eVqMORHKw61mm24AjH7jPZAhUaN3YeJrZ1bdpQFQ@mail.gmail.com>

On Mon, Apr 27, 2026 at 3:18 PM shveta malik <[email protected]> wrote:
>
> On Mon, Apr 27, 2026 at 2:11 PM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> >
> > Hi Hackers,
> >
> > When a subscription has retain_dead_tuples enabled with maxretention set
> > to zero (unlimited retention), adjust_xid_advance_interval() caps
> > xid_advance_interval to Min(interval, maxretention).  Since maxretention
> > is zero, this always collapses the interval to zero milliseconds.
> >
> > A zero makes  TimestampDifferenceExceeds(last_time, now, 0)  always
> > true in get_candidate_xid(). This causes the apply worker to call
> > GetOldestActiveTransactionId() on every single WAL message. This results in
> > a huge number of ProcArrayLock acquisitions under moderate write load.
> >

 I agree with the problem statement. I can see it in my debugging.

> > Fix by adding a maxretention > 0 guard to the cap. When maxretention is zero ,
> > the exponential back-off in adjust_xid_advance_interval()
> > now works correctly, growing the interval from 100 ms toward the 180 s
> > ceiling.

Yes, this should work. Let's see what others have to say on this.

> > Measured with perf uprobe counting GetOldestActiveTransactionId calls
> > at ~39K TPS (pgbench, 5 clients):
> >
> >   Before fix: 25,104 calls / 5 s  (~5,021/s)
> >   After fix:     31 calls / 5 s  (~6/s)
> >

Just curious, how did you catch this problem? Did it show up in any of
your profiling reports?

thanks
Shveta

view thread (7+ 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], [email protected]
  Subject: Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0
  In-Reply-To: <CAJpy0uCxN3t==fHuB6oPsUqJ-MHk7OTK7uJETyjSa90noe4A9w@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