Re: autovacuum not prioritising for-wraparound tables - Mailing list pgsql-hackers
From | Andres Freund |
---|---|
Subject | Re: autovacuum not prioritising for-wraparound tables |
Date | |
Msg-id | 20130130145521.GB3355@awork2.anarazel.de Whole thread Raw |
In response to | Re: autovacuum not prioritising for-wraparound tables (Andres Freund <andres@2ndquadrant.com>) |
Responses |
Re: autovacuum not prioritising for-wraparound tables
Re: autovacuum not prioritising for-wraparound tables Re: autovacuum not prioritising for-wraparound tables |
List | pgsql-hackers |
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
Attachment
pgsql-hackers by date: