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

On Wed, Mar 15, 2023 at 6:46 AM Ants Aasma <ants@cybertec.at> wrote:
>
> On Wed, 15 Mar 2023 at 02:29, Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> > 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.
>
> To be clear I'm not at all arguing against configurability. I was
> thinking that dynamic use could make the configuration simpler by self
> tuning to use no more buffers than is useful.

Yes, but I am struggling with how we would define "useful".

> > 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.
>
> I think it should self stabilize around the point where the WAL is
> either flushed by other commit activity, WAL writer or WAL buffers
> filling up. Writing out their own dirtied buffers will still happen,
> just the associated WAL flushes will be in larger chunks and possibly
> done by other processes.

They will have to write out any WAL associated with modifications to the
dirty buffer before flushing it, so I'm not sure I understand how this
would work.

> > 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.
>
> Right, but while the buffer is actively used by the ring it is
> unlikely that clocksweep will find it at usage 0 as the ring buffer
> should cycle more often than the clocksweep. Whereas if the ring stops
> using a buffer, clocksweep will eventually come and reclaim it. And if
> the ring shrinking decision turns out to be wrong before the
> clocksweep gets around to reusing it, we can bring the same buffer
> back into the ring.

I can see what you mean about excluding a buffer from the ring being a
more effective way of allowing it to be reclaimed. However, I'm not sure
I understand the use case. If the operation, say vacuum, is actively
using the buffer and keeping its usage count at one, then what would be
the criteria for it to decide to stop using it?

Also, if vacuum used the buffer once and then didn't reuse it but, for
some reason, the vacuum isn't over, it isn't any different at that point
than some other buffer with a usage count of one. It isn't any harder
for it to be reclaimed by the clocksweep.

The argument I could see for decreasing the size even when the buffers
are being used by the operation employing the strategy is if there is
pressure from other workloads to use those buffers. But, designing a
system that would reclaim buffers when needed by other workloads is more
complicated than what is being proposed here.

> > 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.
>
> Ideally yes, though I am not hopeful of finding a solution that does
> this any time soon. Just to take your example, if a nightly
> maintenance job wipes out the shared buffer contents slightly
> optimizing its non time-critical work and then causes morning user
> visible load to have big latency spikes due to cache misses, that's
> not a good tradeoff either.

Yes, that is a valid concern.

- Melanie