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(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.)

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