On 2013-01-30 14:58:24 +0100, Andres Freund wrote:
> > So reducing vacuum_freeze_min_age not only helps minimize the
> > writes that are needed when autovacuum needs to scan the entire
> > heap, but also decreases the frequency of those full-table scans.
> But it increases the amount of pages that are written out multiple times
> because they contain tuples of different ages, in contrast to increasing
> vacuum_freeze_table_age which doesn't have that problem. In combination
> with full_page_writes that makes a noticeable different in total write
Btw, as far as I read the code that behaviour only exists insofar that
the last time vacuum runs it freezes all tuples below freeze_min_age but
not newer ones, so relfrozenxid will only be set to current_xmin -
freeze_min_age. But if you manually freeze or no such old tuples exist
its solely influenced by freeze_table_age.
The relevant parts of the code are:
* Determine the table freeze age to use: as specified by the caller,
* or vacuum_freeze_table_age, but in any case not more than
* autovacuum_freeze_max_age * 0.95, so that if you have e.g nightly
* VACUUM schedule, the nightly VACUUM gets a chance to freeze tuples
* before anti-wraparound autovacuum is launched.
freezetable = freeze_min_age;
if (freezetable < 0)
freezetable = vacuum_freeze_table_age;
freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
Assert(freezetable >= 0);
* Compute the cutoff XID, being careful not to generate a "permanent"
limit = ReadNewTransactionId() - freezetable;
limit = FirstNormalTransactionId;
*freezeTableLimit = limit;
scan_all = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
If youre careful you can also notice that there is an interesting typo
in the freeze table computation. Namely it uses freeze_min_age instead
of freeze_table_age. Which probably explains why I had so bad
performance results with lowering vacuum_freeze_min_age, it basically
radically increases the amount of full-table-scans, far more than it
I can't imagine that anybody with a large database ran pg successfully
with a small freeze_min_age due to this.
It seems to be broken since the initial introduction of freeze_table_age
in 6587818542e79012276dcfedb2f97e3522ee5e9b. I guess it wasn't noticed
because the behaviour is only visible via autovacuum because a
user-issued VACUUM passes -1 as freeze_min_age.
Trivial patch attached.
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
In response to
pgsql-hackers by date
|Next:||From: Tom Lane||Date: 2013-01-30 14:58:27|
|Subject: Re: pg_dump --pretty-print-views|
|Previous:||From: Zoltán Böszörményi||Date: 2013-01-30 14:45:42|
|Subject: Re: Strange Windows problem, lock_timeout test request|