On Wed, Jan 30, 2013 at 6:55 AM, Andres Freund <and...@2ndquadrant.com> wrote: > > 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.
As far as I can tell this bug kicks in when your cluster gets to be older than freeze_min_age, and then lasts forever after. After that point pretty much every auto-vacuum inspired by update/deletion activity will get promoted to a full table scan. (Which makes me wonder how much field-testing the vm-only vacuum has received, if it was rarely happening in practice due to this bug.) Lowering the setting of freeze_min_age does not make the bug worse, it only makes it manifest earlier in the lifetime of the database. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
