Horton

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

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>

View thread View on postgr.es
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 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-02-28T08:16:32Z
Lists: pgsql-hackers 
On Thu, Jan 12, 2023 at 6:06 AM Andres Freund <andres@anarazel.de> wrote:
>
> On 2023-01-11 17:26:19 -0700, David G. Johnston wrote:
> > Should we just add "ring_buffers" to the existing "shared_buffers" and
> > "temp_buffers" settings?
>
> The different types of ring buffers have different sizes, for good reasons. So
> I don't see that working well. I also think it'd be more often useful to
> control this on a statement basis - if you have a parallel import tool that
> starts NCPU COPYs you'd want a smaller buffer than a single threaded COPY. Of
> course each session can change the ring buffer settings, but still.

How about having GUCs for each ring buffer (bulk_read_ring_buffers,
bulk_write_ring_buffers, vacuum_ring_buffers - ah, 3 more new GUCs)?
These options can help especially when statement level controls aren't
easy to add (COPY, CREATE TABLE AS/CTAS, REFRESH MAT VIEW/RMV)? If
needed users can also set them at the system level. For instance, one
can set bulk_write_ring_buffers to other than 16MB or -1 to disable
the ring buffer to use shared_buffers and run a bunch of bulk write
queries.

Although I'm not quite opposing the idea of statement level controls
(like the VACUUM one proposed here), it is better to make these ring
buffer sizes configurable across the system to help with the other
similar cases e.g., a CTAS or RMV can help subsequent reads from
shared buffers if ring buffer is skipped.

Thoughts?

-- 
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

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