Re: New GUC autovacuum_max_threshold ?

HI
> In any case, we should do the tests that Robert suggested and/or come up
> with a good mathematical model, because we are in the dark at the moment.
I think SQL Server has given us great inspiration
>I think we should indeed provide a retro-compatible behaviour (so maybe
> another GUC after all).
I am ready to implement a new guc parameter,Enable database administrators
to configure appropriate calculation methods（The default value is the
original calculation formula）

Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com> 于2024年11月13日周三 18:03写道：

>
>
> On 11/9/24 16:59, Nathan Bossart wrote:
> > AFAICT the main advantage of these formulas is that you don't need
> another
> > GUC, but they also makes the existing ones more difficult to configure.
>
> I wouldn't say that's the main advantage. It doesn't seem very clean to
> me to cap to a fixed value. Because you could take Robert's
> demonstration with a bigger table, and come to the same conclusion:
>
> Let's compare the current situation to the situation post-Nathan's-patch
> with a cap of 100M. Consider a table 100 times larger than the one of
> Robert's previous example, so pgbench scale factor 2_560_000, size on
> disk 32TB.
> 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 256 billion
> tuples, so that means that we're going to vacuum it when there are
> more than 51 billion dead tuples. Post-patch, we will vacuum when we
> have 100 million 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 / 512 = 168 seconds).
>
> (compare with every 55 min with my formula)
>
> Of course, this a theoretical example that is probably unrealistic. I
> don't know, really. I don't know if Robert's example was realistic in
> the first place.
>
> In any case, we should do the tests that Robert suggested and/or come up
> with a good mathematical model, because we are in the dark at the moment.
>
> > Plus, there's no way to go back to the existing behavior.
>
> I think we should indeed provide a retro-compatible behaviour (so maybe
> another GUC after all).
>
>