Re: autovacuum not prioritising for-wraparound tables

On Sat, Feb 2, 2013 at 2:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund <andres(at)2ndquadrant(dot)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.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"