Re: Changing shared_buffers without restart

On 25/02/2025 11:52 am, Dmitry Dolgov wrote:
>> On Fri, Oct 18, 2024 at 09:21:19PM GMT, Dmitry Dolgov wrote:
>> TL;DR A PoC for changing shared_buffers without PostgreSQL restart, via
>> changing shared memory mapping layout. Any feedback is appreciated.

Hi Dmitry,

I am sorry that I have not participated in the discussion in this thread 
from the very beginning, although I am also very interested in dynamic 
shared buffer resizing and evn proposed my own implementation of it: 
https://github.com/knizhnik/postgres/pull/2 based on memory ballooning 
and using `madvise`. And it really works (returns unused memory to the 
system).
This PoC allows me to understand the main drawbacks of this approach:

1. Performance of Postgres CLOCK page eviction algorithm depends on 
number of shared buffers. My first native attempt just to mark unused 
buffers as invalid cause significant degrade of performance

pgbench -c 32 -j 4 -T 100 -P1 -M prepared -S

(here shared_buffers - is maximal shared buffers size and 
`available_buffers` - is used part:

| shared_buffers | available_buffers | TPS | | ------------------| 
---------------------------- | ---- | | 128MB | -1 | 280k | | 1GB | -1 | 
324k | | 2GB | -1 | 358k | | 32GB | -1 | 350k | | 2GB | 128Mb | 130k | | 
2GB | 1Gb | 311k | | 32GB | 128Mb | 13k | | 32GB | 1Gb | 140k | | 32GB | 
2Gb | 348k |

My first thought is to replace clock with LRU based in double-linked 
list. As far as there is no lockless double-list implementation,
it need some global lock. This lock can become bottleneck. The standard 
solution is partitioning: use N  LRU lists instead of 1.
Just as partitioned has table used by buffer manager to lockup buffers. 
Actually we can use the same partitions locks to protect LRU list.
But it not clear what to do with ring buffers (strategies).So I decided 
not to perform such revolution in bufmgr, but optimize clock to more 
efficiently split reserved buffers.
Just add|skip_count|field to buffer descriptor. And it helps! Now the 
worst case shared_buffer/available_buffers = 32Gb/128Mb
shows the same performance 280k as  shared_buffers=128Mb without ballooning.

2. There are several data structures i Postgres which size depends on 
number of buffers.
In my patch I used in some cases dynamic shared buffer size, but if this 
structure has to be allocated in shared memory then still maximal size 
has to be used. We have the buffers themselves (8 kB per buffer), then 
the main BufferDescriptors array (64 B), the BufferIOCVArray (16 B), 
checkpoint's CkptBufferIds (20 B), and the hashmap on the buffer cache 
(24B+8B/entry).
128 bytes per 8kb bytes seems to  large overhead (~1%) but but it may be 
quote noticeable with size differences larger than 2 orders of magnitude:
E.g. to support scaling to from 0.5Gb to 128GB , with 128 bytes/buffer 
we'd have ~2GiB of static overhead on only 0.5GiB of actual buffers.

3. `madvise` is not portable.

Certainly you have moved much further in your proposal comparing with my 
PoC (including huge pages support).
But it is still not quite clear to me how you are going to solve the 
problems with large memory overhead in case of ~100x times variation of 
shared buffers size.




I