Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Cc: tender wang <tndrwang(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock
Date: 2024-01-25 16:22:07
Message-ID: 202401251622.prhfglyhyy72@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Here's a touched-up version of this patch.

First, PROC_GLOBAL->clogGroupFirst and SlruCtl->latest_page_number
change from being protected by locks to being atomics, but there's no
mention of considering memory barriers that they should have. Looking
at the code, I think the former doesn't need any additional barriers,
but latest_page_number is missing some, which I have added. This
deserves another careful look.

Second and most user visibly, the GUC names seem to have been chosen
based on the source-code variables, which have never meant to be user-
visible. So I renamed a few:

xact_buffers -> transaction_buffers
subtrans_buffers -> subtransaction_buffers
serial_buffers -> serializable_buffers
commit_ts_buffers -> commit_timestamp_buffers

(unchanged: multixact_offsets_buffers, multixact_members_buffers,

I did this explicitly trying to avoid using the term SLRU in a
user-visible manner, because what do users care? But immediately after
doing this I realized that we already have pg_stat_slru, so maybe the
cat is already out of the bag, and so perhaps we should name these GUCS
as, say slru_transaction_buffers? That may make the connection between
these things a little more explicit. (I do think we need to add
cross-links in the documentation of those GUCs to the pg_stat_slru
docs and vice-versa.)

Another thing that bothered me a bit is that we have auto-tuning for
transaction_buffers and commit_timestamp_buffers, but not for
subtransaction_buffers. (Autotuning means you set the GUC to 0 and it
scales with shared_buffers). I don't quite understand what's the reason
for the ommision, so I added it for subtrans too. I think it may make
sense to do likewise for the multixact ones too, not sure. It doesn't
seem worth having that for pg_serial and notify.

While messing about with these GUCs I realized that the usage of the
show_hook to print what the current number is, when autoturning is used,
was bogus: SHOW would print the number of blocks for (say)
transaction_buffers, but if you asked it to print (say)
multixact_offsets_buffers, it would give a size in MB or kB. I'm sure
such an inconsistency would bite us. So, digging around I found that a
good way to handle this is to remove the show_hook, and instead call
SetConfigOption() at the time when the ShmemInit function is called,
with the correct number of buffers determined. This is pretty much what
is used for XLOGbuffers, and it works correctly as far as my testing

Still with these auto-tuning GUCs, I noticed that the auto-tuning code
would continue to grow the buffer sizes with shared_buffers to
arbitrarily large values. I added an arbitrary maximum of 1024 (8 MB),
which is much higher than the current value of 128; but if you have
(say) 30 GB of shared_buffers (not uncommon these days), do you really
need 30MB of pg_clog cache? It seems mostly unnecessary ... and you can
still set it manually that way if you need it. So, largely I just
rewrote those small functions completely.

I also made the SGML documentation and postgresql.sample.conf all match
what the code actually docs. The whole thing wasn't particularly

I rewrote a bunch of code comments and moved stuff around to appear in
alphabetical order, etc.

More comment rewriting still pending.

Álvaro Herrera Breisgau, Deutschland —

Attachment Content-Type Size
v15-slru-optimization.patch text/x-diff 111.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tristan Partin 2024-01-25 16:25:19 Re: make dist using git archive
Previous Message Melanie Plageman 2024-01-25 16:18:55 Re: Emit fewer vacuum records by reaping removable tuples during pruning