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);