Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode - Mailing list pgsql-hackers

Thanks for your interest in this patch!

On Mon, Mar 13, 2023 at 8:38 AM Ants Aasma <ants@cybertec.at> wrote:
>
> On Sat, 11 Mar 2023 at 16:55, Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> >
> > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy
> > > <bharath.rupireddyforpostgres@gmail.com> wrote:
> > >
> > > > 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.
> >
> > In attached v3, I've changed the name of the guc from buffer_usage_limit
> > to vacuum_buffer_usage_limit, since it is only used for vacuum and
> > autovacuum.
>
> Sorry for arriving late to this thread, but what about sizing the ring
> dynamically? From what I gather the primary motivation for larger ring
> size is avoiding WAL flushes due to dirty buffer writes. We already
> catch that event with StrategyRejectBuffer(). So maybe a dynamic
> sizing algorithm could be applied to the ringbuffer. Make the buffers
> array in strategy capable of holding up to the limit of buffers, but
> set ring size conservatively. If we have to flush WAL, double the ring
> size (up to the limit). If we loop around the ring without flushing,
> decrease the ring size by a small amount to let clock sweep reclaim
> them for use by other backends.

So, the original motivation of this patch was to allow autovacuum in
failsafe mode to abandon use of a buffer access strategy, since, at that
point, there is no reason to hold back. The idea was expanded to be an
option to explicit vacuum, since users often must initiate an explicit
vacuum after a forced shutdown due to transaction ID wraparound.

As for routine vacuuming and the other buffer access strategies, I think
there is an argument for configurability based on operator knowledge --
perhaps your workload will use the data you are COPYing as soon as the
COPY finishes, so you might as well disable a buffer access strategy or
use a larger fraction of shared buffers. Also, the ring sizes were
selected sixteen years ago and average server memory and data set sizes
have changed.

StrategyRejectBuffer() will allow bulkreads to, as you say, use more
buffers than the original ring size, since it allows them to kick
dirty buffers out of the ring and claim new shared buffers.

Bulkwrites and vacuums, however, will inevitably dirty buffers and
require flushing the buffer (and thus flushing the associated WAL) when
reusing them. Bulkwrites and vacuum do not kick dirtied buffers out of
the ring, since dirtying buffers is their common case. A dynamic
resizing like the one you suggest would likely devolve to vacuum and
bulkwrite strategies always using the max size.

As for decreasing the ring size, buffers are only "added" to the ring
lazily and, technically, as it is now, buffers which have been added
added to the ring can always be reclaimed by the clocksweep (as long as
they are not pinned). The buffer access strategy is more of a
self-imposed restriction than it is a reservation. Since the ring is
small and the buffers are being frequently reused, odds are the usage
count will be 1 and we will be the one who set it to 1, but there is no
guarantee. If, when attempting to reuse the buffer, its usage count is
> 1 (or it is pinned), we also will kick it out of the ring and go look
for a replacement buffer.

I do think that it is a bit unreasonable to expect users to know how
large they would like to make their buffer access strategy ring. What we
want is some way of balancing different kinds of workloads and
maintenance tasks reasonably. If your database has no activity because
it is the middle of the night or it was shutdown because of transaction
id wraparound, there is no reason why vacuum should limit the number of
buffers it uses. I'm sure there are many other such examples.

- Melanie