Re: New GUC autovacuum_max_threshold ? - Mailing list pgsql-hackers

From Nathan Bossart
Subject Re: New GUC autovacuum_max_threshold ?
Date
Msg-id 20240425153058.GA865187@nathanxps13
Whole thread Raw
In response to Re: New GUC autovacuum_max_threshold ?  (Frédéric Yhuel <frederic.yhuel@dalibo.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: minor error message inconsistency in make_pathkey_from_sortinfo
Next
From: Maxim Orlov
Date:
Subject: Build with meson + clang + sanitizer resulted in undefined reference