Re: autovacuum not prioritising for-wraparound tables

On Sat, Feb 2, 2013 at 2:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund <andres@2ndquadrant.com> wrote:

You're right, this doesn't work superbly well, especially for
insert-only tables... But imo the place to fix it is not the
priorization logic but relation_needs_vacanalyze, since fixing it in
priorization won't prevent the BAM just the timing of it.

Agreed.

I think scheduling a table for a partial vacuum every min_freeze * 2
xids, even if its insert only, would go a long way of reducing the
impact of full-table vacuums. Obviously that would require to retain the
last xid a vacuum was executed in...

I'm not sure that min_freeze * 2 is the right value, but otherwise agreed.

I keep coming back to the idea that vacuum should have a high-priority
queue and a low-priority queue.  When stuff meets the current
thresholds, it goes into the high-priority queue.  But then there
should be a low-priority queue where we do partial vacuums of things
that meet some lower threshold - like the unfrozen portions of
insert-only tables.

When I was thinking about your desire for "unitless" values, I found
myself uncomfortable about that, and I think I've mentioned that.

On further reflection, there's good reason.  The need to vacuum
tables with lots of dead tuples has very different characteristics
from the need to vacuum tables to avoid XID rollover.  Trying to
force them onto the same units seems unlikely to turn out
happily.

On the other hand, I always thought that there was use for having
multiple autovacuum queues, and giving queues different
shaped policies, one for each purpose, seems like a mighty
fine idea.  That way we don't need to worry about mixing the
policies.  There can be two "best policies."

I'd go further, and have 3 queues:

a) A queue devoted to vacuuming small tables.  Anything
with more than [some number of relpages] need not apply.

b) A queue devoted to vacuuming tables with a lot of dead
tuples.

c) A queue devoted to vacuuming tables before their XID
rollover.

The appropriate "strength" functions for b) and c) can be
pretty simple, possibly the relevant bits of the functions that
Nasby and I have suggested.  And any time b) and c) find
small tables, throw them to queue a), essentially doing
the "quick & easy" vacuums.

A table with 10 live rows and a 100 dead tuples should surely have a higher priority of being vacuumed than a table with a 1000 rows and 100 dead tuples?  Especially for tables with hundreds of millions of rows!


Cheers,
Gavin