Thread: Making autovacuum logs indicate if insert-based threshold was the triggering condition

It's quite possible (and probably very common) for certain tables to
have autovacuum scheduling trigger autovacuums based on both the
"regular" bloat-orientated thresholds, and the newer insert-based
thresholds. It may be far from obvious which triggering condition
autovacuum.c must have applied to trigger any given autovacuum, since
that information isn't currently passed down to lazyvacuum.c. This
seems like a problem to me; how are users supposed to tune
autovacuum's thresholds without even basic feedback about how the
thresholds get applied?

Attached patch teaches autovacuum.c to pass the information down to
lazyvacuum.c, which includes the information in the autovacuum log.
The approach I've taken is very similar to the existing approach with
anti-wraparound autovacuum. It's pretty straightforward. Note that a
VACUUM that is an "automatic vacuum for inserted tuples" cannot also
be an antiwraparound autovacuum, nor can it also be a "regular"
autovacuum/VACUUM -- there are now 3 distinct "triggering conditions"
for autovacuum.

Although this patch doesn't touch antiwraparound autovacuums at all, I
will note in passing that I think that anti-wraparound autovacuums
should become just another triggering condition for autovacuum -- IMV
they shouldn't be special in *any* way. We'd still need to keep
antiwraparound's "cannot automatically cancel autovacuum worker"
behavior in some form, but that would become dynamic, a little like
the failsafe is today, and would trigger on its own timeline (probably
*much* later than we first trigger antiwraparound autovacuum). We'd
also need to decouple "aggressiveness" (the behaviors that we
associate with aggressive mode in vacuumlazy.c) from the condition of
the table/system when VACUUM first began -- those could all become
dynamic, too.

-- 
Peter Geoghegan

On Sat, Aug 06, 2022 at 01:03:57PM -0700, Peter Geoghegan wrote:
> thresholds. It may be far from obvious which triggering condition
> autovacuum.c must have applied to trigger any given autovacuum, since
> that information isn't currently passed down to lazyvacuum.c. This
> seems like a problem to me; how are users supposed to tune
> autovacuum's thresholds without even basic feedback about how the
> thresholds get applied?

+1

This sounded familiar, and it seems like I anticipated that it might be an
issue.  Here, I was advocating for the new insert-based GUCs to default to -1,
to have insert-based autovacuum fall back to the thresholds specified by the
pre-existing GUCs (20% + 50), which would (in my proposal) remain be the normal
way to tune any type of vacuum.

https://www.postgresql.org/message-id/20200317233218.GD26184@telsasoft.com

I haven't heard of anyone who had trouble setting the necessary GUC, but I'm
not surprised if most postgres installations are running versions before 13.

> Note that a VACUUM that is an "automatic vacuum for inserted tuples" cannot
> [...] also be a "regular" autovacuum/VACUUM

Why not ?

$ ./tmp_install/usr/local/pgsql/bin/postgres -D src/test/regress/tmp_check/data -c log_min_messages=debug3 -c
autovacuum_naptime=9s&
DROP TABLE t; CREATE TABLE t (i int); INSERT INTO t SELECT generate_series(1,99999); DELETE FROM t; INSERT INTO t
SELECTgenerate_series(1,99999);
 

2022-08-06 16:47:47.674 CDT autovacuum worker[12707] DEBUG:  t: vac: 99999 (threshold 50), ins: 99999 (threshold 1000),
anl:199998 (threshold 50)
 

-- 
Justin

On Sat, Aug 6, 2022 at 2:50 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> This sounded familiar, and it seems like I anticipated that it might be an
> issue.  Here, I was advocating for the new insert-based GUCs to default to -1,
> to have insert-based autovacuum fall back to the thresholds specified by the
> pre-existing GUCs (20% + 50), which would (in my proposal) remain be the normal
> way to tune any type of vacuum.
>
> https://www.postgresql.org/message-id/20200317233218.GD26184@telsasoft.com
>
> I haven't heard of anyone who had trouble setting the necessary GUC, but I'm
> not surprised if most postgres installations are running versions before 13.

ISTM that having insert-based triggering conditions is definitely a
good idea, but what we have right now still has problems. It currently
won't work very well unless the user goes out of their way to tune
freezing to do the right thing. Typically we miss out on the
opportunity to freeze early, because without sophisticated
intervention from the user there is only a slim chance of *any*
freezing taking place outside of the inevitable antiwraparound
autovacuum.

> > Note that a VACUUM that is an "automatic vacuum for inserted tuples" cannot
> > [...] also be a "regular" autovacuum/VACUUM
>
> Why not ?

Well, autovacuum.c should have (and/or kind of already has) 3
different triggering conditions. These are mutually exclusive
conditions -- technically autovacuum.c always launches an autovacuum
against a table because exactly 1 of the 3 thresholds were crossed. My
patch makes sure that it always gives exactly one reason why
autovacuum.c decided to VACUUM, so by definition there is only one
relevant piece of information for vacuumlazy.c to report in the log.
That's fairly simple and high level, and presumably something that
users won't have much trouble understanding.

Right now antiwraparound autovacuum "implies aggressive", in that it
almost always makes vacuumlazy.c use aggressive mode, but this seems
totally arbitrary to me -- they don't have to be virtually synonymous.
I think that antiwraparound autovacuum could even be rebranded as "an
autovacuum that takes place because the table hasn't had one in a long
time". This is much less scary, and makes it clearer that autovacuum.c
shouldn't be expected to really understand what will turn out to be
important "at runtime". That's the time to make important decisions
about what work to do -- when we actually have accurate information.

My antiwraparound example is just that: an example. There is a broader
idea: we shouldn't be too confident that the exact triggering
condition autovacuum.c applied to launch an autovacuum worker turns
out to be the best reason to VACUUM, or even a good reason --
vacuumlazy.c should be able to cope with that. The user is kept in the
loop about both, by reporting the triggering condition and the details
of what really happened at runtime. Maybe lazyvacuum.c can be taught
to speed up and slow down based on the conditions it observes as it
scans the heap -- there are many possibilities.

This broader idea is pretty much what you were getting at with your
example, I think.

-- 
Peter Geoghegan

On Sat, Aug 06, 2022 at 03:41:57PM -0700, Peter Geoghegan wrote:
> > > Note that a VACUUM that is an "automatic vacuum for inserted tuples" cannot
> > > [...] also be a "regular" autovacuum/VACUUM
> >
> > Why not ?

I think maybe you missed my intent in trimming the "anti-wraparound" part of
your text.

My point was concerning your statement that "autovacuum for inserted tuples ..
cannot also be a regular autovacuum" (meaning triggered by dead tuples).

> Well, autovacuum.c should have (and/or kind of already has) 3
> different triggering conditions. These are mutually exclusive
> conditions -- technically autovacuum.c always launches an autovacuum
> against a table because exactly 1 of the 3 thresholds were crossed.

The issue being that both thresholds can be crossed:

>> 2022-08-06 16:47:47.674 CDT autovacuum worker[12707] DEBUG:  t: VAC: 99999 (THRESHOLD 50), INS: 99999 (THRESHOLD
1000),anl: 199998 (threshold 50)
 

-- 
Justin

On Sat, Aug 6, 2022 at 3:51 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > Well, autovacuum.c should have (and/or kind of already has) 3
> > different triggering conditions. These are mutually exclusive
> > conditions -- technically autovacuum.c always launches an autovacuum
> > against a table because exactly 1 of the 3 thresholds were crossed.
>
> The issue being that both thresholds can be crossed:
>
> >> 2022-08-06 16:47:47.674 CDT autovacuum worker[12707] DEBUG:  t: VAC: 99999 (THRESHOLD 50), INS: 99999 (THRESHOLD
1000),anl: 199998 (threshold 50)
 

What are the chances that both thresholds will be crossed at *exactly*
(not approximately) the same time in a real world case, where the
table isn't tiny (tiny relative to the "autovacuum_naptime quantum")?
This is a very narrow case.

Besides, the same can already be said with how autovacuum.c crosses
the XID-based antiwraparound threshold. Yet we still arbitrarily
report that it's antiwraparound in the logs, which (at least right
now) is generally assumed to mostly be about advancing relfrozenxid.
(Or maybe it's the other way around; it doesn't matter.)

It might make sense to *always* show how close we were to hitting each
of the thresholds, including the ones that we didn't end up hitting
(we may come pretty close quite often, which seems like it might
matter). But showing multiple conditions together just because the
planets aligned (we hit multiple thresholds together) emphasizes the
low-level mechanism, which is pretty far removed from anything that
matters. You might as well pick either threshold at random once this
happens -- even an expert won't be able to tell the difference.

-- 
Peter Geoghegan

On Sat, Aug 06, 2022 at 04:09:28PM -0700, Peter Geoghegan wrote:
> What are the chances that both thresholds will be crossed at *exactly*
> (not approximately) the same time in a real world case, where the
> table isn't tiny (tiny relative to the "autovacuum_naptime quantum")?
> This is a very narrow case.

The threshold wouldn't need to be crossed within autovacuum_naptime, since all
the workers might be busy.  Consider autovacuum delay, analyze on long/wide
tables, multiple extended stats objects, or autovacuum parameters which are
changed off-hours by a cronjob.

> It might make sense to *always* show how close we were to hitting each
> of the thresholds, including the ones that we didn't end up hitting
> (we may come pretty close quite often, which seems like it might
> matter). But showing multiple conditions together just because the
> planets aligned (we hit multiple thresholds together) emphasizes the
> low-level mechanism, which is pretty far removed from anything that
> matters. You might as well pick either threshold at random once this
> happens -- even an expert won't be able to tell the difference.

I don't have strong feelings about it; I'm just pointing out that the
two of the conditions aren't actually exclusive.

It seems like it could be less confusing to show both.  Consider someone who is
trying to *reduce* how often autovacuum runs, or give priority to some tables
by raising the thresholds for other tables.

-- 
Justin

On Fri, Sep 9, 2022 at 11:11 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > It might make sense to *always* show how close we were to hitting each
> > of the thresholds, including the ones that we didn't end up hitting
> > (we may come pretty close quite often, which seems like it might
> > matter). But showing multiple conditions together just because the
> > planets aligned (we hit multiple thresholds together) emphasizes the
> > low-level mechanism, which is pretty far removed from anything that
> > matters. You might as well pick either threshold at random once this
> > happens -- even an expert won't be able to tell the difference.
>
> I don't have strong feelings about it; I'm just pointing out that the
> two of the conditions aren't actually exclusive.

Fair enough. I'm just pointing out that the cutoffs are continuous for
all practical purposes, even if there are cases where they seem kind
of discrete, due only to implementation details (e.g.
autovacuum_naptime stuff). Displaying only one reason for triggering
an autovacuum is consistent with the idea that the cutoffs are
continuous. It's not literally true that they're continuous, but it
might as well be.

I think of it as similar to how it's not literally true that a coin
toss is always either heads or tails, though it might as well be true.
Sure, even a standard fair coin toss might result in the coin landing
on its side. That'll probably never happen even once, but if does:
just flip the coin again! The physical coin toss was never the
important part.

> It seems like it could be less confusing to show both.  Consider someone who is
> trying to *reduce* how often autovacuum runs, or give priority to some tables
> by raising the thresholds for other tables.

My objection to that sort of approach is that it suggests a difference
in what each VACUUM actually does -- as if autovacuum.c actually
decided on a particular runtime behavior for the VACUUM up front,
based on its own considerations that come from statistics about the
workload. I definitely want to avoid creating that false impression in
the minds of users.

-- 
Peter Geoghegan

On Sat, Aug 6, 2022 at 1:03 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Attached patch teaches autovacuum.c to pass the information down to
> lazyvacuum.c, which includes the information in the autovacuum log.
> The approach I've taken is very similar to the existing approach with
> anti-wraparound autovacuum. It's pretty straightforward.

I have formally withdrawn this patch. I still think it's a good idea,
and I'm not abandoning the idea. The patch has just been superseded by
another patch of mine:

https://postgr.es/m/CAH2-Wz=hj-RCr6fOj_L3_0J1Ws8fOoxTQLmtM57gPc19beZz=Q@mail.gmail.com

This other patch has a much broader goal: it decouples
"antiwraparound-ness vs regular-ness" from the criteria that triggered
autovacuum (which includes a "table age" trigger criteria). Since the
other patch has to invent the whole concept of an autovacuum trigger
criteria (which it reports on via a line in autovacuum server log
reports), it seemed best to do everything together.

Thanks
-- 
Peter Geoghegan