Re: autovacuum not prioritising for-wraparound tables - Mailing list pgsql-hackers

<div class="moz-cite-prefix">On 03/02/13 15:08, Christopher Browne wrote:<br /></div><blockquote
cite="mid:CAFNqd5WXnHJsewFugQUDQD1SHbGRku_9-7P2BskzQX+qh8+mvQ@mail.gmail.com"type="cite"><pre wrap="">On Sat, Feb 2,
2013at 2:54 PM, Robert Haas <a class="moz-txt-link-rfc2396E"
href="mailto:robertmhaas@gmail.com"><robertmhaas@gmail.com></a>wrote:
 
</pre><blockquote type="cite"><pre wrap="">On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund <a
class="moz-txt-link-rfc2396E"href="mailto:andres@2ndquadrant.com"><andres@2ndquadrant.com></a> wrote:
 
</pre><blockquote type="cite"><pre wrap="">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.
</pre></blockquote><pre wrap="">
Agreed.

</pre><blockquote type="cite"><pre wrap="">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...
</pre></blockquote><pre wrap="">
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.
</pre></blockquote><pre wrap="">
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.
</pre></blockquote><font size="-1">Hmm...<br /><br /><font size="-1">Could there be some measure of bloatedness?<br
/></font></font><pclass="western" style="margin-bottom: 0cm"><font size="2">A table with 10 live rows and a 100 dead
tuplesshould surely have a higher priority of being vacuumed than a table with a 1000 ro</font><font
size="2">w</font><fontsize="2">s and 100 dead tuples?  Especially for tables with <font size="2">hundreds of
</font>millionsof rows<font size="2">!</font><br /></font><font size="-1"><font size="-1"><font size="-1"><font
size="-1"><fontsize="-1"><font size="-1"><font size="-1"><br /><br /><font size="-1">Cheers,<br /><font
size="-1">Gavin</font><br/></font></font></font></font></font></font></font></font>