Re: Make autovacuum sort tables in descending order of xid_age

On Thu, Jan 9, 2020 at 12:07 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Thu, Dec 12, 2019 at 2:26 PM David Fetter <david(at)fetter(dot)org> wrote:
> > > I wonder if you might add information about table size, table changes,
> > > and bloat to your RelFrozenXidAge struct and modify rfxa_comparator to
> > > use a heuristic to cost the (age, size, bloat, changed) grouping and
> > > sort on that cost, such that really large bloated tables with old xids
> > > might get vacuumed before smaller, less bloated tables that have
> > > even older xids. Sorting the tables based purely on xid_age seems to
> > > ignore other factors that are worth considering. I do not have a
> > > formula for how those four factors should be weighted in the heuristic,
> > > but you are implicitly assigning three of them a weight of zero in
> > > your current patch.
> >
> > I think it's vastly premature to come up with complex sorting systems
> > right now. Just sorting in descending order of age should either have
> > or not have positive effects.
>
> A lot of previous efforts to improve autovacuum scheduling have fallen
> down precisely because they did something that was so simple that it
> was doomed to regress as many cases as it improved, so I wouldn't be
> too quick to dismiss Mark's suggestion. In general, sorting by XID age
> seems like it should be better, but it's not hard to come up with a
> counterexample: suppose table T1 is going to wrap around in 4 hours
> and takes 4 hours to vacuum, but table T2 is going to wrap around in 2
> hours and takes 1 hour to vacuum.

Ah, so primary purpose of this patch is to add smarts when autovacuum
is triggered to handle wrap around?

> I've had the thought for a while now that perhaps we ought to try to
> estimate the rate of XID consumption, because without that it's really
> hard to make smart decisions.

Very interesting.

Could there be value in making this feature more preventative, perhaps
by triggering emergency autovacuum earlier based on some combination
of these heuristics rather than autovacuum_freeze_max_age alone?

Thanks,
David