Re: autovacuum not prioritising for-wraparound tables

On 2013-01-25 11:51:33 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > 2. for other tables, consider floor(log(size)).  This makes tables of
> > sizes in the same ballpark be considered together.
>
> > 3. For tables of similar size, consider
> > (n_dead_tuples - threshold) / threshold.
> > "threshold" is what gets calculated as the number of tuples over which
> > a table is considered for vacuuming.  This number, then, is a relative
> > measure of how hard is vacuuming needed.
>
> The floor(log(size)) part seems like it will have rather arbitrary
> behavioral shifts when a table grows just past a log boundary.  Also,
> I'm not exactly sure whether you're proposing smaller tables first or
> bigger tables first, nor that either of those orderings is a good thing.

That seems dubious to me as well.

> I think sorting by just age(relfrozenxid) for for-wraparound tables, and
> just the n_dead_tuples measurement for others, is probably reasonable
> for now.  If we find out that has bad behaviors then we can look at how
> to fix them, but I don't think we have enough understanding yet of what
> the bad behaviors might be.

If we want another ordering criterion than that it might be worth
thinking about something like n_dead_tuples/relpages to make sure that
small tables with a high dead tuples ratio get vacuumed in time.

Greetings,

Andres Freund

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