Re: autovacuum not prioritising for-wraparound tables

On Wed, Jan 30, 2013 at 6:55 AM, Andres Freund <andres(at)2ndquadrant(dot)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