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

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Making autovacuum logs indicate if insert-based threshold was the triggering condition
Date: 2022-08-06 20:03:57
Message-ID: CAH2-WznEqmkmry8feuDK8XdpH37-4anyGF7a04bWXOc1GKd0Yg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Show-when-autovacuum-is-insert-driven-in-log.patch application/octet-stream 8.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2022-08-06 21:07:24 failing to build preproc.c on solaris with sun studio
Previous Message Álvaro Herrera 2022-08-06 20:03:55 Re: pgsql: BRIN: mask BRIN_EVACUATE_PAGE for WAL consistency checking