Re: New GUC autovacuum_max_threshold ?

On Fri, Apr 26, 2024 at 4:43 AM Michael Banck <mbanck(at)gmx(dot)net> wrote:
> > I believe that the defaults should work well in moderately sized databases
> > with moderate usage characteristics. If you have large tables or a high
> > number of transactions per second, you can be expected to make the effort
> > and adjust the settings for your case. Adding more GUCs makes life *harder*
> > for the users who are trying to understand and configure how autovacuum works.
>
> Well, I disagree to some degree. I agree that the defaults should work
> well in moderately sized databases with moderate usage characteristics.
> But I also think we can do better than telling DBAs to they have to
> manually fine-tune autovacuum for large tables (and frequenlty
> implementing by hand what this patch is proposed, namely setting
> autovacuum_vacuum_scale_factor to 0 and autovacuum_vacuum_threshold to a
> high number), as this is cumbersome and needs adult supervision that is
> not always available. Of course, it would be great if we just slap some
> AI into the autovacuum launcher that figures things out automagically,
> but I don't think we are there, yet.
>
> So this proposal (probably along with a higher default threshold than
> 500000, but IMO less than what Robert and Nathan suggested) sounds like
> a stop forward to me. DBAs can set the threshold lower if they want, or
> maybe we can just turn it off by default if we cannot agree on a sane
> default, but I think this (using the simplified formula from Nathan) is
> a good approach that takes some pain away from autovacuum tuning and
> reserves that for the really difficult cases.

I agree with this. If having an extra setting substantially reduces
the number of cases that require manual tuning, it's totally worth it.
And I think it will.

To be clear, I don't think this is the biggest problem with the
autovacuum algorithm, not by quite a bit. But it's a relatively easy
one to fix.

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