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 13:58:17 -0500
Message-ID: <aPkpSXHB66islP3h@nathan> (raw)
In-Reply-To: <aPklC5V61VcTu7IP@nathan>
References: <[email protected]>
<[email protected]>
<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>
On Wed, Oct 22, 2025 at 01:40:11PM -0500, Nathan Bossart wrote:
> On Wed, Oct 22, 2025 at 09:07:33AM +1300, David Rowley wrote:
>> However, just thinking of non-standard setting... I do wonder if it'll
>> be aggressive enough if someone did something like raise the
>> *freeze_max_age to 1 billion (it's certainly common that people raise
>> this). With a 1.6 billion vacuum_failsafe_age, a table at
>> freeze_max_age only scores in at 110. I guess there's no reason we
>> couldn't keep your calc and then scale the score further once over
>> vacuum_failsafe_age to ensure those are the highest priority. There is
>> a danger that if a table scores too low when age(relfrozenxid) >
>> vacuum_failsafe_age that autovacuum dawdles along handling bloated
>> tables while oblivious to the nearing armageddon.
>
> That's a good point. I wonder if we should try to make the wraparound
> score independent of the *_freeze_max_age parameters (once the table age
> surpasses said parameters). Else, different settings will greatly impact
> how aggressively tables are prioritized the closer they are to wraparound.
> Even if autovacuum_freeze_max_age is set to 200M, it's not critically
> important for autovacuum to pick up tables right away as soon as their age
> reaches 200M. But if the parameter is set to 2B, we _do_ want autovacuum
> to prioritize tables right away once their age reaches 2B.
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
400000000 | 54.48913545427955
500000000 | 148.0428361625591
600000000 | 402.22112456608977
700000000 | 1092.804199384323
800000000 | 2969.065882554825
900000000 | 8066.726152697397
1000000000 | 21916.681339054314
1100000000 | 59545.956045257895
1200000000 | 161781.8330472099
1300000000 | 439548.9340069078
1400000000 | 1194221.0181920114
1500000000 | 3244607.664704634
1600000000 | 8815352.21495106
1700000000 | 23950641.403886583
1800000000 | 65072070.82261215
1900000000 | 176795866.53808445
2000000000 | 480340920.9176516
(21 rows)
--
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: <aPkpSXHB66islP3h@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