Re: another autovacuum scheduling thread - Mailing list pgsql-hackers
| From | wenhui qiu |
|---|---|
| Subject | Re: another autovacuum scheduling thread |
| Date | |
| Msg-id | CAGjGUAJ-PBzayrf793PRdnX52KvMJ8xVmQhm6BwKsf0-5qC3aQ@mail.gmail.com Whole thread |
| In response to | Re: another autovacuum scheduling thread (Sami Imseih <samimseih@gmail.com>) |
| List | pgsql-hackers |
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:
> 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 <samimseih@gmail.com> 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)
pgsql-hackers by date: