Re: autovacuum not prioritising for-wraparound tables

On 03/02/13 15:08, Christopher Browne wrote:
> 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.
Hmm...

Could there be some measure of bloatedness?

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