Re: autovacuum not prioritising for-wraparound tables

On 2013-02-01 16:59:52 -0500, Robert Haas wrote:
> I don't think I really understand the origin of the formula, so
> perhaps if someone would try to characterize why it seems to behave
> reasonably that would be helpful (at least to me).
>
> > f(deadtuples, relpages, age) =
> > deadtuples/relpages + e ^ (age*ln(relpages)/2^32)
>
> To maybe make that discussion go more quickly let me kvetch about a
> few things to kick things off:

I am not too happy with the formula yet either, but it seems youve
started the discussion into the right direction...

> - It's probably important to have a formula where we can be sure that
> the wrap-around term will eventually dominate the dead-tuple term,
> with enough time to spare to make sure nothing really bad happens; on
> the other hand, it's also desirable to avoid the case where a table
> that has just crossed the threshold for wraparound vacuuming doesn't
> immediately shoot to the top of the list even if it isn't truly
> urgent. It's unclear to me just from looking at this formula how well
> the second term meets those goals.

I just wanted to mention that if everything goes well, we won't *ever*
get to an anti-wraparound-vacuum. Normally the table should cross the
vacuum_table_age barrier earlier and promote a normal vacuum to a
full-table vacuum which will set relfrozenxid to a new and lower value
and thus prevent anti-wraparound vacuums from occurring.
So priorizing anti-wraparound vacuums immediately and heavily doesn't
seem to be too bad.

> - More generally, it seems to me that we ought to be trying to think
> about the units in which these various quantities are measured. Each
> term ought to be unit-less. So perhaps the first term ought to divide
> dead tuples by total tuples, which has the nice property that the
> result is a dimensionless quantity that never exceeds 1.0. Then the
> second term can be scaled somehow based on that value.

I think we also need to be careful to not try to get too elaborate on
this end. Once the general code for priorization is in, the exact
priorization formula can be easily incrementally tweaked. Just about any
half-way sensible priorization is better than what we have right now and
we might discover new effects once we do marginally better.

Imo the browne_strength field should be called 'priority' and the
priorization calculation formula should be moved qinto an extra
function.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services