Re: autovacuum not prioritising for-wraparound tables

On Fri, Feb 1, 2013 at 2:34 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:

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

Right. Since freeze_min_age was mistakenly being used, the limit
would be 50 million in the past (rather than 150 million) under
defaults. But since the last full-table vacuum, whenever that was,
used freeze_min_age for its intended purpose, that means the 50
million in the past *at the time of that last vacuum* is the highest
that relfrozenxid can be. And that is going to be further back than
50 million from right now, so the vacuum will always be promoted to a
full scan.

I am not entirely sure of my logic above[1], but I'm depending on
empirical observation for my conclusion. The attached patch emits a
log entry telling if scan_all is being used, and it always is used
(under the bug) once the database gets old enough. Or at least, I've
never seen it not use scan_all after that point.

As an aside, it does seem like log_autovacuum_min_duration=0 should
log whether a scan_all was done, and if so what relfrozenxid got set
to. But looking at where the log message is generated, I don't know
where to retrieve that info.

[1] I don't know why it is that a scan_all vacuum with a
freeze_min_age of 50m (or a freezeLimit of 50 million ago) will not
set relfrozenxid to a higher value than that if it discovers that it
can, but it doesn't seem to.

Cheers,

Jeff