Thanks for your interest in this patch! On Mon, Mar 13, 2023 at 8:38 AM Ants Aasma <a...@cybertec.at> wrote: > > On Sat, 11 Mar 2023 at 16:55, Melanie Plageman > <melanieplage...@gmail.com> wrote: > > > > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > > > <bharath.rupireddyforpostg...@gmail.com> wrote: > > > > > > > On Thu, Jan 12, 2023 at 6:06 AM Andres Freund <and...@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
