On 9/11/26 22:51, Andres Freund wrote:
Maybe I am just missing something, but is this ever a benefit outside of
completely arbitrary scenarios? Who has >= 8k concurrent connections doing
nothing but connecting/disconnecting? Even if one were to agree that it's
useful to optimize large huge_pages=off workloads, surely that's an absurd
enough workload that nobody cares?
Hi,
as Matthias noted, ProcArrayAdd/ProcArrayRemove are used not only for
connect
but also for a two-phase commit. Therefore, it could be said, that we
optimize
the execution time of the `ProcArrayAdd` and `ProcArrayRemove` methods
for specific
configurations without huge_pages specifically for cases with great
utilization of
these methods. The scenario with connections is just the way to
reproduce this high
utilization. It seems that a real, significant effect from the patch in
production
systems can be most visible during moments of anomalous, sudden spikes
in load; but
anyway, the patch prove and eliminates a dependency of page faults and
make system more
stable.
On 9/18/26 04:54, shihao zhong wrote:
The real problem there is locks and long transactions. This patch
might help
the connection storm, but may not fix it.
I agree, this patch cannot fix the "storm" issue, as such storms are
typically a consequence
of underlying infrastructure problems and should be addressed at the
infrastructure level.
Nevertheless, the patch helps to mitigate these effects and achieve more
stable behavior.
On 9/11/26 22:51, Andres Freund wrote:
This isn't entirely free, needing to look at a separate cachelines that are
frequently modified (and thus commonly won't be in the cpu-local cache), in
reasonably common codepaths like ProcArrayEndTransactionInternal(),
TransactionIdIsInProgress(), GetSnapshotData() is far from free.
Thanks for this point, here [1] I've done some experiments for huge_page=on
scenarios. It is interesting because from one side here we didn't detect any
page_faults, so we exclude patch influence onĀ base problem, and from the
other side, we didn't see any degradations, so we didn't see any side effect
of potential false sharing. Also I see that ProcGlobal->xids and
ProcGlobal->subxidStates are optimized by the same way, so suggested
approach
looks code consistent.
P.S. I agree that patch looks controversial. My motivation was
that even if some configuration isn't commonly used, it does not mean
that it isn't interesting for someone else at all and, as a consequence,
it can be optimized without degradation for basic scenarios.
Patch is rebased to SHA 5ffc8210377 and re-attached.
Best regards,
Maksim Melnikov
Links:
[1]
https://www.postgresql.org/message-id/08e7c338-e175-4474-be53-6538bb67d4b6%40postgrespro.ru
From d743d84247605424bf1e346e599e3bcbdde56c18 Mon Sep 17 00:00:00 2001
From: Maksim Melnikov <[email protected]>
Date: Tue, 18 Nov 2025 17:20:09 +0300
Subject: [PATCH v7] This patch reduce connection init/close time.
ProcArrayRemove/ProcArrayAdd are expensive in terms of accessing pgxactoff
field in PGPROC, because its are placed on different pages and it the
reason of page_faults occurence. Now one of the use case with pgxactoff
is iteration with gaps over PGPROCs and read/write pgxactoff, PGPROC
allocate ~1KB and it quite enough to have page_faults in case of such
accessing.
So we placed all pgxactoff in the separate array pgxactoffs to have adjacent
pages for them all. Indexes in allProcs aligned with pgxactoffs array, so
the Nth element in pgxactoffs refer to Nth PGPROC.
Eventually it helps to avoid extra page_faults and reduce connection
init/close time. The main benefit is seen on configurations without huge
pages.
---
src/backend/access/transam/varsup.c | 10 ++---
src/backend/commands/indexcmds.c | 2 +-
src/backend/commands/vacuum.c | 2 +-
src/backend/replication/logical/logical.c | 2 +-
src/backend/replication/slot.c | 2 +-
src/backend/replication/walsender.c | 2 +-
src/backend/storage/ipc/procarray.c | 55 ++++++++++++++---------
src/backend/storage/lmgr/proc.c | 6 ++-
src/include/storage/proc.h | 23 ++++++----
9 files changed, 63 insertions(+), 41 deletions(-)
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 912f7cb1d2c..2a632be0014 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -85,7 +85,7 @@ GetNewTransactionId(bool isSubXact)
{
Assert(!isSubXact);
MyProc->xid = BootstrapTransactionId;
- ProcGlobal->xids[MyProc->pgxactoff] = BootstrapTransactionId;
+ ProcGlobal->xids[ProcGetMyXactOff()] = BootstrapTransactionId;
return FullTransactionIdFromEpochAndXid(0, BootstrapTransactionId);
}
@@ -244,18 +244,18 @@ GetNewTransactionId(bool isSubXact)
*/
if (!isSubXact)
{
- Assert(ProcGlobal->subxidStates[MyProc->pgxactoff].count == 0);
- Assert(!ProcGlobal->subxidStates[MyProc->pgxactoff].overflowed);
+ Assert(ProcGlobal->subxidStates[ProcGetMyXactOff()].count == 0);
+ Assert(!ProcGlobal->subxidStates[ProcGetMyXactOff()].overflowed);
Assert(MyProc->subxidStatus.count == 0);
Assert(!MyProc->subxidStatus.overflowed);
/* LWLockRelease acts as barrier */
MyProc->xid = xid;
- ProcGlobal->xids[MyProc->pgxactoff] = xid;
+ ProcGlobal->xids[ProcGetMyXactOff()] = xid;
}
else
{
- XidCacheStatus *substat = &ProcGlobal->subxidStates[MyProc->pgxactoff];
+ XidCacheStatus *substat = &ProcGlobal->subxidStates[ProcGetMyXactOff()];
int nxids = MyProc->subxidStatus.count;
Assert(substat->count == MyProc->subxidStatus.count);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 68fc87b76a0..933346ec319 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -4859,6 +4859,6 @@ set_indexsafe_procflags(void)
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc->statusFlags |= PROC_IN_SAFE_IC;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
LWLockRelease(ProcArrayLock);
}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 63f6b8a733a..2be95bdae56 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -2104,7 +2104,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params,
MyProc->statusFlags |= PROC_IN_VACUUM;
if (params.is_wraparound)
MyProc->statusFlags |= PROC_VACUUM_FOR_WRAPAROUND;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
LWLockRelease(ProcArrayLock);
}
diff --git a/src/backend/replication/logical/logical.c b/src/backend/replication/logical/logical.c
index 8fe255fa960..8be0291f7a4 100644
--- a/src/backend/replication/logical/logical.c
+++ b/src/backend/replication/logical/logical.c
@@ -300,7 +300,7 @@ StartupDecodingContext(List *output_plugin_options,
{
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc->statusFlags |= PROC_IN_LOGICAL_DECODING;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
LWLockRelease(ProcArrayLock);
}
diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c
index a311aee85a9..e06e111b308 100644
--- a/src/backend/replication/slot.c
+++ b/src/backend/replication/slot.c
@@ -841,7 +841,7 @@ ReplicationSlotRelease(void)
{
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc->statusFlags &= ~PROC_IN_LOGICAL_DECODING;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
LWLockRelease(ProcArrayLock);
}
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index d0e335629e7..95346cfd22b 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -358,7 +358,7 @@ InitWalSender(void)
Assert(MyProc->xmin == InvalidTransactionId);
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc->statusFlags |= PROC_AFFECTS_ALL_HORIZONS;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
LWLockRelease(ProcArrayLock);
}
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 230663d1352..65369f1ab82 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -284,6 +284,13 @@ typedef enum KAXCompressReason
static PGPROC *allProcs;
+/*
+ * offsets into various ProcGlobal->arrays with data mirrored from
+ * appropriate PGPROCs, procno define offset index in pgxactoffs array(See
+ * PROC_HDR for details).
+ */
+static int *pgxactoffs;
+
/*
* Cache to reduce overhead of repeated calls to TransactionIdIsInProgress()
*/
@@ -449,12 +456,14 @@ ProcArrayShmemInit(void *arg)
TransamVariables->xactCompletionCount = 1;
allProcs = ProcGlobal->allProcs;
+ pgxactoffs = ProcGlobal->pgxactoffs;
}
static void
ProcArrayShmemAttach(void *arg)
{
allProcs = ProcGlobal->allProcs;
+ pgxactoffs = ProcGlobal->pgxactoffs;
}
/*
@@ -498,7 +507,7 @@ ProcArrayAdd(PGPROC *proc)
int this_procno = arrayP->pgprocnos[index];
Assert(this_procno >= 0 && this_procno < (arrayP->maxProcs + NUM_AUXILIARY_PROCS));
- Assert(allProcs[this_procno].pgxactoff == index);
+ Assert(pgxactoffs[this_procno] == index);
/* If we have found our right position in the array, break */
if (this_procno > pgprocno)
@@ -520,23 +529,23 @@ ProcArrayAdd(PGPROC *proc)
movecount * sizeof(*ProcGlobal->statusFlags));
arrayP->pgprocnos[index] = pgprocno;
- proc->pgxactoff = index;
+ pgxactoffs[arrayP->pgprocnos[index]] = index;
ProcGlobal->xids[index] = proc->xid;
ProcGlobal->subxidStates[index] = proc->subxidStatus;
ProcGlobal->statusFlags[index] = proc->statusFlags;
arrayP->numProcs++;
- /* adjust pgxactoff for all following PGPROCs */
+ /* adjust appropriate pgxactoff for all following PGPROCs */
index++;
for (; index < arrayP->numProcs; index++)
{
int procno = arrayP->pgprocnos[index];
Assert(procno >= 0 && procno < (arrayP->maxProcs + NUM_AUXILIARY_PROCS));
- Assert(allProcs[procno].pgxactoff == index - 1);
+ Assert(pgxactoffs[procno] == index - 1);
- allProcs[procno].pgxactoff = index;
+ pgxactoffs[procno] = index;
}
/*
@@ -574,10 +583,10 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
- myoff = proc->pgxactoff;
+ myoff = ProcGetXactOff(GetNumberFromPGProc(proc));
Assert(myoff >= 0 && myoff < arrayP->numProcs);
- Assert(ProcGlobal->allProcs[arrayP->pgprocnos[myoff]].pgxactoff == myoff);
+ Assert(ProcGlobal->pgxactoffs[arrayP->pgprocnos[myoff]] == myoff);
if (TransactionIdIsValid(latestXid))
{
@@ -624,17 +633,17 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
arrayP->numProcs--;
/*
- * Adjust pgxactoff of following procs for removed PGPROC (note that
- * numProcs already has been decremented).
+ * Adjust appropriate pgxactoff of following procs for removed PGPROC
+ * (note that numProcs already has been decremented).
*/
for (int index = myoff; index < arrayP->numProcs; index++)
{
int procno = arrayP->pgprocnos[index];
Assert(procno >= 0 && procno < (arrayP->maxProcs + NUM_AUXILIARY_PROCS));
- Assert(allProcs[procno].pgxactoff - 1 == index);
+ Assert(pgxactoffs[procno] - 1 == index);
- allProcs[procno].pgxactoff = index;
+ pgxactoffs[procno] = index;
}
/*
@@ -706,11 +715,13 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
/* avoid unnecessarily dirtying shared cachelines */
if (proc->statusFlags & PROC_VACUUM_STATE_MASK)
{
+ int pgxactoff = ProcGetXactOff(GetNumberFromPGProc(proc));
+
Assert(!LWLockHeldByMe(ProcArrayLock));
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
- Assert(proc->statusFlags == ProcGlobal->statusFlags[proc->pgxactoff]);
+ Assert(proc->statusFlags == ProcGlobal->statusFlags[pgxactoff]);
proc->statusFlags &= ~PROC_VACUUM_STATE_MASK;
- ProcGlobal->statusFlags[proc->pgxactoff] = proc->statusFlags;
+ ProcGlobal->statusFlags[pgxactoff] = proc->statusFlags;
LWLockRelease(ProcArrayLock);
}
}
@@ -724,7 +735,7 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
static inline void
ProcArrayEndTransactionInternal(PGPROC *proc, TransactionId latestXid)
{
- int pgxactoff = proc->pgxactoff;
+ int pgxactoff = ProcGetXactOff(GetNumberFromPGProc(proc));
/*
* Note: we need exclusive lock here because we're going to change other
@@ -747,7 +758,7 @@ ProcArrayEndTransactionInternal(PGPROC *proc, TransactionId latestXid)
if (proc->statusFlags & PROC_VACUUM_STATE_MASK)
{
proc->statusFlags &= ~PROC_VACUUM_STATE_MASK;
- ProcGlobal->statusFlags[proc->pgxactoff] = proc->statusFlags;
+ ProcGlobal->statusFlags[pgxactoff] = proc->statusFlags;
}
/* Clear the subtransaction-XID cache too while holding the lock */
@@ -903,7 +914,7 @@ ProcArrayClearTransaction(PGPROC *proc)
/*
* Currently we need to lock ProcArrayLock exclusively here, as we
* increment xactCompletionCount below. We also need it at least in shared
- * mode for pgproc->pgxactoff to stay the same below.
+ * mode for proc's pgxactoff to stay the same below.
*
* We could however, as this action does not actually change anyone's view
* of the set of running XIDs (our entry is duplicate with the gxact that
@@ -916,7 +927,7 @@ ProcArrayClearTransaction(PGPROC *proc)
*/
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
- pgxactoff = proc->pgxactoff;
+ pgxactoff = ProcGetXactOff(GetNumberFromPGProc(proc));
ProcGlobal->xids[pgxactoff] = InvalidTransactionId;
proc->xid = InvalidTransactionId;
@@ -1475,7 +1486,7 @@ TransactionIdIsInProgress(TransactionId xid)
}
/* No shortcuts, gotta grovel through the array */
- mypgxactoff = MyProc->pgxactoff;
+ mypgxactoff = ProcGetMyXactOff();
numProcs = arrayP->numProcs;
for (int pgxactoff = 0; pgxactoff < numProcs; pgxactoff++)
{
@@ -2184,7 +2195,7 @@ GetSnapshotData(Snapshot snapshot)
}
latest_completed = TransamVariables->latestCompletedXid;
- mypgxactoff = MyProc->pgxactoff;
+ mypgxactoff = ProcGetMyXactOff();
myxid = other_xids[mypgxactoff];
Assert(myxid == MyProc->xid);
@@ -2223,7 +2234,7 @@ GetSnapshotData(Snapshot snapshot)
TransactionId xid = UINT32_ACCESS_ONCE(other_xids[pgxactoff]);
uint8 statusFlags;
- Assert(allProcs[arrayP->pgprocnos[pgxactoff]].pgxactoff == pgxactoff);
+ Assert(pgxactoffs[arrayP->pgprocnos[pgxactoff]] == pgxactoff);
/*
* If the transaction has no XID assigned, we can skip it; it
@@ -2596,7 +2607,7 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc)
MyProc->xmin = TransactionXmin = xmin;
MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) |
(proc->statusFlags & PROC_XMIN_FLAGS);
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
+ ProcGlobal->statusFlags[ProcGetMyXactOff()] = MyProc->statusFlags;
result = true;
}
@@ -4041,7 +4052,7 @@ XidCacheRemoveRunningXids(TransactionId xid,
*/
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
- mysubxidstat = &ProcGlobal->subxidStates[MyProc->pgxactoff];
+ mysubxidstat = &ProcGlobal->subxidStates[ProcGetMyXactOff()];
/*
* Under normal circumstances xid and xids[] will be in increasing order,
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index a168bac3bc9..e9d4a3a86ac 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -163,6 +163,7 @@ ProcGlobalShmemRequest(void *arg)
size = add_size(size, mul_size(TotalProcs, sizeof(PGPROC)));
size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->xids)));
size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->subxidStates)));
+ size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->pgxactoffs)));
size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->statusFlags)));
ProcGlobalAllProcsShmemSize = size;
ShmemRequestStruct(.name = "PGPROC structures",
@@ -267,6 +268,9 @@ ProcGlobalShmemInit(void *arg)
ProcGlobal->xids = (TransactionId *) ptr;
ptr = ptr + (TotalProcs * sizeof(*ProcGlobal->xids));
+ ProcGlobal->pgxactoffs = (int *) ptr;
+ ptr = ptr + (TotalProcs * sizeof(*ProcGlobal->pgxactoffs));
+
ProcGlobal->subxidStates = (XidCacheStatus *) ptr;
ptr = ptr + (TotalProcs * sizeof(*ProcGlobal->subxidStates));
@@ -1552,7 +1556,7 @@ ProcSleep(LOCALLOCK *locallock)
* the lock held, which is much more undesirable.
*/
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
- statusFlags = ProcGlobal->statusFlags[autovac->pgxactoff];
+ statusFlags = ProcGlobal->statusFlags[ProcGetXactOff(GetNumberFromPGProc(autovac))];
lockmethod_copy = lock->tag.locktag_lockmethodid;
locktag_copy = lock->tag;
LWLockRelease(ProcArrayLock);
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index abe40001d9a..ff0fd39ba1f 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -175,7 +175,8 @@ typedef enum
*
* Some fields in PGPROC (see "mirrored in ..." comment) are mirrored into an
* element of more densely packed ProcGlobal arrays. These arrays are indexed
- * by PGPROC->pgxactoff. Both copies need to be maintained coherently.
+ * by a PGPROC's pgxactoffs entry. Both copies need to be maintained
+ * coherently.
*
* NB: The pgxactoff indexed value can *never* be accessed without holding
* locks.
@@ -211,9 +212,6 @@ typedef struct PGPROC
Oid tempNamespaceId; /* OID of temp schema this backend is
* using */
- int pgxactoff; /* offset into various ProcGlobal->arrays with
- * data mirrored from this PGPROC */
-
uint8 statusFlags; /* this backend's status flags, see PROC_*
* above. mirrored in
* ProcGlobal->statusFlags[pgxactoff] */
@@ -403,10 +401,11 @@ extern PGDLLIMPORT PGPROC *MyProc;
* for PGPROCs that have been added to the shared array with ProcArrayAdd()
* (in contrast to PGPROC array which has unused PGPROCs interspersed).
*
- * The dense arrays are indexed by PGPROC->pgxactoff. Any concurrent
- * ProcArrayAdd() / ProcArrayRemove() can lead to pgxactoff of a procarray
- * member to change. Therefore it is only safe to use PGPROC->pgxactoff to
- * access the dense array while holding either ProcArrayLock or XidGenLock.
+ * The dense arrays are indexed by the PGPROC's corresponding offset in
+ * pgxactoff. Any concurrent ProcArrayAdd() / ProcArrayRemove() can cause
+ * the pgxactoff of a procarray member to change. Therefore it is only safe
+ * to use a proc's pgxactoff to access the dense array while holding either
+ * ProcArrayLock or XidGenLock.
*
* As long as a PGPROC is in the procarray, the mirrored values need to be
* maintained in both places in a coherent manner.
@@ -447,6 +446,12 @@ typedef struct PROC_HDR
/* Array of PGPROC structures (not including dummies for prepared txns) */
PGPROC *allProcs;
+ /*
+ * Dense offsets into various ProcGlobal->arrays with data mirrored from
+ * appropriate PGPROCs. Like allProcs, values are indexed by ProcNumber.
+ */
+ int *pgxactoffs;
+
/* Array mirroring PGPROC.xid for each PGPROC currently in the procarray */
TransactionId *xids;
@@ -512,6 +517,8 @@ extern PGDLLIMPORT PGPROC *PreparedXactProcs;
*/
#define GetPGProcByNumber(n) (&ProcGlobal->allProcs[(n)])
#define GetNumberFromPGProc(proc) ((proc) - &ProcGlobal->allProcs[0])
+#define ProcGetXactOff(procno) (ProcGlobal->pgxactoffs[(procno)])
+#define ProcGetMyXactOff() (ProcGetXactOff(MyProcNumber))
/*
* We set aside some extra PGPROC structures for "special worker" processes,
--
2.43.0