Horton

Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Geoghegan <pg@bowt.ie>, pgsql-hackers@postgresql.org, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Date: 2023-01-11T21:39:05Z
Lists: pgsql-hackers 
Hi,

On 2023-01-11 16:18:34 -0500, Tom Lane wrote:
> Peter Geoghegan <pg@bowt.ie> writes:
> > On Wed, Jan 11, 2023 at 11:18 AM Andres Freund <andres@anarazel.de> wrote:
> >> I don't like that - it's also quite useful to disable use of ringbuffers when
> >> you actually need to clean up indexes. Especially when we have a lot of dead
> >> tuples we'll rescan indexes over and over...
> 
> > That's a fair point.
> 
> > My vote goes to "REUSE_BUFFERS", then.
> 
> I wonder whether it could make sense to allow a larger ringbuffer size,
> rather than just the limit cases of "on" and "off".

I can see that making sense, particularly if we were to later extend this to
other users of ringbuffers. E.g. COPYs us of the ringbuffer makes loading of
data > 16MB but also << s_b vastly slower, but it can still be very important
to use if there's lots of parallel processes loading data.

Maybe BUFFER_USAGE_LIMIT, with a value from -1 to N, with -1 indicating the
default value, 0 preventing use of a buffer access strategy, and 1..N
indicating the size in blocks?

Would we want to set an upper limit lower than implied by the memory limit for
the BufferAccessStrategy allocation?

Greetings,

Andres Freund

Commits

  1. Add unit to vacuum_buffer_usage_limit value in postgresql.conf.sample.

  2. Improve VACUUM/ANALYZE BUFFER_USAGE_LIMIT docs

  3. Add --buffer-usage-limit option to vacuumdb

  4. Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option

  5. Move various prechecks from vacuum() into ExecVacuum()

  6. Always make a BufferAccessStrategy for ANALYZE

  7. Rename BufferAccessStrategyData.ring_size to nbuffers

  8. Disable vacuum's use of a buffer access strategy during failsafe

  9. Only make buffer strategy for vacuum when it's likely needed

  10. Remove some global variables from vacuum.c

  11. Doc: add Buffer Access Strategy to the glossary