Re: autovacuum not prioritising for-wraparound tables

On Wed, Jan 30, 2013 at 6:55 AM, Andres Freund <andres@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