Re: autovacuum not prioritising for-wraparound tables

On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
> 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.)

I think you're misreading the code. freezeTableLimit is calculated by
> >                 limit = ReadNewTransactionId() - freezetable;
which is always relative to the current xid. The bug was that
freezetable had the wrong value in autovac due to freeze_min_age being
used instead of freeze_table_age.


Greetings,

Andres Freund

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