Re: autovacuum not prioritising for-wraparound tables

On 2013-01-30 14:58:24 +0100, Andres Freund wrote:
> > So reducing vacuum_freeze_min_age not only helps minimize the
> > writes that are needed when autovacuum needs to scan the entire
> > heap, but also decreases the frequency of those full-table scans.
>
> But it increases the amount of pages that are written out multiple times
> because they contain tuples of different ages, in contrast to increasing
> vacuum_freeze_table_age which doesn't have that problem. In combination
> with full_page_writes that makes a noticeable different in total write
> volume.

Btw, as far as I read the code that behaviour only exists insofar that
the last time vacuum runs it freezes all tuples below freeze_min_age but
not newer ones, so relfrozenxid will only be set to current_xmin -
freeze_min_age. But if you manually freeze or no such old tuples exist
its solely influenced by freeze_table_age.

The relevant parts of the code are:

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.

It seems to be broken since the initial introduction of freeze_table_age
in 6587818542e79012276dcfedb2f97e3522ee5e9b. I guess it wasn't noticed
because the behaviour is only visible via autovacuum because a
user-issued VACUUM passes -1 as freeze_min_age.

Trivial patch attached.

Greetings,

Andres Freund

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