Re: New GUC autovacuum_max_threshold ?

On Thu, Apr 25, 2024 at 2:52 AM Frédéric Yhuel
<frederic(dot)yhuel(at)dalibo(dot)com> wrote:
>
> Le 24/04/2024 à 21:10, Melanie Plageman a écrit :
> > On Wed, Apr 24, 2024 at 8:08 AM Frédéric Yhuel
> > <frederic(dot)yhuel(at)dalibo(dot)com> wrote:
> >>
> >> Hello,
> >>
> >> I would like to suggest a new parameter, autovacuum_max_threshold, which
> >> would set an upper limit on the number of tuples to delete/update/insert
> >> prior to vacuum/analyze.
> >
> > Hi Frédéric, thanks for the proposal! You are tackling a very tough
> > problem. I would also find it useful to know more about what led you
> > to suggest this particular solution. I am very interested in user
> > stories around difficulties with what tables are autovacuumed and
> > when.
> >
>
> Hi Melanie! I can certainly start compiling user stories about that.

Cool! That would be very useful.

> >> The idea would be to replace the following calculation :
> >>
> >> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
> >>
> >> with this one :
> >>
> >> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples / (1
> >> + vac_scale_factor * reltuples / autovacuum_max_threshold)
> >>
> >> (and the same for the others, vacinsthresh and anlthresh).
> >
> > My first thought when reviewing the GUC and how it is used is
> > wondering if its description is a bit misleading.
> >
> > autovacuum_vacuum_threshold is the "minimum number of updated or
> > deleted tuples needed to trigger a vacuum". That is, if this many
> > tuples are modified, it *may* trigger a vacuum, but we also may skip
> > vacuuming the table for other reasons or due to other factors.
> > autovacuum_max_threshold's proposed definition is the upper
> > limit/maximum number of tuples to insert/update/delete prior to
> > vacuum/analyze. This implies that if that many tuples have been
> > modified or inserted, the table will definitely be vacuumed -- which
> > isn't true. Maybe that is okay, but I thought I would bring it up.
> >
>
> I'm not too sure I understand. What are the reasons it might by skipped?
> I can think of a concurrent index creation on the same table, or
> anything holding a SHARE UPDATE EXCLUSIVE lock or above. Is this the
> sort of thing you are talking about?

No, I was thinking more literally that, if reltuples (assuming
reltuples is modified/inserted tuples) > autovacuum_max_threshold, I
would expect the table to be vacuumed. However, with your formula,
that wouldn't necessarily be true.

I think there are values of reltuples and autovacuum_max_threshold at
which reltuples > autovacuum_max_threshold but reltuples <=
vac_base_thresh + vac_scale_factor * reltuples / (1 + vac_scale_factor
* reltuples / autovacuum_max_threshold)

I tried to reduce the formula to come up with a precise definition of
the range of values for which this is true, however I wasn't able to
reduce it to something nice.

Here is just an example of a case:

vac_base_thresh = 2000
vac_scale_factor = 0.9
reltuples = 3200
autovacuum_max_threshold = 2500

total_thresh = vac_base_thresh + vac_scale_factor * reltuples / (1 +
vac_scale_factor * reltuples / autovacuum_max_threshold)

total_thresh: 3338. dead tuples: 3200. autovacuum_max_threshold: 2500

so there are more dead tuples than the max threshold, so it should
trigger a vacuum, but it doesn't because the total calculated
threshold is higher than the number of dead tuples.

This of course may not be a realistic scenario in practice. It works
best the closer scale factor is to 1 (wish I had derived the formula
successfully) and when autovacuum_max_threshold > 2 * vac_base_thresh.
So, maybe it is not an issue.

> Perhaps a better name for the GUC would be
> autovacuum_asymptotic_limit... or something like that?

If we keep the asymptotic part, that makes sense. I wonder if we have
to add another "vacuum" in there (e.g.
autovacuum_vacuum_max_threshold) to be consistent with the other gucs.
I don't really know why they have that extra "vacuum" in them, though.
Makes the names so long.

- Melanie