public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Anastasia Lubennikova <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: Kyotaro Horiguchi <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: MultiXact\SLRU buffers configuration
Date: Wed, 28 Oct 2020 12:34:58 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <20201028013651.de5cj2xadgmba5nf@development>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfduekT=BrM3rLqvo4ajzKYmDk+6aCoYJrNHhK0Je+v2z=Q@mail.gmail.com>
<[email protected]>
<CAPpHfduT0Kb+8fMt4t0EF5G_3hig+wYgeRadQnNq20F4VxP7LA@mail.gmail.com>
<CAPpHfdsUtSGxRrKn65F_wrE66sNztNY4TpDGDHh2kvAZZDOMqA@mail.gmail.com>
<20201028013651.de5cj2xadgmba5nf@development>
Tomas, thanks for looking into this!
> 28 окт. 2020 г., в 06:36, Tomas Vondra <[email protected]> написал(а):
>
>
> This thread started with a discussion about making the SLRU sizes
> configurable, but this patch version only adds a local cache. Does this
> achieve the same goal, or would we still gain something by having GUCs
> for the SLRUs?
>
> If we're claiming this improves performance, it'd be good to have some
> workload demonstrating that and measurements. I don't see anything like
> that in this thread, so it's a bit hand-wavy. Can someone share details
> of such workload (even synthetic one) and some basic measurements?
All patches in this thread aim at the same goal: improve performance in presence of MultiXact locks contention.
I could not build synthetical reproduction of the problem, however I did some MultiXact stressing here [0]. It's a clumsy test program, because it still is not clear to me which parameters of workload trigger MultiXact locks contention. In generic case I was encountering other locks like *GenLock: XidGenLock, MultixactGenLock etc. Yet our production system encounters this problem approximately once in a month through this year.
Test program locks for share different set of tuples in presence of concurrent full scans.
To produce a set of locks we choose one of 14 bits. If a row number has this bit set to 0 we add lock it.
I've been measuring time to lock all rows 3 time for each of 14 bits, observing total time to set all locks.
During the test I was observing locks in pg_stat_activity, if they did not contain enough MultiXact locks I was tuning parameters further (number of concurrent clients, number of bits, select queries etc).
Why is it so complicated? It seems that other reproductions of a problem were encountering other locks.
Lets describe patches in this thread from the POV of these test.
*** Configurable SLRU buffers for MultiXact members and offsets.
From tests it is clear that high and low values for these buffers affect the test time. Here are time for a one test run with different offsets and members sizes [1]
Our production currently runs with (numbers are pages of buffers)
+#define NUM_MXACTOFFSET_BUFFERS 32
+#define NUM_MXACTMEMBER_BUFFERS 64
And, looking back to incidents in summer and fall 2020, seems like it helped mostly.
But it's hard to give some tuning advises based on test results. Values (32,64) produce 10% better result than current hardcoded values (8,16). In generic case this is not what someone should tune first.
*** Configurable caches of MultiXacts.
Tests were specifically designed to beat caches. So, according to test the bigger cache is - the more time it takes to accomplish the test [2].
Anyway cache is local for backend and it's purpose is deduplication of written MultiXacts, not enhancing reads.
*** Using advantage of SimpleLruReadPage_ReadOnly() in MultiXacts.
This simply aligns MultiXact with Subtransactions and CLOG. Other SLRUs already take advantage of reading SLRU with shared lock.
On synthetical tests without background selects this patch adds another ~4.7% of performance [3] against [4]. This improvement seems consistent between different parameter values, yet within measurements deviation (see difference between warmup run [5] and closing run [6]).
All in all, these attempts to measure impact are hand-wavy too. But it makes sense to use consistent approach among similar subsystems (MultiXacts, Subtrans, CLOG etc).
*** Reduce sleep in GetMultiXactIdMembers() on standby.
The problem with pg_usleep(1000L) within GetMultiXactIdMembers() manifests on standbys during contention of MultiXactOffsetControlLock. It's even harder to reproduce.
Yet it seems obvious that reducing sleep to shorter time frame will make count of sleeping backend smaller.
For consistency I've returned patch with SLRU buffer configs to patchset (other patches are intact). But I'm mostly concerned about patches 1 and 3.
Thanks!
Best regards, Andrey Borodin.
[0] https://github.com/x4m/multixact_stress
[1] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L22-L39
[2] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L83-L99
[3] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L9
[4] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L29
[5] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L3
[6] https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L19
Attachments:
[application/octet-stream] v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch (12.9K, ../[email protected]/2-v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch)
download | inline diff:
From bcbd4f6d88d7b6dd9cf779b62e0e0261edadc71b Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Tue, 27 Oct 2020 19:29:56 +0300
Subject: [PATCH v6 1/4] Use shared lock in GetMultiXactIdMembers for offsets
and members
Previously the read of multixact required exclusive control locks for both
offsets and members SLRUs. This could lead to the excessive lock contention.
This commit we makes multixacts SLRU take advantage of SimpleLruReadPage_ReadOnly
similar to clog, commit_ts and subtrans.
In order to evade extra reacquiring of CLRU lock, we teach
SimpleLruReadPage_ReadOnly() to take into account the current lock mode and
report resulting lock mode back.
Discussion: https://postgr.es/m/a7f1c4e1-1015-92a4-2bd4-6736bd13d03e%40postgrespro.ru#c496c4e75fc0605094a0e1f763e6a6ec
Author: Andrey Borodin
Reviewed-by: Kyotaro Horiguchi, Daniel Gustafsson
Reviewed-by: Anastasia Lubennikova, Alexander Korotkov
---
src/backend/access/transam/clog.c | 4 +++-
src/backend/access/transam/commit_ts.c | 4 +++-
src/backend/access/transam/multixact.c | 22 +++++++++++++++-------
src/backend/access/transam/slru.c | 23 +++++++++++++++++------
src/backend/access/transam/subtrans.c | 4 +++-
src/backend/commands/async.c | 4 +++-
src/backend/storage/lmgr/lwlock.c | 3 +++
src/backend/storage/lmgr/predicate.c | 4 +++-
src/include/access/slru.h | 2 +-
src/include/storage/lwlock.h | 2 ++
10 files changed, 53 insertions(+), 19 deletions(-)
diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
index 034349aa7b..4c372065de 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -640,10 +640,11 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
int lsnindex;
char *byteptr;
XidStatus status;
+ LWLockMode lockmode = LW_NONE;
/* lock is acquired by SimpleLruReadPage_ReadOnly */
- slotno = SimpleLruReadPage_ReadOnly(XactCtl, pageno, xid);
+ slotno = SimpleLruReadPage_ReadOnly(XactCtl, pageno, xid, &lockmode);
byteptr = XactCtl->shared->page_buffer[slotno] + byteno;
status = (*byteptr >> bshift) & CLOG_XACT_BITMASK;
@@ -651,6 +652,7 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
lsnindex = GetLSNIndex(slotno, xid);
*lsn = XactCtl->shared->group_lsn[lsnindex];
+ Assert(lockmode != LW_NONE);
LWLockRelease(XactSLRULock);
return status;
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index cb8a968801..c19b0b5399 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -288,6 +288,7 @@ TransactionIdGetCommitTsData(TransactionId xid, TimestampTz *ts,
CommitTimestampEntry entry;
TransactionId oldestCommitTsXid;
TransactionId newestCommitTsXid;
+ LWLockMode lockmode = LW_NONE;
if (!TransactionIdIsValid(xid))
ereport(ERROR,
@@ -342,7 +343,7 @@ TransactionIdGetCommitTsData(TransactionId xid, TimestampTz *ts,
}
/* lock is acquired by SimpleLruReadPage_ReadOnly */
- slotno = SimpleLruReadPage_ReadOnly(CommitTsCtl, pageno, xid);
+ slotno = SimpleLruReadPage_ReadOnly(CommitTsCtl, pageno, xid, &lockmode);
memcpy(&entry,
CommitTsCtl->shared->page_buffer[slotno] +
SizeOfCommitTimestampEntry * entryno,
@@ -352,6 +353,7 @@ TransactionIdGetCommitTsData(TransactionId xid, TimestampTz *ts,
if (nodeid)
*nodeid = entry.nodeid;
+ Assert(lockmode != LW_NONE);
LWLockRelease(CommitTsSLRULock);
return *ts != 0;
}
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 43653fe572..ccbce90f0e 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1237,6 +1237,7 @@ GetMultiXactIdMembers(MultiXactId multi, MultiXactMember **members,
MultiXactId tmpMXact;
MultiXactOffset nextOffset;
MultiXactMember *ptr;
+ LWLockMode lockmode = LW_NONE;
debug_elog3(DEBUG2, "GetMembers: asked for %u", multi);
@@ -1340,12 +1341,13 @@ GetMultiXactIdMembers(MultiXactId multi, MultiXactMember **members,
* time on every multixact creation.
*/
retry:
- LWLockAcquire(MultiXactOffsetSLRULock, LW_EXCLUSIVE);
pageno = MultiXactIdToOffsetPage(multi);
entryno = MultiXactIdToOffsetEntry(multi);
- slotno = SimpleLruReadPage(MultiXactOffsetCtl, pageno, true, multi);
+ /* lock is acquired by SimpleLruReadPage_ReadOnly */
+ slotno = SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl, pageno,
+ multi, &lockmode);
offptr = (MultiXactOffset *) MultiXactOffsetCtl->shared->page_buffer[slotno];
offptr += entryno;
offset = *offptr;
@@ -1377,7 +1379,8 @@ retry:
entryno = MultiXactIdToOffsetEntry(tmpMXact);
if (pageno != prev_pageno)
- slotno = SimpleLruReadPage(MultiXactOffsetCtl, pageno, true, tmpMXact);
+ slotno = SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl, pageno,
+ tmpMXact, &lockmode);
offptr = (MultiXactOffset *) MultiXactOffsetCtl->shared->page_buffer[slotno];
offptr += entryno;
@@ -1395,14 +1398,14 @@ retry:
length = nextMXOffset - offset;
}
+ Assert(lockmode != LW_NONE);
LWLockRelease(MultiXactOffsetSLRULock);
ptr = (MultiXactMember *) palloc(length * sizeof(MultiXactMember));
*members = ptr;
/* Now get the members themselves. */
- LWLockAcquire(MultiXactMemberSLRULock, LW_EXCLUSIVE);
-
+ lockmode = LW_NONE;
truelength = 0;
prev_pageno = -1;
for (i = 0; i < length; i++, offset++)
@@ -1418,7 +1421,8 @@ retry:
if (pageno != prev_pageno)
{
- slotno = SimpleLruReadPage(MultiXactMemberCtl, pageno, true, multi);
+ slotno = SimpleLruReadPage_ReadOnly(MultiXactMemberCtl, pageno,
+ multi, &lockmode);
prev_pageno = pageno;
}
@@ -1441,6 +1445,7 @@ retry:
truelength++;
}
+ Assert(lockmode != LW_NONE);
LWLockRelease(MultiXactMemberSLRULock);
/*
@@ -2733,6 +2738,7 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
int entryno;
int slotno;
MultiXactOffset *offptr;
+ LWLockMode lockmode = LW_NONE;
Assert(MultiXactState->finishedStartup);
@@ -2749,10 +2755,12 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
return false;
/* lock is acquired by SimpleLruReadPage_ReadOnly */
- slotno = SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl, pageno, multi);
+ slotno = SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl, pageno,
+ multi, &lockmode);
offptr = (MultiXactOffset *) MultiXactOffsetCtl->shared->page_buffer[slotno];
offptr += entryno;
offset = *offptr;
+ Assert(lockmode != LW_NONE);
LWLockRelease(MultiXactOffsetSLRULock);
*result = offset;
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 16a7898697..ca0a23ab87 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -487,17 +487,23 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
* Return value is the shared-buffer slot number now holding the page.
* The buffer's LRU access info is updated.
*
- * Control lock must NOT be held at entry, but will be held at exit.
- * It is unspecified whether the lock will be shared or exclusive.
+ * Control lock must be held in *lock_mode mode, which may be LW_NONE. Control
+ * lock will be held at exit in at least shared mode. Resulting control lock
+ * mode is set to *lock_mode.
*/
int
-SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
+SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid,
+ LWLockMode *lock_mode)
{
SlruShared shared = ctl->shared;
int slotno;
/* Try to find the page while holding only shared lock */
- LWLockAcquire(shared->ControlLock, LW_SHARED);
+ if (*lock_mode == LW_NONE)
+ {
+ LWLockAcquire(shared->ControlLock, LW_SHARED);
+ *lock_mode = LW_SHARED;
+ }
/* See if page is already in a buffer */
for (slotno = 0; slotno < shared->num_slots; slotno++)
@@ -517,8 +523,13 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
}
/* No luck, so switch to normal exclusive lock and do regular read */
- LWLockRelease(shared->ControlLock);
- LWLockAcquire(shared->ControlLock, LW_EXCLUSIVE);
+ if (*lock_mode != LW_EXCLUSIVE)
+ {
+ Assert(*lock_mode == LW_NONE);
+ LWLockRelease(shared->ControlLock);
+ LWLockAcquire(shared->ControlLock, LW_EXCLUSIVE);
+ *lock_mode = LW_EXCLUSIVE;
+ }
return SimpleLruReadPage(ctl, pageno, true, xid);
}
diff --git a/src/backend/access/transam/subtrans.c b/src/backend/access/transam/subtrans.c
index 0111e867c7..7bb1543189 100644
--- a/src/backend/access/transam/subtrans.c
+++ b/src/backend/access/transam/subtrans.c
@@ -113,6 +113,7 @@ SubTransGetParent(TransactionId xid)
int slotno;
TransactionId *ptr;
TransactionId parent;
+ LWLockMode lockmode = LW_NONE;
/* Can't ask about stuff that might not be around anymore */
Assert(TransactionIdFollowsOrEquals(xid, TransactionXmin));
@@ -123,12 +124,13 @@ SubTransGetParent(TransactionId xid)
/* lock is acquired by SimpleLruReadPage_ReadOnly */
- slotno = SimpleLruReadPage_ReadOnly(SubTransCtl, pageno, xid);
+ slotno = SimpleLruReadPage_ReadOnly(SubTransCtl, pageno, xid, &lockmode);
ptr = (TransactionId *) SubTransCtl->shared->page_buffer[slotno];
ptr += entryno;
parent = *ptr;
+ Assert(lockmode != LW_NONE);
LWLockRelease(SubtransSLRULock);
return parent;
diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c
index 8dbcace3f9..8b41957559 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -2002,6 +2002,7 @@ asyncQueueReadAllNotifications(void)
int curoffset = QUEUE_POS_OFFSET(pos);
int slotno;
int copysize;
+ LWLockMode lockmode = LW_NONE;
/*
* We copy the data from SLRU into a local buffer, so as to avoid
@@ -2010,7 +2011,7 @@ asyncQueueReadAllNotifications(void)
* part of the page we will actually inspect.
*/
slotno = SimpleLruReadPage_ReadOnly(NotifyCtl, curpage,
- InvalidTransactionId);
+ InvalidTransactionId, &lockmode);
if (curpage == QUEUE_POS_PAGE(head))
{
/* we only want to read as far as head */
@@ -2027,6 +2028,7 @@ asyncQueueReadAllNotifications(void)
NotifyCtl->shared->page_buffer[slotno] + curoffset,
copysize);
/* Release lock that we got from SimpleLruReadPage_ReadOnly() */
+ Assert(lockmode != LW_NONE);
LWLockRelease(NotifySLRULock);
/*
diff --git a/src/backend/storage/lmgr/lwlock.c b/src/backend/storage/lmgr/lwlock.c
index 2fa90cc095..8a7726f11c 100644
--- a/src/backend/storage/lmgr/lwlock.c
+++ b/src/backend/storage/lmgr/lwlock.c
@@ -1068,6 +1068,8 @@ LWLockWakeup(LWLock *lock)
static void
LWLockQueueSelf(LWLock *lock, LWLockMode mode)
{
+ Assert(mode != LW_NONE);
+
/*
* If we don't have a PGPROC structure, there's no way to wait. This
* should never occur, since MyProc should only be null during shared
@@ -1828,6 +1830,7 @@ LWLockRelease(LWLock *lock)
elog(ERROR, "lock %s is not held", T_NAME(lock));
mode = held_lwlocks[i].mode;
+ Assert(mode == LW_EXCLUSIVE || mode == LW_SHARED);
num_held_lwlocks--;
for (; i < num_held_lwlocks; i++)
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index 8a365b400c..a4df90a8ae 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -924,6 +924,7 @@ SerialGetMinConflictCommitSeqNo(TransactionId xid)
TransactionId tailXid;
SerCommitSeqNo val;
int slotno;
+ LWLockMode lockmode = LW_NONE;
Assert(TransactionIdIsValid(xid));
@@ -946,8 +947,9 @@ SerialGetMinConflictCommitSeqNo(TransactionId xid)
* but will return with that lock held, which must then be released.
*/
slotno = SimpleLruReadPage_ReadOnly(SerialSlruCtl,
- SerialPage(xid), xid);
+ SerialPage(xid), xid, &lockmode);
val = SerialValue(slotno, xid);
+ Assert(lockmode != LW_NONE);
LWLockRelease(SerialSLRULock);
return val;
}
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index b39b43504d..4b66d3b592 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -142,7 +142,7 @@ extern int SimpleLruZeroPage(SlruCtl ctl, int pageno);
extern int SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
TransactionId xid);
extern int SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno,
- TransactionId xid);
+ TransactionId xid, LWLockMode *lock_mode);
extern void SimpleLruWritePage(SlruCtl ctl, int slotno);
extern void SimpleLruWriteAll(SlruCtl ctl, bool allow_redirtied);
extern void SimpleLruTruncate(SlruCtl ctl, int cutoffPage);
diff --git a/src/include/storage/lwlock.h b/src/include/storage/lwlock.h
index af9b41795d..e680c6397a 100644
--- a/src/include/storage/lwlock.h
+++ b/src/include/storage/lwlock.h
@@ -129,6 +129,8 @@ extern PGDLLIMPORT int NamedLWLockTrancheRequests;
typedef enum LWLockMode
{
+ LW_NONE, /* Not a lock mode. Indicates that there is no
+ * lock. */
LW_EXCLUSIVE,
LW_SHARED,
LW_WAIT_UNTIL_FREE /* A special mode used in PGPROC->lwWaitMode,
--
2.24.3 (Apple Git-128)
[application/octet-stream] v6-0004-Add-GUCs-to-tune-MultiXact-SLRUs.patch (6.3K, ../[email protected]/3-v6-0004-Add-GUCs-to-tune-MultiXact-SLRUs.patch)
download | inline diff:
From a5e15ae42bd6c99d39bac7a9988f586c48ceba13 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Sat, 9 May 2020 16:42:07 +0500
Subject: [PATCH v6 4/4] Add GUCs to tune MultiXact SLRUs
---
doc/src/sgml/config.sgml | 31 ++++++++++++++++++++++++++
src/backend/access/transam/multixact.c | 8 +++----
src/backend/utils/init/globals.c | 2 ++
src/backend/utils/misc/guc.c | 22 ++++++++++++++++++
src/include/access/multixact.h | 4 ----
src/include/miscadmin.h | 2 ++
6 files changed, 61 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fd4ca29347..d2ca4934de 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1838,6 +1838,37 @@ include_dir 'conf.d'
</para>
</listitem>
</varlistentry>
+
+ <varlistentry id="guc-multixact-offsets-slru-buffers" xreflabel="multixact_offsets_slru_buffers">
+ <term><varname>multixact_offsets_slru_buffers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>multixact_offsets_slru_buffers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the amount of memory to be used for MultiXact offsets. MultiXact offsets
+ are used to store informaion about offsets of multiple row lockers (caused by SELECT FOR UPDATE and others).
+ It defaults to 64 kilobytes (<literal>64KB</literal>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-multixact-members-slru-buffers" xreflabel="multixact_members_slru_buffers">
+ <term><varname>multixact_members_slru_buffers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>multixact_members_slru_buffers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the amount of memory to be used for MultiXact members. MultiXact members
+ are used to store informaion about XIDs of multiple row lockers. Tipically <varname>multixact_members_slru_buffers</varname>
+ is twice more than <varname>multixact_offsets_slru_buffers</varname>.
+ It defaults to 128 kilobytes (<literal>128KB</literal>).
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
<term><varname>max_stack_depth</varname> (<type>integer</type>)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 70d977cba3..1c177242c5 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1866,8 +1866,8 @@ MultiXactShmemSize(void)
mul_size(sizeof(MultiXactId) * 2, MaxOldestSlot))
size = SHARED_MULTIXACT_STATE_SIZE;
- size = add_size(size, SimpleLruShmemSize(NUM_MULTIXACTOFFSET_BUFFERS, 0));
- size = add_size(size, SimpleLruShmemSize(NUM_MULTIXACTMEMBER_BUFFERS, 0));
+ size = add_size(size, SimpleLruShmemSize(multixact_offsets_slru_buffers, 0));
+ size = add_size(size, SimpleLruShmemSize(multixact_members_slru_buffers, 0));
return size;
}
@@ -1883,12 +1883,12 @@ MultiXactShmemInit(void)
MultiXactMemberCtl->PagePrecedes = MultiXactMemberPagePrecedes;
SimpleLruInit(MultiXactOffsetCtl,
- "MultiXactOffset", NUM_MULTIXACTOFFSET_BUFFERS, 0,
+ "MultiXactOffset", multixact_offsets_slru_buffers, 0,
MultiXactOffsetSLRULock, "pg_multixact/offsets",
LWTRANCHE_MULTIXACTOFFSET_BUFFER,
SYNC_HANDLER_MULTIXACT_OFFSET);
SimpleLruInit(MultiXactMemberCtl,
- "MultiXactMember", NUM_MULTIXACTMEMBER_BUFFERS, 0,
+ "MultiXactMember", multixact_members_slru_buffers, 0,
MultiXactMemberSLRULock, "pg_multixact/members",
LWTRANCHE_MULTIXACTMEMBER_BUFFER,
SYNC_HANDLER_MULTIXACT_MEMBER);
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 9ca71933dc..a5ec7bfe88 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -151,3 +151,5 @@ bool VacuumCostActive = false;
double vacuum_cleanup_index_scale_factor;
int multixact_local_cache_entries = 256;
+int multixact_offsets_slru_buffers = 8;
+int multixact_members_slru_buffers = 16;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d667578cc3..7682c8be1e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2267,6 +2267,28 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"multixact_offsets_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+ gettext_noop("Sets the number of shared memory buffers used for MultiXact offsets SLRU."),
+ NULL,
+ GUC_UNIT_BLOCKS
+ },
+ &multixact_offsets_slru_buffers,
+ 8, 2, INT_MAX / 2,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"multixact_members_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+ gettext_noop("Sets the number of shared memory buffers used for MultiXact members SLRU."),
+ NULL,
+ GUC_UNIT_BLOCKS
+ },
+ &multixact_members_slru_buffers,
+ 16, 2, INT_MAX / 2,
+ NULL, NULL, NULL
+ },
+
{
{"temp_buffers", PGC_USERSET, RESOURCES_MEM,
gettext_noop("Sets the maximum number of temporary buffers used by each session."),
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index 9a30380901..630ceaea4d 100644
--- a/src/include/access/multixact.h
+++ b/src/include/access/multixact.h
@@ -29,10 +29,6 @@
#define MaxMultiXactOffset ((MultiXactOffset) 0xFFFFFFFF)
-/* Number of SLRU buffers to use for multixact */
-#define NUM_MULTIXACTOFFSET_BUFFERS 8
-#define NUM_MULTIXACTMEMBER_BUFFERS 16
-
/*
* Possible multixact lock modes ("status"). The first four modes are for
* tuple locks (FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE); the
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 01af61c963..ef8abea84d 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -161,6 +161,8 @@ extern PGDLLIMPORT int MaxBackends;
extern PGDLLIMPORT int MaxConnections;
extern PGDLLIMPORT int max_worker_processes;
extern PGDLLIMPORT int max_parallel_workers;
+extern PGDLLIMPORT int multixact_offsets_slru_buffers;
+extern PGDLLIMPORT int multixact_members_slru_buffers;
extern PGDLLIMPORT int multixact_local_cache_entries;
--
2.24.3 (Apple Git-128)
[application/octet-stream] v6-0003-Add-conditional-variable-to-wait-for-next-MultXac.patch (4.4K, ../[email protected]/4-v6-0003-Add-conditional-variable-to-wait-for-next-MultXac.patch)
download | inline diff:
From d25e7d68a7979fc391c5d988b256a49cdd916bfb Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 26 Oct 2020 01:55:31 +0300
Subject: [PATCH v6 3/4] Add conditional variable to wait for next MultXact
offset in corner case
GetMultiXactIdMembers() has a corner case, when the next multixact offset is
not yet set. In this case GetMultiXactIdMembers() has to sleep till this offset
is set. Currently the sleeping is implemented in naive way using pg_sleep()
and retry. This commit implements sleeping with conditional variable, which
provides more efficient way for waiting till the event.
Discussion: https://postgr.es/m/a7f1c4e1-1015-92a4-2bd4-6736bd13d03e%40postgrespro.ru#c496c4e75fc0605094a0e1f763e6a6ec
Author: Andrey Borodin
Reviewed-by: Kyotaro Horiguchi, Daniel Gustafsson
Reviewed-by: Anastasia Lubennikova, Alexander Korotkov
---
src/backend/access/transam/multixact.c | 35 ++++++++++++++++++++++++--
src/backend/postmaster/pgstat.c | 2 ++
src/include/pgstat.h | 1 +
3 files changed, 36 insertions(+), 2 deletions(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 57be24c0cc..70d977cba3 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -82,6 +82,7 @@
#include "lib/ilist.h"
#include "miscadmin.h"
#include "pg_trace.h"
+#include "pgstat.h"
#include "postmaster/autovacuum.h"
#include "storage/lmgr.h"
#include "storage/pmsignal.h"
@@ -233,6 +234,13 @@ typedef struct MultiXactStateData
/* support for members anti-wraparound measures */
MultiXactOffset offsetStopLimit; /* known if oldestOffsetKnown */
+ /*
+ * Conditional variable for waiting till the filling of the next multixact
+ * will be finished. See GetMultiXactIdMembers() and RecordNewMultiXact()
+ * for details.
+ */
+ ConditionVariable nextoffCV;
+
/*
* Per-backend data starts here. We have two arrays stored in the area
* immediately following the MultiXactStateData struct. Each is indexed by
@@ -892,6 +900,14 @@ RecordNewMultiXact(MultiXactId multi, MultiXactOffset offset,
/* Exchange our lock */
LWLockRelease(MultiXactOffsetSLRULock);
+ /*
+ * Let everybody know the offset of this mxid is recorded now. The
+ * waiters are waiting for the offset of the mxid next of the target to
+ * know the number of members of the target mxid, so we don't need to wait
+ * for members of this mxid are recorded.
+ */
+ ConditionVariableBroadcast(&MultiXactState->nextoffCV);
+
LWLockAcquire(MultiXactMemberSLRULock, LW_EXCLUSIVE);
prev_pageno = -1;
@@ -1389,9 +1405,23 @@ retry:
if (nextMXOffset == 0)
{
/* Corner case 2: next multixact is still being filled in */
+
+ /*
+ * The recorder of the next mxid is just before writing the
+ * offset. Wait for the offset to be written.
+ */
+ ConditionVariablePrepareToSleep(&MultiXactState->nextoffCV);
+
+ /*
+ * We don't have to recheck if multixact was filled in during
+ * ConditionVariablePrepareToSleep(), because we were holding
+ * MultiXactOffsetSLRULock.
+ */
LWLockRelease(MultiXactOffsetSLRULock);
- CHECK_FOR_INTERRUPTS();
- pg_usleep(1000L);
+
+ ConditionVariableSleep(&MultiXactState->nextoffCV,
+ WAIT_EVENT_WAIT_NEXT_MXMEMBERS);
+ ConditionVariableCancelSleep();
goto retry;
}
@@ -1873,6 +1903,7 @@ MultiXactShmemInit(void)
/* Make sure we zero out the per-backend state */
MemSet(MultiXactState, 0, SHARED_MULTIXACT_STATE_SIZE);
+ ConditionVariableInit(&MultiXactState->nextoffCV);
}
else
Assert(found);
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 822f0ebc62..b99398a97e 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -4020,6 +4020,8 @@ pgstat_get_wait_ipc(WaitEventIPC w)
break;
case WAIT_EVENT_XACT_GROUP_UPDATE:
event_name = "XactGroupUpdate";
+ case WAIT_EVENT_WAIT_NEXT_MXMEMBERS:
+ event_name = "MultiXactWaitNextMembers";
break;
/* no default case, so that compiler will warn */
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a821ff4f15..75ede141ab 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -952,6 +952,7 @@ typedef enum
WAIT_EVENT_REPLICATION_SLOT_DROP,
WAIT_EVENT_SAFE_SNAPSHOT,
WAIT_EVENT_SYNC_REP,
+ WAIT_EVENT_WAIT_NEXT_MXMEMBERS,
WAIT_EVENT_XACT_GROUP_UPDATE
} WaitEventIPC;
--
2.24.3 (Apple Git-128)
[application/octet-stream] v6-0002-Make-MultiXact-local-cache-size-configurable.patch (3.7K, ../[email protected]/5-v6-0002-Make-MultiXact-local-cache-size-configurable.patch)
download | inline diff:
From d0cd1ce556ad3f4e7bbebf0468dc6e00765d79ae Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 26 Oct 2020 03:44:50 +0300
Subject: [PATCH v6 2/4] Make MultiXact local cache size configurable
---
doc/src/sgml/config.sgml | 16 ++++++++++++++++
src/backend/access/transam/multixact.c | 2 +-
src/backend/utils/init/globals.c | 2 ++
src/backend/utils/misc/guc.c | 10 ++++++++++
src/include/miscadmin.h | 2 ++
5 files changed, 31 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f043433e31..fd4ca29347 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1823,6 +1823,22 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-multixact-local-cache-entries" xreflabel="multixact_local-cache-entries">
+ <term><varname>multixact_local_cache_entries</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>multixact_local_cache_entries</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number cached MultiXact by backend. Any SLRU lookup is preceeded
+ by cache lookup. Higher numbers of cache size help to deduplicate lock sets, while
+ lower values make cache lookup faster.
+ It defaults to 256.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
<term><varname>max_stack_depth</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index ccbce90f0e..57be24c0cc 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1613,7 +1613,7 @@ mXactCachePut(MultiXactId multi, int nmembers, MultiXactMember *members)
qsort(entry->members, nmembers, sizeof(MultiXactMember), mxactMemberComparator);
dlist_push_head(&MXactCache, &entry->node);
- if (MXactCacheMembers++ >= MAX_CACHE_ENTRIES)
+ if (MXactCacheMembers++ >= multixact_local_cache_entries)
{
dlist_node *node;
mXactCacheEnt *entry;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 6ab8216839..9ca71933dc 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -149,3 +149,5 @@ int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
double vacuum_cleanup_index_scale_factor;
+
+int multixact_local_cache_entries = 256;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a62d64eaa4..d667578cc3 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2257,6 +2257,16 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"multixact_local_cache_entries", PGC_SUSET, RESOURCES_MEM,
+ gettext_noop("Sets the number of cached MultiXact by backend."),
+ NULL
+ },
+ &multixact_local_cache_entries,
+ 256, 2, INT_MAX / 2,
+ NULL, NULL, NULL
+ },
+
{
{"temp_buffers", PGC_USERSET, RESOURCES_MEM,
gettext_noop("Sets the maximum number of temporary buffers used by each session."),
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 72e3352398..01af61c963 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -162,6 +162,8 @@ extern PGDLLIMPORT int MaxConnections;
extern PGDLLIMPORT int max_worker_processes;
extern PGDLLIMPORT int max_parallel_workers;
+extern PGDLLIMPORT int multixact_local_cache_entries;
+
extern PGDLLIMPORT int MyProcPid;
extern PGDLLIMPORT pg_time_t MyStartTime;
extern PGDLLIMPORT TimestampTz MyStartTimestamp;
--
2.24.3 (Apple Git-128)
view thread (81+ 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], [email protected]
Subject: Re: MultiXact\SLRU buffers configuration
In-Reply-To: <[email protected]>
* 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