Re: New GUC autovacuum_max_threshold ?

On Fri, Apr 26, 2024 at 9:40 AM Joe Conway <mail@joeconway.com> wrote:
> > Can you elaborate on why you think that? I mean, to me, that's almost
> > equivalent to removing autovacuum_vacuum_scale_factor entirely,
> > because only for very small tables will that calculation produce a
> > value lower than 500k.
>
> If I understood Nathan's proposed calc, for small tables you would still
> get (thresh + sf * numtuples). Once that number exceeds the new limit
> parameter, then the latter would kick in. So small tables would retain
> the current behavior and large enough tables would be clamped.

Right. But with a 500k threshold, "large enough" is not very large at
all. The default scale factor is 20%, so the crossover point is at 2.5
million tuples. That's pgbench scale factor 25, which is a 320MB
table.

> It depends on workload to be sure. Just because a table is large, it
> doesn't mean that dead rows are generated that fast.

That is true, as far as it goes.

> Admittedly it has been quite a while since I looked at all this that
> closely, but if A/V runs on some large busy table for a few milliseconds
> once every few minutes, that is far less disruptive than A/V running for
> tens of seconds once every few hours or for minutes ones every few days
> -- or whatever. The key thing to me is the "few milliseconds" runtime.
> The short duration means that no one notices an impact, and the longer
> duration almost guarantees that an impact will be felt.

Sure, I mean, I totally agree with that, but how is a vacuum on a
large table going to run for milliseconds? If it can skip index
vacuuming, sure, then it's quick, because it only needs to scan the
heap pages that are not all-visible. But as soon as index vacuuming is
triggered, it's going to take a while. You can't afford to trigger
that constantly.

Let's compare the current situation to the situation post-patch with a
cap of 500k. Consider a table 1024 times larger than the one I
mentioned above, so pgbench scale factor 25600, size on disk 320GB.
Currently, that table will be vacuumed for bloat when the number of
dead tuples exceeds 20% of the table size, because that's the default
value of autovacuum_vacuum_scale_factor. The table has 2.56 billion
tuples, so that means that we're going to vacuum it when there are
more than 510 million dead tuples. Post-patch, we will vacuum when we
have 500 thousand dead tuples. Suppose a uniform workload that slowly
updates rows in the table. If we were previously autovacuuming the
table once per day (1440 minutes) we're now going to try to vacuum it
almost every minute (1440 minutes / 1024 = 84 seconds).

Unless I'm missing something major, that's completely bonkers. It
might be true that it would be a good idea to vacuum such a table more
often than we do at present, but there's no shot that we want to do it
that much more often. The pgbench_accounts_pkey index will, I believe,
be on the order of 8-10GB at that scale. We can't possibly want to
incur that much extra I/O every minute, and I don't think it's going
to finish in milliseconds, either.

--
Robert Haas
EDB: http://www.enterprisedb.com