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

On Wed, Apr 26, 2023 at 9:59 PM Melanie Plageman wrote: > > > On Wed, Apr 26, 2023 at 8:31 AM Daniel Gustafsson wrote: >> >> > On 26 Apr 2023, at 13:26, David Rowley wrote: >> > On Wed, 26 Apr 2023, 8:48 pm Masahiko Sawada, > > > wrote: >> >> > It works but I think

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

On Wed, Apr 26, 2023 at 8:31 AM Daniel Gustafsson wrote: > > On 26 Apr 2023, at 13:26, David Rowley wrote: > > On Wed, 26 Apr 2023, 8:48 pm Masahiko Sawada, > wrote: > > > It works but I think we might want to add the unit kB for > > understandability and

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

> On 26 Apr 2023, at 13:26, David Rowley wrote: > On Wed, 26 Apr 2023, 8:48 pm Masahiko Sawada, > wrote: > It works but I think we might want to add the unit kB for > understandability and consistency with other GUC_UNIT_KB parameters. > I've attached a small patch

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

On Wed, 26 Apr 2023, 8:48 pm Masahiko Sawada, wrote: > I realized that the value of vacuum_buffer_usage_limit parameter in > postgresql.conf.sample doesn't have the unit: > > #vacuum_buffer_usage_limit = 256 # size of vacuum and analyze buffer > access strategy ring. >

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

Hi, On Sun, Apr 16, 2023 at 9:09 AM David Rowley wrote: > > On Sat, 15 Apr 2023 at 12:59, David Rowley wrote: > > These are all valid points. I've attached a patch aiming to address > > each of them. > > I tweaked this a little further and pushed it. > I realized that the value of

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

On Tue, 18 Apr 2023 at 09:21, Melanie Plageman wrote: > Are we still thinking that reordering the VACUUM (and ANALYZE) options > makes sense. And, if so, should it be alphabetical within parameter > category? That is, all actual parameters (e.g. FULL and FREEZE) are > alphabetically organized

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

On Sat, Apr 15, 2023 at 12:59:52PM +1200, David Rowley wrote: > On Fri, 14 Apr 2023 at 19:20, Peter Eisentraut > wrote: > > > > I came across these new options and had a little bit of trouble figuring > > them out from the documentation. Maybe this could be polished a bit. > > > > vacuumdb

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

On Sat, 15 Apr 2023 at 12:59, David Rowley wrote: > These are all valid points. I've attached a patch aiming to address > each of them. I tweaked this a little further and pushed it. David

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

On Fri, 14 Apr 2023 at 19:20, Peter Eisentraut wrote: > > I came across these new options and had a little bit of trouble figuring > them out from the documentation. Maybe this could be polished a bit. > > vacuumdb --help says > > --buffer-usage-limit=BUFSIZE > > I can guess what a "SIZE"

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

On 07.04.23 02:52, David Rowley wrote: On Fri, 7 Apr 2023 at 09:44, Melanie Plageman wrote: Otherwise, LGTM. Thanks for looking. I've also taken Justin's comments about the README into account and fixed that part. I've pushed the patch after a little more adjusting. I added some text to

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

On Fri, 7 Apr 2023 at 09:44, Melanie Plageman wrote: > Otherwise, LGTM. Thanks for looking. I've also taken Justin's comments about the README into account and fixed that part. I've pushed the patch after a little more adjusting. I added some text to the docs that mention larger

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

On Fri, Apr 07, 2023 at 09:12:32AM +1200, David Rowley wrote: > On Fri, 7 Apr 2023 at 05:20, Melanie Plageman > wrote: > > GUC name mentioned in comment is inconsistent with current GUC name. > > > > > +/* > > > + * Upper and lower hard limits for the buffer access strategy ring size > > > + *

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

+VACUUM uses a ring like sequential scans, however, the size this ring +controlled by the vacuum_buffer_usage_limit GUC. Dirty pages are not removed should say: ".. the size OF this ring IS .." ?

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

On Fri, 7 Apr 2023 at 05:20, Melanie Plageman wrote: > > diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml > This still says that the default value is -1. Oops, I had this staged but didn't commit and formed the patch with "git diff master.." instead of "git diff master", so

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

On Thu, Apr 06, 2023 at 11:34:44PM +1200, David Rowley wrote: > second On Thu, 6 Apr 2023 at 14:14, Melanie Plageman > wrote: > > > > Attached is v14 which adds back in tests for the BUFFER_USAGE_LIMIT > > option. > > I've spent quite a bit of time looking at this since you sent it. I've > also

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

second On Thu, 6 Apr 2023 at 14:14, Melanie Plageman wrote: > > Attached is v14 which adds back in tests for the BUFFER_USAGE_LIMIT > option. I've spent quite a bit of time looking at this since you sent it. I've also made quite a few changes, mostly cosmetic ones, but there are a few things

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

Attached is v14 which adds back in tests for the BUFFER_USAGE_LIMIT option. I haven't included a test for VACUUM (BUFFER_USAGE_LIMIT x, PARALLEL x) for the reason I mentioned upthread -- even if we force it to actually do the parallel vacuuming, we are adding exercising the code where parallel

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

On Thu, 6 Apr 2023 at 13:14, David Rowley wrote: > Is it intended that VACUUM t1,t2; now share the same strategy? > Currently, in master, we'll allocate a new strategy for t2 after > vacuuming t1. Does this not mean we'll now leave fewer t1 pages in > shared_buffers because the reuse of the

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

On Wed, Apr 5, 2023 at 9:15 PM David Rowley wrote: > > On Thu, 6 Apr 2023 at 12:42, Melanie Plageman > wrote: > > Attached is a v12 of the whole vacuum_buffer_usage_limit patch set which > > includes a commit to fix the bug in master and a commit to move relevant > > code from vacuum() up into

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

On Thu, 6 Apr 2023 at 12:42, Melanie Plageman wrote: > Attached is a v12 of the whole vacuum_buffer_usage_limit patch set which > includes a commit to fix the bug in master and a commit to move relevant > code from vacuum() up into ExecVacuum(). I'm still playing catch up to the moving of the

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

On Wed, Apr 5, 2023 at 6:55 PM Melanie Plageman wrote: > > On Wed, Apr 5, 2023 at 5:15 PM Andres Freund wrote: > > > > Hi, > > > > On 2023-04-05 16:17:20 -0400, Melanie Plageman wrote: > > > On Wed, Apr 5, 2023 at 1:05 PM Andres Freund wrote: > > > > Perhaps the best solution for autovac vs

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

On Wed, Apr 5, 2023 at 5:15 PM Andres Freund wrote: > > Hi, > > On 2023-04-05 16:17:20 -0400, Melanie Plageman wrote: > > On Wed, Apr 5, 2023 at 1:05 PM Andres Freund wrote: > > > Perhaps the best solution for autovac vs interactive vacuum issue would > > > be to > > > move the allocation of

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

On Wed, Apr 5, 2023 at 2:33 PM David Rowley wrote: > I read this twice yesterday and again this morning. It looks like > you're taking an opportunity to complain/vent about > vacuum_freeze_table_age and didn't really answer my query about why > all the vacuum GUCs aren't defined in the one file.

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

On Wed, 5 Apr 2023 at 16:37, Peter Geoghegan wrote: > > On Tue, Apr 4, 2023 at 8:14 PM David Rowley wrote: > > 14. Not related to this patch, but why do we have half the vacuum > > related GUCs in vacuum.c and the other half in globals.c? I see > > vacuum_freeze_table_age is defined in vacuum.c

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

Hi, On 2023-04-05 16:17:20 -0400, Melanie Plageman wrote: > On Wed, Apr 5, 2023 at 1:05 PM Andres Freund wrote: > > Perhaps the best solution for autovac vs interactive vacuum issue would be > > to > > move the allocation of the bstrategy to ExecVacuum()? > > So, I started looking into

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

On Wed, Apr 5, 2023 at 1:05 PM Andres Freund wrote: > > Hi, > > On 2023-04-04 13:53:15 -0400, Melanie Plageman wrote: > > > 8. I don't quite follow this comment: > > > > > > /* > > > * TODO: should this be 0 so that we are sure that vacuum() never > > > * allocates a new bstrategy for us, even if

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

v11 attached with updates detailed below. On Tue, Apr 4, 2023 at 11:14 PM David Rowley wrote: > > On Wed, 5 Apr 2023 at 05:53, Melanie Plageman > wrote: > > Attached v10 addresses the review feedback below. > > Thanks. Here's another round on v10-0001: > > 1. The following documentation

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

Hi, On 2023-04-04 13:53:15 -0400, Melanie Plageman wrote: > > 8. I don't quite follow this comment: > > > > /* > > * TODO: should this be 0 so that we are sure that vacuum() never > > * allocates a new bstrategy for us, even if we pass in NULL for that > > * parameter? maybe could change how

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

On Tue, Apr 4, 2023 at 8:14 PM David Rowley wrote: > 14. Not related to this patch, but why do we have half the vacuum > related GUCs in vacuum.c and the other half in globals.c? I see > vacuum_freeze_table_age is defined in vacuum.c but is also needed in > autovacuum.c, so that rules out the

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

On Wed, 5 Apr 2023 at 05:53, Melanie Plageman wrote: > Attached v10 addresses the review feedback below. Thanks. Here's another round on v10-0001: 1. The following documentation fragment does not seem to be aligned with the code: 16 GB. The minimum size is the lesser of 1/8

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

On Mon, Apr 3, 2023 at 8:37 PM David Rowley wrote: > > On Tue, 4 Apr 2023 at 02:49, Melanie Plageman > wrote: > > v9 attached. > > I've made a pass on the v9-0001 patch only. Here's what I noted down: Thanks for the review! Attached v10 addresses the review feedback below. Remaining TODOs:

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

On Tue, 4 Apr 2023 at 02:49, Melanie Plageman wrote: > v9 attached. I've made a pass on the v9-0001 patch only. Here's what I noted down: v9-0001: 1. In the documentation and comments, generally we always double-space after a period. I see quite often you're not following this. 2. Doc: We

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

On Mon, Apr 3, 2023 at 1:09 AM David Rowley wrote: > > On Sat, 1 Apr 2023 at 13:24, Melanie Plageman > wrote: > > Your diff LGTM. > > > > Earlier upthread in [1], Bharath had mentioned in a review comment about > > removing the global variables that he would have expected the analogous > >

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

I've now pushed up v8-0004. Can rebase the remaining 2 patches on top of master again and resend? On Mon, 3 Apr 2023 at 08:11, Melanie Plageman wrote: > I still have a few open questions: > - what the initial value of ring_size for autovacuum should be (see the > one remaining TODO in the

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

On Sat, 1 Apr 2023 at 13:24, Melanie Plageman wrote: > Your diff LGTM. > > Earlier upthread in [1], Bharath had mentioned in a review comment about > removing the global variables that he would have expected the analogous > global in analyze.c to also be removed (vac_strategy [and analyze.c also

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

On Sat, Apr 1, 2023 at 1:57 PM Justin Pryzby wrote: > > On Sat, Apr 01, 2023 at 01:29:13PM -0400, Melanie Plageman wrote: > > Hi, > > > > I was just doing some cleanup on the main patch in this set and realized > > that it was missing a few things. One of which is forbidding the > >

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

On Sat, Apr 01, 2023 at 01:29:13PM -0400, Melanie Plageman wrote: > Hi, > > I was just doing some cleanup on the main patch in this set and realized > that it was missing a few things. One of which is forbidding the > BUFFER_USAGE_LIMIT with VACUUM FULL since VACUUM FULL does not use a >

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

Hi, I was just doing some cleanup on the main patch in this set and realized that it was missing a few things. One of which is forbidding the BUFFER_USAGE_LIMIT with VACUUM FULL since VACUUM FULL does not use a BAS_VACUUM strategy. VACUUM FULL technically uses a bulkread buffer access strategy

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

On Fri, Mar 31, 2023 at 8:05 PM David Rowley wrote: > > On Sat, 1 Apr 2023 at 12:57, Melanie Plageman > wrote: > > I've attached v7 with that commit dropped and with support for parallel > > vacuum workers to use the same number of buffers in their own Buffer > > Access Strategy ring as the

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

On Sat, Apr 01, 2023 at 01:05:19PM +1300, David Rowley wrote: > Attached is what I propose in .diff form so that the CFbot can run on > your v7 patches without picking this up. But it processes .diff, too https://wiki.postgresql.org/wiki/Cfbot#Which_attachments_are_considered_to_be_patches.3F

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

On Sat, 1 Apr 2023 at 12:57, Melanie Plageman wrote: > I've attached v7 with that commit dropped and with support for parallel > vacuum workers to use the same number of buffers in their own Buffer > Access Strategy ring as the main vacuum phase did. I also updated the > docs to indicate that

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

On Fri, Mar 31, 2023 at 5:47 PM David Rowley wrote: > > On Sat, 1 Apr 2023 at 02:52, Melanie Plageman > wrote: > > There was one small typo keeping this from compiling. Also a repeated > > word. I've fixed these. I also edited a bit of indentation and tweaked > > some wording. Diff attached (to

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

On Sat, 1 Apr 2023 at 02:52, Melanie Plageman wrote: > There was one small typo keeping this from compiling. Also a repeated > word. I've fixed these. I also edited a bit of indentation and tweaked > some wording. Diff attached (to be applied on top of your diff). Thanks for fixing that mistake.

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

On Thu, Mar 30, 2023 at 11:54 PM David Rowley wrote: > > On Mon, 20 Mar 2023 at 11:50, Melanie Plageman > wrote: > > Attached is an updated v6. > > I had a look over the v6-0001 patch. There are a few things I think > could be done better: > > "Some operations will access a large number of

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

On Mon, 20 Mar 2023 at 11:50, Melanie Plageman wrote: > Attached is an updated v6. I had a look over the v6-0001 patch. There are a few things I think could be done better: "Some operations will access a large number of pages at a time", does this really need "at a time"? I think it's more

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

On Tue, Mar 21, 2023 at 6:03 AM Ants Aasma wrote: > > On Mon, 20 Mar 2023 at 00:59, Melanie Plageman > wrote: > > > > On Wed, Mar 15, 2023 at 6:46 AM Ants Aasma wrote: > > > > > > On Wed, 15 Mar 2023 at 02:29, Melanie Plageman > > > wrote: > > > > As for routine vacuuming and the other buffer

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

On Mon, 20 Mar 2023 at 00:59, Melanie Plageman wrote: > > On Wed, Mar 15, 2023 at 6:46 AM Ants Aasma wrote: > > > > On Wed, 15 Mar 2023 at 02:29, Melanie Plageman > > wrote: > > > As for routine vacuuming and the other buffer access strategies, I think > > > there is an argument for

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

On Wed, Mar 15, 2023 at 6:46 AM Ants Aasma wrote: > > On Wed, 15 Mar 2023 at 02:29, Melanie Plageman > 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

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

Thanks for the review! Attached is an updated v6. v6 has some updates and corrections. It has two remaining TODOs in the code: one is around what value to initialize the ring_size to in VacuumParams, the other is around whether or not parallel vacuum index workers should in fact stick with the

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

> Subject: [PATCH v4 3/3] add vacuum[db] option to specify ring_size and guc > +Specifies the ring buffer size to be used for a given invocation of > +VACUUM or instance of autovacuum. This size is > +converted to a number of shared buffers which will be reused as part >

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

On Wed, Mar 15, 2023 at 9:03 PM Melanie Plageman wrote: > On Sat, Mar 11, 2023 at 2:16 PM Justin Pryzby wrote: > > On Sat, Mar 11, 2023 at 09:55:33AM -0500, Melanie Plageman wrote: > > This patch should add support in vacuumdb.c. And maybe a comment about > > adding support there, since it's

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

Thanks for the reviews and for trying the patch! On Wed, Mar 15, 2023 at 4:31 AM Masahiko Sawada wrote: > > On Sat, Mar 11, 2023 at 11:55 PM Melanie Plageman > wrote: > > > > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > > > wrote: > > > > > > > On Thu, Jan 12, 2023 at 6:06 AM Andres

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

On Tue, Mar 14, 2023 at 08:56:58PM -0400, Melanie Plageman wrote: > On Sat, Mar 11, 2023 at 2:16 PM Justin Pryzby wrote: > > > @@ -586,6 +587,45 @@ GetAccessStrategy(BufferAccessStrategyType btype) > > > +BufferAccessStrategy > > > +GetAccessStrategyWithSize(BufferAccessStrategyType btype, int

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

On Wed, 15 Mar 2023 at 02:57, Melanie Plageman wrote: > > > Subject: [PATCH v3 3/3] add vacuum option to specify ring_size and guc > > > > > #define INT_ACCESS_ONCE(var) ((int)(*((volatile int *)&(var > > > +#define bufsize_limit_to_nbuffers(bufsize) (bufsize * 1024 / BLCKSZ) > > > > Macros

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

On Wed, 15 Mar 2023 at 02:29, Melanie Plageman 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

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

On Sat, Mar 11, 2023 at 11:55 PM Melanie Plageman wrote: > > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > > wrote: > > > > > On Thu, Jan 12, 2023 at 6:06 AM Andres Freund wrote: > > > > > > > > On 2023-01-11 17:26:19 -0700, David G. Johnston wrote: > > > > > Should we just add

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

Thanks for the review! On Sat, Mar 11, 2023 at 2:16 PM Justin Pryzby wrote: > > On Sat, Mar 11, 2023 at 09:55:33AM -0500, Melanie Plageman wrote: > > Subject: [PATCH v3 2/3] use shared buffers when failsafe active > > > > + /* > > + * Assume the caller who allocated the

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

Thanks for your interest in this patch! On Mon, Mar 13, 2023 at 8:38 AM Ants Aasma wrote: > > On Sat, 11 Mar 2023 at 16:55, Melanie Plageman > wrote: > > > > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > > > wrote: > > > > > > > On Thu, Jan 12, 2023 at 6:06 AM Andres Freund > > > >

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

On Sat, 11 Mar 2023 at 16:55, Melanie Plageman wrote: > > > On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > > wrote: > > > > > On Thu, Jan 12, 2023 at 6:06 AM Andres Freund wrote: > > > > > > > > On 2023-01-11 17:26:19 -0700, David G. Johnston wrote: > > > > > Should we just add

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

On Sat, Mar 11, 2023 at 09:55:33AM -0500, Melanie Plageman wrote: > Subject: [PATCH v3 2/3] use shared buffers when failsafe active > > + /* > + * Assume the caller who allocated the memory for the > + * BufferAccessStrategy object will free it. > +

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

> On Tue, Feb 28, 2023 at 3:16 AM Bharath Rupireddy > wrote: > > > On Thu, Jan 12, 2023 at 6:06 AM Andres Freund 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"

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

Thank you to all reviewers! This email is in answer to the three reviews done since my last version. Attached is v2 and inline below is replies to all the review comments. The main difference between this version and the previous version is that I've added a guc, buffer_usage_limit and the

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

On Thu, Feb 23, 2023 at 3:03 AM Melanie Plageman wrote: > > Hi, > > So, I attached a rough implementation of both the autovacuum failsafe > reverts to shared buffers and the vacuum option (no tests or docs or > anything). Thanks for the patches. I have some comments. 0001: 1. I don't quite

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

On Thu, Jan 12, 2023 at 6:06 AM Andres Freund 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.

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

Hi, On 2023-02-22 16:32:53 -0500, Melanie Plageman wrote: > I was wondering about the status of the autovacuum wraparound failsafe > test suggested in [1]. I don't see it registered for the March's > commitfest. I'll probably review it since it will be useful for this > patchset. It's pretty

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

Hi, So, I attached a rough implementation of both the autovacuum failsafe reverts to shared buffers and the vacuum option (no tests or docs or anything). The first three patches in the set are just for enabling use of shared buffers in failsafe mode for autovacuum. I haven't actually ensured it

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

Hi, 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

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

On Wed, Jan 11, 2023 at 2:39 PM Andres Freund wrote: > Hi, > > On 2023-01-11 16:18:34 -0500, Tom Lane wrote: > > Peter Geoghegan writes: > > > On Wed, Jan 11, 2023 at 11:18 AM Andres Freund > wrote: > > >> I don't like that - it's also quite useful to disable use of > ringbuffers when > > >>

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

Hi, On 2023-01-11 16:18:34 -0500, Tom Lane wrote: > Peter Geoghegan writes: > > On Wed, Jan 11, 2023 at 11:18 AM Andres Freund 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

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

Peter Geoghegan writes: > On Wed, Jan 11, 2023 at 11:18 AM Andres Freund 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... >

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

Hi, On 2023-01-11 14:38:34 -0600, Justin Pryzby wrote: > On Wed, Jan 11, 2023 at 10:58:54AM -0800, Andres Freund wrote: > > Some ideas: > > > > USE_RING_BUFFERS on|off > > REUSE_BUFFERS on|off > > +1 for either of these. Then I'd go for REUSE_BUFFERS. What made you prefer it over

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

On Wed, Jan 11, 2023 at 10:58:54AM -0800, Andres Freund wrote: > Hi, > > On 2023-01-11 10:35:19 -0800, Peter Geoghegan wrote: > > On Wed, Jan 11, 2023 at 10:27 AM Andres Freund wrote: > > > Therefore I'd like to add an option to the VACUUM command to use to > > > disable > > > the use of the

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

On Wed, Jan 11, 2023 at 11:18 AM Andres Freund 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

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

Hi, On 2023-01-11 11:06:26 -0800, Peter Geoghegan wrote: > On Wed, Jan 11, 2023 at 10:58 AM Andres Freund wrote: > > Any idea about the name? The obvious thing is to reference ring buffers in > > the > > option name, but that's more of an implementation detail... > > What are the chances that

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

On Wed, Jan 11, 2023 at 10:58 AM Andres Freund wrote: > Any idea about the name? The obvious thing is to reference ring buffers in the > option name, but that's more of an implementation detail... What are the chances that anybody using this feature via a manual VACUUM command will also use

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

Hi, On 2023-01-11 10:35:19 -0800, Peter Geoghegan wrote: > On Wed, Jan 11, 2023 at 10:27 AM Andres Freund wrote: > > Therefore I'd like to add an option to the VACUUM command to use to disable > > the use of the ringbuffer. Not sure about the name yet. > > Sounds like a good idea. Any idea

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

Hi, On 2023-01-11 10:27:20 -0800, Andres Freund wrote: > On cloud hardware with higher fsync latency I've seen > 15x time differences > between using the ringbuffers and avoiding them by using pg_prewarm. A slightly edited version of what I've in the past to defeat the ringbuffers using

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

On Wed, Jan 11, 2023 at 10:27 AM Andres Freund wrote: > Therefore I'd like to add an option to the VACUUM command to use to disable > the use of the ringbuffer. Not sure about the name yet. Sounds like a good idea. > I think we should auto-enable that mode once we're using the failsafe mode, >

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

Hi, The use of the ringbuffer in VACUUM often causes very substantial slowdowns. The primary reason for that is that often most/all the buffers in the ringbuffer have been dirtied when they were processed, with an associated WAL record. When we then reuse the buffer via the (quite small)