public inbox for [email protected]
help / color / mirror / Atom feedFrom: wenhui qiu <[email protected]>
To: Sami Imseih <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: [email protected]
Subject: Re: another autovacuum scheduling thread
Date: Wed, 11 Mar 2026 11:56:04 +0800
Message-ID: <CAGjGUAJ-PBzayrf793PRdnX52KvMJ8xVmQhm6BwKsf0-5qC3aQ@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
References: <CAApHDvqK=dUa35oZjG8kh+A-aPof2pfsNmb-WXTMVHduKpm6bQ@mail.gmail.com>
<CA+TgmoYCLqE-1vX9uhryF7NhOAT0v6+RP8E303u6rRgpFUiWyg@mail.gmail.com>
<CAApHDvqFyEdWyEDT7NCKcqEi2sdchhOsA-+yWv_Zk=dRek4kgg@mail.gmail.com>
<CA+TgmoZE=-Pw=85u+eX6UpMdY02o1otEhQba0n2T78yVyV2N9g@mail.gmail.com>
<CAA5RZ0vzu4vfRP0podGDCBOn-OaK9Qs-oyksER5OtzanacqMQw@mail.gmail.com>
<CA+TgmoZ44jzFBB4zxoXrX6iMxv7TPzv5duPe44UPpoyBBDL48g@mail.gmail.com>
<CAApHDvo97hqpZR+vgVVSLQsPhVCA=yEerAGn9wzB_67vjmu6cA@mail.gmail.com>
<CA+TgmoaAgv5D6u+_4KKqQFGH1n+jsFZ1mFWh+CSGqDUtyBNsXg@mail.gmail.com>
<aam3dmZcia95-vB6@nathan>
<abAzhNqs5F8sJDdm@nathan>
<abBEiOICOdbbhsEI@nathan>
<CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com>
<CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
HI Sami
> Do you think it will be better to just to add the age to the
> score?
>
> if (xid_age >= effective_xid_failsafe_age)
> xid_score += (double) xid_age;
> if (mxid_age >= effective_mxid_failsafe_age)
> mxid_score += (double) mxid_age
>
> For most cases, this should be high enough to to make the
> score high enough to sort to the top, as mentioned in the
> comments:
Agree, +1.The calculation resulted in a very large number. I previously
suggested modifying the algorithm like this:
effective_xid_failsafe_age = (vacuum_failsafe_age +
autovacuum_freeze_max_age) / 2.0. Typically, the `vacuum_failsafe_age`
parameter is rarely adjusted by DBAs.My view has always been that tables
whose age cannot be reduced should be prioritized, while we should try to
avoid tables whose age is already close to vacuum_failsafe_age.
Thanks
On Wed, Mar 11, 2026 at 9:08 AM Sami Imseih <[email protected]> wrote:
> > Just a few things:
> >
> > 1/
> > + Oid xid_age;
> > + Oid mxid_age;
> >
> > Is using Oid here intentional? I'm curious why not use uint32 for
> clarity?
> >
> > 2/
> > The new GUC docs says "...component of the score...", but without
> > introducing the concept of the prioritization score.
> > I think we should expand a bit more on this topic to help a user
> > understand and tune these more effectively. Attached is my
> > proposal for the docs. I tried to keep it informative without
> > being too verbose, and avoided making specific recommendations.
>
> My apologies. I found something else that may need
> addressing.
>
> + if (xid_age >= effective_xid_failsafe_age)
> + xid_score = pow(xid_score, Max(1.0, (double)
> xid_age / 100000000));
> + if (mxid_age >= effective_mxid_failsafe_age)
> + mxid_score = pow(mxid_score, Max(1.0, (double)
> mxid_age / 100000000));
> +
>
> The current scaling calculation for force_vacuum could lead to
> exorbitantly high scores.
> Using DEBUG3 and consume_xids_until(2000000000), notice how the score goes
> from 7.93 to 661828682916018.125 once past failsafe age.
>
> 36), anl: 0 (threshold 97929), score: 7.930
> 2026-03-10 19:41:11.979 CDT [74007] DEBUG: foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 7.930
> 2026-03-10 19:41:32.062 CDT [74038] DEBUG: foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 661828682916018.125
> 2026-03-10 19:41:32.063 CDT [74038] DEBUG: foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 661828682916018.125
> 2026-03-10 19:41:51.961 CDT [74066] DEBUG: foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 26761249940789168.000
>
> Do you think it will be better to just to add the age to the
> score?
>
> if (xid_age >= effective_xid_failsafe_age)
> xid_score += (double) xid_age;
> if (mxid_age >= effective_mxid_failsafe_age)
> mxid_score += (double) mxid_age
>
> For most cases, this should be high enough to to make the
> score high enough to sort to the top, as mentioned in the
> comments:
>
> + * As in vacuum_xid_failsafe_check(), the effective
> failsafe age is no
> + * less than 105% the value of the respective
> *_freeze_max_age
> + * parameter. Note that per-table settings could
> result in a low
> + * score even if the table surpasses the failsafe
> settings. However,
> + * this is a strange enough corner case that we don't
> bother trying to
> + * handle it.
> + */
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>
>
>
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], [email protected], [email protected]
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <CAGjGUAJ-PBzayrf793PRdnX52KvMJ8xVmQhm6BwKsf0-5qC3aQ@mail.gmail.com>
* 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