Skip site navigation (1) Skip section navigation (2)

Re: autovacuum not prioritising for-wraparound tables

From: Kevin Grittner <kgrittn(at)ymail(dot)com>
To: Jim Nasby <jim(at)nasby(dot)net>, Christopher Browne <cbbrowne(at)gmail(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autovacuum not prioritising for-wraparound tables
Date: 2013-01-30 21:28:36
Message-ID: 1359581316.46973.YahooMailNeo@web162903.mail.bf1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Jim Nasby <jim(at)nasby(dot)net> wrote:

> the only reason to freeze is XID wrap

The conclusions you draw seem to be based on a slightly different
premise than stated here; the conclusions are only supported by the
assumption that "the only reason to freeze at any particular moment
is to assure that all tuples are frozen in time to prevent
transaction wraparound."  In a mostly write-only database, that has
to happen for nearly every tuple at some point -- but *which* point
is best is not necessarily "as late as possible".

> then the *ideal* time to start a freeze vacuum on a table is so
> that the vacuum would end *exactly* as we were about to hit XID
> wrap.

For a tuple which you know is going to survive long enough to be
frozen, the *ideal* time to freeze a tuple, from a performance
standpoint, is when the tuple is written, to eliminate a separate
WAL-log operation.  The *next best* time to freeze is when the hint
bits are set, to avoid a separate page write.  If you are doing
differential backups, the *third best* time to freeze is before the
first differential backup of the tuple, to avoid a separate backup
after the freeze.  And so on.

-Kevin


In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2013-01-30 21:29:47
Subject: Re: plpgsql versus SPI plan abstraction
Previous:From: Tom LaneDate: 2013-01-30 21:23:18
Subject: plpgsql versus SPI plan abstraction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group