public inbox for [email protected]help / color / mirror / Atom feed
[Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 7+ messages / 4 participants [nested] [flat]
* [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 @ 2026-04-27 08:41 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: SATYANARAYANA NARLAPURAM @ 2026-04-27 08:41 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]> 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. 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. 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) Thank Satya Attachments: [application/octet-stream] 0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch (1.9K, 3-0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch) download | inline diff: From dfa89aba06742428fe56a263a9236b862b14e34b Mon Sep 17 00:00:00 2001 From: Satya Narlapuram <[email protected]> Date: Mon, 27 Apr 2026 08:17:42 +0000 Subject: [PATCH] Fix apply worker busy loop when subscription max_retention_duration is zero 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 internal 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. 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. --- src/backend/replication/logical/worker.c | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 9f2a16b1..dd6fc38a 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -4997,9 +4997,10 @@ adjust_xid_advance_interval(RetainDeadTuplesData *rdt_data, bool new_xid_found) /* * Ensure the wait time remains within the maximum retention time limit - * when retention is active. + * when retention is active. Skip this cap when maxretention is zero. */ - if (MySubscription->retentionactive) + if (MySubscription->retentionactive && MySubscription->maxretention > 0) rdt_data->xid_advance_interval = Min(rdt_data->xid_advance_interval, MySubscription->maxretention); } -- 2.43.0 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> @ 2026-04-27 09:48 ` shveta malik <[email protected]> 2026-04-27 10:15 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 2026-04-27 17:02 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: shveta malik @ 2026-04-27 09:48 UTC (permalink / raw) To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]>; shveta malik <[email protected]> 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. > > 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. > > 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) > Thanks for reporting it. I am reveiwing the problem sattement. Meanwhile can you please look at it, I am getting the following error while applying the patch on my Ubuntu setup (git am): error: corrupt patch at line 22 thanks Shveta ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> @ 2026-04-27 10:15 ` shveta malik <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: shveta malik @ 2026-04-27 10:15 UTC (permalink / raw) To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]>; Zhijie Hou (Fujitsu) <[email protected]>; shveta malik <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> @ 2026-04-27 17:02 ` SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-28 04:08 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 2026-04-28 08:33 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 Nisha Moond <[email protected]> 1 sibling, 2 replies; 7+ messages in thread From: SATYANARAYANA NARLAPURAM @ 2026-04-27 17:02 UTC (permalink / raw) To: shveta malik <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]> Hi, On Mon, Apr 27, 2026 at 2:48 AM 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. > > > > 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. > > > > 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) > > > > Thanks for reporting it. I am reveiwing the problem sattement. > Meanwhile can you please look at it, I am getting the following error > while applying the patch on my Ubuntu setup (git am): > > error: corrupt patch at line 22 > Thanks! Please find the updated v2 patch. Attachments: [application/octet-stream] v2-0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch (1.9K, 3-v2-0001-Fix-apply-worker-busy-loop-when-subscription-max_ret.patch) download | inline diff: From 18c4738ab100ea29966cedcabcd1de1645ff3578 Mon Sep 17 00:00:00 2001 From: Satya Narlapuram <[email protected]> Date: Mon, 27 Apr 2026 16:53:37 +0000 Subject: [PATCH] Fix apply worker busy loop when subscription max_retention_duration is zero 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 internal 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. 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. --- src/backend/replication/logical/worker.c | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 9f2a16b1..dd6fc38a 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -4997,9 +4997,10 @@ adjust_xid_advance_interval(RetainDeadTuplesData *rdt_data, bool new_xid_found) /* * Ensure the wait time remains within the maximum retention time limit - * when retention is active. + * when retention is active. Skip this cap when maxretention is zero, + * which means unlimited retention (no timeout). */ - if (MySubscription->retentionactive) + if (MySubscription->retentionactive && MySubscription->maxretention > 0) rdt_data->xid_advance_interval = Min(rdt_data->xid_advance_interval, MySubscription->maxretention); } -- 2.43.0 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 2026-04-27 17:02 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> @ 2026-04-28 04:08 ` shveta malik <[email protected]> 2026-04-28 10:58 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 Amit Kapila <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: shveta malik @ 2026-04-28 04:08 UTC (permalink / raw) To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]>; shveta malik <[email protected]> On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM <[email protected]> wrote: > > Hi, > > On Mon, Apr 27, 2026 at 2:48 AM 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. >> > >> > 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. >> > >> > 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) >> > >> >> Thanks for reporting it. I am reveiwing the problem sattement. >> Meanwhile can you please look at it, I am getting the following error >> while applying the patch on my Ubuntu setup (git am): >> >> error: corrupt patch at line 22 > > > Thanks! Please find the updated v2 patch. Thanks. The patch looks good. thanks Shveta ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 2026-04-27 17:02 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-28 04:08 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> @ 2026-04-28 10:58 ` Amit Kapila <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Amit Kapila @ 2026-04-28 10:58 UTC (permalink / raw) To: shveta malik <[email protected]>; +Cc: SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Apr 28, 2026 at 9:38 AM shveta malik <[email protected]> wrote: > > On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM > <[email protected]> wrote: > > > > Hi, > > > > On Mon, Apr 27, 2026 at 2:48 AM 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. > >> > > >> > 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. > >> > > >> > 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) > >> > > >> > >> Thanks for reporting it. I am reveiwing the problem sattement. > >> Meanwhile can you please look at it, I am getting the following error > >> while applying the patch on my Ubuntu setup (git am): > >> > >> error: corrupt patch at line 22 > > > > > > Thanks! Please find the updated v2 patch. > > Thanks. The patch looks good. > LGTM as well, so pushed. -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 shveta malik <[email protected]> 2026-04-27 17:02 ` Re: [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> @ 2026-04-28 08:33 ` Nisha Moond <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Nisha Moond @ 2026-04-28 08:33 UTC (permalink / raw) To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: shveta malik <[email protected]>; PostgreSQL Hackers <[email protected]>; Amit Kapila <[email protected]> On Mon, Apr 27, 2026 at 10:32 PM SATYANARAYANA NARLAPURAM <[email protected]> wrote: > > Hi, > > On Mon, Apr 27, 2026 at 2:48 AM 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. >> > >> > 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. >> > >> > 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) >> > >> >> Thanks for reporting it. I am reveiwing the problem sattement. >> Meanwhile can you please look at it, I am getting the following error >> while applying the patch on my Ubuntu setup (git am): >> >> error: corrupt patch at line 22 > > > Thanks! Please find the updated v2 patch. Thanks for the patch. I am able to reproduce the reported issue in debugging. The xid_advance_interval stays 0 when retain_dead_tuples is enabled but max_retention_duration is off which is unexpected behavior. Confirmed that the patch fixed it. -- Thanks, Nisha ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-04-28 10:58 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-04-27 08:41 [Patch]: Fix excessive ProcArrayLock acquisitions with subscription max_retention_duration=0 SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-27 09:48 ` shveta malik <[email protected]> 2026-04-27 10:15 ` shveta malik <[email protected]> 2026-04-27 17:02 ` SATYANARAYANA NARLAPURAM <[email protected]> 2026-04-28 04:08 ` shveta malik <[email protected]> 2026-04-28 10:58 ` Amit Kapila <[email protected]> 2026-04-28 08:33 ` Nisha Moond <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox