Hi hackers!

When transaction_buffers is set to 0 (auto), CLOGShmemBuffers() calls
SimpleLruAutotuneBuffers(512, 1024), which divides shared_buffers by 512
and clamps to at most 1024.  For small shared_buffers this produces a very
small CLOG buffer pool: 128MB of shared_buffers yields only 32 slots
(256kB), i.e. just two SLRU banks.

A pool that small cannot hold the hot window of CLOG pages plus the pages
allocated by new transactions, so the same pages are evicted and
re-read constantly.  Every re-read has to acquire the LWLock that
protects that bank of slots, and under high concurrency this becomes
a severe contention point (waiting processes pile up on
LWLock:XactSLRU).

Measured on a 128-core machine (128MB shared_buffers, pgbench TPC-B
scale=300, 90s runs, --no-vacuum -M prepared).  blks_read is from
pg_stat_slru (name = 'transaction').

  clients   auto tps   fixed tps   delta   auto blks_read   fixed blks_read
    128      111.3k      110.4k      ~0%        1,383,386      172
    256      106.0k      109.5k     +3.3%        1,680,213          0
    512       93.2k      100.8k     +8.1%        2,040,246      1,139
   1024       79.2k       83.8k     +5.6%        1,744,888        991

(auto: transaction_buffers not set (or 0); fixed: transaction_buffers = 2048)

The auto setting produces ~1.4-2.0M CLOG page re-reads per 90s run,
which translate to that many bank-LWLock acquisitions on top of the
normal traffic.  Raising transaction_buffers to 2048 removes essentially
all re-reads.

At c=128 the tps difference is within noise, but from c=256 upward the
gap becomes clear, peaking at +8.1% for c=512.

Fix: clamp the auto-tuned value to at least 8 banks (128 slots, 1MB).
This is cheap (1MB of shared memory) and matches the smallest pool
size that stops the pathological re-read behaviour.  8 banks also
leaves room for the LRU victim search to have something to work with;
a 2-bank pool has essentially no eviction headroom.

Why 8 banks (128 slots)?  Measured at c=512:

  slots   banks   tps      blks_read
    16      1      9.9k        492k   (single-bank serialization)
    32      2     93.2k      2040k   (re-read storm)
    64      4    101.2k       406k
   128      8    101.3k        54k   (full tps, 1MB)
  2048    128    100.8k         1k

tps reaches its plateau at 64 slots (4 banks); 128 slots (8 banks)
provides headroom and cuts blks_read by another ~7x.  Larger values
give no tps benefit at 16x the memory cost.


Patch follows.

--
With best regards,
Sergey Tatarintsev,
PostgresPro
diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
index 6f7f6b86eb6..8796477105f 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -777,7 +777,17 @@ CLOGShmemBuffers(void)
 {
 	/* auto-tune based on shared buffers */
 	if (transaction_buffers == 0)
-		return SimpleLruAutotuneBuffers(512, 1024);
+	{
+		int			autotune = SimpleLruAutotuneBuffers(512, 1024);
+
+		/*
+		 * Clamp to at least 8 banks (128 slots, 1MB).  A smaller pool
+		 * causes CLOG page re-reads under concurrency.  8 is chosen
+		 * empirically: 4 banks (64 slots) reaches peak throughput, 8
+		 * provides headroom and reduces re-reads ~7x vs 4.
+		 */
+		return Max(autotune, 8 * SLRU_BANK_SIZE);
+	}
 
 	return Min(Max(16, transaction_buffers), CLOG_MAX_ALLOWED_BUFFERS);
 }
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 885fd068535..dd63e5e6380 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -135,16 +135,6 @@ typedef struct SlruWriteAllData
 typedef struct SlruWriteAllData *SlruWriteAll;
 
 
-/*
- * Bank size for the slot array.  Pages are assigned a bank according to their
- * page number, with each bank being this size.  We want a power of 2 so that
- * we can determine the bank number for a page with just bit shifting; we also
- * want to keep the bank size small so that LRU victim search is fast.  16
- * buffers per bank seems a good number.
- */
-#define SLRU_BANK_BITSHIFT		4
-#define SLRU_BANK_SIZE			(1 << SLRU_BANK_BITSHIFT)
-
 /*
  * Macro to get the bank number to which the slot belongs.
  */
diff --git a/src/include/access/slru.h b/src/include/access/slru.h
index b4adb1789c7..0d625369956 100644
--- a/src/include/access/slru.h
+++ b/src/include/access/slru.h
@@ -218,6 +218,16 @@ extern void SimpleLruRequestWithOpts(const SlruOpts *options);
 #define SimpleLruRequest(...)  \
 	SimpleLruRequestWithOpts(&(SlruOpts){__VA_ARGS__})
 
+/*
+ * Bank size for the slot array.  Pages are assigned a bank according to their
+ * page number, with each bank being this size.  We want a power of 2 so that
+ * we can determine the bank number for a page with just bit shifting; we also
+ * want to keep the bank size small so that LRU victim search is fast.  16
+ * buffers per bank seems a good number.
+ */
+#define SLRU_BANK_BITSHIFT		4
+#define SLRU_BANK_SIZE			(1 << SLRU_BANK_BITSHIFT)
+
 extern int	SimpleLruAutotuneBuffers(int divisor, int max);
 extern int	SimpleLruZeroPage(SlruDesc *ctl, int64 pageno);
 extern void SimpleLruZeroAndWritePage(SlruDesc *ctl, int64 pageno);

Reply via email to