Re: Eagerly scan all-visible pages to amortize aggressive vacuum

version 13 attached.

On Tue, Jan 28, 2025 at 1:49 PM Robert Treat <rob@xzilla.net> wrote:
>
> On Mon, Jan 27, 2025 at 12:45 PM Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> >
> > On Fri, Jan 24, 2025 at 3:43 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > >
> > > On Fri, Jan 24, 2025 at 3:02 PM Melanie Plageman
> > > <melanieplageman@gmail.com> wrote:
> > attached v11 uses a fraction with this name. It follows the
> > conventions and I find it descriptive.
> >
> > Changing the configuration to a fraction does mean that quite a few of
> > the comments are different in this version. I think the description of
> > the guc in
> > config.sgml could use some review in particular. I tried to make it
> > clear that the percentage is not the maximum number of blocks that
> > could be eager scanned, because you may also eagerly scan blocks and
> > succeed.
> >
>
> Specifies the maximum fraction of pages that <command>VACUUM</command>
> may scan and <emphasis>fail</emphasis> to set all-frozen in the
> visibility map before disabling eager scanning. A value of 0 disables
> eager scanning altogether. The default is 0.03 (3%).
>
> Note that when eager scanning is enabled, successful page freeze
> attempts will not count against this limit, although they are
> internally capped at 20% of the all-visible but not all-frozen pages
> within the relation, in an effort to amortize overhead for future
> aggressive vacuums.
>
> This parameter can only be set in the
> <filename>postgresql.conf</filename> file or on the server command
> line; but the setting can be overridden for individual tables by
> changing the <link
> linkend="reloption-vacuum-max-eager-freeze-failure-rate">corresponding
> table storage parameter</link>. For more information, see <xref
> linkend="vacuum-for-wraparound"/>.

Thanks! I've updated it to mostly be the same as your suggestion.

> > Other note: I noticed AutoVacOpts that are floating point numbers (like
> > vacuum_cost_delay) are float8s but their associated GUCs (like
> > autovacuum_vacuum_cost_delay) are doubles. These are going to be equivalent,
> > but I wanted to note the inconsistency in case I was missing something.
> >
>
> ::thinking emoji::
>
> I think what you have is right, though certainly better to have a more
> qualified opinion.
> On a not entirely related item, I find it interesting that you set
> your max value to 1.0, but vacuum_scale_factor (and similar) set to
> 100.00.
> I think you have it right though, what does it mean to set a table's
> scale factor to 10,000%?

Yes, I was a bit torn about this. The other GUCs with "rate" or
"fraction" in the name -- log_transaction_sample_rate,
log_statement_sample_rate, cursor_tuple_fraction, etc have a min of 0
and a max of 1 -- and their docs mention they are a "fraction of X".

However, the gucs with "factor" in the name -- e.g.
autovacuum_vacuum_scale_factor have a min of 0 and a max of 100 -- but
those are taken as fractions. So, if you specify 50, that isn't 50%
but 5000% (which is what you are saying).
AFAICT, that just gets multiplied by reltuples and added to the base
threshold in relation_needs_vacanalyze(). So, I mean I guess maybe
that is purposeful, but it is quite confusing. I imagine there are
some users out there with it set to 20 thinking that means 20% of
reltuples when it really means 20x reltuples.

> > I have changed it to DEBUG2. I started it as INFO because the other vacuum
> > messages about whether or not the vacuum is an aggressive vacuum were at INFO
> > level.I don't know what a user might prefer. Treat said this downthread:
snip
> > I'm happy to go either way. I don't want users mad about verbosity, but if they
> > think it's helpful, then that seems good.
>
> If there is a configurable, people will want to tune it, and DEBUG
> level messages aren't a usable solution.

In this version, I've made it INFO when verbose is set and DEBUG1
otherwise -- which aligns somewhat with some of the other messages in
vacuumlazy.c

- Melanie