Horton

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

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Peter Geoghegan <pg@bowt.ie>, pgsql-hackers@postgresql.org, Robert Haas <robertmhaas@gmail.com>, David Rowley <dgrowleyml@gmail.com>, "jelte.fennema@microsoft.com" <jelte.fennema@microsoft.com>
Date: 2023-03-17T00:35:21Z
Lists: pgsql-hackers

Attachments

On Wed, Mar 15, 2023 at 9:03 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
> On Sat, Mar 11, 2023 at 2:16 PM Justin Pryzby <pryzby@telsasoft.com> 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 annoying when it the release notes one
> > year say "support VACUUM (FOO)" and then one year later say "support
> > vacuumdb --foo".
>
> So, v4 adds support for buffer-usage-limit to vacuumdb. There are a few
> issues. The main one is that no other vacuumdb option takes a size as a
> parameter. I couldn't actually find any other client with a parameter
> specified as a size.
>
> My VACUUM option code is using the GUC size parsing code from
> parse_int() -- including the unit flag GUC_UNIT_KB. Now that vacuumdb
> also needs to parse sizes, I think we'll need to lift the parse_int()
> code and the unit_conversion struct and
> unit_conversion_memory_unit_conversion_table out of guc.c and put it
> somewhere that it can be accessed for more than guc parsing (e.g. option
> parsing).
>
> For vacuumdb in this version, I just specified buffer-usage-limit is
> only in kB and thus can only be specified as an int.
>
> If we had something like pg_parse_size() in common, would this make
> sense? It would be a little bit of work to figure out what to do about
> the flags, etc.
>
> Another issue is the server-side guc
> #define MAX_BAS_RING_SIZE_KB (16 * 1024 * 1024)
> I just redefined it in vacuumdb code. I'm not sure what the preferred
> method for dealing with this is.
>
> I know this validation would get done server-side if I just passed the
> user-specified option through, but all of the other vacuumdb options
> appear to be doing min/max boundary validation on the client side.

So, after discussing vacuumdb client-side validation off-list with Jelte,
I realized that I was trying to do too much there.

Attached v5 passes the contents of the buffer-usage-limit option to
vacuumdb unvalidated into the VACUUM command string which vacuumdb
builds. This solves most of the problems.

I also improved the error messages coming from VACUUM
(buffer_usage_limit) handling.

- Melanie

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