Re: autovacuum not prioritising for-wraparound tables

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

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:

c.f.
vacuum_set_xid_limits:
		/*
		 * 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"
		 * XID.
		 */
		limit = ReadNewTransactionId() - freezetable;
		if (!TransactionIdIsNormal(limit))
			limit = FirstNormalTransactionId;

		*freezeTableLimit = limit;

lazy_vacuum_rel:
	scan_all = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
						 freezeTableLimit);

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

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.

Greetings,

Andres Freund

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