Subject: MultiXact\SLRU buffers configuration

From: "Andrey M. Borodin" <[email-masked]>
Date: Fri, 8 May 2020 21:36:40 +0500

Hi, hackers!

*** The problem ***
I'm investigating some cases of reduced database performance due to MultiXactOffsetLock contention (80% MultiXactOffsetLock, 20% IO DataFileRead).
The problem manifested itself during index repack and constraint validation. Both being effectively full table scans.
The database workload contains a lot of select for share\select for update queries. I've tried to construct synthetic world generator and could not achieve similar lock configuration: I see a lot of different locks in wait events, particularly a lot more MultiXactMemberLocks. But from my experiments with synthetic workload, contention of MultiXactOffsetLock can be reduced by increasing NUM_MXACTOFFSET_BUFFERS=8 to bigger numbers.

*** Question 1 ***
Is it safe to increase number of buffers of MultiXact\All SLRUs, recompile and run database as usual?
I cannot experiment much with production. But I'm mostly sure that bigger buffers will solve the problem.

*** Question 2 ***
Probably, we could do GUCs for SLRU sizes? Are there any reasons not to do them configurable? I think multis, clog, subtransactions and others will benefit from bigger buffer. But, probably, too much of knobs can be confusing.

*** Question 3 ***
MultiXact offset lock is always taken as exclusive lock. It turns MultiXact Offset subsystem to single threaded. If someone have good idea how to make it more concurrency-friendly, I'm willing to put some efforts into this.
Probably, I could just add LWlocks for each offset buffer page. Is it something worth doing? Or are there any hidden cavers and difficulties?

Thanks!

Best regards, Andrey Borodin.

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Mon, 11 May 2020 16:17:58 +0500

> 8 мая 2020 г., в 21:36, Andrey M. Borodin <[email-masked]> написал(а):
> 
> *** The problem ***

I've created benchmark[0] imitating MultiXact pressure on my laptop: 7 clients are concurrently running select "select * from table where primary_key = ANY ($1) for share" where $1 is array of identifiers so that each tuple in a table is locked by different set of XIDs. During this benchmark I observe contention of MultiXactControlLock in pg_stat_activity

                                    пятница,  8 мая 2020 г. 15:08:37 (every 1s)

  pid  |         wait_event         | wait_event_type | state  |                       query                        
-------+----------------------------+-----------------+--------+----------------------------------------------------
 41344 | ClientRead                 | Client          | idle   | insert into t1 select generate_series(1,1000000,1)
 41375 | MultiXactOffsetControlLock | LWLock          | active | select * from t1 where i = ANY ($1) for share
 41377 | MultiXactOffsetControlLock | LWLock          | active | select * from t1 where i = ANY ($1) for share
 41378 |                            |                 | active | select * from t1 where i = ANY ($1) for share
 41379 | MultiXactOffsetControlLock | LWLock          | active | select * from t1 where i = ANY ($1) for share
 41381 |                            |                 | active | select * from t1 where i = ANY ($1) for share
 41383 | MultiXactOffsetControlLock | LWLock          | active | select * from t1 where i = ANY ($1) for share
 41385 | MultiXactOffsetControlLock | LWLock          | active | select * from t1 where i = ANY ($1) for share
(8 rows)

Finally, the benchmark is measuring time to execute select for update 42 times.

I've went ahead and created 3 patches:
1. Configurable SLRU buffer sizes for MultiXacOffsets and MultiXactMembers
2. Reduce locking level to shared on read of MultiXactId members
3. Configurable cache size

I've found out that:
1. When MultiXact working set does not fit into buffers - benchmark results grow very high. Yet, very big buffers slow down benchmark too. For this benchmark optimal SLRU size id 32 pages for offsets and 64 pages for members (defaults are 8 and 16 respectively).
2. Lock optimisation increases performance by 5% on default SLRU sizes. Actually, benchmark does not explicitly read MultiXactId members, but when it replaces one with another - it have to read previous set. I understand that we can construct benchmark to demonstrate dominance of any algorithm and 5% of synthetic workload is not a very big number. But it just make sense to try to take shared lock for reading.
3. Manipulations with cache size do not affect benchmark anyhow. It's somewhat expected: benchmark is designed to defeat cache, either way OffsetControlLock would not be stressed.

For our workload, I think we will just increase numbers of SLRU sizes. But patchset may be useful for tuning and as a performance optimisation of MultiXact.

Also MultiXacts seems to be not very good fit into SLRU design. I think it would be better to use B-tree as a container. Or at least make MultiXact members extendable in-place (reserve some size when multixact is created).
When we want to extend number of locks for a tuple currently we will:
1. Iterate through all SLRU buffers for offsets to read current offset (with exclusive lock for offsets)
2. Iterate through all buffers for members to find current members (with exclusive lock for members)
3. Create new members array with +1 xid
4. Iterate through all cache members to find out maybe there are any such cache item as what we are going to create
5. iterate over 1 again for write
6. Iterate over 2 again for write

Obviously this does not scale well - we cannot increase SLRU sizes for too long.

Thanks! I'd be happy to hear any feedback.

[0] https://github.com/x4m/multixact_stress

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 13 May 2020 23:08:37 +0500

> 11 мая 2020 г., в 16:17, Andrey M. Borodin <[email-masked]> написал(а):
> 

I'm looking more at MultiXact and it seems to me that we have a race condition there.

When we create a new MultiXact we do:
1. Generate new MultiXactId under MultiXactGenLock
2. Record new mxid with members and offset to WAL
3. Write offset to SLRU under MultiXactOffsetControlLock
4. Write members to SLRU under MultiXactMemberControlLock

When we read MultiXact we do:
1. Retrieve offset by mxid from SLRU under MultiXactOffsetControlLock
2. If offset is 0 - it's not filled in at step 4 of previous algorithm, we sleep and goto 1
3. Retrieve members from SLRU under MultiXactMemberControlLock
4. ..... what we do if there are just zeroes because step 4 is not executed yet? Nothing, return empty members list.

What am I missing?

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Thu, 14 May 2020 10:25:26 +0900 (JST)

At Wed, 13 May 2020 23:08:37 +0500, "Andrey M. Borodin" <[email-masked]> wrote in 
> 4. Write members to SLRU under MultiXactMemberControlLock

But, don't we hold exclusive lock on the buffer through all the steps
above?

> 4. ..... what we do if there are just zeroes because step 4 is not executed yet? Nothing, return empty members list.

So transactions never see such incomplete mxids, I believe.

> What am I missing?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Thu, 14 May 2020 10:19:42 +0500

> 14 мая 2020 г., в 06:25, Kyotaro Horiguchi <[email-masked]> написал(а):
> 
Yes...Unless MultiXact is observed on StandBy. This could lead to observing inconsistent snapshot: one of lockers committed tuple delete, but standby sees it as alive.

> So transactions never see such incomplete mxids, I believe.
I've observed sleep in step 2. I believe it's possible to observe special effects of step 4 too.
Maybe we could add lock on standby to dismiss this 1000us wait? Sometimes it hits hard on Standbys: if someone is locking whole table on primary - all seq scans on standbys follow him with MultiXactOffsetControlLock contention.

It looks like this:
0x00007fcd56896ff7 in __GI___select (nfds=nfds@entry=0, readfds=readfds@entry=0x0, writefds=writefds@entry=0x0, exceptfds=exceptfds@entry=0x0, timeout=timeout@entry=0x7ffd83376fe0) at ../sysdeps/unix/sysv/linux/select.c:41
#0  0x00007fcd56896ff7 in __GI___select (nfds=nfds@entry=0, readfds=readfds@entry=0x0, writefds=writefds@entry=0x0, exceptfds=exceptfds@entry=0x0, timeout=timeout@entry=0x7ffd83376fe0) at ../sysdeps/unix/sysv/linux/select.c:41
#1  0x000056186e0d54bd in pg_usleep (microsec=microsec@entry=1000) at ./build/../src/port/pgsleep.c:56
#2  0x000056186dd5edf2 in GetMultiXactIdMembers (from_pgupgrade=0 '\000', onlyLock=<optimized out>, members=0x7ffd83377080, multi=3106214809) at ./build/../src/backend/access/transam/multixact.c:1370
#3  GetMultiXactIdMembers () at ./build/../src/backend/access/transam/multixact.c:1202
#4  0x000056186dd2d2d9 in MultiXactIdGetUpdateXid (xmax=<optimized out>, t_infomask=<optimized out>) at ./build/../src/backend/access/heap/heapam.c:7039
#5  0x000056186dd35098 in HeapTupleGetUpdateXid (tuple=tuple@entry=0x7fcba3b63d58) at ./build/../src/backend/access/heap/heapam.c:7080
#6  0x000056186e0cd0f8 in HeapTupleSatisfiesMVCC (htup=<optimized out>, snapshot=0x56186f44a058, buffer=230684) at ./build/../src/backend/utils/time/tqual.c:1091
#7  0x000056186dd2d922 in heapgetpage (scan=scan@entry=0x56186f4c8e78, page=page@entry=3620) at ./build/../src/backend/access/heap/heapam.c:439
#8  0x000056186dd2ea7c in heapgettup_pagemode (key=0x0, nkeys=0, dir=ForwardScanDirection, scan=0x56186f4c8e78) at ./build/../src/backend/access/heap/heapam.c:1034
#9  heap_getnext (scan=scan@entry=0x56186f4c8e78, direction=direction@entry=ForwardScanDirection) at ./build/../src/backend/access/heap/heapam.c:1801
#10 0x000056186de84f51 in SeqNext (node=node@entry=0x56186f4a4f78) at ./build/../src/backend/executor/nodeSeqscan.c:81
#11 0x000056186de6a3f1 in ExecScanFetch (recheckMtd=0x56186de84ef0 <SeqRecheck>, accessMtd=0x56186de84f20 <SeqNext>, node=0x56186f4a4f78) at ./build/../src/backend/executor/execScan.c:97
#12 ExecScan (node=0x56186f4a4f78, accessMtd=0x56186de84f20 <SeqNext>, recheckMtd=0x56186de84ef0 <SeqRecheck>) at ./build/../src/backend/executor/execScan.c:164

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Thu, 14 May 2020 15:16:59 +0900 (JST)

At Thu, 14 May 2020 10:19:42 +0500, "Andrey M. Borodin" <[email-masked]> wrote in 
> Yes...Unless MultiXact is observed on StandBy. This could lead to observing inconsistent snapshot: one of lockers committed tuple delete, but standby sees it as alive.

Ah, right. I looked from GetNewMultiXactId. Actually
XLOG_MULTIXACT_CREATE_ID is not protected from concurrent reference to
the creating mxact id. And GetMultiXactIdMembers is considering that
case.

> Maybe we could add lock on standby to dismiss this 1000us wait? Sometimes it hits hard on Standbys: if someone is locking whole table on primary - all seq scans on standbys follow him with MultiXac…

GetMultiXactIdMembers believes that 4 is successfully done if 2
returned valid offset, but actually that is not obvious.

If we add a single giant lock just to isolate ,say,
GetMultiXactIdMember and RecordNewMultiXact, it reduces concurrency
unnecessarily.  Perhaps we need finer-grained locking-key for standby
that works similary to buffer lock on primary, that doesn't cause
confilicts between irrelevant mxids.

regards.

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Thu, 14 May 2020 11:44:01 +0500

> 14 мая 2020 г., в 11:16, Kyotaro Horiguchi <[email-masked]> написал(а):
> 
We can just replay members before offsets. If offset is already there - members are there too.
But I'd be happy if we could mitigate those 1000us too - with a hint about last maixd state in a shared MX state, for example.

Actually, if we read empty mxid array instead of something that is replayed just yet - it's not a problem of inconsistency, because transaction in this mxid could not commit before we started. ISTM.
So instead of fix, we, probably, can just add a comment. If this reasoning is correct.

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Fri, 15 May 2020 09:03:33 +0900 (JST)

At Thu, 14 May 2020 11:44:01 +0500, "Andrey M. Borodin" <[email-masked]> wrote in 
> But I'd be happy if we could mitigate those 1000us too - with a hint about last maixd state in a shared MX state, for example.

Generally in such cases, condition variables would work.  In the
attached PoC, the reader side gets no penalty in the "likely" code
path.  The writer side always calls ConditionVariableBroadcast but the
waiter list is empty in almost all cases.  But I couldn't cause the
situation where the sleep 1000u is reached.

> So instead of fix, we, probably, can just add a comment. If this reasoning is correct.

The step 4 of the reader side reads the members of the target mxid. It
is already written if the offset of the *next* mxid is filled-in.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index e2aa5c9ce4..9db8f6cddd 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,7 @@ typedef struct MultiXactStateData
 	/* support for members anti-wraparound measures */
 	MultiXactOffset offsetStopLimit;	/* known if oldestOffsetKnown */
 
+	ConditionVariable nextoff_cv;
 	/*
 	 * Per-backend data starts here.  We have two arrays stored in the area
 	 * immediately following the MultiXactStateData struct. Each is indexed by
@@ -873,6 +875,14 @@ RecordNewMultiXact(MultiXactId multi, MultiXactOffset offset,
 	/* Exchange our lock */
 	LWLockRelease(MultiXactOffsetControlLock);
 
+	/*
+	 *  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->nextoff_cv);
+
 	LWLockAcquire(MultiXactMemberControlLock, LW_EXCLUSIVE);
 
 	prev_pageno = -1;
@@ -1367,9 +1377,19 @@ 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->nextoff_cv);
+
 			LWLockRelease(MultiXactOffsetControlLock);
 			CHECK_FOR_INTERRUPTS();
-			pg_usleep(1000L);
+
+			ConditionVariableSleep(&MultiXactState->nextoff_cv,
+								   WAIT_EVENT_WAIT_NEXT_MXMEMBERS);
+			ConditionVariableCancelSleep();
 			goto retry;
 		}
 
@@ -1847,6 +1867,7 @@ MultiXactShmemInit(void)
 
 		/* Make sure we zero out the per-backend state */
 		MemSet(MultiXactState, 0, SHARED_MULTIXACT_STATE_SIZE);
+		ConditionVariableInit(&MultiXactState->nextoff_cv);
 	}
 	else
 		Assert(found);
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 0ecd29a1d9..1ac6b37188 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -3879,6 +3879,9 @@ pgstat_get_wait_ipc(WaitEventIPC w)
 		case WAIT_EVENT_SYNC_REP:
 			event_name = "SyncRep";
 			break;
+		case WAIT_EVENT_WAIT_NEXT_MXMEMBERS:
+			event_name = "Mact/WaitNextXactMembers";
+			break;
 			/* no default case, so that compiler will warn */
 	}
 
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ae9a39573c..e79bba0bef 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -886,7 +886,8 @@ typedef enum
 	WAIT_EVENT_REPLICATION_ORIGIN_DROP,
 	WAIT_EVENT_REPLICATION_SLOT_DROP,
 	WAIT_EVENT_SAFE_SNAPSHOT,
-	WAIT_EVENT_SYNC_REP
+	WAIT_EVENT_SYNC_REP,
+	WAIT_EVENT_WAIT_NEXT_MXMEMBERS
 } WaitEventIPC;
 
 /* ----------

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Fri, 15 May 2020 14:01:46 +0500

> 15 мая 2020 г., в 05:03, Kyotaro Horiguchi <[email-masked]> написал(а):
> 
Thanks! That really looks like a good solution without magic timeouts. Beautiful!
I think I can create temporary extension which calls MultiXact API and tests edge-cases like this 1000us wait.
This extension will also be also useful for me to assess impact of bigger buffers, reduced read locking (as in my 2nd patch) and other tweaks.

> is already written if the offset of the *next* mxid is filled-in.
Most often - yes, but members are not guaranteed to be filled in order. Those who win MXMemberControlLock will write first.
But nobody can read members of MXID before it is returned. And its members will be written before returning MXID.

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Wed, 20 May 2020 13:54:04 +0900 (JST)

At Fri, 15 May 2020 14:01:46 +0500, "Andrey M. Borodin" <[email-masked]> wrote in 
> This extension will also be also useful for me to assess impact of bigger buffers, reduced read locking (as in my 2nd patch) and other tweaks.

Happy to hear that, It would need to use timeout just in case, though.

> But nobody can read members of MXID before it is returned. And its members will be written before returning MXID.

Yeah, right.  Otherwise assertion failure happens.

regards.

---

From: Daniel Gustafsson <[email-masked]>
Date: Thu, 2 Jul 2020 14:02:45 +0200

> On 15 May 2020, at 02:03, Kyotaro Horiguchi <[email-masked]> wrote:

> situation where the sleep 1000u is reached.

The submitted patch no longer applies, can you please submit an updated
version?  I'm marking the patch Waiting on Author in the meantime.

cheers ./daniel

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 8 Jul 2020 12:03:54 +0500

> 2 июля 2020 г., в 17:02, Daniel Gustafsson <[email-masked]> написал(а):
> 
Thanks, Daniel! PFA V2

---

From: Daniel Gustafsson <[email-masked]>
Date: Sun, 2 Aug 2020 23:30:21 +0200

> On 8 Jul 2020, at 09:03, Andrey M. Borodin <[email-masked]> wrote:
> 
> 
> 

This version too has stopped applying according to the CFbot.  I've moved it to
the next commitfest as we're out of time in this one and it was only pointed
out now, but kept it Waiting on Author.

cheers ./daniel

---

From: Anastasia Lubennikova <[email-masked]>
Date: Fri, 28 Aug 2020 21:08:34 +0300

> Best regards, Andrey Borodin.

1) The first patch is sensible and harmless, so I think it is ready for 
committer. I haven't tested the performance impact, though.

2) I like the initial proposal to make various SLRU buffers 
configurable, however, I doubt if it really solves the problem, or just 
moves it to another place?

The previous patch you sent was based on some version that contained 
changes for other slru buffers numbers: 'multixact_offsets_slru_buffers' 
and 'multixact_members_slru_buffers'. Have you just forgot to attach 
them? The patch message "[PATCH v2 2/4]" hints that you had 4 patches)
Meanwhile, I attach the rebased patch to calm down the CFbot. The 
changes are trivial.

2.1) I think that both min and max values for this parameter are too 
extreme. Have you tested them?

+               &multixact_local_cache_entries,
+               256, 2, INT_MAX / 2,

2.2) MAX_CACHE_ENTRIES is not used anymore, so it can be deleted.

3) No changes for third patch. I just renamed it for consistency.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Mon, 28 Sep 2020 19:41:41 +0500

Hi, Anastasia!

> 28 авг. 2020 г., в 23:08, Anastasia Lubennikova <[email-masked]> написал(а):
> 
> 1) The first patch is sensible and harmless, so I think it is ready for committer. I haven't tested the performance impact, though.
> 
> 2) I like the initial proposal to make various SLRU buffers configurable, however, I doubt if it really solves the problem, or just moves it to another place?
> 
> The previous patch you sent was based on some version that contained changes for other slru buffers numbers: 'multixact_offsets_slru_buffers' and 'multixact_members_slru_buffers'. Have you just forgot to attach them? The patch message "[PATCH v2 2/4]" hints that you had 4 patches)
> Meanwhile, I attach the rebased patch to calm down the CFbot. The changes are trivial.
> 
> 2.1) I think that both min and max values for this parameter are too extreme. Have you tested them?
> 

Thank you for your review. 

Indeed, I had 4th patch with tests, but these tests didn't work well: I still did not manage to stress SLRUs to reproduce problem from production...

You are absolutely correct in point 2: I did only tests with sane values. And observed extreme performance degradation with values ~ 64 megabytes. I do not know which highest values should we pick? 1Gb? Or highest possible functioning value?

I greatly appreciate your review, sorry for so long delay. Thanks!

---

From: Anastasia Lubennikova <[email-masked]>
Date: Wed, 7 Oct 2020 17:23:28 +0300

> You are absolutely correct in point 2: I did only tests with sane values. And observed extreme performance degradation with values ~ 64 megabytes. I do not know which highest values should we pick?…

I would go with the values that we consider adequate for this setting. 
As I see, there is no strict rule about it in guc.c and many variables 
have large border values. Anyway, we need to explain it at least in the 
documentation and code comments.

It seems that the default was conservative enough, so it can be also a 
minimal value too. As for maximum, can you provide any benchmark 
results? If we have a peak and a noticeable performance degradation 
after that, we can use it to calculate the preferable maxvalue.

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 26 Oct 2020 04:05:26 +0300

Hi!

> I greatly appreciate your review, sorry for so long delay. Thanks!

I took a look at this patchset.

The 1st and 3rd patches look good to me.  I made just minor improvements.
1) There is still a case when SimpleLruReadPage_ReadOnly() relocks the
SLRU lock, which is already taken in exclusive mode.  I've evaded this
by passing the lock mode as a parameter to
SimpleLruReadPage_ReadOnly().
3) CHECK_FOR_INTERRUPTS() is not needed anymore, because it's called
inside ConditionVariableSleep() if needed.  Also, no current wait
events use slashes, and I don't think we should introduce slashes
here.  Even if we should, then we should also rename existing wait
events to be consistent with a new one.  So, I've renamed the new wait
event to remove the slash.

Regarding the patch 2.  I see the current documentation in the patch
doesn't explain to the user how to set the new parameter.  I think we
should give users an idea what workloads need high values of
multixact_local_cache_entries parameter and what doesn't.  Also, we
should explain the negative aspects of high values
multixact_local_cache_entries.  Ideally, we should get the advantage
without overloading users with new nontrivial parameters, but I don't
have a particular idea here.

I'd like to propose committing 1 and 3, but leave 2 for further review.

------
Regards,
Alexander Korotkov

---

From: Andrey Borodin <[email-masked]>
Date: Mon, 26 Oct 2020 20:45:29 +0500

> 26 окт. 2020 г., в 06:05, Alexander Korotkov <[email-masked]> написал(а):
> 
> Hi!
> 

Thanks for your review, Alexander! 
+1 for avoiding double locking in SimpleLruReadPage_ReadOnly().
Other changes seem correct to me too.

I've tried to find optimal value for cache size and it seems to me that it affects multixact scalability much less than sizes of offsets\members buffers. I concur that patch 2 of the patchset does not seem documented enough.

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 27 Oct 2020 20:02:20 +0300

> I've tried to find optimal value for cache size and it seems to me that it affects multixact scalability much less than sizes of offsets\members buffers. I concur that patch 2 of the patchset does…

Thank you.  I've made a few more minor adjustments to the patchset.
I'm going to push 0001 and 0003 if there are no objections.

------
Regards,
Alexander Korotkov

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 27 Oct 2020 20:23:26 +0300

> I'm going to push 0001 and 0003 if there are no objections.

I get that patchset v5 doesn't pass the tests due to typo in assert.
The fixes version is attached.

------
Regards,
Alexander Korotkov

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 28 Oct 2020 02:36:51 +0100

>The fixes version is attached.

I did a quick review on this patch series. A couple comments:

0001
----

This looks quite suspicious to me - SimpleLruReadPage_ReadOnly is
changed to return information about what lock was used, merely to allow
the callers to do an Assert() that the value is not LW_NONE.

IMO we could achieve exactly the same thing by passing a simple flag
that would say 'make sure we got a lock' or something like that. In
fact, aren't all callers doing the assert? That'd mean we can just do
the check always, without the flag. (I see GetMultiXactIdMembers does
two calls and only checks the second result, but I wonder if that's
intended or omission.)

In any case, it'd make the lwlock.c changes unnecessary, I think.

0002
----

Specifies the number cached MultiXact by backend. Any SLRU lookup ...

should be 'number of cached ...'

0003
----

     * Conditional variable for waiting till the filling of the next multixact
     * will be finished.  See GetMultiXactIdMembers() and RecordNewMultiXact()
     * for details.

Perhaps 'till the next multixact is filled' or 'gets full' would be
better. Not sure.

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?

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 28 Oct 2020 12:34:58 +0500

Tomas, thanks for looking into this!

> 28 окт. 2020 г., в 06:36, Tomas Vondra <[email-masked]> написал(а):
> 
> 

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!

[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

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 28 Oct 2020 22:36:39 +0300

Hi, Tomas!

> the callers to do an Assert() that the value is not LW_NONE.

Yes, but this is not merely to allow callers to do an Assert().
Sometimes in multixacts it could save us some relocks.  So, we can
skip relocking lock to exclusive mode if it's in exclusive already.
Adding Assert() to every caller is probably overkill.

> two calls and only checks the second result, but I wonder if that's

Having just the flag is exactly what the original version by Andrey
did.  But if we have to read two multixact offsets pages or multiple
members page in one GetMultiXactIdMembers()), then it does relocks
from exclusive mode to exclusive mode.  I decide that once we decide
to optimize this locks, this situation is nice to evade.

> In any case, it'd make the lwlock.c changes unnecessary, I think.

I agree that it would be better to not touch lwlock.c.  But I didn't
find a way to evade relocking exclusive mode to exclusive mode without
touching lwlock.c or making code cumbersome in other places.

> should be 'number of cached ...'

Sounds reasonable.

> Perhaps 'till the next multixact is filled' or 'gets full' would be

Sounds reasonable as well.

------
Regards,
Alexander Korotkov

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 29 Oct 2020 00:21:01 +0100

>Adding Assert() to every caller is probably overkill.

Hmm, OK. That can only happen in GetMultiXactIdMembers, which is the
only place where we do retry, right? Do we actually know this makes any
measurable difference? It seems we're mostly imagining that it might
help, but we don't have any actual proof of that (e.g. a workload which
we might benchmark). Or am I missing something?

For me, the extra conditions make it way harder to reason about the
behavior of the code, and I can't convince myself it's worth it.

>touching lwlock.c or making code cumbersome in other places.

Hmm. OK.

regards

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 29 Oct 2020 00:32:43 +0100

Hi,

>Why is it so complicated? It seems that other reproductions of a problem were encountering other locks.

It's not my intention to be mean or anything like that, but to me this
means we don't really understand the problem we're trying to solve. Had
we understood it, we should be able to construct a workload reproducing
the issue ...

I understand what the individual patches are doing, and maybe those
changes are desirable in general. But without any benchmarks from a
plausible workload I find it hard to convince myself that:

(a) it actually will help with the issue you're observing on production

and 

(b) it's actually worth the extra complexity (e.g. the lwlock changes)

I'm willing to invest some of my time into reviewing/testing this, but I
think we badly need better insight into the issue, so that we can build
a workload reproducing it. Perhaps collecting some perf profiles and a
sample of the queries might help, but I assume you already tried that.

regards

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 29 Oct 2020 12:08:21 +0500

> 29 окт. 2020 г., в 04:32, Tomas Vondra <[email-masked]> написал(а):
> 

Thanks, Tomas! This totally makes sense.

Indeed, collecting queries did not help yet. We have loadtest environment equivalent to production (but with 10x less shards), copy of production workload queries. But the problem does not manifest there.
Why do I think the problem is in MultiXacts?
Here is a chart with number of wait events on each host

During the problem MultiXactOffsetControlLock and SLRURead dominate all other lock types. After primary switchover to another node SLRURead continued for a bit there, then disappeared.
Backtraces on standbys during the problem show that most of backends are sleeping in pg_sleep(1000L) and are not included into wait stats on these charts.

Currently I'm considering writing test that directly calls MultiXactIdExpand(), MultiXactIdCreate(), and GetMultiXactIdMembers() from an extension. How do you think, would benchmarks in such tests be meaningful?

Thanks!

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 29 Oct 2020 14:49:33 +0100

>During the problem MultiXactOffsetControlLock and SLRURead dominate all other lock types. After primary switchover to another node SLRURead continued for a bit there, then disappeared.

OK, so most of this seems to be due to SLRURead and
MultiXactOffsetControlLock. Could it be that there were too many
multixact members, triggering autovacuum to prevent multixact
wraparound? That might generate a lot of IO on the SLRU. Are you
monitoring the size of the pg_multixact directory?

>Currently I'm considering writing test that directly calls MultiXactIdExpand(), MultiXactIdCreate(), and GetMultiXactIdMembers() from an extension. How do you think, would benchmarks in such tests b…

I don't know. I'd much rather have a SQL-level benchmark than an
extension doing this kind of stuff.

regards

---

From: Andrey Borodin <[email-masked]>
Date: Mon, 2 Nov 2020 17:45:33 +0500

> 29 окт. 2020 г., в 18:49, Tomas Vondra <[email-masked]> написал(а):
> 

Yes, we had some problems with 'multixact "members" limit exceeded' long time ago.
We tuned autovacuum_multixact_freeze_max_age = 200000000 and vacuum_multixact_freeze_table_age = 75000000 (half of defaults) and since then did not ever encounter this problem (~5 months).
But the MultiXactOffsetControlLock problem persists. Partially the problem was solved by adding more shards. But when one of shards encounters a problem it's either MultiXacts or vacuum causing relation truncation (unrelated to this thread).

Thanks!

---

From: Tomas Vondra <[email-masked]>
Date: Tue, 10 Nov 2020 01:13:22 +0100

Hi,

After the issue reported in [1] got fixed, I've restarted the multi-xact
stress test, hoping to reproduce the issue. But so far no luck :-(

I've started slightly different tests on two machines - on one machine
I've done this:

  a) init.sql

  create table t (a int);
  insert into t select i from generate_series(1,100000000) s(i);
  alter table t add primary key (a);

  b) select.sql

  SELECT * FROM t
   WHERE a = (1+mod(abs(hashint4(extract(epoch from now())::int)),
                    100000000)) FOR KEY SHARE;

  c) pgbench -n -c 32 -j 8 -f select.sql -T $((24*3600)) test

The idea is to have large table and many clients hitting a small random
subset of the rows. A sample of wait events from ~24h run looks like this:

      e_type  |        e_name        |   sum
    ----------+----------------------+----------
     LWLock   | BufferContent        | 13913863
              |                      |  7194679
     LWLock   | WALWrite             |  1710507
     Activity | LogicalLauncherMain  |   726599
     Activity | AutoVacuumMain       |   726127
     Activity | WalWriterMain        |   725183
     Activity | CheckpointerMain     |   604694
     Client   | ClientRead           |   599900
     IO       | WALSync              |   502904
     Activity | BgWriterMain         |   378110
     Activity | BgWriterHibernate    |   348464
     IO       | WALWrite             |   129412
     LWLock   | ProcArray            |     6633
     LWLock   | WALInsert            |     5714
     IO       | SLRUWrite            |     2580
     IPC      | ProcArrayGroupUpdate |     2216
     LWLock   | XactSLRU             |     2196
     Timeout  | VacuumDelay          |     1078
     IPC      | XactGroupUpdate      |      737
     LWLock   | LockManager          |      503
     LWLock   | WALBufMapping        |      295
     LWLock   | MultiXactMemberSLRU  |      267
     IO       | DataFileWrite        |       68
     LWLock   | BufferIO             |       59
     IO       | DataFileRead         |       27
     IO       | DataFileFlush        |       14
     LWLock   | MultiXactGen         |        7
     LWLock   | BufferMapping        |        1

So, nothing particularly interesting - there certainly are not many wait
events related to SLRU.

On the other machine I did this:

  a) init.sql
  create table t (a int primary key);
  insert into t select i from generate_series(1,1000) s(i);

  b) select.sql
  select * from t for key share;

  c) pgbench -n -c 32 -j 8 -f select.sql -T $((24*3600)) test

and the wait events (24h run too) look like this:

      e_type   |        e_name         |   sum
    -----------+-----------------------+----------
     LWLock    | BufferContent         | 20804925
               |                       |  2575369
     Activity  | LogicalLauncherMain   |   745780
     Activity  | AutoVacuumMain        |   745292
     Activity  | WalWriterMain         |   740507
     Activity  | CheckpointerMain      |   737691
     Activity  | BgWriterHibernate     |   731123
     LWLock    | WALWrite              |   570107
     IO        | WALSync               |   452603
     Client    | ClientRead            |   151438
     BufferPin | BufferPin             |    23466
     LWLock    | WALInsert             |    21631
     IO        | WALWrite              |    19050
     LWLock    | ProcArray             |    15082
     Activity  | BgWriterMain          |    14655
     IPC       | ProcArrayGroupUpdate  |     7772
     LWLock    | WALBufMapping         |     3555
     IO        | SLRUWrite             |     1951
     LWLock    | MultiXactGen          |     1661
     LWLock    | MultiXactMemberSLRU   |      359
     LWLock    | MultiXactOffsetSLRU   |      242
     LWLock    | XactSLRU              |      141
     IPC       | XactGroupUpdate       |      104
     LWLock    | LockManager           |       28
     IO        | DataFileRead          |        4
     IO        | ControlFileSyncUpdate |        1
     Timeout   | VacuumDelay           |        1
     IO        | WALInitWrite          |        1

Also nothing particularly interesting - few SLRU wait events.

So unfortunately this does not really reproduce the SLRU locking issues
you're observing - clearly, there has to be something else triggering
it. Perhaps this workload is too simplistic, or maybe we need to run
different queries. Or maybe the hw needs to be somewhat different (more
CPUs? different storage?)

[1]
https://www.postgresql.org/message-id/20201104013205.icogbi773przyny5@development

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Andrey Borodin <[email-masked]>
Date: Tue, 10 Nov 2020 11:16:49 +0500

> 10 нояб. 2020 г., в 05:13, Tomas Vondra <[email-masked]> написал(а):

Tomas, many thanks for looking into this. I figured out that to make multixact sets bigger transactions must hang for a while and lock large set of tuples. But not continuous range to avoid locking on buffer_content.
I did not manage to implement this via pgbench, that's why I was trying to hack on separate go program. But, essentially, no luck either.
I was observing something resemblant though

but this picture was not stable.

How do you collect wait events for aggregation? just insert into some table with cron?

Thanks!

---

From: Tomas Vondra <[email-masked]>
Date: Tue, 10 Nov 2020 19:07:07 +0100

>   pid  |         wait_event         | wait_event_type | state  |                       query                        

Seems we haven't made much progress in reproducing the issue :-( I guess
we'll need to know more about the machine where this happens. Is there
anything special about the hardware/config? Are you monitoring size of
the pg_multixact directory?

> How do you collect wait events for aggregation? just insert into some table with cron?

No, I have a simple shell script (attached) sampling data from
pg_stat_activity regularly. Then I load it into a table and aggregate to
get a summary.

regards

---

From: Thomas Munro <[email-masked]>
Date: Wed, 11 Nov 2020 07:41:39 +1300

> the pg_multixact directory?

Which release was the original problem seen on?

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 13 Nov 2020 16:49:36 +0500

> 10 нояб. 2020 г., в 23:07, Tomas Vondra <[email-masked]> написал(а):
> 

It's Ubuntu 18.04.4 LTS, Intel Xeon E5-2660 v4, 56 CPU cores with 256Gb of RAM.
PostgreSQL 10.14, compiled by gcc 7.5.0, 64-bit

No, unfortunately we do not have signals for SLRU sizes.
3.5Tb mdadm raid10 over 28 SSD drives, 82% full.

First incident triggering investigation was on 2020-04-19, at that time cluster was running on PG 10.11. But I think it was happening before.

I'd say nothing special...

Thanks!

---

From: Gilles Darold <[email-masked]>
Date: Tue, 8 Dec 2020 17:05:34 +0100

Hi,

Some time ago I have encountered a contention on 
MultiXactOffsetControlLock with a performances benchmark. Here are the 
wait event monitoring result with a pooling each 10 seconds and a 30 
minutes run for the benchmarl:

  event_type |           event            |   sum
------------+----------------------------+----------
  Client     | ClientRead                 | 44722952
  LWLock     | MultiXactOffsetControlLock | 30343060
  LWLock     | multixact_offset           | 16735250
  LWLock     | MultiXactMemberControlLock |  1601470
  LWLock     | buffer_content             |   991344
  LWLock     | multixact_member           |   805624
  Lock       | transactionid              |   204997
  Activity   | LogicalLauncherMain        |   198834
  Activity   | CheckpointerMain           |   198834
  Activity   | AutoVacuumMain             |   198469
  Activity   | BgWriterMain               |   184066
  Activity   | WalWriterMain              |   171571
  LWLock     | WALWriteLock               |    72428
  IO         | DataFileRead               |    35708
  Activity   | BgWriterHibernate          |    12741
  IO         | SLRURead                   |     9121
  Lock       | relation                   |     8858
  LWLock     | ProcArrayLock              |     7309
  LWLock     | lock_manager               |     6677
  LWLock     | pg_stat_statements         |     4194
  LWLock     | buffer_mapping             |     3222

After reading this thread I change the value of the buffer size to 32 
and 64 and obtain the following results:

  event_type |           event            |    sum
------------+----------------------------+-----------
  Client     | ClientRead                 | 268297572
  LWLock     | MultiXactMemberControlLock |  65162906
  LWLock     | multixact_member           |  33397714
  LWLock     | buffer_content             |   4737065
  Lock       | transactionid              |   2143750
  LWLock     | SubtransControlLock        |   1318230
  LWLock     | WALWriteLock               |   1038999
  Activity   | LogicalLauncherMain        |    940598
  Activity   | AutoVacuumMain             |    938566
  Activity   | CheckpointerMain           |    799052
  Activity   | WalWriterMain              |    749069
  LWLock     | subtrans                   |    710163
  Activity   | BgWriterHibernate          |    536763
  Lock       | object                     |    514225
  Activity   | BgWriterMain               |    394206
  LWLock     | lock_manager               |    295616
  IO         | DataFileRead               |    274236
  LWLock     | ProcArrayLock              |     77099
  Lock       | tuple                      |     59043
  IO         | CopyFileWrite              |     45611
  Lock       | relation                   |     42714

There was still contention on multixact but less than the first run. I 
have increased the buffers to 128 and 512 and obtain the best results 
for this bench:

  event_type |           event            |    sum
------------+----------------------------+-----------
  Client     | ClientRead                 | 160463037
  LWLock     | MultiXactMemberControlLock |   5334188
  LWLock     | buffer_content             |   5228256
  LWLock     | buffer_mapping             |   2368505
  LWLock     | SubtransControlLock        |   2289977
  IPC        | ProcArrayGroupUpdate       |   1560875
  LWLock     | ProcArrayLock              |   1437750
  Lock       | transactionid              |    825561
  LWLock     | subtrans                   |    772701
  LWLock     | WALWriteLock               |    666138
  Activity   | LogicalLauncherMain        |    492585
  Activity   | CheckpointerMain           |    492458
  Activity   | AutoVacuumMain             |    491548
  LWLock     | lock_manager               |    426531
  Lock       | object                     |    403581
  Activity   | WalWriterMain              |    394668
  Activity   | BgWriterHibernate          |    293112
  Activity   | BgWriterMain               |    195312
  LWLock     | MultiXactGenLock           |    177820
  LWLock     | pg_stat_statements         |    173864
  IO         | DataFileRead               |    173009

I hope these metrics can have some interest to show the utility of this 
patch but unfortunately I can not be more precise and provide reports 
for the entire patch. The problem is that this benchmark is run on an 
application that use PostgreSQL 11 and I can not back port the full 
patch, there was too much changes since PG11. I have just increase the 
size of NUM_MXACTOFFSET_BUFFERS and NUM_MXACTMEMBER_BUFFERS. This allow 
us to triple the number of simultaneous connections between the first 
and the last test.

I know that this report is not really helpful but at least I can give 
more information on the benchmark that was used. This is the proprietary 
zRef benchmark which compares the same Cobol programs (transactional and 
batch) executed both on mainframes and on x86 servers. Instead  of a DB2 
z/os database we use PostgreSQL v11. This test has extensive use of 
cursors (each select, even read only, is executed through a cursor) and 
the contention was observed with update on tables with some foreign 
keys. There is no explicit FOR SHARE on the queries, only some FOR 
UPDATE clauses. I guess that the multixact contention is the result of 
the for share locks produced for FK.

So in our case being able to tune the multixact buffers could help a lot 
to improve the performances.

-- 
Gilles Darold

---

From: Andrey Borodin <[email-masked]>
Date: Tue, 8 Dec 2020 22:52:52 +0500

Hi Gilles!

Many thanks for your message!

> 8 дек. 2020 г., в 21:05, Gilles Darold <[email-masked]> написал(а):
> 
> I know that this report is not really helpful 

Quite contrary - this benchmarks prove that controllable reproduction exists. I've rebased patches for PG11. Can you please benchmark them (without extending SLRU)?

---

From: Gilles Darold <[email-masked]>
Date: Wed, 9 Dec 2020 11:51:36 +0100

Hi Andrey,

Thanks for the backport. I have issue with the first patch "Use shared 
lock in GetMultiXactIdMembers for offsets and members" 
(v1106-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-o.patch) the 
applications are not working anymore when I'm applying it. Also PG 
regression tests are failing too on several part.

    test insert_conflict              ... ok
    test create_function_1            ... FAILED
    test create_type                  ... FAILED
    test create_table                 ... FAILED
    test create_function_2            ... FAILED
    test copy                         ... FAILED
    test copyselect                   ... ok
    test copydml                      ... ok
    test create_misc                  ... FAILED
    test create_operator              ... FAILED
    test create_procedure             ... ok
    test create_index                 ... FAILED
    test index_including              ... ok
    test create_view                  ... FAILED
    test create_aggregate             ... ok
    test create_function_3            ... ok
    test create_cast                  ... ok
    test constraints                  ... FAILED
    test triggers                     ... FAILED
    test inherit                      ...
    ^C

This is also where I left my last try to back port for PG11, I will try 
to fix it again but it could take time to have it working.

Best regards,

-- 
Gilles Darold

---

From: Gilles Darold <[email-masked]>
Date: Wed, 9 Dec 2020 12:06:10 +0100

Le 09/12/2020 à 11:51, Gilles Darold a écrit :
> Also PG regression tests are failing too on several part.

Forget this, I have not run the regression tests in the right repository:

...

=======================
  All 189 tests passed.
=======================

I'm looking why the application is failing.

-- 
Gilles Darold
http://www.darold.net/

---

From: Gilles Darold <[email-masked]>
Date: Thu, 10 Dec 2020 15:45:56 +0100

Hi,

Running tests yesterday with the patches has reported log of failures 
with error on INSERT and UPDATE statements:

    ERROR:  lock MultiXactOffsetControlLock is not held

After a patch review this morning I think I have found what's going 
wrong. In patch 
v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch I think 
there is a missing reinitialisation of the lockmode variable to LW_NONE 
inside the retry loop after the call to LWLockRelease() in 
src/backend/access/transam/multixact.c:1392:GetMultiXactIdMembers(). 
I've attached a new version of the patch for master that include the fix 
I'm using now with PG11 and with which everything works very well now.

I'm running more tests to see the impact on the performances to play 
with multixact_offsets_slru_buffers, multixact_members_slru_buffers and 
multixact_local_cache_entries. I will reports the results later today.

-- 
Gilles Darold
http://www.darold.net/

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 2fe551f17e..2699de033d 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 eb8de7cf32..56bdd04364 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;
@@ -1387,6 +1390,7 @@ retry:
 		{
 			/* Corner case 2: next multixact is still being filled in */
 			LWLockRelease(MultiXactOffsetSLRULock);
+			lockmode = LW_NONE;
 			CHECK_FOR_INTERRUPTS();
 			pg_usleep(1000L);
 			goto retry;
@@ -1395,14 +1399,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 +1422,8 @@ retry:
 
 		if (pageno != prev_pageno)
 		{
-			slotno = SimpleLruReadPage(MultiXactMemberCtl, pageno, true, multi);
+			slotno = SimpleLruReadPage_ReadOnly(MultiXactMemberCtl, pageno,
+												multi, &lockmode);
 			prev_pageno = pageno;
 		}
 
@@ -1441,6 +1446,7 @@ retry:
 		truelength++;
 	}
 
+	Assert(lockmode != LW_NONE);
 	LWLockRelease(MultiXactMemberSLRULock);
 
 	/*
@@ -2733,6 +2739,7 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
 	int			entryno;
 	int			slotno;
 	MultiXactOffset *offptr;
+	LWLockMode	lockmode = LW_NONE;
 
 	Assert(MultiXactState->finishedStartup);
 
@@ -2749,10 +2756,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 cec17cb2ae..e271b37d7b 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 c0763c63e2..ef1dbbe53d 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -2011,6 +2011,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
@@ -2019,7 +2020,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 */
@@ -2036,6 +2037,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 108e652179..ad1dcc7140 100644
--- a/src/backend/storage/lmgr/lwlock.c
+++ b/src/backend/storage/lmgr/lwlock.c
@@ -1067,6 +1067,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
@@ -1827,6 +1829,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,

---

From: Gilles Darold <[email-masked]>
Date: Fri, 11 Dec 2020 18:50:25 +0100

Le 10/12/2020 à 15:45, Gilles Darold a écrit :
>>> 8 дек. 2020 г., в 21:05, Gilles Darold<[email-masked]>  написал(а):
>>>
> v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch I 
> think there is a missing reinitialisation of the lockmode variable to 
> LW_NONE inside the retry loop after the call to LWLockRelease() in 
> I've attached a new version of the patch for master that include the 
> fix I'm using now with PG11 and with which everything works very well now.
>
>
> with multixact_offsets_slru_buffers, multixact_members_slru_buffers 
> and multixact_local_cache_entries. I will reports the results later today.
>

Hi,

Sorry for the delay, I have done some further tests to try to reach the 
limit without bottlenecks on multixact or shared buffers. The tests was 
done on a Microsoft Asure machine with 2TB of RAM and 4 sockets Intel 
Xeon Platinum 8280M (128 cpu). PG configuration:

     max_connections = 4096
     shared_buffers = 64GB
     max_prepared_transactions = 2048
     work_mem = 256MB
     maintenance_work_mem = 2GB
     wal_level = minimal
     synchronous_commit = off
     commit_delay = 1000
     commit_siblings = 10
     checkpoint_timeout = 1h
     max_wal_size = 32GB
     checkpoint_completion_target = 0.9

I have tested with several values for the different buffer's variables 
starting from:

     multixact_offsets_slru_buffers = 64
     multixact_members_slru_buffers = 128
     multixact_local_cache_entries = 256

to the values with the best performances we achieve with this test to 
avoid MultiXactOffsetControlLock or MultiXactMemberControlLock:

     multixact_offsets_slru_buffers = 128
     multixact_members_slru_buffers = 512
     multixact_local_cache_entries = 1024

Also shared_buffers have been increased up to 256GB to avoid 
buffer_mapping contention.

Our last best test reports the following wait events:

      event_type |           event            |    sum
     ------------+----------------------------+-----------
      Client     | ClientRead                 | 321690211
      LWLock     | buffer_content             |   2970016
      IPC        | ProcArrayGroupUpdate       |   2317388
      LWLock     | ProcArrayLock              |   1445828
      LWLock     | WALWriteLock               |   1187606
      LWLock     | SubtransControlLock        |    972889
      Lock       | transactionid              |    840560
      Lock       | relation                   |    587600
      Activity   | LogicalLauncherMain        |    529599
      Activity   | AutoVacuumMain             |    528097

At this stage I don't think we can have better performances by tuning 
these buffers at least with PG11.

About performances gain related to the patch for shared lock in 
GetMultiXactIdMembers unfortunately I can not see a difference with or 
without this patch, it could be related to our particular benchmark. But 
clearly the patch on multixact buffers should be committed as this is 
really helpfull to be able to tuned PG when multixact bottlenecks are found.

Best regards,

-- 
Gilles Darold
LzLabs GmbH
https://www.lzlabs.com/

---

From: Gilles Darold <[email-masked]>
Date: Sun, 13 Dec 2020 10:17:51 +0100

Le 11/12/2020 à 18:50, Gilles Darold a écrit :
> Sorry for the delay, I have done some further tests to try to reach
> the limit without bottlenecks on multixact or shared buffers. The
> tests was done on a Microsoft Asure machine with 2TB of RAM and 4
> sockets Intel Xeon Platinum 8280M (128 cpu). PG configuration:
>
>     max_connections = 4096
>     shared_buffers = 64GB
> without this patch, it could be related to our particular benchmark.
> But clearly the patch on multixact buffers should be committed as this
> is really helpfull to be able to tuned PG when multixact bottlenecks
> are found.

I've done more review on these patches.

1) as reported in my previous message patch 0001 looks useless as it
doesn't allow measurable performances gain.

2) In patch 0004 there is two typo: s/informaion/information/ will fix them

3) the GUC are missing in the postgresql.conf.sample file, see patch in
attachment for a proposal.

Best regards,

-- 
Gilles Darold
LzLabs GmbH
https://www.lzlabs.com/

diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b7fb2ec1fe..1fb33809ee 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -189,6 +189,14 @@
 					# (change requires restart)
 #backend_flush_after = 0		# measured in pages, 0 disables
 
+# - MultiXact Buffers -
+
+#multixact_offsets_slru_buffers = 8	# memory used for MultiXact offsets
+					# (change requires restart)
+#multixact_members_slru_buffers = 16	# memory used for MultiXact members
+					# (change requires restart)
+#multixact_local_cache_entries = 256	# number of cached MultiXact by backend
+					# (change requires restart)
 
 #------------------------------------------------------------------------------
 # WRITE-AHEAD LOG

---

From: Andrey Borodin <[email-masked]>
Date: Sun, 13 Dec 2020 22:24:49 +0500

> 13 дек. 2020 г., в 14:17, Gilles Darold <[email-masked]> написал(а):
> 

Thanks, Gilles! I'll incorporate all your fixes to patchset.
Can you also benchmark conditional variable sleep? The patch "Add conditional variable to wait for next MultXact offset in corner case"?
The problem manifests on Standby when Primary is heavily loaded with MultiXactOffsetControlLock.

Thanks!

---

From: Andrey Borodin <[email-masked]>
Date: Mon, 14 Dec 2020 11:31:32 +0500

> 13 дек. 2020 г., в 22:24, Andrey Borodin <[email-masked]> написал(а):
> 
> 
> 

PFA patches.
Also, I've noted that patch "Add conditional variable to wait for next MultXact offset in corner case" removes CHECK_FOR_INTERRUPTS();, I'm not sure it's correct.

Thanks!

---

From: Gilles Darold <[email-masked]>
Date: Wed, 23 Dec 2020 17:31:17 +0100

Hi Andrey,

Sorry for the response delay, we have run several others tests trying to 
figure out the performances gain per patch but unfortunately we have 
very heratic results. With the same parameters and patches the test 
doesn't returns the same results following the day or the hour of the 
day. This is very frustrating and I suppose that this is related to the 
Azure architecture. The only thing that I am sure is that we had the 
best performances results with all patches and

    multixact_offsets_slru_buffers = 256
    multixact_members_slru_buffers = 512
    multixact_local_cache_entries = 4096

but I can not say if all or part of the patches are improving the 
performances. My feeling is that performances gain related to patches 1 
(shared lock) and 3 (conditional variable) do not have much to do with 
the performances gain compared to just tuning the multixact buffers. 
This is when the multixact contention is observed but perhaps they are 
delaying the contention. It's all the more frustrating that we had a 
test case to reproduce the contention but not the architecture apparently.

Can't do much more at this point.

Best regards,

-- 
Gilles Darold
LzLabs GmbH
http://www.lzlabs.com/

---

From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 22:17:40 +0500

> 23 дек. 2020 г., в 21:31, Gilles Darold <[email-masked]> написал(а):
> 
> Sorry for the response delay, we have run several others tests trying to figure out the performances gain per patch but unfortunately we have very heratic results. With the same parameters and patches the test doesn't returns the same results following the day or the hour of the day. This is very frustrating and I suppose that this is related to the Azure architecture. The only thing that I am sure is that we had the best performances results with all patches and
> 
> but I can not say if all or part of the patches are improving the performances. My feeling is that performances gain related to patches 1 (shared lock) and 3 (conditional variable) do not have much to do with the performances gain compared to just tuning the multixact buffers. This is when the multixact contention is observed but perhaps they are delaying the contention. It's all the more frustrating that we had a test case to reproduce the contention but not the architecture apparently.

Hi! Thanks for the input.
I think we have a consensus here that configuring SLRU size is beneficial for MultiXacts.
There is proposal in nearby thread [0] on changing default value of commit_ts SLRU buffers.
In my experience from time to time there can be problems with subtransactions cured by extending subtrans SLRU.

Let's make all SLRUs configurable?
PFA patch with draft of these changes.

[0] https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql

---

From: Gilles Darold <[email-masked]>
Date: Thu, 11 Mar 2021 16:50:48 +0100

> [0] https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql

The patch doesn't apply anymore in master cause of error: patch failed: 
src/backend/utils/init/globals.c:150

An other remark about this patch is that it should be mentionned in the 
documentation (doc/src/sgml/config.sgml) that the new configuration 
variables need a server restart, for example by adding "This parameter 
can only be set at server start." like for shared_buffers. Patch on 
postgresql.conf mention it.

And some typo to be fixed:

    s/Tipically/Typically/

    s/asincronous/asyncronous/

    s/confugured/configured/

    s/substrnsactions/substransactions/

-- 
Gilles Darold
LzLabs GmbH

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 12 Mar 2021 17:44:02 +0500

> 11 марта 2021 г., в 20:50, Gilles Darold <[email-masked]> написал(а):
> 
> 
> The patch doesn't apply anymore in master cause of error: patch failed: src/backend/utils/init/globals.c:150
> 
> 
> 
> An other remark about this patch is that it should be mentionned in the documentation (doc/src/sgml/config.sgml) that the new configuration variables need a server restart, for example by adding "This parameter can only be set at server start." like for shared_buffers. Patch on postgresql.conf mention it.
> 

Thanks, Gilles! Fixed.

---

From: Gilles Darold <[email-masked]>
Date: Mon, 15 Mar 2021 16:41:04 +0100

Hi Andrey,

I found two problems in this patch, first in src/include/miscadmin.h
multixact_members_slru_buffers is declared twice:

 extern PGDLLIMPORT int max_parallel_workers;
+extern PGDLLIMPORT int multixact_offsets_slru_buffers;
+extern PGDLLIMPORT int multixact_members_slru_buffers;
+extern PGDLLIMPORT int multixact_members_slru_buffers;  <---------
+extern PGDLLIMPORT int subtrans_slru_buffers;

In file src/backend/access/transam/multixact.c the second variable
should be multixact_buffers_slru_buffers and not
multixact_offsets_slru_buffers.

@@ -1848,13 +1848,13 @@ 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);
        SlruPagePrecedesUnitTests(MultiXactOffsetCtl,
MULTIXACT_OFFSETS_PER_PAGE);
        SimpleLruInit(MultiXactMemberCtl,
-                                 "MultiXactMember",
NUM_MULTIXACTMEMBER_BUFFERS, 0,
+                                 "MultiXactMember",
multixact_offsets_slru_buffers, 0,    <------------------
                                  MultiXactMemberSLRULock,
"pg_multixact/members",
                                  LWTRANCHE_MULTIXACTMEMBER_BUFFER,
                                  SYNC_HANDLER_MULTIXACT_MEMBER);

Please fix them so that I can end the review.

-- 
Gilles Darold
LzLabs GmbH
http://www.lzlabs.com/

---

From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:31:56 +1300

Hi Andrey,

On Sat, Mar 13, 2021 at 1:44 AM Andrey Borodin <[email-masked]> wrote:
> [v10]

+int            multixact_offsets_slru_buffers = 8;
+int            multixact_members_slru_buffers = 16;
+int            subtrans_slru_buffers = 32;
+int            notify_slru_buffers = 8;
+int            serial_slru_buffers = 16;
+int            clog_slru_buffers = 0;
+int            commit_ts_slru_buffers = 0;

I don't think we should put "slru" (the name of the buffer replacement
algorithm, implementation detail) in the GUC names.

+        It defaults to 0, in this case CLOG size is taken as
<varname>shared_buffers</varname> / 512.

We already know that increasing the number of CLOG buffers above the
current number hurts as the linear search begins to dominate
(according to the commit message for 5364b357), and it doesn't seem
great to ship a new feature that melts your CPU when you turn it up.
Perhaps, to ship this, we need to introduce a buffer mapping table?  I
have attached a "one coffee" attempt at that, on top of your v10 patch
(unmodified), for discussion.  It survives basic testing but I don't
know how it performs.
From 4817d16cfb6704d43a7bef12648e753d239c809c Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v10 1/2] Make all SLRU buffer sizes configurable

---
 doc/src/sgml/config.sgml                      | 108 ++++++++++++++++++
 src/backend/access/transam/clog.c             |   6 +
 src/backend/access/transam/commit_ts.c        |   5 +-
 src/backend/access/transam/multixact.c        |   8 +-
 src/backend/access/transam/subtrans.c         |   5 +-
 src/backend/commands/async.c                  |   8 +-
 src/backend/storage/lmgr/predicate.c          |   4 +-
 src/backend/utils/init/globals.c              |   8 ++
 src/backend/utils/misc/guc.c                  |  77 +++++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  16 +++
 src/include/access/multixact.h                |   4 -
 src/include/access/subtrans.h                 |   3 -
 src/include/commands/async.h                  |   5 -
 src/include/miscadmin.h                       |   8 ++
 src/include/storage/predicate.h               |   4 -
 15 files changed, 240 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ddc6d789d8..0adcf0efaf 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1886,6 +1886,114 @@ 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 information about offsets of multiple row lockers (caused by SELECT FOR UPDATE and others).
+        It defaults to 64 kilobytes (<literal>64KB</literal>).
+        This parameter can only be set at server start.
+       </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 information about XIDs of multiple row lockers. Typically <varname>multixact_members_slru_buffers</varname>
+        is twice more than <varname>multixact_offsets_slru_buffers</varname>.
+        It defaults to 128 kilobytes (<literal>128KB</literal>).
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-subtrans-buffers" xreflabel="subtrans_slru_buffers">
+      <term><varname>subtrans_slru_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>subtrans_slru_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used for subtransactions.
+        It defaults to 256 kilobytes (<literal>256KB</literal>).
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-notify-buffers" xreflabel="notify_slru_buffers">
+      <term><varname>notify_slru_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>notify_slru_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used for asyncronous notifications (NOTIFY, LISTEN).
+        It defaults to 64 kilobytes (<literal>64KB</literal>).
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-serial-buffers" xreflabel="serial_slru_buffers">
+      <term><varname>serial_slru_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>serial_slru_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used for predicate locks.
+        It defaults to 128 kilobytes (<literal>128KB</literal>).
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-clog-buffers" xreflabel="clog_slru_buffers">
+      <term><varname>clog_slru_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>clog_slru_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used for CLOG.
+        It defaults to 0, in this case CLOG size is taken as <varname>shared_buffers</varname> / 512.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-commit-ts-buffers" xreflabel="commit_ts_slru_buffers">
+      <term><varname>commit_ts_slru_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>commit_ts_slru_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used for commit timestamps.
+        It defaults to 0, in this case CLOG size is taken as <varname>shared_buffers</varname> / 512.
+        This parameter can only be set at server start.
+       </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/clog.c b/src/backend/access/transam/clog.c
index 6fa4713fb4..e1d34aa361 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -659,6 +659,9 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
 /*
  * Number of shared CLOG buffers.
  *
+ * If values is configured via GUC - just use given value. Otherwise
+ * apply following euristics.
+ *
  * On larger multi-processor systems, it is possible to have many CLOG page
  * requests in flight at one time which could lead to disk access for CLOG
  * page if the required page is not found in memory.  Testing revealed that we
@@ -675,6 +678,9 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
 Size
 CLOGShmemBuffers(void)
 {
+	/* consider 0 and 1 as unset GUC */
+	if (clog_slru_buffers > 1)
+		return clog_slru_buffers;
 	return Min(128, Max(4, NBuffers / 512));
 }
 
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 48e8d66286..7de3bca63d 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -530,7 +530,10 @@ pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
 Size
 CommitTsShmemBuffers(void)
 {
-	return Min(16, Max(4, NBuffers / 1024));
+	/* consider 0 and 1 as unset GUC */
+	if (commit_ts_slru_buffers > 1)
+		return commit_ts_slru_buffers;
+	return Min(16, Max(4, NBuffers / 512));
 }
 
 /*
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 1f9f1a1fa1..370c01e72b 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1831,8 +1831,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;
 }
 
 	SimpleLruInit(MultiXactOffsetCtl,
-				  "MultiXactOffset", NUM_MULTIXACTOFFSET_BUFFERS, 0,
+				  "MultiXactOffset", multixact_offsets_slru_buffers, 0,
 				  MultiXactOffsetSLRULock, "pg_multixact/offsets",
 				  LWTRANCHE_MULTIXACTOFFSET_BUFFER,
 				  SYNC_HANDLER_MULTIXACT_OFFSET);
 	SlruPagePrecedesUnitTests(MultiXactOffsetCtl, MULTIXACT_OFFSETS_PER_PAGE);
 	SimpleLruInit(MultiXactMemberCtl,
-				  "MultiXactMember", NUM_MULTIXACTMEMBER_BUFFERS, 0,
+				  "MultiXactMember", multixact_offsets_slru_buffers, 0,
 				  MultiXactMemberSLRULock, "pg_multixact/members",
 				  LWTRANCHE_MULTIXACTMEMBER_BUFFER,
 				  SYNC_HANDLER_MULTIXACT_MEMBER);
diff --git a/src/backend/access/transam/subtrans.c b/src/backend/access/transam/subtrans.c
index 6a8e521f89..0c24353d3a 100644
--- a/src/backend/access/transam/subtrans.c
+++ b/src/backend/access/transam/subtrans.c
@@ -31,6 +31,7 @@
 #include "access/slru.h"
 #include "access/subtrans.h"
 #include "access/transam.h"
+#include "miscadmin.h"
 #include "pg_trace.h"
 #include "utils/snapmgr.h"
 
@@ -184,14 +185,14 @@ SubTransGetTopmostTransaction(TransactionId xid)
 Size
 SUBTRANSShmemSize(void)
 {
-	return SimpleLruShmemSize(NUM_SUBTRANS_BUFFERS, 0);
+	return SimpleLruShmemSize(subtrans_slru_buffers, 0);
 }
 
 void
 SUBTRANSShmemInit(void)
 {
 	SubTransCtl->PagePrecedes = SubTransPagePrecedes;
-	SimpleLruInit(SubTransCtl, "Subtrans", NUM_SUBTRANS_BUFFERS, 0,
+	SimpleLruInit(SubTransCtl, "Subtrans", subtrans_slru_buffers, 0,
 				  SubtransSLRULock, "pg_subtrans",
 				  LWTRANCHE_SUBTRANS_BUFFER, SYNC_HANDLER_NONE);
 	SlruPagePrecedesUnitTests(SubTransCtl, SUBTRANS_XACTS_PER_PAGE);
diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c
index 4b16fb5682..f5c5592057 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -107,7 +107,7 @@
  * frontend during startup.)  The above design guarantees that notifies from
  * other backends will never be missed by ignoring self-notifies.
  *
- * The amount of shared memory used for notify management (NUM_NOTIFY_BUFFERS)
+ * The amount of shared memory used for notify management (notify_slru_buffers)
  * can be varied without affecting anything but performance.  The maximum
  * amount of notification data that can be queued at one time is determined
  * by slru.c's wraparound limit; see QUEUE_MAX_PAGE below.
@@ -225,7 +225,7 @@ typedef struct QueuePosition
  *
  * Resist the temptation to make this really large.  While that would save
  * work in some places, it would add cost in others.  In particular, this
- * should likely be less than NUM_NOTIFY_BUFFERS, to ensure that backends
+ * should likely be less than notify_slru_buffers, to ensure that backends
  * catch up before the pages they'll need to read fall out of SLRU cache.
  */
 #define QUEUE_CLEANUP_DELAY 4
@@ -514,7 +514,7 @@ AsyncShmemSize(void)
 	size = mul_size(MaxBackends + 1, sizeof(QueueBackendStatus));
 	size = add_size(size, offsetof(AsyncQueueControl, backend));
 
-	size = add_size(size, SimpleLruShmemSize(NUM_NOTIFY_BUFFERS, 0));
+	size = add_size(size, SimpleLruShmemSize(notify_slru_buffers, 0));
 
 	return size;
 }
@@ -562,7 +562,7 @@ AsyncShmemInit(void)
 	 * Set up SLRU management of the pg_notify data.
 	 */
 	NotifyCtl->PagePrecedes = asyncQueuePagePrecedes;
-	SimpleLruInit(NotifyCtl, "Notify", NUM_NOTIFY_BUFFERS, 0,
+	SimpleLruInit(NotifyCtl, "Notify", notify_slru_buffers, 0,
 				  NotifySLRULock, "pg_notify", LWTRANCHE_NOTIFY_BUFFER,
 				  SYNC_HANDLER_NONE);
 
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index d493aeef0f..fad8cc572e 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -872,7 +872,7 @@ SerialInit(void)
 	 */
 	SerialSlruCtl->PagePrecedes = SerialPagePrecedesLogically;
 	SimpleLruInit(SerialSlruCtl, "Serial",
-				  NUM_SERIAL_BUFFERS, 0, SerialSLRULock, "pg_serial",
+				  serial_slru_buffers, 0, SerialSLRULock, "pg_serial",
 				  LWTRANCHE_SERIAL_BUFFER, SYNC_HANDLER_NONE);
 #ifdef USE_ASSERT_CHECKING
 	SerialPagePrecedesLogicallyUnitTests();
@@ -1395,7 +1395,7 @@ PredicateLockShmemSize(void)
 
 	/* Shared memory structures for SLRU tracking of old committed xids. */
 	size = add_size(size, sizeof(SerialControlData));
-	size = add_size(size, SimpleLruShmemSize(NUM_SERIAL_BUFFERS, 0));
+	size = add_size(size, SimpleLruShmemSize(serial_slru_buffers, 0));
 
 	return size;
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 73e0a672ae..f163ca17e9 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -148,3 +148,11 @@ int64		VacuumPageDirty = 0;
 
 int			VacuumCostBalance = 0;	/* working state for vacuum */
 bool		VacuumCostActive = false;
+
+int			multixact_offsets_slru_buffers = 8;
+int			multixact_members_slru_buffers = 16;
+int			subtrans_slru_buffers = 32;
+int			notify_slru_buffers = 8;
+int			serial_slru_buffers = 16;
+int			clog_slru_buffers = 0;
+int			commit_ts_slru_buffers = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 0c5dc4d3e8..b65a4ae9ce 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2305,6 +2305,83 @@ 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
+	},
+
+	{
+		{"subtrans_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for substransactions SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&subtrans_slru_buffers,
+		32, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"notify_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for asyncronous notifications SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&notify_slru_buffers,
+		8, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"serial_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for predicate locks SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&serial_slru_buffers,
+		16, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"clog_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for commit log SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&serial_slru_buffers,
+		0, 0, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"commit_ts_slru_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for commit timestamps SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&commit_ts_slru_buffers,
+		0, 0, 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b234a6bfe6..308fd565d3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -190,6 +190,22 @@
 					# (change requires restart)
 #backend_flush_after = 0		# measured in pages, 0 disables
 
+# - SLRU Buffers -
+
+#multixact_offsets_slru_buffers = 8	# memory used for MultiXact offsets
+					# (change requires restart)
+#multixact_members_slru_buffers = 16	# memory used for MultiXact members
+					# (change requires restart)
+#subtrans_slru_buffers = 32			# memory used for subtransactions
+					# (change requires restart)
+#notify_slru_buffers = 8			# memory used for asynchronous notifications
+					# (change requires restart)
+#serial_slru_buffers = 16			# memory used for predicate locks
+					# (change requires restart)
+#clog_slru_buffers = 0				# memory used for CLOG
+					# (change requires restart)
+#commit_ts_slru_buffers = 0			# memory used for commit timestamps
+					# (change requires restart)
 
 #------------------------------------------------------------------------------
 # WRITE-AHEAD LOG
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index 4bbb035eae..97c0a46376 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/access/subtrans.h b/src/include/access/subtrans.h
index d0ab44ae82..ca0999056e 100644
--- a/src/include/access/subtrans.h
+++ b/src/include/access/subtrans.h
@@ -11,9 +11,6 @@
 #ifndef SUBTRANS_H
 #define SUBTRANS_H
 
-/* Number of SLRU buffers to use for subtrans */
-#define NUM_SUBTRANS_BUFFERS	32
-
 extern void SubTransSetParent(TransactionId xid, TransactionId parent);
 extern TransactionId SubTransGetParent(TransactionId xid);
 extern TransactionId SubTransGetTopmostTransaction(TransactionId xid);
diff --git a/src/include/commands/async.h b/src/include/commands/async.h
index 9217f66b91..fa831e3721 100644
--- a/src/include/commands/async.h
+++ b/src/include/commands/async.h
@@ -15,11 +15,6 @@
 
 #include <signal.h>
 
-/*
- * The number of SLRU page buffers we use for the notification queue.
- */
-#define NUM_NOTIFY_BUFFERS	8
-
 extern bool Trace_notify;
 extern volatile sig_atomic_t notifyInterruptPending;
 
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 013850ac28..3d9f585fb9 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -162,6 +162,14 @@ 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_members_slru_buffers;
+extern PGDLLIMPORT int subtrans_slru_buffers;
+extern PGDLLIMPORT int notify_slru_buffers;
+extern PGDLLIMPORT int serial_slru_buffers;
+extern PGDLLIMPORT int clog_slru_buffers;
+extern PGDLLIMPORT int commit_ts_slru_buffers;
 
 extern PGDLLIMPORT int MyProcPid;
 extern PGDLLIMPORT pg_time_t MyStartTime;
diff --git a/src/include/storage/predicate.h b/src/include/storage/predicate.h
index 152b698611..c72779bd88 100644
--- a/src/include/storage/predicate.h
+++ b/src/include/storage/predicate.h
@@ -26,10 +26,6 @@ extern int	max_predicate_locks_per_xact;
 extern int	max_predicate_locks_per_relation;
 extern int	max_predicate_locks_per_page;
 
-
-/* Number of SLRU buffers to use for Serial SLRU */
-#define NUM_SERIAL_BUFFERS		16
-
 /*
  * A handle used for sharing SERIALIZABLEXACT objects between the participants
  * in a parallel query.
-- 
2.30.1

From 692d03167f4124e6daabdb8d4a72ff0b494efe96 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v10 2/2] Add buffer mapping table for SLRUs.

---
 src/backend/access/transam/slru.c | 87 ++++++++++++++++++++++++++++---
 src/include/access/slru.h         |  2 +
 2 files changed, 83 insertions(+), 6 deletions(-)

diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 82149ad782..2d6e84266e 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -58,6 +58,8 @@
 #include "pgstat.h"
 #include "storage/fd.h"
 #include "storage/shmem.h"
+#include "utils/dynahash.h"
+#include "utils/hsearch.h"
 
 #define SlruFileName(ctl, path, seg) \
 	snprintf(path, MAXPGPATH, "%s/%04X", (ctl)->Dir, seg)
@@ -79,6 +81,12 @@ typedef struct SlruWriteAllData
 
 typedef struct SlruWriteAllData *SlruWriteAll;
 
+typedef struct SlruMappingTableEntry
+{
+	int			pageno;
+	int			slotno;
+} SlruMappingTableEntry;
+
 /*
  * Populate a file tag describing a segment file.  We only use the segment
  * number, since we can derive everything else we need by having separate
@@ -146,6 +154,9 @@ static int	SlruSelectLRUPage(SlruCtl ctl, int pageno);
 static bool SlruScanDirCbDeleteCutoff(SlruCtl ctl, char *filename,
 									  int segpage, void *data);
 static void SlruInternalDeleteSegment(SlruCtl ctl, int segno);
+static void	SlruMappingAdd(SlruCtl ctl, int pageno, int slotno);
+static void	SlruMappingRemove(SlruCtl ctl, int pageno);
+static int	SlruMappingFind(SlruCtl ctl, int pageno);
 
 /*
  * Initialization of shared memory
@@ -168,7 +179,8 @@ SimpleLruShmemSize(int nslots, int nlsns)
 	if (nlsns > 0)
 		sz += MAXALIGN(nslots * nlsns * sizeof(XLogRecPtr));	/* group_lsn[] */
 
-	return BUFFERALIGN(sz) + BLCKSZ * nslots;
+	return BUFFERALIGN(sz) + BLCKSZ * nslots +
+		hash_estimate_size(nslots, sizeof(SlruMappingTableEntry));
 }
 
 /*
@@ -187,6 +199,9 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 			  LWLock *ctllock, const char *subdir, int tranche_id,
 			  SyncRequestHandler sync_handler)
 {
+	char		mapping_table_name[SHMEM_INDEX_KEYSIZE];
+	HASHCTL		mapping_table_info;
+	HTAB	   *mapping_table;
 	SlruShared	shared;
 	bool		found;
 
@@ -258,11 +273,21 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 	else
 		Assert(found);
 
+	/* Create or find the buffer mapping table. */
+	memset(&mapping_table_info, 0, sizeof(mapping_table_info));
+	mapping_table_info.keysize = sizeof(int);
+	mapping_table_info.entrysize = sizeof(SlruMappingTableEntry);
+	snprintf(mapping_table_name, sizeof(mapping_table_name),
+			 "%s Mapping Table", name);
+	mapping_table = ShmemInitHash(mapping_table_name, nslots, nslots,
+								  &mapping_table_info, HASH_ELEM | HASH_BLOBS);
+
 	/*
 	 * Initialize the unshared control struct, including directory path. We
 	 * assume caller set PagePrecedes.
 	 */
 	ctl->shared = shared;
+	ctl->mapping_table = mapping_table;
 	ctl->sync_handler = sync_handler;
 	strlcpy(ctl->Dir, subdir, sizeof(ctl->Dir));
 }
@@ -289,6 +314,9 @@ SimpleLruZeroPage(SlruCtl ctl, int pageno)
 		   shared->page_number[slotno] == pageno);
 
 	/* Mark the slot as containing this page */
+	if (shared->page_status[slotno] == SLRU_PAGE_VALID)
+		SlruMappingRemove(ctl, shared->page_number[slotno]);
+	SlruMappingAdd(ctl, pageno, slotno);
 	shared->page_number[slotno] = pageno;
 	shared->page_status[slotno] = SLRU_PAGE_VALID;
 	shared->page_dirty[slotno] = true;
@@ -362,7 +390,10 @@ SimpleLruWaitIO(SlruCtl ctl, int slotno)
 		{
 			/* indeed, the I/O must have failed */
 			if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
+			{
+				SlruMappingRemove(ctl, shared->page_number[slotno]);
 				shared->page_status[slotno] = SLRU_PAGE_EMPTY;
+			}
 			else				/* write_in_progress */
 			{
 				shared->page_status[slotno] = SLRU_PAGE_VALID;
@@ -436,6 +467,7 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 				!shared->page_dirty[slotno]));
 
 		/* Mark the slot read-busy */
+		SlruMappingAdd(ctl, pageno, slotno);
 		shared->page_number[slotno] = pageno;
 		shared->page_status[slotno] = SLRU_PAGE_READ_IN_PROGRESS;
 		shared->page_dirty[slotno] = false;
@@ -459,7 +491,13 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 			   shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS &&
 			   !shared->page_dirty[slotno]);
 
-		shared->page_status[slotno] = ok ? SLRU_PAGE_VALID : SLRU_PAGE_EMPTY;
+		if (ok)
+			shared->page_status[slotno] = SLRU_PAGE_VALID;
+		else
+		{
+			SlruMappingRemove(ctl, pageno);
+			shared->page_status[slotno] =  SLRU_PAGE_EMPTY;
+		}
 
 		LWLockRelease(&shared->buffer_locks[slotno].lock);
 
@@ -1029,11 +1067,12 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */
 
 		/* See if page already has a buffer assigned */
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		slotno = SlruMappingFind(ctl, pageno);
+		if (slotno >= 0)
 		{
-			if (shared->page_number[slotno] == pageno &&
-				shared->page_status[slotno] != SLRU_PAGE_EMPTY)
-				return slotno;
+			Assert(shared->page_number[slotno] == pageno);
+			Assert(shared->page_status[slotno] != SLRU_PAGE_EMPTY);
+			return slotno;
 		}
 
 		/*
@@ -1266,6 +1305,7 @@ restart:;
 		if (shared->page_status[slotno] == SLRU_PAGE_VALID &&
 			!shared->page_dirty[slotno])
 		{
+			SlruMappingRemove(ctl, shared->page_number[slotno]);
 			shared->page_status[slotno] = SLRU_PAGE_EMPTY;
 			continue;
 		}
@@ -1348,6 +1388,7 @@ restart:
 		if (shared->page_status[slotno] == SLRU_PAGE_VALID &&
 			!shared->page_dirty[slotno])
 		{
+			SlruMappingRemove(ctl, shared->page_number[slotno]);
 			shared->page_status[slotno] = SLRU_PAGE_EMPTY;
 			continue;
 		}
@@ -1609,3 +1650,37 @@ SlruSyncFileTag(SlruCtl ctl, const FileTag *ftag, char *path)
 	errno = save_errno;
 	return result;
 }
+
+static int
+SlruMappingFind(SlruCtl ctl, int pageno)
+{
+	SlruMappingTableEntry *mapping;
+
+	mapping = hash_search(ctl->mapping_table, &pageno, HASH_FIND, NULL);
+	if (mapping)
+		return mapping->pageno;
+
+	return -1;
+}
+
+static void
+SlruMappingAdd(SlruCtl ctl, int pageno, int slotno)
+{
+	SlruMappingTableEntry *mapping;
+	bool		found PG_USED_FOR_ASSERTS_ONLY;
+
+	mapping = hash_search(ctl->mapping_table, &pageno, HASH_ENTER, &found);
+	mapping->slotno = slotno;
+
+	Assert(!found);
+}
+
+static void
+SlruMappingRemove(SlruCtl ctl, int pageno)
+{
+	bool		found PG_USED_FOR_ASSERTS_ONLY;
+
+	hash_search(ctl->mapping_table, &pageno, HASH_REMOVE, &found);
+
+	Assert(found);
+}
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index dd52e8cec7..8aa3efc0ee 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -16,6 +16,7 @@
 #include "access/xlogdefs.h"
 #include "storage/lwlock.h"
 #include "storage/sync.h"
+#include "utils/hsearch.h"

 /*
@@ -110,6 +111,7 @@ typedef SlruSharedData *SlruShared;
 typedef struct SlruCtlData
 {
 	SlruShared	shared;
+	HTAB	   *mapping_table;
 
 	/*
 	 * Which sync handler function to use when handing sync requests over to
-- 
2.30.1

---

From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 14:03:38 +1300

> (unmodified), for discussion.  It survives basic testing but I don't

Hrrr... Cfbot showed an assertion failure.  Here's the two coffee
version with a couple of silly mistakes fixed.
From 4817d16cfb6704d43a7bef12648e753d239c809c Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v11 1/2] Make all SLRU buffer sizes configurable

---

 
 
 /*

 	return size;
 }

 
 void
 SUBTRANSShmemInit(void)
 {

 	return size;
 }

 
 	return size;
 }

+	{
 
+# - SLRU Buffers -
+

 

 #include <signal.h>
 
-/*

 
-

From 65600b53939c34abf43e62f3f59be5671c43d301 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v11 2/2] Add buffer mapping table for SLRUs.

---

index 82149ad782..487585bb60 100644

 
 
 /*

 /*

 
+	if (shared->page_status[slotno] != SLRU_PAGE_EMPTY)

 

 
 		/*
+		return mapping->slotno;

 /*
 
 	/*

---

From: Thomas Munro <[email-masked]>
Date: Fri, 26 Mar 2021 16:46:06 +1300

Hi Andrey, all,

I propose some changes, and I'm attaching a new version:

I renamed the GUCs as clog_buffers etc (no "_slru_").  I fixed some
copy/paste mistakes where the different GUCs were mixed up.  I made
some changes to the .conf.sample.  I rewrote the documentation so that
it states the correct unit and defaults, and refers to the
subdirectories that are cached by these buffers instead of trying to
give a new definition of each of the SLRUs.

Do you like those changes?

Some things I thought about but didn't change:

I'm not entirely sure if we should use the internal and historical
names well known to hackers (CLOG), or the visible directory names (I
mean, we could use pg_xact_buffers instead of clog_buffers).  I am not
sure why these GUCs need to be PGDLLIMPORT, but I see that NBuffers is
like that.

I wanted to do some very simple smoke testing of CLOG sizes on my
local development machine:

  pgbench -i -s1000 postgres
  pgbench -t4000000 -c8 -j8 -Mprepared postgres

I disabled autovacuum after running that just to be sure it wouldn't
interfere with my experiment:

  alter table pgbench_accounts set (autovacuum_enabled = off);

Then I shut the cluster down and made a copy, so I could do some
repeated experiments from the same initial conditions each time.  At
this point I had 30 files 0000-001E under pg_xact, holding 256kB = ~1
million transactions each.  It'd take ~960 buffers to cache it all.
So how long does VACUUM FREEZE pgbench_accounts take?

I tested with just the 0001 patch, and also with the 0002 patch
(improved version, attached):

clog_buffers=128:  0001=2:28.499, 0002=2:17:891
clog_buffers=1024: 0001=1:38.485, 0002=1:29.701

I'm sure the speedup of the 0002 patch can be amplified by increasing
the number of transactions referenced in the table OR number of
clog_buffers, considering that the linear search produces
O(transactions * clog_buffers) work.  That was 32M transactions and
8MB of CLOG, but I bet if you double both of those numbers once or
twice things start to get hot.  I don't see why you shouldn't be able
to opt to cache literally all of CLOG if you want (something like 50MB
assuming default autovacuum_freeze_max_age, scale to taste, up to
512MB for the theoretical maximum useful value).

I'm not saying the 0002 patch is bug-free yet though, it's a bit finickity.
From e7a8b9a0e27de80bab13f004b4d7e3fa1da677c4 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v12 1/2] Make all SLRU buffer sizes configurable.

Provide new GUCs to set the number of buffers, instead of using hard
coded defaults.

Author: Andrey M. Borodin <[email-masked]>
Reviewed-by: Anastasia Lubennikova <[email-masked]>
Reviewed-by: Tomas Vondra <[email-masked]>
Reviewed-by: Alexander Korotkov <[email-masked]>
Reviewed-by: Gilles Darold <[email-masked]>
Reviewed-by: Thomas Munro <[email-masked]>
Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 doc/src/sgml/config.sgml                      | 137 ++++++++++++++++++
 src/backend/access/transam/clog.c             |   6 +
 src/backend/access/transam/commit_ts.c        |   5 +-
 src/backend/access/transam/multixact.c        |   8 +-
 src/backend/access/transam/subtrans.c         |   5 +-
 src/backend/commands/async.c                  |   8 +-
 src/backend/storage/lmgr/predicate.c          |   4 +-
 src/backend/utils/init/globals.c              |   8 +
 src/backend/utils/misc/guc.c                  |  77 ++++++++++
 src/backend/utils/misc/postgresql.conf.sample |   9 ++
 src/include/access/multixact.h                |   4 -
 src/include/access/subtrans.h                 |   3 -
 src/include/commands/async.h                  |   5 -
 src/include/miscadmin.h                       |   7 +
 src/include/storage/predicate.h               |   4 -
 15 files changed, 261 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ddc6d789d8..f1112bfa9c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1886,6 +1886,143 @@ include_dir 'conf.d'
        </para>
       </listitem>
      </varlistentry>
+     
+    <varlistentry id="guc-multixact-offsets-buffers" xreflabel="multixact_offsets_buffers">
+      <term><varname>multixact_offsets_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>multixact_offsets_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_multixact/offsets</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+
+    <varlistentry id="guc-multixact-members-buffers" xreflabel="multixact_members_buffers">
+      <term><varname>multixact_members_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>multixact_members_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_multixact/members</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>16</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-subtrans-buffers" xreflabel="subtrans_buffers">
+      <term><varname>subtrans_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>subtrans_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_subtrans</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-notify-buffers" xreflabel="notify_buffers">
+      <term><varname>notify_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>notify_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_notify</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-serial-buffers" xreflabel="serial_buffers">
+      <term><varname>serial_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>serial_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_serial</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>16</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-clog-buffers" xreflabel="clog_buffers">
+      <term><varname>clog_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>clog_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to used to cache the contents
+        of <literal>pg_xact</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>0</literal>, which requests
+        <varname>shared_buffers</varname> / 512, but not more than 128 or
+        fewer than 4 blocks.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-commit-ts-buffers" xreflabel="commit_ts_buffers">
+      <term><varname>commit_ts_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>commit_ts_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to be used to cache the cotents of
+        <literal>pg_commit_ts</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>0</literal>, which requests
+        <varname>shared_buffers</varname> / 512, but not more than 128 or
+        fewer than 16 blocks.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
 
index 6fa4713fb4..0318e8ff59 100644
+	if (clog_buffers > 1)
+		return clog_buffers;
 
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 268bdba339..0d2632b90e 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -530,7 +530,10 @@ pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
 Size
 CommitTsShmemBuffers(void)
 {
-	return Min(16, Max(4, NBuffers / 1024));
+	/* consider 0 and 1 as unset GUC */
+	if (commit_ts_buffers > 1)
+		return commit_ts_buffers;
+	return Min(16, Max(4, NBuffers / 512));
 }
 
 /*
index 1f9f1a1fa1..21787765e2 100644
 
 	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_buffers, 0));
+	size = add_size(size, SimpleLruShmemSize(multixact_members_buffers, 0));
 
 	return size;
 }
 
+				  "MultiXactOffset", multixact_offsets_buffers, 0,
+				  "MultiXactMember", multixact_offsets_buffers, 0,
index 6a8e521f89..785f2520fd 100644
 
@@ -184,14 +185,14 @@ SubTransGetTopmostTransaction(TransactionId xid)
 Size
 SUBTRANSShmemSize(void)
 {
-	return SimpleLruShmemSize(NUM_SUBTRANS_BUFFERS, 0);
+	return SimpleLruShmemSize(subtrans_buffers, 0);
 }
 
 void
 SUBTRANSShmemInit(void)
 {
+	SimpleLruInit(SubTransCtl, "Subtrans", subtrans_buffers, 0,
index 4b16fb5682..de17f52cd7 100644
+ * The amount of shared memory used for notify management (notify_buffers)
+ * should likely be less than notify_buffers, to ensure that backends
 
-	size = add_size(size, SimpleLruShmemSize(NUM_NOTIFY_BUFFERS, 0));
+	size = add_size(size, SimpleLruShmemSize(notify_buffers, 0));
 
 	return size;
 }
+	SimpleLruInit(NotifyCtl, "Notify", notify_buffers, 0,
 
index d493aeef0f..b1f4f1651d 100644
+				  serial_buffers, 0, SerialSLRULock, "pg_serial",
 
 	/* Shared memory structures for SLRU tracking of old committed xids. */
 	size = add_size(size, sizeof(SerialControlData));
-	size = add_size(size, SimpleLruShmemSize(NUM_SERIAL_BUFFERS, 0));
+	size = add_size(size, SimpleLruShmemSize(serial_buffers, 0));
 
 	return size;
 }
index 73e0a672ae..e90275be6c 100644
 
 int			VacuumCostBalance = 0;	/* working state for vacuum */
 bool		VacuumCostActive = false;
+
+int			multixact_offsets_buffers = 8;
+int			multixact_members_buffers = 16;
+int			subtrans_buffers = 32;
+int			notify_buffers = 8;
+int			serial_buffers = 16;
+int			clog_buffers = 0;
+int			commit_ts_buffers = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 0c5dc4d3e8..003bc820d2 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2305,6 +2305,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"multixact_offsets_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_buffers,
+		8, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"multixact_members_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_buffers,
+		16, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"subtrans_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for substransactions SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&subtrans_buffers,
+		32, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"notify_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for asyncronous notifications SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&notify_buffers,
+		8, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"serial_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for predicate locks SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&serial_buffers,
+		16, 2, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"clog_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for commit log SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&clog_buffers,
+		0, 0, INT_MAX / 2,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"commit_ts_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for commit timestamps SLRU."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&commit_ts_buffers,
+		0, 0, 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b234a6bfe6..1b8515989b 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -190,6 +190,15 @@
 					# (change requires restart)
 #backend_flush_after = 0		# measured in pages, 0 disables
 
+# - SLRU Buffers (change requires restart) -
+
+#clog_buffers = 0			# memory for pg_xact (0 = auto)
+#subtrans_buffers = 32			# memory for pg_subtrans
+#multixact_offsets_buffers = 8		# memory for pg_multixact/offsets
+#multixact_members_buffers = 16		# memory for pg_multixact/members
+#notify_buffers = 8			# memory for pg_nofity
+#serial_buffers = 16			# memory for pg_serial
+#commit_ts_buffers = 0			# memory for pg_commit_ts (0 = auto)

 

 #include <signal.h>
 
-/*
 
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 013850ac28..9c325b4312 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -162,6 +162,13 @@ 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_buffers;
+extern PGDLLIMPORT int multixact_members_buffers;
+extern PGDLLIMPORT int subtrans_buffers;
+extern PGDLLIMPORT int notify_buffers;
+extern PGDLLIMPORT int serial_buffers;
+extern PGDLLIMPORT int clog_buffers;
+extern PGDLLIMPORT int commit_ts_buffers;

-

From 98092b5b4e7a9a11a5bbd50c36ad8f635fdfab6f Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v12 2/2] Add buffer mapping table for SLRUs.

Instead of doing a linear search for the buffer holding a given page
number, use a hash table.

Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 src/backend/access/transam/slru.c | 111 +++++++++++++++++++++++++-----
 src/include/access/slru.h         |   2 +
 2 files changed, 96 insertions(+), 17 deletions(-)

index 82149ad782..f823c2a0c8 100644

 
 
 /*

 /*

 
@@ -436,6 +467,9 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 
@@ -459,7 +493,13 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,

 
@@ -500,20 +540,20 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
 	LWLockAcquire(shared->ControlLock, LW_SHARED);
 
 	/* See if page is already in a buffer */
-	for (slotno = 0; slotno < shared->num_slots; slotno++)
+	slotno = SlruMappingFind(ctl, pageno);
+	if (slotno >= 0 &&
+		shared->page_status[slotno] != SLRU_PAGE_READ_IN_PROGRESS)
 	{
-		if (shared->page_number[slotno] == pageno &&
-			shared->page_status[slotno] != SLRU_PAGE_EMPTY &&
-			shared->page_status[slotno] != SLRU_PAGE_READ_IN_PROGRESS)
-		{
-			/* See comments for SlruRecentlyUsed macro */
-			SlruRecentlyUsed(shared, slotno);
+		Assert(shared->page_status[slotno] != SLRU_PAGE_EMPTY);
+		Assert(shared->page_number[slotno] == pageno);
 
-			/* update the stats counter of pages found in the SLRU */
-			pgstat_count_slru_page_hit(shared->slru_stats_idx);
+		/* See comments for SlruRecentlyUsed macro */
+		SlruRecentlyUsed(shared, slotno);
 
-			return slotno;
-		}
+		/* update the stats counter of pages found in the SLRU */
+		pgstat_count_slru_page_hit(shared->slru_stats_idx);
+
+		return slotno;
 	}
 
 	/* No luck, so switch to normal exclusive lock and do regular read */
@@ -1029,11 +1069,12 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */

 		/*
@@ -1266,6 +1307,7 @@ restart:;
@@ -1348,6 +1390,7 @@ restart:
@@ -1609,3 +1652,37 @@ SlruSyncFileTag(SlruCtl ctl, const FileTag *ftag, char *path)

 /*
 
 	/*

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 26 Mar 2021 11:00:51 +0500

Hi Thomas, Gilles, all!

Thanks for reviewing this!

> 25 марта 2021 г., в 02:31, Thomas Munro <[email-masked]> написал(а):
> 
+1

> current number hurts as the linear search begins to dominate
Uh, my intent was to copy original approach of CLOG SLRU size, I just missed that Min(,) thing in shared_buffers logic.

> 26 марта 2021 г., в 08:46, Thomas Munro <[email-masked]> написал(а):
> 
> Hi Andrey, all,
> 
Yes!

> mean, we could use pg_xact_buffers instead of clog_buffers).
While it is good idea to make notes about directory name, I think the real naming criteria is to help find this GUC when user encounters wait events in pg_stat_activity. I think there is no CLOG mentions in docs [0], only XactBuffer, XactSLRU et c.

> I'm not saying the 0002 patch is bug-free yet though, it's a bit finickity.
I think the idea of speeding up linear search is really really good for scaling SLRUs. It's not even about improving normal performance of the cluster, but it's important from preventing pathological degradation under certain circumstances. Bigger cache really saves SLAs :) I'll look into the patch more closely this weekend. Thank you!

[0] https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 26 Mar 2021 20:52:22 +0500

> 26 марта 2021 г., в 11:00, Andrey Borodin <[email-masked]> написал(а):
> 

Some thoughts on HashTable patch:
1. Can we allocate bigger hashtable to reduce probability of collisions?
2. Can we use specialised hashtable for this case? I'm afraid hash_search() does comparable number of CPU cycles as simple cycle from 0 to 128. We could inline everything and avoid hashp->hash(keyPtr, hashp->keysize) call. I'm not insisting on special hash though, just an idea.
3. pageno in SlruMappingTableEntry seems to be unused.

Thanks!

---

From: Thomas Munro <[email-masked]>
Date: Sat, 27 Mar 2021 09:26:40 +1300

> 1. Can we allocate bigger hashtable to reduce probability of collisions?

Yeah, good idea, might require some study.

> 2. Can we use specialised hashtable for this case? I'm afraid hash_search() does comparable number of CPU cycles as simple cycle from 0 to 128. We could inline everything and avoid hashp->hash(keyP…

I tried really hard to not fall into this rabbit h.... [hack hack
hack], OK, here's a first attempt to use simplehash, Andres's
steampunk macro-based robinhood template that we're already using for
several other things, and murmurhash which is inlineable and
branch-free.  I had to tweak it to support "in-place" creation and
fixed size (in other words, no allocators, for use in shared memory).
Then I was annoyed that I had to add a "status" member to our struct,
so I tried to fix that.  Definitely needs more work to think about
failure modes when running out of memory, how much spare space you
need, etc.

I have not experimented with this much beyond hacking until the tests
pass, but it *should* be more efficient...

> 3. pageno in SlruMappingTableEntry seems to be unused.

It's the key (dynahash uses the first N bytes of your struct as the
key, but in this new simplehash version it's more explicit).

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 27 Mar 2021 10:31:54 +0500

> 27 марта 2021 г., в 01:26, Thomas Munro <[email-masked]> написал(а):
> 
In a long run we always have this table filled with nslots. But the keys will be usually consecutive numbers (current working set of CLOG\Multis\etc). So in a happy hashing scenario collisions will only appear for some random backward jumps. I think just size = nslots * 2 will produce results which cannot be improved significantly.
And this reflects original growth strategy SH_GROW(tb, tb->size * 2).

> hack], OK, here's a first attempt to use simplehash,

> Andres's
> steampunk macro-based robinhood template
Sounds magnificent.

> that we're already using for
> several other things
I could not find much tests to be sure that we do not break something...

> , and murmurhash which is inlineable and
> branch-free.
I think pageno is a hash already. Why hash any further? And pages accessed together will have smaller access time due to colocation.

>  I had to tweak it to support "in-place" creation and
We really need to have a test to know what happens when this structure goes out of memory, as you mentioned below. What would be apropriate place for simplehash tests?

> so I tried to fix that.
Indeed, sizeof(SlruMappingTableEntry) == 9 seems strange. Will simplehash align it well?

Thanks!

---

From: Thomas Munro <[email-masked]>
Date: Mon, 29 Mar 2021 10:15:31 +1300

> I think pageno is a hash already. Why hash any further? And pages accessed together will have smaller access time due to colocation.

Yeah, if clog_buffers is large enough then it's already a "perfect
hash", but if it's not then you might get some weird "harmonic"
effects (not sure if that's the right word), basically higher or lower
collision rate depending on coincidences in the data.  If you apply a
hash, the collisions should be evenly spread out so at least it'll be
somewhat consistent.  Does that make sense?

(At some point I figured out that the syscaches have lower collision
rates and perform better if you use oids directly instead of hashing
them... but then it's easy to create a pathological pattern of DDL
that turns your hash table into a linked list.  Not sure what to think
about that.)

> We really need to have a test to know what happens when this structure goes out of memory, as you mentioned below. What would be apropriate place for simplehash tests?

Good questions.  This has to be based on being guaranteed to have
enough space for all of the entries, so the question is really just
"how bad can performance get with different load factors".  FWIW there
were some interesting cases with clustering when simplehash was first
used in the executor (see commits ab9f2c42 and parent) which required
some work on hashing quality to fix.

> Indeed, sizeof(SlruMappingTableEntry) == 9 seems strange. Will simplehash align it well?

With that "intrusive status" patch, the size is back to 8.  But I
think I made a mistake: I made it steal some key space to indicate
presence, but I think the presence test should really get access to
the whole entry so that you can encode it in more ways.  For example,
with slotno == -1.

Alright, considering the date, if we want to get this into PostgreSQL
14 it's time to make some decisions.

1.  Do we want customisable SLRU sizes in PG14?

+1 from me, we have multiple reports of performance gains from
increasing various different SLRUs, and it's easy to find workloads
that go faster.

One thought: it'd be nice if the user could *see* the current size,
when using the default.  SHOW clog_buffers -> 0 isn't very helpful if
you want to increase it, but don't know what it's currently set to.
Not sure off the top of my head how best to do that.

2.  What names do we want the GUCs to have?  Here's what we have:

Proposed GUC              Directory            System views
clog_buffers              pg_xact              Xact
multixact_offsets_buffers pg_multixact/offsets MultiXactOffset
multixact_members_buffers pg_multixact/members MultiXactMember
notify_buffers            pg_notify            Notify
serial_buffers            pg_serial            Serial
subtrans_buffers          pg_subtrans          Subtrans
commit_ts_buffers         pg_commit_ts         CommitTs

By system views, I mean pg_stat_slru, pg_shmem_allocations and
pg_stat_activity (lock names add "SLRU" on the end).

Observations:

It seems obvious that "clog_buffers" should be renamed to "xact_buffers".
It's not clear whether the multixact GUCs should have the extra "s"
like the directories, or not, like the system views.
It see that we have "Shared Buffer Lookup Table" in
pg_shmem_allocations, so where I generated names like "Subtrans
Mapping Table" I should change that to "Lookup" to match.

3.  What recommendations should we make about how to set it?

I think the answer depends partially on the next questions!  I think
we should probably at least say something short about the pg_stat_slru
view (cache miss rate) and pg_stat_actitity view (waits on locks), and
how to tell if you might need to increase it.  I think this probably
needs a new paragraph, separate from the docs for the individual GUC.

4.  Do we want to ship the dynahash patch?

+0.9.  The slight hesitation is that it's new code written very late
in the cycle, so it may still have bugs or unintended consequences,
and as you said, at small sizes the linear search must be faster than
the hash computation.  Could you help test it, and try to break it?
Can we quantify the scaling effect for some interesting workloads, to
see at what size the dynahash beats the linear search, so that we can
make an informed decision?  Of course, without a hash table, large
sizes will surely work badly, so it'd be tempting to restrict the size
you can set the GUC to.

If we do include the dynahash patch, then I think it would also be
reasonable to change the formula for the default, to make it higher on
large systems.  The restriction to 128 buffers (= 1MB) doesn't make
much sense on a high frequency OLTP system with 128GB of shared
buffers or even 4GB.  I think "unleashing better defaults" would
actually be bigger news than the GUC for typical users, because
they'll just see PG14 use a few extra MB and go faster without having
to learn about these obscure new settings.

5.  Do we want to ship the simplehash patch?

-0.5.  It's a bit too exciting for the last minute, so I'd be inclined
to wait until the next cycle to do some more research and testing.  I
know it's a better idea in the long run.

---

From: Andrey Borodin <[email-masked]>
Date: Mon, 29 Mar 2021 13:26:02 +0500

> 29 марта 2021 г., в 02:15, Thomas Munro <[email-masked]> написал(а):
> 
As far as I understand "Harmonic" effects only make sense if the distribution is unknown. Hash protects from "periodic" data when periods are equal to hash table size. I don't think we need to protect from this case, SLRU data is expected to be localised...
Cost of this protection is necessity to calculate murmur hash on each SLRU lookup. Probably, 10-100ns. Seems like not a big deal.

> some work on hashing quality to fix.
Interesting read, I didn't know much about simple hash, but seems like there is still many cases where it can be used for good. I always wondered why Postgres uses only Larson's linear hash.

> increasing various different SLRUs, and it's easy to find workloads
Yes, this is main point of this discussion. So +1 from me too.

> Not sure off the top of my head how best to do that.
Don't we expect that SHOW command indicate exactly same value as in config or SET command? If this convention does not exist - probably showing effective value is a good idea.

+1
> like the directories, or not, like the system views.
I think we show break the ties by native English speaker's ears or typing habits. I'm not a native speaker.

> needs a new paragraph, separate from the docs for the individual GUC.
I can only suggest incident-driven approach.
1. Observe ridiculous amount of backends waiting on particular SLRU.
2. Double SLRU buffers for that SLRU.
3. Goto 1.
I don't think we should mention this approach in docs.

> 4.  Do we want to ship the dynahash patch?

This patch allows to throw infinite amount of memory on a problem of SLRU waiting for IO. So the scale of improvement is much higher. Do I want that we ship this patch? Definitely. Does this change much? I don't know.

> the hash computation.  Could you help test it, and try to break it?
I'll test it and try to break.

> make an informed decision?
I think we cannot statistically distinguish linear search from hash search by means of SLRU. But we can create some synthetic benchmarks.

> Of course, without a hash table, large
I agree. I don't see why we would need to limit buffers to 128 in presence of hash search.

> know it's a better idea in the long run.
OK, obviously, it's safer decision.

My TODO list:
1. Try to break patch set v13-[0001-0004]
2. Think how to measure performance of linear search versus hash search in SLRU buffer mapping.

Thanks!

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 1 Apr 2021 00:09:43 +0300

> 29 марта 2021 г., в 11:26, Andrey Borodin <[email-masked]> написал(а):
> 
> My TODO list:

Hi Thomas!
I'm still doing my homework. And to this moment all my catch is that "utils/dynahash.h" is not necessary.

I'm thinking about hashtables and measuring performance near optimum of linear search does not seem a good idea now.
It's impossible to prove that difference is statistically significant on all platforms. But even on one platform measurements are just too noisy.

Shared buffers lookup table is indeed very similar to this SLRU lookup table. And it does not try to use more memory than needed. I could not find pgbench-visible impact of growing shared buffer lookup table. Obviously, because it's not a bottleneck on regular workload. And it's hard to guess representative pathological workload.

In fact, this thread started with proposal to use reader-writer lock for multis (instead of exclusive lock), and this proposal encountered same problem. It's very hard to create stable reproduction of pathological workload when this lock is heavily contented. Many people observed the problem, but still there is no open repro.

I bet it will be hard to prove that simplehash is any better then HTAB. But if it is really better, shared buffers could benefit from the same technique.

I think its just fine to use HTAB with normal size, as long as shared buffers do so. But there we allocate slightly more space InitBufTable(NBuffers + NUM_BUFFER_PARTITIONS). Don't we need to allocate nslots + 1 ? It seems that we always do SlruMappingRemove() before SlruMappingAdd() and it is not necessary.

Thanks!

---

From: Thomas Munro <[email-masked]>
Date: Thu, 1 Apr 2021 16:40:16 +1300

> I'm still doing my homework. And to this moment all my catch is that "utils/dynahash.h" is not necessary.

Thanks.  Here's a new patch with that fixed, and also:

1.  New names ("... Mapping Table" -> "... Lookup Table" in
pg_shmem_allocations view, "clog_buffers" -> "xact_buffers") and a
couple of typos fixed here and there.

2.  Remove the cap of 128 buffers for xact_buffers as agreed.  We
still need a cap though, to avoid a couple of kinds of overflow inside
slru.c, both when computing the default value and accepting a
user-provided number.  I introduced SLRU_MAX_ALLOWED_BUFFERS to keep
it <= 1GB, and tested this on a 32 bit build with extreme block sizes.

Likewise, I removed the cap of 16 buffers for commit_ts_buffers, but
only if you have track_commit_timestamp enabled.  It seems silly to
waste 1MB per 1GB of shared_buffers on a feature you're not using.  So
the default is capped at 16 in that case to preserve existing
behaviour, but otherwise can be set very large if you want.

I think it's plausible that we'll want to make the multixact sizes
adaptive too, but I that might have to be a job for later.  Likewise,
I am sure that substransaction-heavy workloads might be slower than
they need to be due to the current default, but I have not done the
research,  With these new GUCs, people are free to experiment and
develop theories about what the defaults should be in v15.

3.  In the special case of xact_buffers, there is a lower cap of
512MB, because there's no point trying to cache more xids than there
can be in existence, and that is computed by working backwards from
CLOG_XACTS_PER_PAGE etc.  It's not possible to do the same sort of
thing for the other SLRUs without overflow problems, and it doesn't
seem worth trying to fix that right now (1GB of cached commit
timestamps ought to be enough for anyone™, while the theoretical
maximum is 10 bytes for 2b xids = 20GB).

To make this more explicit for people not following our discussion in
detail: with shared_buffers=0...512MB, the behaviour doesn't change.
But for shared_buffers=1GB you'll get twice as many xact_buffers as
today (2MB instead of being capped at 1MB), and it keeps scaling
linearly from there at 0.2%.  In other words, all real world databases
will get a boost in this department.

4.  Change the default for commit_ts_buffers back to shared_buffers /
1024 (with a minimum of 4), because I think you might have changed it
by a copy and paste error -- or did you intend to make the default
higher?

5.  Improve descriptions for the GUCs, visible in pg_settings view, to
match the documentation for related wait events.  So "for commit log
SLRU" -> "for the transaction status SLRU cache", and similar
corrections elsewhere.  (I am tempted to try to find a better word
than "SLRU", which doesn't seem relevant to users, but for now
consistency is good.)

6.  Added a callback so that SHOW xact_buffers and SHOW
commit_ts_buffers display the real size in effect (instead of "0" for
default).

I tried running with xact_buffers=1 and soon saw why you change it to
interpret 1 the same as 0; with 1 you hit buffer starvation and get
stuck.  I wish there were some way to say "the default for this GUC is
0, but if it's not zero then it's got to be at least 4".  I didn't
study the theoretical basis for the previous minimum value of 4, so I
think we should keep it that way, so that if you say 3 you get 4.  I
thought it was better to express that like so:

    /* Use configured value if provided. */
    if (xact_buffers > 0)
        return Max(4, xact_buffers);
    return Min(CLOG_MAX_ALLOWED_BUFFERS, Max(4, NBuffers / 512));

> Shared buffers lookup table is indeed very similar to this SLRU lookup table. And it does not try to use more memory than needed. I could not find pgbench-visible impact of growing shared buffer lo…

Thanks for testing.  I agree that it's a good idea to follow the main
buffer pool's approach for our first version of this.  One small
difference is that the SLRU patch performs the hash computation while
it holds the lock.  If we computed the hash first and used
hash_search_with_hash_value(), we could compute it before we obtain
the lock, like the main buffer pool.

If we computed the hash value first, we could also ignore the rule in
the documentation for hash_search_with_hash_value() that says that you
must calculate it with get_hash_value(), and just call the hash
function ourselves, so that it's fully inlinable.  The same
opportunity exists for the main buffer pool.  That'd get you one of
the micro-optimisations that simplehash.h offers.  Whether it's worth
bothering with, I don't know.

> I bet it will be hard to prove that simplehash is any better then HTAB. But if it is really better, shared buffers could benefit from the same technique.

Agreed, there are a lot of interesting future projects in this area,
when you compare the main buffer pool, these special buffer pools, and
maybe also the "shared relfilenode" pool patch I have proposed for v15
(CF entry 2933).  All have mapping tables and buffer replacement
algorithms (why should they be different?), one has partitions, some
have atomic-based header locks, they interlock with WAL differently
(on page LSN, FPIs and checksum support), ... etc etc.

> I think its just fine to use HTAB with normal size, as long as shared buffers do so. But there we allocate slightly more space InitBufTable(NBuffers + NUM_BUFFER_PARTITIONS). Don't we need to alloc…

Yeah, we never try to add more elements than allowed, because we have
a big lock around the mapping.  The main buffer mapping table has a
more concurrent design and might temporarily have one extra entry per
partition.
From 05ce938fef85a5f23407d6977165319650b05d63 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v14 1/2] Add a buffer mapping table for SLRUs.

Instead of doing a linear search for the buffer holding a given page
number, use a hash table.  This will allow us to increase the size of
these caches.

Reviewed-by: Andrey M. Borodin <[email-masked]>
Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 src/backend/access/transam/slru.c | 110 +++++++++++++++++++++++++-----
 src/include/access/slru.h         |   2 +
 2 files changed, 95 insertions(+), 17 deletions(-)

index 82149ad782..a88e7a38a3 100644
@@ -58,6 +58,7 @@
 
 #define SlruFileName(ctl, path, seg) \
 	snprintf(path, MAXPGPATH, "%s/%04X", (ctl)->Dir, seg)
@@ -79,6 +80,12 @@ typedef struct SlruWriteAllData

@@ -146,6 +153,9 @@ static int	SlruSelectLRUPage(SlruCtl ctl, int pageno);
 
 /*
  * Initialization of shared memory
@@ -168,7 +178,8 @@ SimpleLruShmemSize(int nslots, int nlsns)
 	if (nlsns > 0)
 		sz += MAXALIGN(nslots * nlsns * sizeof(XLogRecPtr));	/* group_lsn[] */

 /*
@@ -187,6 +198,9 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 
@@ -258,11 +272,21 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 	else
 		Assert(found);
 
+			 "%s Lookup Table", name);
@@ -289,6 +313,9 @@ SimpleLruZeroPage(SlruCtl ctl, int pageno)
 
@@ -362,7 +389,10 @@ SimpleLruWaitIO(SlruCtl ctl, int slotno)
@@ -436,6 +466,9 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 
@@ -459,7 +492,13 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,

 
@@ -500,20 +539,20 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
 	LWLockAcquire(shared->ControlLock, LW_SHARED);

 
-			return slotno;
-		}
 
 	/* No luck, so switch to normal exclusive lock and do regular read */
@@ -1029,11 +1068,12 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */

 		/*
@@ -1266,6 +1306,7 @@ restart:;
@@ -1348,6 +1389,7 @@ restart:
@@ -1609,3 +1651,37 @@ SlruSyncFileTag(SlruCtl ctl, const FileTag *ftag, char *path)

 /*
 
 	/*
2.30.2

From edd07254e3f018ee3afe3f4251ab2cc0406d8b7a Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v14 2/2] Make all SLRU buffer sizes configurable.

Remove the limits on xact_buffers and commit_ts_buffers.  The default
sizes for those caches are ~0.2% and ~0.1% of shared_buffers, as before,
but now there is no cap at 128 and 16 buffers respectively (unless
track_commit_timestamp is disabled, in the latter case, then we might as
well keep it tiny).  Sizes much larger than the old limits have been
shown to be useful on modern systems, and an earlier commit replaced a
linear search with a hash table to avoid problems with extreme cases.

Author: Andrey M. Borodin <[email-masked]>
Reviewed-by: Anastasia Lubennikova <[email-masked]>
Reviewed-by: Tomas Vondra <[email-masked]>
Reviewed-by: Alexander Korotkov <[email-masked]>
Reviewed-by: Gilles Darold <[email-masked]>
Reviewed-by: Thomas Munro <[email-masked]>
Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 doc/src/sgml/config.sgml                      | 135 ++++++++++++++++++
 src/backend/access/transam/clog.c             |  23 ++-
 src/backend/access/transam/commit_ts.c        |  10 +-
 src/backend/access/transam/multixact.c        |   8 +-
 src/backend/access/transam/subtrans.c         |   5 +-
 src/backend/commands/async.c                  |   8 +-
 src/backend/storage/lmgr/predicate.c          |   4 +-
 src/backend/utils/init/globals.c              |   8 ++
 src/backend/utils/misc/guc.c                  |  99 +++++++++++++
 src/backend/utils/misc/postgresql.conf.sample |   9 ++
 src/include/access/clog.h                     |  10 ++
 src/include/access/commit_ts.h                |   1 -
 src/include/access/multixact.h                |   4 -
 src/include/access/slru.h                     |   5 +
 src/include/access/subtrans.h                 |   2 -
 src/include/commands/async.h                  |   5 -
 src/include/miscadmin.h                       |   7 +
 src/include/storage/predicate.h               |   4 -
 18 files changed, 301 insertions(+), 46 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 701cb65cc7..58388f144f 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1886,6 +1886,141 @@ include_dir 'conf.d'
        </para>
       </listitem>
      </varlistentry>
+     
+    <varlistentry id="guc-multixact-offsets-buffers" xreflabel="multixact_offsets_buffers">
+      <term><varname>multixact_offsets_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>multixact_offsets_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_multixact/offsets</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+
+    <varlistentry id="guc-multixact-members-buffers" xreflabel="multixact_members_buffers">
+      <term><varname>multixact_members_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>multixact_members_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_multixact/members</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>16</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-subtrans-buffers" xreflabel="subtrans_buffers">
+      <term><varname>subtrans_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>subtrans_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_subtrans</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-notify-buffers" xreflabel="notify_buffers">
+      <term><varname>notify_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>notify_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_notify</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>8</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-serial-buffers" xreflabel="serial_buffers">
+      <term><varname>serial_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>serial_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_serial</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>16</literal>.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-xact-buffers" xreflabel="xact_buffers">
+      <term><varname>xact_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>xact_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of shared memory to use to cache the contents
+        of <literal>pg_xact</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>0</literal>, which requests
+        <varname>shared_buffers</varname> / 512, but not fewer than 4 blocks.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+     
+    <varlistentry id="guc-commit-ts-buffers" xreflabel="commit_ts_buffers">
+      <term><varname>commit_ts_buffers</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>commit_ts_buffers</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the amount of memory to use to cache the cotents of
+        <literal>pg_commit_ts</literal> (see
+        <xref linkend="pgdata-contents-table"/>).
+        If this value is specified without units, it is taken as blocks,
+        that is <symbol>BLCKSZ</symbol> bytes, typically 8kB.
+        The default value is <literal>0</literal>, which requests
+        <varname>shared_buffers</varname> / 1024, but not fewer than 4 blocks.
+        This parameter can only be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
 
index 6fa4713fb4..dd2d7a5184 100644
@@ -58,8 +58,8 @@
 
 /* We need two bits per xact, so four xacts fit in a byte */
 #define CLOG_BITS_PER_XACT	2
-#define CLOG_XACTS_PER_BYTE 4
-#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+StaticAssertDecl((CLOG_BITS_PER_XACT * CLOG_XACTS_PER_BYTE) == BITS_PER_BYTE,
+				 "CLOG_BITS_PER_XACT and CLOG_XACTS_PER_BYTE are inconsistent");
 #define CLOG_XACT_BITMASK	((1 << CLOG_BITS_PER_XACT) - 1)
 
 #define TransactionIdToPage(xid)	((xid) / (TransactionId) CLOG_XACTS_PER_PAGE)
@@ -659,23 +659,16 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
 /*
  * Number of shared CLOG buffers.
  *
- * On larger multi-processor systems, it is possible to have many CLOG page
- * requests in flight at one time which could lead to disk access for CLOG
- * page if the required page is not found in memory.  Testing revealed that we
- * can get the best performance by having 128 CLOG buffers, more than that it
- * doesn't improve performance.
- *
- * Unconditionally keeping the number of CLOG buffers to 128 did not seem like
- * a good idea, because it would increase the minimum amount of shared memory
- * required to start, which could be a problem for people running very small
- * configurations.  The following formula seems to represent a reasonable
- * compromise: people with very low values for shared_buffers will get fewer
- * CLOG buffers as well, and everyone else will get 128.
+ * By default, we'll use 2MB of for every 1GB of shared buffers, up to the
+ * theoretical maximum useful value, but always at least 4 buffers.
  */
 Size
 CLOGShmemBuffers(void)
 {
-	return Min(128, Max(4, NBuffers / 512));
+	/* Use configured value if provided. */
+	if (xact_buffers > 0)
+		return Max(4, xact_buffers);
+	return Min(CLOG_MAX_ALLOWED_BUFFERS, Max(4, NBuffers / 512));
 }
 
 /*
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 268bdba339..729a4b9212 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -524,13 +524,17 @@ pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
 /*
  * Number of shared CommitTS buffers.
  *
- * We use a very similar logic as for the number of CLOG buffers; see comments
- * in CLOGShmemBuffers.
+ * By default, we'll use 1MB of for every 1GB of shared buffers, up to the
+ * maximum value that slru.c will allow, but always at least 4 buffers.
  */
 Size
 CommitTsShmemBuffers(void)
 {
-	return Min(16, Max(4, NBuffers / 1024));
+	/* Use configured value if provided. */
+	if (commit_ts_buffers > 0)
+		return Max(4, commit_ts_buffers);
+	return Min(track_commit_timestamp ? SLRU_MAX_ALLOWED_BUFFERS : 16,
+			   Max(4, NBuffers / 1024));
 }
 
 /*

 	return size;
 }

 
 void
 SUBTRANSShmemInit(void)
 {

 	return size;
 }

 
 	return size;
 }
index 73e0a672ae..3de58042d3 100644
 
+int			xact_buffers = 0;
index 03daec9a08..e05f05ffea 100644
@@ -29,9 +29,11 @@
 #endif
 #include <unistd.h>
 
+#include "access/clog.h"
 #include "access/commit_ts.h"
 #include "access/gin.h"
 #include "access/rmgr.h"
+#include "access/slru.h"
 #include "access/tableam.h"
 #include "access/toast_compression.h"
 #include "access/transam.h"
@@ -196,6 +198,8 @@ static const char *show_tcp_keepalives_idle(void);
 static const char *show_tcp_keepalives_interval(void);
 static const char *show_tcp_keepalives_count(void);
 static const char *show_tcp_user_timeout(void);
+static const char *show_xact_buffers(void);
+static const char *show_commit_ts_buffers(void);
 static bool check_maxconnections(int *newval, void **extra, GucSource source);
 static bool check_max_worker_processes(int *newval, void **extra, GucSource source);
 static bool check_autovacuum_max_workers(int *newval, void **extra, GucSource source);
@@ -2315,6 +2319,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"multixact_offsets_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the MultiXact offset SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&multixact_offsets_buffers,
+		8, 2, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"multixact_members_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the MultiXact member SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&multixact_members_buffers,
+		16, 2, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"subtrans_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the sub-transaction SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&subtrans_buffers,
+		32, 2, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"notify_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the NOTIFY message SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&notify_buffers,
+		8, 2, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"serial_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the serializable transaction SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&serial_buffers,
+		16, 2, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, NULL
+	},
+
+	{
+		{"xact_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the number of shared memory buffers used for the transaction status SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&xact_buffers,
+		0, 0, CLOG_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, show_xact_buffers
+	},
+
+	{
+		{"commit_ts_buffers", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("Sets the size of the dedicated buffer pool used for the commit timestamp SLRU cache."),
+			NULL,
+			GUC_UNIT_BLOCKS
+		},
+		&commit_ts_buffers,
+		0, 0, SLRU_MAX_ALLOWED_BUFFERS,
+		NULL, NULL, show_commit_ts_buffers
+	},
+
 	{
 		{"temp_buffers", PGC_USERSET, RESOURCES_MEM,
 			gettext_noop("Sets the maximum number of temporary buffers used by each session."),
@@ -11871,6 +11952,24 @@ show_tcp_user_timeout(void)
 	return nbuf;
 }
 
+static const char *
+show_xact_buffers(void)
+{
+	static char nbuf[16];
+
+	snprintf(nbuf, sizeof(nbuf), "%zu", CLOGShmemBuffers());
+	return nbuf;
+}
+
+static const char *
+show_commit_ts_buffers(void)
+{
+	static char nbuf[16];
+
+	snprintf(nbuf, sizeof(nbuf), "%zu", CommitTsShmemBuffers());
+	return nbuf;
+}
+
 static bool
 check_maxconnections(int *newval, void **extra, GucSource source)
 {
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 791d39cf07..a380d063da 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -190,6 +190,15 @@
 					# (change requires restart)
 #backend_flush_after = 0		# measured in pages, 0 disables
 
+# - SLRU Buffers (change requires restart) -
+
+#xact_buffers = 0			# memory for pg_xact (0 = auto)
+#subtrans_buffers = 32			# memory for pg_subtrans
+#multixact_offsets_buffers = 8		# memory for pg_multixact/offsets
+#multixact_members_buffers = 16		# memory for pg_multixact/members
+#notify_buffers = 8			# memory for pg_notify
+#serial_buffers = 16			# memory for pg_serial
+#commit_ts_buffers = 0			# memory for pg_commit_ts (0 = auto)
 
 #------------------------------------------------------------------------------
 # WRITE-AHEAD LOG
diff --git a/src/include/access/clog.h b/src/include/access/clog.h
index 39b8e4afa8..739a292f7f 100644
--- a/src/include/access/clog.h
+++ b/src/include/access/clog.h
@@ -15,6 +15,16 @@
 #include "storage/sync.h"
 #include "lib/stringinfo.h"
 
+/*
+ * Don't allow xact_buffers to be set higher than could possibly be useful or
+ * SLRU would allow.
+ */
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+#define CLOG_MAX_ALLOWED_BUFFERS \
+	Min(SLRU_MAX_ALLOWED_BUFFERS, \
+		(((MaxTransactionId / 2) + (CLOG_XACTS_PER_PAGE - 1)) / CLOG_XACTS_PER_PAGE))
+
 /*
  * Possible transaction statuses --- note that all-zeroes is the initial
  * state.
diff --git a/src/include/access/commit_ts.h b/src/include/access/commit_ts.h
index 750369104a..e4cf988609 100644
--- a/src/include/access/commit_ts.h
+++ b/src/include/access/commit_ts.h
@@ -17,7 +17,6 @@
 #include "storage/sync.h"
 #include "utils/guc.h"
 
-
 extern PGDLLIMPORT bool track_commit_timestamp;
 
 extern bool check_track_commit_timestamp(bool *newval, void **extra,

index 8aa3efc0ee..97ea837646 100644
@@ -18,6 +18,11 @@
 #include "utils/hsearch.h"
 
+/*
+ * To avoid overflowing internal arithmetic and the size_t data type, the
+ * number of buffers should not exceed this number.
+ */
+#define SLRU_MAX_ALLOWED_BUFFERS ((1024 * 1024 * 1024) / BLCKSZ)
 
 /*
  * Define SLRU segment size.  A page is the same BLCKSZ as is used everywhere
diff --git a/src/include/access/subtrans.h b/src/include/access/subtrans.h
index d0ab44ae82..64fa86938e 100644
--- a/src/include/access/subtrans.h
+++ b/src/include/access/subtrans.h
@@ -11,8 +11,6 @@
 #ifndef SUBTRANS_H
 #define SUBTRANS_H

 
 #include <signal.h>
 
-/*
 
index 013850ac28..eb2c1f18f3 100644
+extern PGDLLIMPORT int xact_buffers;

-
2.30.2

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 3 Apr 2021 22:57:23 +0300

> 1 апр. 2021 г., в 06:40, Thomas Munro <[email-masked]> написал(а):
> 
BTW we do not document maximum values right now.
I was toying around with big values. For example if we set different big xact_buffers we can get something like
FATAL:  not enough shared memory for data structure "Notify" (72768 bytes requested)
FATAL:  not enough shared memory for data structure "Async Queue Control" (2492 bytes requested)
FATAL:  not enough shared memory for data structure "Checkpointer Data" (393280 bytes requested)

But never anything about xact_buffers. I don't think it's important, though.

> only if you have track_commit_timestamp enabled.  
Is there a reason to leave 16 pages if commit_ts is disabled? They might be useful for some artefacts of previously enabled commit_ts?

> by a copy and paste error -- or did you intend to make the default
I changed default due to some experiments with https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql
In fact most important part of that thread was removing the cap, which is done by the patchset now.

Thanks!

---

From: Thomas Munro <[email-masked]>
Date: Wed, 7 Apr 2021 17:59:19 +1200

> But never anything about xact_buffers. I don't think it's important,
though.

I had added the hash table size in SimpleLruShmemSize(), but then
SimpleLruInit() passed that same value in when allocating the struct, so
the struct was oversized.  Oops.  Fixed.

> Is there a reason to leave 16 pages if commit_ts is disabled? They might
be useful for some artefacts of previously enabled commit_ts?

Alvaro, do you have an opinion on that?

The remaining thing that bothers me about this patch set is that there is
still a linear search in the replacement algorithm, and it runs with an
exclusive lock.  That creates a serious problem for large caches that still
aren't large enough.  I wonder if we can do something to improve that
situation in the time we have.  I considered a bunch of ideas but could
only find one that fits with slru.c's simplistic locking while tracking
recency.  What do you think about a hybrid of SLRU and random replacement,
that retains some characteristics of both?  You could think of it as being
a bit like the tournament selection of the genetic algorithm, with a
tournament size of (say) 8 or 16.  Any ideas on how to evaluate this and
choose the number?  See attached.
From 5f61bd7d227077f86649a890be603eae01c828f8 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v15 1/3] Add a buffer mapping table for SLRUs.

Reviewed-by: Andrey M. Borodin <[email-masked]>
Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 src/backend/access/transam/slru.c | 121 +++++++++++++++++++++++++-----
 src/include/access/slru.h         |   2 +
 2 files changed, 103 insertions(+), 20 deletions(-)

index 82149ad782..82c61c475b 100644

 
@@ -146,13 +153,16 @@ static int	SlruSelectLRUPage(SlruCtl ctl, int pageno);
 
 /*
 
-Size
-SimpleLruShmemSize(int nslots, int nlsns)
+static Size
+SimpleLruStructSize(int nslots, int nlsns)
 {
 	Size		sz;
 
@@ -167,10 +177,16 @@ SimpleLruShmemSize(int nslots, int nlsns)
 
 	if (nlsns > 0)
 		sz += MAXALIGN(nslots * nlsns * sizeof(XLogRecPtr));	/* group_lsn[] */
-
 	return BUFFERALIGN(sz) + BLCKSZ * nslots;
 }
 
+Size
+SimpleLruShmemSize(int nslots, int nlsns)
+{
+	return SimpleLruStructSize(nslots, nlsns) +
+		hash_estimate_size(nslots, sizeof(SlruMappingTableEntry));
+}
+
 /*
  * Initialize, or attach to, a simple LRU cache in shared memory.
  *
@@ -187,11 +203,14 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 			  LWLock *ctllock, const char *subdir, int tranche_id,
 			  SyncRequestHandler sync_handler)
 {
+	char		mapping_table_name[SHMEM_INDEX_KEYSIZE];
+	HASHCTL		mapping_table_info;
+	HTAB	   *mapping_table;
 	SlruShared	shared;
 	bool		found;
 
 	shared = (SlruShared) ShmemInitStruct(name,
-										  SimpleLruShmemSize(nslots, nlsns),
+										  SimpleLruStructSize(nslots, nlsns),
 										  &found);
 
 	if (!IsUnderPostmaster)
@@ -258,11 +277,21 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 	else
 		Assert(found);
 
@@ -289,6 +318,9 @@ SimpleLruZeroPage(SlruCtl ctl, int pageno)
 
@@ -362,7 +394,10 @@ SimpleLruWaitIO(SlruCtl ctl, int slotno)
@@ -436,6 +471,9 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 
@@ -459,7 +497,13 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,

 
@@ -500,20 +544,20 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
 	LWLockAcquire(shared->ControlLock, LW_SHARED);

 
-			return slotno;
-		}
 
 	/* No luck, so switch to normal exclusive lock and do regular read */
@@ -1029,11 +1073,12 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */

 		/*
@@ -1266,6 +1311,7 @@ restart:;
@@ -1348,6 +1394,7 @@ restart:
@@ -1609,3 +1656,37 @@ SlruSyncFileTag(SlruCtl ctl, const FileTag *ftag, char *path)

 /*
 
 	/*
2.27.0

From 3462ffe7d740ef0a8d6b217a338fa5cb55a1fbcc Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v15 2/3] Make all SLRU buffer sizes configurable.

index e51639d56c..79f46a3df8 100644
@@ -1924,6 +1924,141 @@ include_dir 'conf.d'

 
 
 /*
 
 /*

 	return size;
 }

 
 void
 SUBTRANSShmemInit(void)
 {

 	return size;
 }

 
 	return size;
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 381d9e548d..c83151d5ab 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -150,3 +150,11 @@ int64		VacuumPageDirty = 0;
 
index c9c9da85f3..d0f0532daa 100644
@@ -32,9 +32,11 @@
 #endif
 #include <unistd.h>
 
@@ -200,6 +202,8 @@ static const char *show_tcp_keepalives_idle(void);
@@ -2330,6 +2334,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
@@ -11897,6 +11978,24 @@ show_tcp_user_timeout(void)
 	return nbuf;
 }
 
+static const char *
+show_xact_buffers(void)
+{
+	static char nbuf[16];
+
index 39da7cc942..5bbbf85923 100644

 
+/*
 
-

 
 
+/*
 
 /*

 
 #include <signal.h>
 
-/*
 
index 95202d37af..495c1bf901 100644
@@ -164,6 +164,13 @@ extern PGDLLIMPORT int MaxBackends;

-
2.27.0

From cea3142af73c434d11206dd39a7edaa8718269b4 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Wed, 7 Apr 2021 16:39:09 +1200
Subject: [PATCH v15 3/3] Use hybrid random/SLRU replacement for SLRUs.

The previous algorithm would search the entire SLRU to find the least
recently used page.  That doesn't seem like a good idea now that the
user can set SLRUs to large sizes.  Instead, apply the existing algorithm
to a small number of randomly chosen buffers, for a cheap hybrid of SLRU
and random replacement that doesn't require a complete rewrite of
slru.c's locking.

XXX experiment
---
 src/backend/access/transam/slru.c | 22 +++++++++++++++++-----
 1 file changed, 17 insertions(+), 5 deletions(-)

diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 82c61c475b..95e404e856 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -71,6 +71,14 @@
  */
 #define MAX_WRITEALL_BUFFERS	16
 
+/*
+ * We need a fast way to choose a buffer to replace, because we hold a coarse
+ * grained exclusive lock while searching.  As an improvement over simple
+ * random replacement that still has some LRU tendancies, we'll compare the
+ * recency of a smallish number of randomly selected buffers.
+ */
+#define MAX_RANDOM_SEARCH		8
+
 typedef struct SlruWriteAllData
 {
 	int			num_files;		/* # files actually open */
@@ -1071,6 +1079,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			bestinvalidslot = 0;	/* keep compiler quiet */
 		int			best_invalid_delta = -1;
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */
+		int			candidates = Min(shared->num_slots, MAX_RANDOM_SEARCH);
 
 		/* See if page already has a buffer assigned */
 		slotno = SlruMappingFind(ctl, pageno);
@@ -1082,9 +1091,10 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		}
 
 		/*
-		 * If we find any EMPTY slot, just select that one. Else choose a
-		 * victim page to replace.  We normally take the least recently used
-		 * valid page, but we will never take the slot containing
+		 * If we find any EMPTY slot, just select that one.  Else choose a
+		 * victim page to replace.  We take the least recently used of a small
+		 * number of randomly chosen pages, to avoid having to scan a
+		 * potentially large number of pages.  We skip the slot containing
 		 * latest_page_number, even if it appears least recently used.  We
 		 * will select a slot that is already I/O busy only if there is no
 		 * other choice: a read-busy slot will not be least recently used once
@@ -1109,11 +1119,13 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		 * multiple pages with the same lru_count.
 		 */
 		cur_count = (shared->cur_lru_count)++;
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		for (int i = 0; i < candidates; ++i)
 		{
 			int			this_delta;
 			int			this_page_number;
 
+retry:
+			slotno = (random() & LONG_MAX) % shared->num_slots;
 			if (shared->page_status[slotno] == SLRU_PAGE_EMPTY)
 				return slotno;
 			this_delta = cur_count - shared->page_lru_count[slotno];
@@ -1131,7 +1143,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 			}
 			this_page_number = shared->page_number[slotno];
 			if (this_page_number == shared->latest_page_number)
-				continue;
+				goto retry;
 			if (shared->page_status[slotno] == SLRU_PAGE_VALID)
 			{
 				if (this_delta > best_valid_delta ||
-- 
2.27.0

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 7 Apr 2021 14:44:32 +0300

> 7 апр. 2021 г., в 08:59, Thomas Munro <[email-masked]> написал(а):
> 
> The remaining thing that bothers me about this patch set is that there is still a linear search in the replacement algorithm, and it runs with an exclusive lock.  That creates a serious problem for large caches that still aren't large enough.  I wonder if we can do something to improve that situation in the time we have.  I considered a bunch of ideas but could only find one that fits with slru.c's simplistic locking while tracking recency.  What do you think about a hybrid of SLRU and random replacement, that retains some characteristics of both?  You could think of it as being a bit like the tournament selection of the genetic algorithm, with a tournament size of (say) 8 or 16.  Any ideas on how to evaluate this and choose the number?  See attached.
> <v15-0001-Add-a-buffer-mapping-table-for-SLRUs.patch><v15-0002-Make-all-SLRU-buffer-sizes-configurable.patch><v15-0003-Use-hybrid-random-SLRU-replacement-for-SLRUs.patch>

Maybe instead of fully associative cache with random replacement we could use 1-associative cache?
i.e. each page can reside only in one spcific buffer slot. If there's something else - evict it.
I think this would be as efficient as RR cache. And it's soooo fast.

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 7 Apr 2021 15:13:26 +0300

> 7 апр. 2021 г., в 14:44, Andrey Borodin <[email-masked]> написал(а):
> 

I thought a bit more and understood that RR is protected from two competing pages in working set, while 1-associative cache is not. So, discard that idea.

---

From: Thomas Munro <[email-masked]>
Date: Thu, 8 Apr 2021 12:30:41 +1200

> I thought a bit more and understood that RR is protected from two competing pages in working set, while 1-associative cache is not. So, discard that idea.

It's an interesting idea.  I know that at least one proprietary fork
just puts the whole CLOG in memory for direct indexing, which is what
we'd have here if we said "oh, your xact_buffers setting is so large
I'm just going to use slotno = pageno & mask".

Here's another approach that is a little less exciting than
"tournament RR" (or whatever that should be called; I couldn't find an
established name for it).  This version is just our traditional linear
search, except that it stops at 128, and remembers where to start from
next time (like a sort of Fisher-Price GCLOCK hand).  This feels more
committable to me.  You can argue that all buffers above 128 are bonus
buffers that PostgreSQL 13 didn't have, so the fact that we can no
longer find the globally least recently used page when you set
xact_buffers > 128 doesn't seem too bad to me, as an incremental step
(but to be clear, of course we can do better than this with more work
in later releases).
From 72ebd5052851aa4b9aa281df30b9bf42c0ad5de4 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 25 Mar 2021 10:11:31 +1300
Subject: [PATCH v16 1/3] Add a buffer mapping table for SLRUs.

 

 
 /*
 
-Size

 	if (nlsns > 0)
 
+Size

 	if (!IsUnderPostmaster)

 

 

 
-			return slotno;
-		}

 
 		/*

 /*
 
 	/*

From 97c5078f661a41617450826173dd252fc4d0c856 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v16 2/3] Make all SLRU buffer sizes configurable.

index 963824d050..58c46edf62 100644

 
 
 /*
 
 /*

 	return size;
 }

 
 void
 SUBTRANSShmemInit(void)
 {

 	return size;
 }

 
 	return size;
 }
 
index bee976bae8..250cef80bd 100644
 
@@ -2340,6 +2344,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
@@ -11959,6 +12040,24 @@ show_tcp_user_timeout(void)
 	return nbuf;
 }
 
+static const char *
+show_xact_buffers(void)
+{
+	static char nbuf[16];
+
index ff9fa006fe..7e14df3b51 100644

 
+/*
 
-

 
 
+/*
 
 /*

 
 #include <signal.h>
 
-/*

 
-

From 79c15f73a21cfb67cb6157fc5b6822bcdc696383 Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Thu, 8 Apr 2021 12:18:25 +1200
Subject: [PATCH v16 3/3] Limit SLRU buffer replacement search.

Now that users can configure large SLRU caches, slru.c's simple buffer
replacement algorithm needs some adjustment.  For now, limit its linear
search for the least recently accessed buffer to an arbitrary cap.  This
means it won't find the globally least recently used buffer, just the
least recently used in a given range of pages.  The cap is initially set
as large as the previous hard-coded search size.

Discussion: https://postgr.es/m/2BEC2B3F-9B61-4C1D-9FB5-5FAB0F05EF86%40yandex-team.ru
---
 src/backend/access/transam/slru.c | 15 ++++++++++++++-
 src/include/access/slru.h         |  3 +++
 2 files changed, 17 insertions(+), 1 deletion(-)

index 82c61c475b..131f7a48d7 100644
@@ -71,6 +71,12 @@
 
+/*
+ * When searching for buffers to replace, we will limit the scope of our search
+ * for now, to avoid holding an exclusive lock for too long.
+ */
+#define MAX_REPLACEMENT_SEARCH	128
+
 typedef struct SlruWriteAllData
 {
 	int			num_files;		/* # files actually open */
@@ -1060,6 +1066,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 {
 	SlruShared	shared = ctl->shared;
 
+
 	/* Outer loop handles restart after I/O */
 	for (;;)
 	{
@@ -1071,6 +1078,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			bestinvalidslot = 0;	/* keep compiler quiet */
 		int			best_invalid_delta = -1;
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */
+		int			max_search;
 
 		/* See if page already has a buffer assigned */
 		slotno = SlruMappingFind(ctl, pageno);
@@ -1108,12 +1116,17 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		 * That gets us back on the path to having good data when there are
 		 * multiple pages with the same lru_count.
 		 */
+		max_search = Min(shared->num_slots, MAX_REPLACEMENT_SEARCH);
 		cur_count = (shared->cur_lru_count)++;
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		for (int i = 0; i < max_search; ++i)
 		{
 			int			this_delta;
 			int			this_page_number;
 
+			slotno = shared->search_slotno++;
+			if (shared->search_slotno == shared->num_slots)
+				shared->search_slotno = 0;
+
 			if (shared->page_status[slotno] == SLRU_PAGE_EMPTY)
 				return slotno;
 			this_delta = cur_count - shared->page_lru_count[slotno];
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index 97ea837646..fb8a03972d 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -63,6 +63,9 @@ typedef struct SlruSharedData
 	/* Number of buffers managed by this SLRU structure */
 	int			num_slots;
 
+	/* Where to start buffer replacement search. */
+	int			search_slotno;
+
 	/*
 	 * Arrays holding info for each buffer slot.  Page number is undefined
 	 * when status is EMPTY, as is page_lru_count.
-- 
2.30.1

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 8 Apr 2021 10:24:55 +0300

> 8 апр. 2021 г., в 03:30, Thomas Munro <[email-masked]> написал(а):
> 
I agree that this version of eviction seems much more effective and less intrusive than RR. And it's still LRU, which is important for subsystem that is called SLRU.
shared->search_slotno is initialized implicitly with memset(). But this seems like a common practice.
Also comment above "max_search = Min(shared->num_slots, MAX_REPLACEMENT_SEARCH);" does not reflect changes.

Besides this patch looks good to me.

Thanks!

---

From: Thomas Munro <[email-masked]>
Date: Fri, 9 Apr 2021 00:22:39 +1200

> Besides this patch looks good to me.

Thanks!  I chickened out of committing a buffer replacement algorithm
patch written 11 hours before the feature freeze, but I also didn't
really want to commit the GUC patch without that.  Ahh, if only we'd
latched onto the real problems here just a little sooner, but there is
always PostgreSQL 15, I heard it's going to be amazing.  Moved to next
CF.

---

From: Andrey Borodin <[email-masked]>
Date: Sun, 11 Apr 2021 21:37:21 +0300

> 8 апр. 2021 г., в 15:22, Thomas Munro <[email-masked]> написал(а):
> 

I have one more idea inspired by CPU caches.
Let's make SLRU n-associative, where n ~ 8.
We can divide buffers into "banks", number of banks must be power of 2.
All banks are of equal size. We choose bank size to approximately satisfy user's configured buffer size.
Each page can live only within one bank. We use same search and eviction algorithms as we used in SLRU, but we only need to search\evict over 8 elements.
All SLRU data of a single bank will be colocated within at most 2 cache line.

I did not come up with idea how to avoid multiplication of bank_number * bank_size in case when user configured 31337 buffers (any number that is radically not a power of 2).

PFA patch implementing this idea.

---

From: Васильев Дмитрий<[email-masked]>
Date: Mon, 14 Jun 2021 20:40:16 +0300

> PFA patch implementing this idea.

I'm benchmarked v17 patches.
Testing was done on a 96-core machine, with PGDATA completely placed in
tmpfs.
PostgreSQL was built with CFLAGS -O2.

for-update PgBench script:
\set aid random_zipfian(1, 100, 2)
begin;
select :aid from pgbench_accounts where aid = :aid for update;
update pgbench_accounts set abalance = abalance + 1 where aid = :aid;
update pgbench_accounts set abalance = abalance * 2 where aid = :aid;
update pgbench_accounts set abalance = abalance - 2 where aid = :aid;
end;

Before each test sample data was filled with "pgbench -i -s 100", testing
was performed 3 times for 1 hour each test.
The benchmark results are presented with changing
multi_xact_members_buffers and multicast_offsets_buffers (1:2 respectively):
settings                          tps
multixact_members_buffers_64Kb   693.2
multixact_members_buffers_128Kb  691.4
multixact_members_buffers_192Kb  696.3
multixact_members_buffers_256Kb  694.4
multixact_members_buffers_320Kb  692.3
multixact_members_buffers_448Kb  693.7
multixact_members_buffers_512Kb  693.3
vanilla                          676.1

Best regards, Dmitry Vasiliev.

---

From: Andrey Borodin <[email-masked]>
Date: Sun, 26 Dec 2021 15:09:59 +0500

> I did not come up with idea how to avoid multiplication of bank_number * bank_size in case when user configured 31337 buffers (any number that is radically not a power of 2).

We can avoid this multiplication by using gapped memory under SLRU page_statuses, but from my POV here complexity does not worth possible performance gain.

PFA rebase of the patchset. Also I've added a patch to combine page_number, page_status, and page_dirty together to touch less cachelines.

From ea59d2ebde818ddc2a9111858b3d956cbcc7bff2 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v=18 1/3] Make all SLRU buffer sizes configurable.

 src/backend/access/transam/commit_ts.c        |   5 +
 18 files changed, 299 insertions(+), 43 deletions(-)

index afbb6c35e30..57d9696abe8 100644
@@ -1952,6 +1952,141 @@ include_dir 'conf.d'
 
index 3ea16a270a8..ca28ada75fa 100644

@@ -664,23 +664,16 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
 
 /*
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index cbbe19fea83..cb2e0ceb1c3 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -511,10 +511,15 @@ pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
  * We use a very similar logic as for the number of CLOG buffers (except we
  * scale up twice as fast with shared buffers, and the maximum is twice as
  * high); see comments in CLOGShmemBuffers.
+ * By default, we'll use 1MB of for every 1GB of shared buffers, up to the
+ * maximum value that slru.c will allow, but always at least 4 buffers.
  */
 Size
 CommitTsShmemBuffers(void)
 {
+	/* Use configured value if provided. */
+	if (commit_ts_buffers > 0)
+		return Max(4, commit_ts_buffers);
 	return Min(256, Max(4, NBuffers / 256));
 }
 
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index e6c70ed0bc2..a29ab4769dc 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1834,8 +1834,8 @@ MultiXactShmemSize(void)
 			 mul_size(sizeof(MultiXactId) * 2, MaxOldestSlot))

 	return size;
 }
@@ -1851,13 +1851,13 @@ MultiXactShmemInit(void)
 	MultiXactMemberCtl->PagePrecedes = MultiXactMemberPagePrecedes;
 
index 6a8e521f894..785f2520fde 100644

 void
 SUBTRANSShmemInit(void)
 {
index 85570085450..7f2b7598449 100644
@@ -117,7 +117,7 @@
@@ -235,7 +235,7 @@ typedef struct QueuePosition
@@ -521,7 +521,7 @@ AsyncShmemSize(void)

 	return size;
 }
@@ -569,7 +569,7 @@ AsyncShmemInit(void)
 
index 4f4d5b0d20f..c5e66757643 100644
@@ -1396,7 +1396,7 @@ PredicateLockShmemSize(void)

 	return size;
 }
index 381d9e548d1..c83151d5ab5 100644
 
index bff949a40bc..eb6bf4c0a04 100644
 
@@ -202,6 +204,8 @@ static const char *show_tcp_keepalives_idle(void);
@@ -2366,6 +2370,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
@@ -12074,6 +12155,24 @@ show_tcp_user_timeout(void)
 	return nbuf;
 }
 
+static const char *
+show_xact_buffers(void)
+{
+	static char nbuf[16];
+
index a1acd46b611..22bda4383ce 100644
@@ -195,6 +195,15 @@
 #old_snapshot_threshold = -1		# 1min-60d; -1 disables; 0 is immediate

index 39b8e4afa8a..739a292f7f3 100644
 
+/*
index a1538978c62..f86760f7240 100644
@@ -16,7 +16,6 @@
 #include "replication/origin.h"
 
-
 
 extern void TransactionTreeSetCommitTsData(TransactionId xid, int nsubxids,
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index 4bbb035eaea..97c0a463768 100644
--- a/src/include/access/multixact.h
+++ b/src/include/access/multixact.h
@@ -29,10 +29,6 @@

index dd52e8cec7e..793c045f160 100644
@@ -17,6 +17,11 @@
 
+/*
 
 /*
index d0ab44ae828..64fa86938e2 100644

index f371ac896b9..99575974982 100644
 
 #include <signal.h>
 
-/*
 
index 90a30160657..22d31546ad5 100644
@@ -176,6 +176,13 @@ extern PGDLLIMPORT int MaxBackends;
 
index 152b6986114..c72779bd88d 100644
 
-
2.33.1

From 5c1aace8b1f5a2b852bf1476dffbd4a43f196ef3 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Sun, 26 Dec 2021 15:03:30 +0500
Subject: [PATCH v=18 3/3] Pack SLRU page_number, page_status and page_dirty
 toogether

This allows to test only one cacheline during successfull
SlruSelectLRUPage().
---
 src/backend/access/transam/clog.c      |  12 +--
 src/backend/access/transam/commit_ts.c |   6 +-
 src/backend/access/transam/multixact.c |  16 +--
 src/backend/access/transam/slru.c      | 143 ++++++++++++-------------
 src/backend/access/transam/subtrans.c  |   4 +-
 src/backend/commands/async.c           |   2 +-
 src/backend/storage/lmgr/predicate.c   |   2 +-
 src/include/access/slru.h              |  13 ++-
 8 files changed, 99 insertions(+), 99 deletions(-)

diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
index ca28ada75fa..7d3a0286a5f 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -375,7 +375,7 @@ TransactionIdSetPageStatusInternal(TransactionId xid, int nsubxids,
 		{
 			for (i = 0; i < nsubxids; i++)
 			{
-				Assert(XactCtl->shared->page_number[slotno] == TransactionIdToPage(subxids[i]));
+				Assert(XactCtl->shared->page_entries[slotno].page_number == TransactionIdToPage(subxids[i]));
 				TransactionIdSetStatusBit(subxids[i],
 										  TRANSACTION_STATUS_SUB_COMMITTED,
 										  lsn, slotno);
@@ -389,11 +389,11 @@ TransactionIdSetPageStatusInternal(TransactionId xid, int nsubxids,
 	/* Set the subtransactions */
 	for (i = 0; i < nsubxids; i++)
 	{
-		Assert(XactCtl->shared->page_number[slotno] == TransactionIdToPage(subxids[i]));
+		Assert(XactCtl->shared->page_entries[slotno].page_number == TransactionIdToPage(subxids[i]));
 		TransactionIdSetStatusBit(subxids[i], status, lsn, slotno);
 	}
 
-	XactCtl->shared->page_dirty[slotno] = true;
+	XactCtl->shared->page_entries[slotno].page_dirty = true;
 }
 
 /*
@@ -713,7 +713,7 @@ BootStrapCLOG(void)
 
 	/* Make sure it's written out */
 	SimpleLruWritePage(XactCtl, slotno);
-	Assert(!XactCtl->shared->page_dirty[slotno]);
+	Assert(!XactCtl->shared->page_entries[slotno].page_dirty);
 
 	LWLockRelease(XactSLRULock);
 }
@@ -798,7 +798,7 @@ TrimCLOG(void)
 		/* Zero the rest of the page */
 		MemSet(byteptr + 1, 0, BLCKSZ - byteno - 1);
 
-		XactCtl->shared->page_dirty[slotno] = true;
+		XactCtl->shared->page_entries[slotno].page_dirty = true;
 	}
 
 	LWLockRelease(XactSLRULock);
@@ -994,7 +994,7 @@ clog_redo(XLogReaderState *record)
 
 		slotno = ZeroCLOGPage(pageno, false);
 		SimpleLruWritePage(XactCtl, slotno);
-		Assert(!XactCtl->shared->page_dirty[slotno]);
+		Assert(!XactCtl->shared->page_entries[slotno].page_dirty);
 
 		LWLockRelease(XactSLRULock);
 	}
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index cb2e0ceb1c3..6879ef3ec71 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -227,7 +227,7 @@ SetXidCommitTsInPage(TransactionId xid, int nsubxids,
 	for (i = 0; i < nsubxids; i++)
 		TransactionIdSetCommitTs(subxids[i], ts, nodeid, slotno);
 
-	CommitTsCtl->shared->page_dirty[slotno] = true;
+	CommitTsCtl->shared->page_entries[slotno].page_dirty = true;
 
 	LWLockRelease(CommitTsSLRULock);
 }
@@ -735,7 +735,7 @@ ActivateCommitTs(void)
 		LWLockAcquire(CommitTsSLRULock, LW_EXCLUSIVE);
 		slotno = ZeroCommitTsPage(pageno, false);
 		SimpleLruWritePage(CommitTsCtl, slotno);
-		Assert(!CommitTsCtl->shared->page_dirty[slotno]);
+		Assert(!CommitTsCtl->shared->page_entries[slotno].page_dirty);
 		LWLockRelease(CommitTsSLRULock);
 	}
 
@@ -1005,7 +1005,7 @@ commit_ts_redo(XLogReaderState *record)
 
 		slotno = ZeroCommitTsPage(pageno, false);
 		SimpleLruWritePage(CommitTsCtl, slotno);
-		Assert(!CommitTsCtl->shared->page_dirty[slotno]);
+		Assert(!CommitTsCtl->shared->page_entries[slotno].page_dirty);
 
 		LWLockRelease(CommitTsSLRULock);
 	}
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index a29ab4769dc..eb32821b717 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -887,7 +887,7 @@ RecordNewMultiXact(MultiXactId multi, MultiXactOffset offset,
 
 	*offptr = offset;
 
-	MultiXactOffsetCtl->shared->page_dirty[slotno] = true;
+	MultiXactOffsetCtl->shared->page_entries[slotno].page_dirty = true;
 
 	/* Exchange our lock */
 	LWLockRelease(MultiXactOffsetSLRULock);
@@ -931,7 +931,7 @@ RecordNewMultiXact(MultiXactId multi, MultiXactOffset offset,
 		flagsval |= (members[i].status << bshift);
 		*flagsptr = flagsval;
 
-		MultiXactMemberCtl->shared->page_dirty[slotno] = true;
+		MultiXactMemberCtl->shared->page_entries[slotno].page_dirty = true;
 	}
 
 	LWLockRelease(MultiXactMemberSLRULock);
@@ -1902,7 +1902,7 @@ BootStrapMultiXact(void)
 
 	/* Make sure it's written out */
 	SimpleLruWritePage(MultiXactOffsetCtl, slotno);
-	Assert(!MultiXactOffsetCtl->shared->page_dirty[slotno]);
+	Assert(!MultiXactOffsetCtl->shared->page_entries[slotno].page_dirty);

@@ -1913,7 +1913,7 @@ BootStrapMultiXact(void)
 
 	/* Make sure it's written out */
 	SimpleLruWritePage(MultiXactMemberCtl, slotno);
-	Assert(!MultiXactMemberCtl->shared->page_dirty[slotno]);
+	Assert(!MultiXactMemberCtl->shared->page_entries[slotno].page_dirty);
 
 	LWLockRelease(MultiXactMemberSLRULock);
 }
@@ -2074,7 +2074,7 @@ TrimMultiXact(void)
 
 		MemSet(offptr, 0, BLCKSZ - (entryno * sizeof(MultiXactOffset)));
 
-		MultiXactOffsetCtl->shared->page_dirty[slotno] = true;
+		MultiXactOffsetCtl->shared->page_entries[slotno].page_dirty = true;
 	}
 
 	LWLockRelease(MultiXactOffsetSLRULock);
@@ -2112,7 +2112,7 @@ TrimMultiXact(void)
 		 * writing.
 		 */
 
-		MultiXactMemberCtl->shared->page_dirty[slotno] = true;
+		MultiXactMemberCtl->shared->page_entries[slotno].page_dirty = true;
 	}
 
 	LWLockRelease(MultiXactMemberSLRULock);
@@ -3251,7 +3251,7 @@ multixact_redo(XLogReaderState *record)
 
 		slotno = ZeroMultiXactOffsetPage(pageno, false);
 		SimpleLruWritePage(MultiXactOffsetCtl, slotno);
-		Assert(!MultiXactOffsetCtl->shared->page_dirty[slotno]);
+		Assert(!MultiXactOffsetCtl->shared->page_entries[slotno].page_dirty);
 
 		LWLockRelease(MultiXactOffsetSLRULock);
 	}
@@ -3266,7 +3266,7 @@ multixact_redo(XLogReaderState *record)
 
 		slotno = ZeroMultiXactMemberPage(pageno, false);
 		SimpleLruWritePage(MultiXactMemberCtl, slotno);
-		Assert(!MultiXactMemberCtl->shared->page_dirty[slotno]);
+		Assert(!MultiXactMemberCtl->shared->page_entries[slotno].page_dirty);
 
 		LWLockRelease(MultiXactMemberSLRULock);
 	}
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 33857bffb79..1f87e50f05c 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -186,9 +186,7 @@ SimpleLruShmemSize(int nslots, int nlsns)
 	/* we assume nslots isn't so large as to risk overflow */
 	sz = MAXALIGN(sizeof(SlruSharedData));
 	sz += MAXALIGN(nslots * sizeof(char *));	/* page_buffer[] */
-	sz += MAXALIGN(nslots * sizeof(SlruPageStatus));	/* page_status[] */
-	sz += MAXALIGN(nslots * sizeof(bool));	/* page_dirty[] */
-	sz += MAXALIGN(nslots * sizeof(int));	/* page_number[] */
+	sz += MAXALIGN(nslots * sizeof(SlruPageEntry));	/* page_entries[] */
 	sz += MAXALIGN(nslots * sizeof(int));	/* page_lru_count[] */
 	sz += MAXALIGN(nslots * sizeof(LWLockPadded));	/* buffer_locks[] */
 
@@ -248,16 +246,13 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 
 		shared->slru_stats_idx = pgstat_slru_index(name);
 
+		Assert(sizeof(SlruPageEntry) == 8);
 		ptr = (char *) shared;
 		offset = MAXALIGN(sizeof(SlruSharedData));
 		shared->page_buffer = (char **) (ptr + offset);
 		offset += MAXALIGN(nslots * sizeof(char *));
-		shared->page_status = (SlruPageStatus *) (ptr + offset);
-		offset += MAXALIGN(nslots * sizeof(SlruPageStatus));
-		shared->page_dirty = (bool *) (ptr + offset);
-		offset += MAXALIGN(nslots * sizeof(bool));
-		shared->page_number = (int *) (ptr + offset);
-		offset += MAXALIGN(nslots * sizeof(int));
+		shared->page_entries = (SlruPageEntry *) (ptr + offset);
+		offset += MAXALIGN(nslots * sizeof(SlruPageEntry));
 		shared->page_lru_count = (int *) (ptr + offset);
 		offset += MAXALIGN(nslots * sizeof(int));
 
@@ -278,8 +273,8 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 							 tranche_id);
 
 			shared->page_buffer[slotno] = ptr;
-			shared->page_status[slotno] = SLRU_PAGE_EMPTY;
-			shared->page_dirty[slotno] = false;
+			shared->page_entries[slotno].page_status = SLRU_PAGE_EMPTY;
+			shared->page_entries[slotno].page_dirty = false;
 			shared->page_lru_count[slotno] = 0;
 			ptr += BLCKSZ;
 		}
@@ -315,15 +310,15 @@ SimpleLruZeroPage(SlruCtl ctl, int pageno)
 
 	/* Find a suitable buffer slot for the page */
 	slotno = SlruSelectLRUPage(ctl, pageno);
-	Assert(shared->page_status[slotno] == SLRU_PAGE_EMPTY ||
-		   (shared->page_status[slotno] == SLRU_PAGE_VALID &&
-			!shared->page_dirty[slotno]) ||
-		   shared->page_number[slotno] == pageno);
+	Assert(shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY ||
+		   (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID &&
+			!shared->page_entries[slotno].page_dirty) ||
+		   shared->page_entries[slotno].page_number == pageno);
 
 	/* Mark the slot as containing this page */
-	shared->page_number[slotno] = pageno;
-	shared->page_status[slotno] = SLRU_PAGE_VALID;
-	shared->page_dirty[slotno] = true;
+	shared->page_entries[slotno].page_number = pageno;
+	shared->page_entries[slotno].page_status = SLRU_PAGE_VALID;
+	shared->page_entries[slotno].page_dirty = true;
 	SlruRecentlyUsed(shared, slotno);
 
 	/* Set the buffer to zeroes */
@@ -387,18 +382,18 @@ SimpleLruWaitIO(SlruCtl ctl, int slotno)
 	 * cheaply test for failure by seeing if the buffer lock is still held (we
 	 * assume that transaction abort would release the lock).
 	 */
-	if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS ||
-		shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS)
+	if (shared->page_entries[slotno].page_status == SLRU_PAGE_READ_IN_PROGRESS ||
+		shared->page_entries[slotno].page_status == SLRU_PAGE_WRITE_IN_PROGRESS)
 	{
 		if (LWLockConditionalAcquire(&shared->buffer_locks[slotno].lock, LW_SHARED))
 		{
 			/* indeed, the I/O must have failed */
-			if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS)
-				shared->page_status[slotno] = SLRU_PAGE_EMPTY;
+			if (shared->page_entries[slotno].page_status == SLRU_PAGE_READ_IN_PROGRESS)
+				shared->page_entries[slotno].page_status = SLRU_PAGE_EMPTY;
 			else				/* write_in_progress */
 			{
-				shared->page_status[slotno] = SLRU_PAGE_VALID;
-				shared->page_dirty[slotno] = true;
+				shared->page_entries[slotno].page_status = SLRU_PAGE_VALID;
+				shared->page_entries[slotno].page_dirty = true;
 			}
 			LWLockRelease(&shared->buffer_locks[slotno].lock);
 		}
@@ -438,15 +433,15 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 		slotno = SlruSelectLRUPage(ctl, pageno);
 
 		/* Did we find the page in memory? */
-		if (shared->page_number[slotno] == pageno &&
-			shared->page_status[slotno] != SLRU_PAGE_EMPTY)
+		if (shared->page_entries[slotno].page_number == pageno &&
+			shared->page_entries[slotno].page_status != SLRU_PAGE_EMPTY)
 		{
 			/*
 			 * If page is still being read in, we must wait for I/O.  Likewise
 			 * if the page is being written and the caller said that's not OK.
 			 */
-			if (shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS ||
-				(shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS &&
+			if (shared->page_entries[slotno].page_status == SLRU_PAGE_READ_IN_PROGRESS ||
+				(shared->page_entries[slotno].page_status == SLRU_PAGE_WRITE_IN_PROGRESS &&
 				 !write_ok))
 			{
 				SimpleLruWaitIO(ctl, slotno);
@@ -463,14 +458,14 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 		}
 
 		/* We found no match; assert we selected a freeable slot */
-		Assert(shared->page_status[slotno] == SLRU_PAGE_EMPTY ||
-			   (shared->page_status[slotno] == SLRU_PAGE_VALID &&
-				!shared->page_dirty[slotno]));
+		Assert(shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY ||
+			   (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID &&
+				!shared->page_entries[slotno].page_dirty));
 
 		/* Mark the slot read-busy */
-		shared->page_number[slotno] = pageno;
-		shared->page_status[slotno] = SLRU_PAGE_READ_IN_PROGRESS;
-		shared->page_dirty[slotno] = false;
+		shared->page_entries[slotno].page_number = pageno;
+		shared->page_entries[slotno].page_status = SLRU_PAGE_READ_IN_PROGRESS;
+		shared->page_entries[slotno].page_dirty = false;
 
 		/* Acquire per-buffer lock (cannot deadlock, see notes at top) */
 		LWLockAcquire(&shared->buffer_locks[slotno].lock, LW_EXCLUSIVE);
@@ -487,11 +482,11 @@ SimpleLruReadPage(SlruCtl ctl, int pageno, bool write_ok,
 		/* Re-acquire control lock and update page state */
 		LWLockAcquire(shared->ControlLock, LW_EXCLUSIVE);
 
-		Assert(shared->page_number[slotno] == pageno &&
-			   shared->page_status[slotno] == SLRU_PAGE_READ_IN_PROGRESS &&
-			   !shared->page_dirty[slotno]);
+		Assert(shared->page_entries[slotno].page_number == pageno &&
+			   shared->page_entries[slotno].page_status == SLRU_PAGE_READ_IN_PROGRESS &&
+			   !shared->page_entries[slotno].page_dirty);
 
-		shared->page_status[slotno] = ok ? SLRU_PAGE_VALID : SLRU_PAGE_EMPTY;
+		shared->page_entries[slotno].page_status = ok ? SLRU_PAGE_VALID : SLRU_PAGE_EMPTY;

@@ -536,9 +531,9 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
 	int bankend = bankstart + shared->bank_size;
 	for (slotno = bankstart; slotno < bankend; slotno++)
 	{
-		if (shared->page_number[slotno] == pageno &&
-			shared->page_status[slotno] != SLRU_PAGE_EMPTY &&
-			shared->page_status[slotno] != SLRU_PAGE_READ_IN_PROGRESS)
+		if (shared->page_entries[slotno].page_number == pageno &&
+			shared->page_entries[slotno].page_status != SLRU_PAGE_EMPTY &&
+			shared->page_entries[slotno].page_status != SLRU_PAGE_READ_IN_PROGRESS)
 		{
 			/* See comments for SlruRecentlyUsed macro */
 			SlruRecentlyUsed(shared, slotno);
@@ -572,12 +567,12 @@ static void
 SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 {
 	SlruShared	shared = ctl->shared;
-	int			pageno = shared->page_number[slotno];
+	int			pageno = shared->page_entries[slotno].page_number;
 	bool		ok;
 
 	/* If a write is in progress, wait for it to finish */
-	while (shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS &&
-		   shared->page_number[slotno] == pageno)
+	while (shared->page_entries[slotno].page_status == SLRU_PAGE_WRITE_IN_PROGRESS &&
+		   shared->page_entries[slotno].page_number == pageno)
 	{
 		SimpleLruWaitIO(ctl, slotno);
 	}
@@ -586,17 +581,17 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 	 * Do nothing if page is not dirty, or if buffer no longer contains the
 	 * same page we were called for.
 	 */
-	if (!shared->page_dirty[slotno] ||
-		shared->page_status[slotno] != SLRU_PAGE_VALID ||
-		shared->page_number[slotno] != pageno)
+	if (!shared->page_entries[slotno].page_dirty ||
+		shared->page_entries[slotno].page_status != SLRU_PAGE_VALID ||
+		shared->page_entries[slotno].page_number != pageno)
 		return;
 
 	/*
 	 * Mark the slot write-busy, and clear the dirtybit.  After this point, a
 	 * transaction status update on this page will mark it dirty again.
 	 */
-	shared->page_status[slotno] = SLRU_PAGE_WRITE_IN_PROGRESS;
-	shared->page_dirty[slotno] = false;
+	shared->page_entries[slotno].page_status = SLRU_PAGE_WRITE_IN_PROGRESS;
+	shared->page_entries[slotno].page_dirty = false;
 
 	/* Acquire per-buffer lock (cannot deadlock, see notes at top) */
 	LWLockAcquire(&shared->buffer_locks[slotno].lock, LW_EXCLUSIVE);
@@ -619,14 +614,14 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 	/* Re-acquire control lock and update page state */
 	LWLockAcquire(shared->ControlLock, LW_EXCLUSIVE);
 
-	Assert(shared->page_number[slotno] == pageno &&
-		   shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS);
+	Assert(shared->page_entries[slotno].page_number == pageno &&
+		   shared->page_entries[slotno].page_status == SLRU_PAGE_WRITE_IN_PROGRESS);
 
 	/* If we failed to write, mark the page dirty again */
 	if (!ok)
-		shared->page_dirty[slotno] = true;
+		shared->page_entries[slotno].page_dirty = true;
 
-	shared->page_status[slotno] = SLRU_PAGE_VALID;
+	shared->page_entries[slotno].page_status = SLRU_PAGE_VALID;

@@ -1067,8 +1062,8 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int bankend = bankstart + shared->bank_size;
 		for (slotno = bankstart; slotno < bankend; slotno++)
 		{
-			if (shared->page_number[slotno] == pageno &&
-				shared->page_status[slotno] != SLRU_PAGE_EMPTY)
+			if (shared->page_entries[slotno].page_number == pageno &&
+				shared->page_entries[slotno].page_status != SLRU_PAGE_EMPTY)
 				return slotno;
 		}
 
@@ -1105,7 +1100,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 			int			this_delta;
 			int			this_page_number;
 
-			if (shared->page_status[slotno] == SLRU_PAGE_EMPTY)
+			if (shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY)
 				return slotno;
 			this_delta = cur_count - shared->page_lru_count[slotno];
 			if (this_delta < 0)
@@ -1120,10 +1115,10 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 				shared->page_lru_count[slotno] = cur_count;
 				this_delta = 0;
 			}
-			this_page_number = shared->page_number[slotno];
+			this_page_number = shared->page_entries[slotno].page_number;
 			if (this_page_number == shared->latest_page_number)
 				continue;
-			if (shared->page_status[slotno] == SLRU_PAGE_VALID)
+			if (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID)
 			{
 				if (this_delta > best_valid_delta ||
 					(this_delta == best_valid_delta &&
@@ -1165,7 +1160,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		/*
 		 * If the selected page is clean, we're set.
 		 */
-		if (!shared->page_dirty[bestvalidslot])
+		if (!shared->page_entries[bestvalidslot].page_dirty)
 			return bestvalidslot;
 
 		/*
@@ -1217,9 +1212,9 @@ SimpleLruWriteAll(SlruCtl ctl, bool allow_redirtied)
 		 * already.  That's okay.
 		 */
 		Assert(allow_redirtied ||
-			   shared->page_status[slotno] == SLRU_PAGE_EMPTY ||
-			   (shared->page_status[slotno] == SLRU_PAGE_VALID &&
-				!shared->page_dirty[slotno]));
+			   shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY ||
+			   (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID &&
+				!shared->page_entries[slotno].page_dirty));
 	}
 
 	LWLockRelease(shared->ControlLock);
@@ -1291,18 +1286,18 @@ restart:;
 
 	for (slotno = 0; slotno < shared->num_slots; slotno++)
 	{
-		if (shared->page_status[slotno] == SLRU_PAGE_EMPTY)
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY)
 			continue;
-		if (!ctl->PagePrecedes(shared->page_number[slotno], cutoffPage))
+		if (!ctl->PagePrecedes(shared->page_entries[slotno].page_number, cutoffPage))
 			continue;
 
 		/*
 		 * If page is clean, just change state to EMPTY (expected case).
 		 */
-		if (shared->page_status[slotno] == SLRU_PAGE_VALID &&
-			!shared->page_dirty[slotno])
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID &&
+			!shared->page_entries[slotno].page_dirty)
 		{
-			shared->page_status[slotno] = SLRU_PAGE_EMPTY;
+			shared->page_entries[slotno].page_status = SLRU_PAGE_EMPTY;
 			continue;
 		}
 
@@ -1316,7 +1311,7 @@ restart:;
 		 * won't have cause to read its data again.  For now, keep the logic
 		 * the same as it was.)
 		 */
-		if (shared->page_status[slotno] == SLRU_PAGE_VALID)
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID)
 			SlruInternalWritePage(ctl, slotno, NULL);
 		else
 			SimpleLruWaitIO(ctl, slotno);
@@ -1371,9 +1366,9 @@ restart:
 	did_write = false;
 	for (slotno = 0; slotno < shared->num_slots; slotno++)
 	{
-		int			pagesegno = shared->page_number[slotno] / SLRU_PAGES_PER_SEGMENT;
+		int			pagesegno = shared->page_entries[slotno].page_number / SLRU_PAGES_PER_SEGMENT;
 
-		if (shared->page_status[slotno] == SLRU_PAGE_EMPTY)
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_EMPTY)
 			continue;
 
 		/* not the segment we're looking for */
@@ -1381,15 +1376,15 @@ restart:
 			continue;
 
 		/* If page is clean, just change state to EMPTY (expected case). */
-		if (shared->page_status[slotno] == SLRU_PAGE_VALID &&
-			!shared->page_dirty[slotno])
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID &&
+			!shared->page_entries[slotno].page_dirty)
 		{
-			shared->page_status[slotno] = SLRU_PAGE_EMPTY;
+			shared->page_entries[slotno].page_status = SLRU_PAGE_EMPTY;
 			continue;
 		}
 
 		/* Same logic as SimpleLruTruncate() */
-		if (shared->page_status[slotno] == SLRU_PAGE_VALID)
+		if (shared->page_entries[slotno].page_status == SLRU_PAGE_VALID)
 			SlruInternalWritePage(ctl, slotno, NULL);
 		else
 			SimpleLruWaitIO(ctl, slotno);
diff --git a/src/backend/access/transam/subtrans.c b/src/backend/access/transam/subtrans.c
index 785f2520fde..11754fdbac6 100644
--- a/src/backend/access/transam/subtrans.c
+++ b/src/backend/access/transam/subtrans.c
@@ -97,7 +97,7 @@ SubTransSetParent(TransactionId xid, TransactionId parent)
 	{
 		Assert(*ptr == InvalidTransactionId);
 		*ptr = parent;
-		SubTransCtl->shared->page_dirty[slotno] = true;
+		SubTransCtl->shared->page_entries[slotno].page_dirty = true;
 	}
 
 	LWLockRelease(SubtransSLRULock);
@@ -220,7 +220,7 @@ BootStrapSUBTRANS(void)
 
 	/* Make sure it's written out */
 	SimpleLruWritePage(SubTransCtl, slotno);
-	Assert(!SubTransCtl->shared->page_dirty[slotno]);
+	Assert(!SubTransCtl->shared->page_entries[slotno].page_dirty);
 
 	LWLockRelease(SubtransSLRULock);
 }
diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c
index 7f2b7598449..86a5bc2430c 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -1445,7 +1445,7 @@ asyncQueueAddEntries(ListCell *nextNotify)
 								   InvalidTransactionId);
 
 	/* Note we mark the page dirty before writing in it */
-	NotifyCtl->shared->page_dirty[slotno] = true;
+	NotifyCtl->shared->page_entries[slotno].page_dirty = true;
 
 	while (nextNotify != NULL)
 	{
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index c5e66757643..53bd7d957ce 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -963,7 +963,7 @@ SerialAdd(TransactionId xid, SerCommitSeqNo minConflictCommitSeqNo)
 		slotno = SimpleLruReadPage(SerialSlruCtl, targetPage, true, xid);
 
 	SerialValue(slotno, xid) = minConflictCommitSeqNo;
-	SerialSlruCtl->shared->page_dirty[slotno] = true;
+	SerialSlruCtl->shared->page_entries[slotno].page_dirty = true;
 
 	LWLockRelease(SerialSLRULock);
 }
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index f4df54d3c12..0a4fae91d7f 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -44,7 +44,7 @@
  * in the latter case it implies that the page has been re-dirtied since
  * the write started.
  */
-typedef enum
+typedef enum SlruPageStatus:int16_t
 {
 	SLRU_PAGE_EMPTY,			/* buffer is not in use */
 	SLRU_PAGE_READ_IN_PROGRESS, /* page is being read in */
@@ -52,6 +52,13 @@ typedef enum
 	SLRU_PAGE_WRITE_IN_PROGRESS /* page is being written out */
 } SlruPageStatus;
 
+typedef struct SlruPageEntry
+{
+	int				page_number;
+	SlruPageStatus	page_status;
+	bool			page_dirty;
+} SlruPageEntry;
+
 /*
  * Shared-memory state
  */
@@ -69,9 +76,7 @@ typedef struct SlruSharedData
 	 * when status is EMPTY, as is page_lru_count.
 	 */
 	char	  **page_buffer;
-	SlruPageStatus *page_status;
-	bool	   *page_dirty;
-	int		   *page_number;
+	SlruPageEntry *page_entries;
 	int		   *page_lru_count;
 	LWLockPadded *buffer_locks;
 
-- 
2.33.1

From 12a1c31240e338c0e8c3c7211fbdd7b2e9666564 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Sun, 11 Apr 2021 21:18:10 +0300
Subject: [PATCH v=18 2/3] Divide SLRU buffers into 8-associative banks

We want to eliminate linear search within SLRU buffers.
To do so we divide SLRU buffers into banks. Each bank holds
approximately 8 buffers. Each SLRU pageno may reside only in one bank.
Adjacent pagenos reside in different banks.
---
 src/backend/access/transam/slru.c | 43 ++++++++++++++++++++++++++++---
 src/include/access/slru.h         |  2 ++
 2 files changed, 41 insertions(+), 4 deletions(-)

diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 7585ae24ce9..33857bffb79 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -134,7 +134,7 @@ typedef enum
 static SlruErrorCause slru_errcause;
 static int	slru_errno;
 
-
+static void SlruAdjustNSlots(int* nslots, int* banksize, int* bankoffset);
 static void SimpleLruZeroLSNs(SlruCtl ctl, int slotno);
 static void SimpleLruWaitIO(SlruCtl ctl, int slotno);
 static void SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata);
@@ -148,6 +148,30 @@ static bool SlruScanDirCbDeleteCutoff(SlruCtl ctl, char *filename,
 									  int segpage, void *data);
 static void SlruInternalDeleteSegment(SlruCtl ctl, int segno);
 
+/*
+ * Pick bank size optimal for N-assiciative SLRU buffers.
+ * We expect bank number to be picked from lowest bits of requested pageno.
+ * Thus we want number of banks to be power of 2. This routine computes number
+ * of banks aiming to make each bank of size 8. So we can pack page number and
+ * statuses of each bank on one cacheline.
+ */
+static void SlruAdjustNSlots(int* nslots, int* banksize, int* bankoffset)
+{
+	*banksize = *nslots;
+	int nbanks = 1;
+	*bankoffset = 0;
+	while (*banksize > 15)
+	{
+		if ((*banksize & 1) != 0)
+			*banksize +=1;
+		*banksize /= 2;
+		nbanks *= 2;
+		*bankoffset += 1;
+	}
+	elog(DEBUG5, "nslots %d banksize %d nbanks %d ", *nslots, *banksize, nbanks);
+	*nslots = *banksize * nbanks;
+}
+
 /*
  * Initialization of shared memory
  */
@@ -156,6 +180,8 @@ Size
 SimpleLruShmemSize(int nslots, int nlsns)
 {
 	Size		sz;
+	int bankoffset, banksize;
+	SlruAdjustNSlots(&nslots, &banksize, &bankoffset);
 
 	/* we assume nslots isn't so large as to risk overflow */
 	sz = MAXALIGN(sizeof(SlruSharedData));
@@ -190,6 +216,8 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 {
 	SlruShared	shared;
 	bool		found;
+	int bankoffset, banksize;
+	SlruAdjustNSlots(&nslots, &banksize, &bankoffset);
 
 	shared = (SlruShared) ShmemInitStruct(name,
 										  SimpleLruShmemSize(nslots, nlsns),
@@ -209,6 +237,9 @@ SimpleLruInit(SlruCtl ctl, const char *name, int nslots, int nlsns,
 		shared->ControlLock = ctllock;
 
 		shared->num_slots = nslots;
+		shared->bank_mask =  (1 << bankoffset) - 1;
+		shared->bank_size = banksize;
+		
 		shared->lsn_groups_per_page = nlsns;
 
 		shared->cur_lru_count = 0;
@@ -501,7 +532,9 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)
 	LWLockAcquire(shared->ControlLock, LW_SHARED);
 
 	/* See if page is already in a buffer */
-	for (slotno = 0; slotno < shared->num_slots; slotno++)
+	int bankstart = (pageno & shared->bank_mask) * shared->bank_size;
+	int bankend = bankstart + shared->bank_size;
+	for (slotno = bankstart; slotno < bankend; slotno++)
 	{
 		if (shared->page_number[slotno] == pageno &&
 			shared->page_status[slotno] != SLRU_PAGE_EMPTY &&
@@ -1030,7 +1063,9 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		int			best_invalid_page_number = 0;	/* keep compiler quiet */
 
 		/* See if page already has a buffer assigned */
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		int bankstart = (pageno & shared->bank_mask) * shared->bank_size;
+		int bankend = bankstart + shared->bank_size;
+		for (slotno = bankstart; slotno < bankend; slotno++)
 		{
 			if (shared->page_number[slotno] == pageno &&
 				shared->page_status[slotno] != SLRU_PAGE_EMPTY)
@@ -1065,7 +1100,7 @@ SlruSelectLRUPage(SlruCtl ctl, int pageno)
 		 * multiple pages with the same lru_count.
 		 */
 		cur_count = (shared->cur_lru_count)++;
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		for (slotno = bankstart; slotno < bankend; slotno++)
 		{
 			int			this_delta;
 			int			this_page_number;
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index 793c045f160..f4df54d3c12 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -61,6 +61,8 @@ typedef struct SlruSharedData
 
+	int			bank_size;
+	int			bank_mask;
 
 	/*
2.33.1

---

From: Yura Sokolov <[email-masked]>
Date: Thu, 21 Jul 2022 16:00:20 +0300

Good day, all.

I did benchmark of patch on 2 socket Xeon 5220 CPU @ 2.20GHz .
I used "benchmark" used to reproduce problems with SLRU on our
customers setup.
In opposite to Shawn's tests I concentrated on bad case: a lot
of contention.

slru-funcs.sql - function definitions
  - functions creates a lot of subtrunsactions to stress subtrans
  - and select random rows for share to stress multixacts

slru-call.sql - function call for benchmark

slru-ballast.sql - randomly select 1000 consequent rows
    "for update skip locked" to stress multixacts

patch1 - make SLRU buffers configurable
patch2 - make "8-associative banks"

Benchmark done by pgbench.
Inited with scale 1 to induce contention.
    pgbench -i -s 1 testdb

Benchmark 1:
- low number of connections (50), 60% slru-call, 40% slru-ballast
    pgbench -f slru-call.sql@60 -f slru-ballast.sql@40 -c 50 -j 75 -P 1 -T 30 testdb

version | subtrans | multixact | tps
        | buffers  | offs/memb | func+ballast
--------+----------+-----------+------
master  | 32       | 8/16      | 184+119
patch1  | 32       | 8/16      | 184+119
patch1  | 1024     | 8/16      | 121+77
patch1  | 1024     | 512/1024  | 118+75
patch2  | 32       | 8/16      | 190+122
patch2  | 1024     | 8/16      | 190+125
patch2  | 1024     | 512/1024  | 190+127

As you see, this test case degrades with dumb increase of
SLRU buffers. But use of "hash table" in form of "associative
buckets" makes performance stable.

Benchmark 2:
- high connection number (600), 98% slru-call, 2% slru-ballast
    pgbench -f slru-call.sql@98 -f slru-ballast.sql@2 -c 600 -j 75 -P 1 -T 30 testdb

I don't paste "ballast" tps here since 2% make them too small,
and they're very noisy.

version | subtrans | multixact | tps
        | buffers  | offs/memb | func
--------+----------+-----------+------
master  | 32       | 8/16      | 13
patch1  | 32  
    | 8/16      | 13
patch1  | 1024     | 8/16      | 31
patch1  | 1024     | 512/1024  | 53
patch2  | 32       | 8/16      | 12
patch2  | 1024     | 8/16      | 34
patch2  | 1024     | 512/1024  | 67

In this case simple buffer increase does help. But "buckets"
increase performance gain.

I didn't paste here results third part of patch ("Pack SLRU...")
because I didn't see any major performance gain from it, and
it consumes large part of patch diff.

Rebased versions of first two patch parts are attached.

regards,

Yura Sokolov
From 41ec9d1c54184c515d53ecc8021c4a998813f2a9 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Sun, 11 Apr 2021 21:18:10 +0300
Subject: [PATCH v21 2/2] Divide SLRU buffers into 8-associative banks

index b65cb49d7ff..abc534bbd06 100644
 
-
 
+/*

 
 
@@ -496,12 +527,14 @@ SimpleLruReadPage_ReadOnly(SlruCtl ctl, int pageno, TransactionId xid)

 
index b7e2e2b55e3..cc9cc10d271 100644

 	/*

From e5f03ebac800f06f45c9ecb5f7139c96032e7fa7 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Mon, 15 Feb 2021 21:51:56 +0500
Subject: [PATCH v21 1/2] Make all SLRU buffer sizes configurable.

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e2d728e0c4f..991dba69757 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1961,6 +1961,141 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
       <indexterm>
index 3d9088a7048..e96a75d6959 100644

@@ -665,23 +665,16 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
 
 /*
index 4dc8d402bd3..48ca5007475 100644
@@ -514,10 +514,15 @@ pg_xact_commit_timestamp_origin(PG_FUNCTION_ARGS)
 
index 8f7d12950e5..71d17e338e9 100644

 	return size;
 }
 
index 66d35481552..862b0eab966 100644

 void
 SUBTRANSShmemInit(void)
 {
index 3e1b92df030..6b980603302 100644

 	return size;
 }
 
index 5136da6ea36..54a0d66d579 100644

 	return size;
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 1a5d29ac9ba..cc0ca91a8b3 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -151,3 +151,11 @@ int64		VacuumPageDirty = 0;
 
index af4a1c30689..2d9121b9265 100644
 
@@ -209,6 +211,8 @@ static const char *show_tcp_keepalives_idle(void);
@@ -2426,6 +2430,83 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
@@ -12447,6 +12528,24 @@ show_tcp_user_timeout(void)
 	return nbuf;
 }
 
+static const char *
+show_xact_buffers(void)
+{
+	static char nbuf[16];
+
index b4bc06e5f5a..2cb827e1e3b 100644

index 543f2e2643a..17d103aa4da 100644
 
+/*
index 7662f8e1a9c..d928dcc9352 100644
 
-
 
index a5600a320ae..da7b8f0abb9 100644

index 130c41c8632..b7e2e2b55e3 100644
 
+/*
 
 /*
index f94e116640b..1ddb62883b0 100644

index 926af933d1b..402d184b9b3 100644
 
 #include <signal.h>
 
-/*
- * The number of SLRU page buffers we use for the notification queue.
- */
-#define NUM_NOTIFY_BUFFERS	8
-
 extern PGDLLIMPORT bool Trace_notify;
 extern PGDLLIMPORT volatile sig_atomic_t notifyInterruptPending;
 
index ea9a56d3955..e65990e04e8 100644
@@ -177,6 +177,13 @@ extern PGDLLIMPORT int MaxBackends;
 
 extern PGDLLIMPORT int MyProcPid;
 extern PGDLLIMPORT pg_time_t MyStartTime;
diff --git a/src/include/storage/predicate.h b/src/include/storage/predicate.h
index 8dfcb3944b4..1e3f757ec30 100644
--- a/src/include/storage/predicate.h
+++ b/src/include/storage/predicate.h
@@ -26,10 +26,6 @@ extern PGDLLIMPORT int max_predicate_locks_per_xact;
 extern PGDLLIMPORT int max_predicate_locks_per_relation;
 extern PGDLLIMPORT int max_predicate_locks_per_page;
 
-

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 23 Jul 2022 13:39:50 +0500

> On 21 Jul 2022, at 18:00, Yura Sokolov <[email-masked]> wrote:
> 
Yura, thank you for your benchmarks!
We already knew that patch can save the day on pathological workloads, now we have a proof of this.
Also there's the evidence that user can blindly increase size of SLRU if they want (with the second patch). So there's no need for hard explanations on how to tune the buffers size.

Thomas, do you still have any doubts? Or is it certain that SLRU will be replaced by any better subsystem in 16?

---

From: Thomas Munro <[email-masked]>
Date: Sat, 23 Jul 2022 20:47:48 +1200

Hi Andrey,

Sorry for my lack of replies on this and the other SLRU thread -- I'm
thinking and experimenting.  More soon.

---

From: [email-masked]
Date: Tue, 16 Aug 2022 22:36:27 +0300

> Andrey Borodin wrote 2022-07-23 11:39:
> 
> Also there's the evidence that user can blindly increase size of SLRU
> if they want (with the second patch). So there's no need for hard
> explanations on how to tune the buffers size.

Hi @Andrey.Borodin, With some considerations and performance checks from 
@Yura.Sokolov we simplified your approach by the following:

1. Preamble. We feel free to increase any SLRU's, since there's no 
performance degradation on large Buffers count using your SLRU buckets 
solution.
2. `slru_buffers_size_scale` is only one config param introduced for all 
SLRUs. It scales SLRUs upper cap by power 2.
3. All SLRU buffers count are capped by both `MBuffers (shared_buffers)` 
and `slru_buffers_size_scale`. see
4. Magic initial constants `NUM_*_BUFFERS << slru_buffers_size_scale` 
are applied for every SLRU.
5. All SLRU buffers are always sized as power of 2, their hash bucket 
size is always 8.

There's attached patch for your consideration. It does gather and 
simplify both `v21-0001-Make-all-SLRU-buffer-sizes-configurable.patch` 
and `v21-0002-Divide-SLRU-buffers-into-8-associative-banks.patch` to 
much simpler approach.

Thank you, Yours,
- Ivan
Index: src/include/miscadmin.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
--- a/src/include/miscadmin.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/miscadmin.h	(date 1660678108730)
@@ -176,6 +176,7 @@
 extern PGDLLIMPORT int MaxConnections;
 extern PGDLLIMPORT int max_worker_processes;
 extern PGDLLIMPORT int max_parallel_workers;
+extern PGDLLIMPORT int slru_buffers_size_scale;
 
 extern PGDLLIMPORT int MyProcPid;
 extern PGDLLIMPORT pg_time_t MyStartTime;
Index: src/include/access/subtrans.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/access/subtrans.h b/src/include/access/subtrans.h
--- a/src/include/access/subtrans.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/access/subtrans.h	(date 1660678108698)
@@ -12,7 +12,7 @@
 #define SUBTRANS_H
 
 /* Number of SLRU buffers to use for subtrans */
-#define NUM_SUBTRANS_BUFFERS	32
+#define NUM_SUBTRANS_BUFFERS	(32 << slru_buffers_size_scale)
 
 extern void SubTransSetParent(TransactionId xid, TransactionId parent);
 extern TransactionId SubTransGetParent(TransactionId xid);
Index: src/backend/utils/init/globals.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
--- a/src/backend/utils/init/globals.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/utils/init/globals.c	(date 1660678064048)
@@ -150,3 +150,5 @@
 
 int			VacuumCostBalance = 0;	/* working state for vacuum */
 bool		VacuumCostActive = false;
+
+int			slru_buffers_size_scale = 2;	/* power 2 scale for SLRU buffers */
Index: src/backend/access/transam/slru.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
--- a/src/backend/access/transam/slru.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/access/transam/slru.c	(date 1660678063980)
@@ -59,6 +59,7 @@
 #include "pgstat.h"
 #include "storage/fd.h"
 #include "storage/shmem.h"
+#include "port/pg_bitutils.h"
 
@@ -71,6 +72,17 @@
 
+/*
+ * To avoid overflowing internal arithmetic and the size_t data type, the
+ * number of buffers should not exceed this number.
+ */
+#define SLRU_MAX_ALLOWED_BUFFERS ((1024 * 1024 * 1024) / BLCKSZ)
+
+/*
+ * SLRU bank size for slotno hash banks
+ */
+#define SLRU_BANK_SIZE 8
+
 typedef struct SlruWriteAllData
 {
 	int			num_files;		/* # files actually open */
@@ -134,7 +146,7 @@
 static SlruErrorCause slru_errcause;
 static int	slru_errno;
 
-
+static void SlruAdjustNSlots(int *nslots, int *bankmask);
@@ -148,6 +160,25 @@
 
+/*
+ * Pick number of slots and bank size optimal for hashed associative SLRU buffers.
+ * We declare SLRU nslots is always power of 2.
+ * We split SLRU to 8-sized hash banks, after some performance benchmarks.
+ * We hash pageno to banks by pageno masked by 3 upper bits.
+ */
+static void
+SlruAdjustNSlots(int *nslots, int *bankmask)
+{
+	Assert(*nslots > 0);
+	Assert(*nslots <= SLRU_MAX_ALLOWED_BUFFERS);
+
+	*nslots = (int) pg_nextpower2_32(Max(SLRU_BANK_SIZE, Min(*nslots, NBuffers / 256)));
+
+	*bankmask = *nslots / SLRU_BANK_SIZE - 1;
+
+	elog(DEBUG5, "nslots %d banksize %d nbanks %d bankmask %x", *nslots, SLRU_BANK_SIZE, *nslots / SLRU_BANK_SIZE, *bankmask);
+}
+
 /*
  * Initialization of shared memory
  */
@@ -156,6 +187,9 @@
 SimpleLruShmemSize(int nslots, int nlsns)
 {
 	Size		sz;
+	int			bankmask_ignore;
+
+	SlruAdjustNSlots(&nslots, &bankmask_ignore);
 
 	/* we assume nslots isn't so large as to risk overflow */
 	sz = MAXALIGN(sizeof(SlruSharedData));
@@ -190,6 +224,9 @@
 {
 	SlruShared	shared;
 	bool		found;
+	int			bankmask;
+
+	SlruAdjustNSlots(&nslots, &bankmask);
 
 	shared = (SlruShared) ShmemInitStruct(name,
 										  SimpleLruShmemSize(nslots, nlsns),
@@ -257,7 +294,10 @@
 		Assert(ptr - (char *) shared <= SimpleLruShmemSize(nslots, nlsns));
 	}
 	else
+	{
 		Assert(found);
+		Assert(shared->num_slots == nslots);
+	}
 
 	/*
 	 * Initialize the unshared control struct, including directory path. We
@@ -265,6 +305,7 @@
 	 */
 	ctl->shared = shared;
 	ctl->sync_handler = sync_handler;
+	ctl->bank_mask = bankmask;
 	strlcpy(ctl->Dir, subdir, sizeof(ctl->Dir));
 }
 
@@ -496,12 +537,14 @@
 {
 	SlruShared	shared = ctl->shared;
 	int			slotno;
+	int			bankstart = (pageno & ctl->bank_mask) * SLRU_BANK_SIZE;
+	int			bankend = bankstart + SLRU_BANK_SIZE;

@@ -1030,7 +1073,10 @@
 
 		/* See if page already has a buffer assigned */
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		int			bankstart = (pageno & ctl->bank_mask) * SLRU_BANK_SIZE;
+		int			bankend = bankstart + SLRU_BANK_SIZE;
+
+		for (slotno = bankstart; slotno < bankend; slotno++)
 		{
 			if (shared->page_number[slotno] == pageno &&
 				shared->page_status[slotno] != SLRU_PAGE_EMPTY)
@@ -1065,7 +1111,7 @@
 		 * multiple pages with the same lru_count.
 		 */
 		cur_count = (shared->cur_lru_count)++;
-		for (slotno = 0; slotno < shared->num_slots; slotno++)
+		for (slotno = bankstart; slotno < bankend; slotno++)
 		{
 			int			this_delta;
 			int			this_page_number;
Index: src/backend/access/transam/clog.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
--- a/src/backend/access/transam/clog.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/access/transam/clog.c	(date 1660677867877)
@@ -74,6 +74,8 @@
 #define GetLSNIndex(slotno, xid)	((slotno) * CLOG_LSNS_PER_PAGE + \
 	((xid) % (TransactionId) CLOG_XACTS_PER_PAGE) / CLOG_XACTS_PER_LSN_GROUP)
 
+#define NUM_CLOG_BUFFERS 	(128 << slru_buffers_size_scale)
+
 /*
  * The number of subtransactions below which we consider to apply clog group
  * update optimization.  Testing reveals that the number higher than this can
@@ -661,42 +663,20 @@
 	return status;
 }
 
-/*
- * Number of shared CLOG buffers.
- *
- * On larger multi-processor systems, it is possible to have many CLOG page
- * requests in flight at one time which could lead to disk access for CLOG
- * page if the required page is not found in memory.  Testing revealed that we
- * can get the best performance by having 128 CLOG buffers, more than that it
- * doesn't improve performance.
- *
- * Unconditionally keeping the number of CLOG buffers to 128 did not seem like
- * a good idea, because it would increase the minimum amount of shared memory
- * required to start, which could be a problem for people running very small
- * configurations.  The following formula seems to represent a reasonable
- * compromise: people with very low values for shared_buffers will get fewer
- * CLOG buffers as well, and everyone else will get 128.
- */
-Size
-CLOGShmemBuffers(void)
-{
-	return Min(128, Max(4, NBuffers / 512));
-}
-
 /*
  * Initialization of shared memory for CLOG
  */
 Size
 CLOGShmemSize(void)
 {
-	return SimpleLruShmemSize(CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE);
+	return SimpleLruShmemSize(NUM_CLOG_BUFFERS, CLOG_LSNS_PER_PAGE);
 }
 
 void
 CLOGShmemInit(void)
 {
 	XactCtl->PagePrecedes = CLOGPagePrecedes;
-	SimpleLruInit(XactCtl, "Xact", CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE,
+	SimpleLruInit(XactCtl, "Xact", NUM_CLOG_BUFFERS, CLOG_LSNS_PER_PAGE,
 				  XactSLRULock, "pg_xact", LWTRANCHE_XACT_BUFFER,
 				  SYNC_HANDLER_CLOG);
 	SlruPagePrecedesUnitTests(XactCtl, CLOG_XACTS_PER_PAGE);
Index: src/include/storage/predicate.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/storage/predicate.h b/src/include/storage/predicate.h
--- a/src/include/storage/predicate.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/storage/predicate.h	(date 1660678108718)
@@ -28,7 +28,7 @@

 /* Number of SLRU buffers to use for Serial SLRU */
-#define NUM_SERIAL_BUFFERS		16
+#define NUM_SERIAL_BUFFERS	(16 << slru_buffers_size_scale)
 
 /*
  * A handle used for sharing SERIALIZABLEXACT objects between the participants
Index: src/backend/utils/misc/postgresql.conf.sample
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
--- a/src/backend/utils/misc/postgresql.conf.sample	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/utils/misc/postgresql.conf.sample	(date 1660678108650)
@@ -155,6 +155,8 @@
 					#   mmap
 					# (change requires restart)
 #min_dynamic_shared_memory = 0MB	# (change requires restart)
+#slru_buffers_size_scale = 2 # SLRU buffers size scale of power 2, range 0..7
+					# (change requires restart)
 
 # - Disk -
 
Index: src/include/access/multixact.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
--- a/src/include/access/multixact.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/access/multixact.h	(date 1660678108678)
@@ -30,8 +30,8 @@
 #define MaxMultiXactOffset	((MultiXactOffset) 0xFFFFFFFF)
 
 /* Number of SLRU buffers to use for multixact */
-#define NUM_MULTIXACTOFFSET_BUFFERS		8
-#define NUM_MULTIXACTMEMBER_BUFFERS		16
+#define NUM_MULTIXACTOFFSET_BUFFERS		(16 << slru_buffers_size_scale)
+#define NUM_MULTIXACTMEMBER_BUFFERS		(32 << slru_buffers_size_scale)
 
 /*
  * Possible multixact lock modes ("status").  The first four modes are for
Index: src/backend/utils/misc/guc.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
--- a/src/backend/utils/misc/guc.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/utils/misc/guc.c	(date 1660678064216)
@@ -32,9 +32,11 @@
 #endif
 #include <unistd.h>
 
+#include "access/clog.h"
 #include "access/commit_ts.h"
 #include "access/gin.h"
 #include "access/rmgr.h"
+#include "access/slru.h"
 #include "access/tableam.h"
 #include "access/toast_compression.h"
 #include "access/transam.h"
@@ -2375,6 +2377,16 @@
 		-1, -1, INT_MAX,
 		NULL, NULL, NULL
 	},
+
+	{
+		{"slru_buffers_size_scale", PGC_POSTMASTER, RESOURCES_MEM,
+			gettext_noop("SLRU buffers size scale of power 2"),
+			NULL
+		},
+		&slru_buffers_size_scale,
+		2, 0, 7,
+		NULL, NULL, NULL
+	},
 
 	{
 		{"temp_buffers", PGC_USERSET, RESOURCES_MEM,
Index: src/include/commands/async.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/commands/async.h b/src/include/commands/async.h
--- a/src/include/commands/async.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/commands/async.h	(date 1660678108702)
@@ -18,7 +18,7 @@
 /*
  * The number of SLRU page buffers we use for the notification queue.
  */
-#define NUM_NOTIFY_BUFFERS	8
+#define NUM_NOTIFY_BUFFERS	(16 << slru_buffers_size_scale)
 
 extern bool Trace_notify;
 extern volatile sig_atomic_t notifyInterruptPending;
Index: src/include/access/slru.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
--- a/src/include/access/slru.h	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/include/access/slru.h	(date 1660678108690)
@@ -134,6 +134,11 @@
 	 * it's always the same, it doesn't need to be in shared memory.
 	 */
 	char		Dir[64];
+
+	/*
+	 * mask for slotno hash bank
+	 */
+	Size		bank_mask;
 } SlruCtlData;
 
 typedef SlruCtlData *SlruCtl;
Index: src/backend/access/transam/subtrans.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/access/transam/subtrans.c b/src/backend/access/transam/subtrans.c
--- a/src/backend/access/transam/subtrans.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/access/transam/subtrans.c	(date 1660678064032)
@@ -31,6 +31,7 @@
 #include "access/slru.h"
 #include "access/subtrans.h"
 #include "access/transam.h"
+#include "miscadmin.h"
 #include "pg_trace.h"
 #include "utils/snapmgr.h"
 
Index: doc/src/sgml/config.sgml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
--- a/doc/src/sgml/config.sgml	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/doc/src/sgml/config.sgml	(date 1660677867861)
@@ -1953,6 +1953,37 @@
       </listitem>
      </varlistentry>
 
+    <varlistentry id="guc-slru-buffers-size-scale" xreflabel="slru_buffers_size_scale">
+     <term><varname>slru_buffers_size_scale</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>slru_buffers_size_scale</varname> configuration parameter</primary>
+     </indexterm>
+     </term>
+     <listitem>
+      <para>
+       Specifies power 2 scale for all SLRU shared memory buffers sizes. Buffers sizes depends on
+       both <literal>guc_slru_buffers_size_scale</literal> and <literal>shared_buffers</literal> params.
+      </para>
+      <para>
+       This affects on buffers in the list below (see also <xref linkend="pgdata-contents-table"/>):
+        <itemizedlist>
+         <listitem><para><literal>NUM_MULTIXACTOFFSET_BUFFERS = Min(32 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_MULTIXACTMEMBER_BUFFERS = Min(64 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_SUBTRANS_BUFFERS = Min(64 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_NOTIFY_BUFFERS = Min(32 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_SERIAL_BUFFERS = Min(32 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_CLOG_BUFFERS = Min(128 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+         <listitem><para><literal>NUM_COMMIT_TS_BUFFERS = Min(128 &lt;&lt; slru_buffers_size_scale, shared_buffers/256)</literal></para></listitem>
+        </itemizedlist>
+      </para>
+      <para>
+       Value is in <literal>0..7</literal> bounds.
+       The default value is <literal>2</literal>.
+       This parameter can only be set at server start.
+      </para>
+     </listitem>
+    </varlistentry>
+
      <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
       <term><varname>max_stack_depth</varname> (<type>integer</type>)
       <indexterm>
Index: src/backend/access/transam/commit_ts.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
--- a/src/backend/access/transam/commit_ts.c	(revision 020258fbd30d37ddd03d0ec68264d1544f8d2838)
+++ b/src/backend/access/transam/commit_ts.c	(date 1660677867889)
@@ -73,6 +73,8 @@
 #define TransactionIdToCTsEntry(xid)	\
 	((xid) % (TransactionId) COMMIT_TS_XACTS_PER_PAGE)
 
+#define NUM_COMMIT_TS_BUFFERS	(128 << slru_buffers_size_scale)
+
 /*
  * Link to shared-memory data structures for CommitTs control
  */
@@ -508,26 +510,13 @@
 	PG_RETURN_DATUM(HeapTupleGetDatum(htup));
 }
 
-/*
- * Number of shared CommitTS buffers.
- *
- * We use a very similar logic as for the number of CLOG buffers (except we
- * scale up twice as fast with shared buffers, and the maximum is twice as
- * high); see comments in CLOGShmemBuffers.
- */
-Size
-CommitTsShmemBuffers(void)
-{
-	return Min(256, Max(4, NBuffers / 256));
-}
-
 /*
  * Shared memory sizing for CommitTs
  */
 Size
 CommitTsShmemSize(void)
 {
-	return SimpleLruShmemSize(CommitTsShmemBuffers(), 0) +
+	return SimpleLruShmemSize(NUM_COMMIT_TS_BUFFERS, 0) +
 		sizeof(CommitTimestampShared);
 }
 
@@ -541,7 +530,7 @@
 	bool		found;
 
 	CommitTsCtl->PagePrecedes = CommitTsPagePrecedes;
-	SimpleLruInit(CommitTsCtl, "CommitTs", CommitTsShmemBuffers(), 0,
+	SimpleLruInit(CommitTsCtl, "CommitTs", NUM_COMMIT_TS_BUFFERS, 0,
 				  CommitTsSLRULock, "pg_commit_ts",
 				  LWTRANCHE_COMMITTS_BUFFER,
 				  SYNC_HANDLER_COMMIT_TS);

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 18 Aug 2022 08:35:15 +0500

> On 17 Aug 2022, at 00:36, [email-masked] wrote:
> 
> Hi @Andrey.Borodin, With some considerations and performance checks from @Yura.Sokolov we simplified your approach by the following:
> 
> 1. Preamble. We feel free to increase any SLRU's, since there's no performance degradation on large Buffers count using your SLRU buckets solution.
> 2. `slru_buffers_size_scale` is only one config param introduced for all SLRUs. It scales SLRUs upper cap by power 2.
> 3. All SLRU buffers count are capped by both `MBuffers (shared_buffers)` and `slru_buffers_size_scale`. see
> 4. Magic initial constants `NUM_*_BUFFERS << slru_buffers_size_scale` are applied for every SLRU.
> 5. All SLRU buffers are always sized as power of 2, their hash bucket size is always 8.
> 
> There's attached patch for your consideration. It does gather and simplify both `v21-0001-Make-all-SLRU-buffer-sizes-configurable.patch` and `v21-0002-Divide-SLRU-buffers-into-8-associative-banks.patch` to much simpler approach.

I like the idea of one knob instead of one per each SLRU. Maybe we even could deduce sane value from NBuffers? That would effectively lead to 0 knobs :)

Your patch have a prefix "v22-0006", does it mean there are 5 previous steps of the patchset?

Thank you!

---

From: [email-masked]
Date: Fri, 19 Aug 2022 18:48:41 +0300

Andrey Borodin wrote 2022-08-18 06:35:
> 
> I like the idea of one knob instead of one per each SLRU. Maybe we
> even could deduce sane value from NBuffers? That would effectively
> lead to 0 knobs :)
> 

Not sure it's possible to deduce from NBuffers only. 
slru_buffers_scale_shift looks like relief valve for systems with ultra 
scaled NBuffers.

Regarding v22-0006 I just tried to choose index unique for this thread 
so now it's fixed to 0001 indexing.

 

 
 
+/*
 
-
 
+/*

 
 	/*
 
@@ -496,12 +537,14 @@
 {

 

-/*
 
 void
 CLOGShmemInit(void)
 {

 /*
 
 # - Disk -

 
 /*

 	{

 

 
-/*
 
@@ -541,7 +530,7 @@
 	bool		found;

---

From: Andrey Borodin <[email-masked]>
Date: Tue, 20 Dec 2022 10:39:29 -0800

> thinking and experimenting.  More soon.

Hi Thomas,

PostgreSQL 16 feature freeze is approaching again. Let's choose
something from possible solutions, even if the chosen one is
temporary.
Thank you!

---

From: vignesh C <[email-masked]>
Date: Sat, 20 Jan 2024 09:01:20 +0530

On Mon, 9 Jan 2023 at 09:49, Andrey Borodin <[email-masked]> wrote:
>
> On Tue, Jan 3, 2023 at 5:02 AM vignesh C <[email-masked]> wrote:
> > does not apply on top of HEAD as in [1], please post a rebased patch:
> >
> Thanks! Here's the rebase.

I'm seeing that there has been no activity in this thread for more
than 1 year now, I'm planning to close this in the current commitfest
unless someone is planning to take it forward.

Regards,
Vignesh

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 27 Jan 2024 09:58:18 +0500

> On 20 Jan 2024, at 08:31, vignesh C <[email-masked]> wrote:
> 

Hi Vignesh,

thanks for the ping! Most important parts of this patch set are discussed in [0]. If that patchset will be committed, I'll withdraw entry for this thread from commitfest.
There's a version of Multixact-specific optimizations [1], but I hope they will not be necessary with effective caches developed in [0]. It seems to me that most important part of those optimization is removing sleeps under SLRU lock on standby [2] by Kyotaro Horiguchi. But given that cache optimizations took 4 years to get closer to commit, I'm not sure we will get this optimization any time soon...

Thanks!

[0] https://www.postgresql.org/message-id/flat/CAFiTN-vzDvNz%3DExGXz6gdyjtzGixKSqs0mKHMmaQ8sOSEFZ33A%40mail.gmail.com
[1] https://www.postgresql.org/message-id/flat/2ECE132B-C042-4489-930E-DBC5D0DAB84A%40yandex-team.ru#5f7e7022647be9eeecfc2ae75d765500
[2] https://www.postgresql.org/message-id/flat/20200515.090333.24867479329066911.horikyota.ntt%40gmail.com#855f8bb7205890579a363d2344b4484d

---

From: Alvaro Herrera <[email-masked]>
Date: Sun, 28 Jan 2024 13:49:31 +0100

> There's a version of Multixact-specific optimizations [1], but I hope they will not be necessary with effective caches developed in [0]. It seems to me that most important part of those optimizatio…

I'd appreciate it if you or Horiguchi-san can update his patch to remove
use of usleep in favor of a CV in multixact, and keep this CF entry to
cover it.

Perhaps a test to make the code reach the usleep(1000) can be written
using injection points (49cd2b93d7db)?

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Sun, 28 Jan 2024 23:17:16 +0500

> On 28 Jan 2024, at 17:49, Alvaro Herrera <[email-masked]> wrote:
> 

Sure! Sounds great!

> using injection points (49cd2b93d7db)?

I've tried to prototype something like that. But interesting point between GetNewMultiXactId() and RecordNewMultiXact() is a critical section, and we cannot have injection points in critical sections...
Also, to implement such a test we need "wait" type of injection points, see step 2 in attachment. With this type of injection points I can stop a backend amidst entering information about new MultiXact.