Re: autovacuum not prioritising for-wraparound tables

On 2013-02-02 11:25:01 -0500, Robert Haas wrote:
> On Sat, Feb 2, 2013 at 8:41 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> >> - It's probably important to have a formula where we can be sure that
> >> the wrap-around term will eventually dominate the dead-tuple term,
> >> with enough time to spare to make sure nothing really bad happens; on
> >> the other hand, it's also desirable to avoid the case where a table
> >> that has just crossed the threshold for wraparound vacuuming doesn't
> >> immediately shoot to the top of the list even if it isn't truly
> >> urgent. It's unclear to me just from looking at this formula how well
> >> the second term meets those goals.
> >
> > I just wanted to mention that if everything goes well, we won't *ever*
> > get to an anti-wraparound-vacuum. Normally the table should cross the
> > vacuum_table_age barrier earlier and promote a normal vacuum to a
> > full-table vacuum which will set relfrozenxid to a new and lower value
> > and thus prevent anti-wraparound vacuums from occurring.
> > So priorizing anti-wraparound vacuums immediately and heavily doesn't
> > seem to be too bad.
>
> IMHO, this is hopelessly optimistic. Yes, it's intended to work that
> way. But INSERT-only or INSERT-mostly tables are far from an uncommon
> use case; and in fact they're probably the most common cause of pain
> in this area. You insert a gajillion tuples, and vacuum never kicks
> off, and then eventually you either update some tuples or hit
> autovacuum_freeze_max_age and suddenly, BAM, you get this gigantic
> vacuum that rewrites the entire table. And then you open a support
> ticket with your preferred PostgreSQL support provider and say
> something like "WTF?".

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.

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...

Greetings,

Andres Freund

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