public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: David Rowley <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: [email protected]
Subject: Re: another autovacuum scheduling thread
Date: Wed, 22 Oct 2025 14:43:50 -0500
Message-ID: <aPkz9grlBAJRXrbe@nathan> (raw)
In-Reply-To: <CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
References: <CAApHDvq76BBseUh2cG0=m=8r-j6HF_jrQt16Eszgsxp3bciGQw@mail.gmail.com>
<aOffPCBoQLG5dGd8@nathan>
<aOlC4aDoQcgW8ZpC@nathan>
<CA+TgmoYC4ShRp8vcyrBjkefSBdFfY1fnUgCvjocN-iq55G-7bA@mail.gmail.com>
<CAApHDvqrd=SHVUytdRj55OWnLH98Rvtzqam5zq2f4XKRZa7t9Q@mail.gmail.com>
<aPea8dG8_lFwTsyo@nathan>
<CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
<aPklC5V61VcTu7IP@nathan>
<aPkpSXHB66islP3h@nathan>
<CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
On Thu, Oct 23, 2025 at 08:34:49AM +1300, David Rowley wrote:
> On Thu, 23 Oct 2025 at 07:58, Nathan Bossart <[email protected]> wrote:
>> I'm imagining something a bit like the following:
>>
>> select xidage "age(relfrozenxid)",
>> power(1.001, xidage::float8 / (select min_val
>> from pg_settings where name = 'autovacuum_freeze_max_age')::float8)
>> xid_age_score from generate_series(0,2_000_000_000,100_000_000) xidage;
>>
>> age(relfrozenxid) | xid_age_score
>> -------------------+--------------------
>> 0 | 1
>> 100000000 | 2.7169239322355936
>> 200000000 | 7.38167565355452
>> 300000000 | 20.055451243143093
>
> This does start to put the score > 1 before the table reaches
> autovacuum_freeze_max_age. I don't think that's great as the score of
> 1.0 was meant to represent that the table now requires some autovacuum
> work.
My thinking was that this formula would only be used once the table reaches
autovacuum_freeze_max_age. If the age is less than that, we'd do something
else, such as dividing the age by the *_max_age setting.
> The main reason I was trying to keep the score scaling with the
> percentage over the given threshold that the table is was that I had
> imagined we could use the score number to start reducing the sleep
> time between autovacuum_vacuum_cost_limit when the highest scoring
> table persists in being high for too long. I was considering this to
> fix the misconfigured autovacuum problem that so many people have. If
> we scaled it the way similar to the query above, the score would look
> high even before it reaches the limit. This is the reason I was
> scaling the score linear with the autovacuum_freeze_max_age with the
> version I sent and only scaling exponentially after the failsafe age.
> I wanted to talk about the "reducing the cost delay" feature
> separately so as not to load up this thread and widen the scope for
> varying opinions, but in its most trivial form, the
> vacuum_cost_limit() code could be adjusted to only sleep for
> autovacuum_vacuum_cost_delay / <the table's score>.
I see.
> I think the one I proposed in [1] does this quite well. The table
> remains eligible to be autovacuumed with any score >= 1.0, and there's
> still a huge window of time to freeze a table once it's over
> autovacuum_freeze_max_age before there are issues and the exponential
> scaling once over failsafe age should ensure that the table is top of
> the list for when the failsafe code kicks in and removes the cost
> limit.
Yeah. I'll update the patch with that formula.
--
nathan
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <aPkz9grlBAJRXrbe@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox