| From: | Sergey Tatarintsev <s(dot)tatarintsev(at)postgrespro(dot)ru> |
|---|---|
| To: | Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | [PATCH] Clamp auto-tuned transaction_buffers to at least 8 SLRU banks |
| Date: | 2026-11-20 06:08:41 |
| Message-ID: | dff1f13f-0c69-4adb-8b9b-3f3363f2b0d6@postgrespro.ru |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| transaction_buffers_autotune.patch | text/x-patch | 2.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Neil Chen | 2026-11-20 06:09:33 | Re: [PATCH] Handle SQL NULL results from casts in JSON generation |
| Previous Message | Dewei Dai | 2026-11-20 05:59:43 | Re: [PATCH v2] Reject duplicate SQL/JSON PASSING argument names |