On 2013-02-02 11:25:01 -0500, Robert Haas wrote: > On Sat, Feb 2, 2013 at 8:41 AM, Andres Freund <and...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
