Re: New GUC autovacuum_max_threshold ?

On Thu, Apr 25, 2024 at 09:13:07AM +0200, Frédéric Yhuel wrote:
> Le 24/04/2024 à 21:57, Nathan Bossart a écrit :
>> Yeah, I'm having trouble following the proposed mechanics for this new GUC,
>> and it's difficult to understand how users would choose a value.  If we
>> just want to cap the number of tuples required before autovacuum takes
>> action, perhaps we could simplify it to something like
>> 
>> 	vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
>> 	vacthresh = Min(vacthres, vac_max_thresh);
>> 
>> This would effectively cause autovacuum_vacuum_scale_factor to be
>> overridden for large tables where the scale factor would otherwise cause
>> the calculated threshold to be extremely high.
> 
> This would indeed work, and the parameter would be easier to define in the
> user documentation. I prefer a continuous function... but that is personal
> taste. It seems to me that autovacuum tuning is quite hard anyway, and that
> it wouldn't be that much difficult with this kind of asymptotic limit
> parameter.

I do think this is a neat idea, but would the two approaches really be much
different in practice?  The scale factor parameters already help keep the
limit smaller for small tables and larger for large ones, so it strikes me
as needless complexity.  I think we'd need some sort of tangible reason to
think the asymptotic limit is better.

> But I think the most important thing is to avoid per-table configuration for
> most of the users, or event autovacuum tuning at all, so either of these two
> formulas would do.

Yeah, I agree with the goal of minimizing the need for per-table
configurations.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com