[PATCH] Clamp auto-tuned transaction_buffers to at least 8 SLRU banks

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

Browse pgsql-hackers by date

  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