Re: autovacuum not prioritising for-wraparound tables

On Sat, Feb 2, 2013 at 5:25 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
>> 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.
>
> Oh, wow. Youre right. I shouldn't answer emails after sport with cramped
> fingers on a friday night... And I should have thought about this
> scenario, because I essentially already explained it upthread, just with
> a different set of variables.
>
> This is rather scary. How come nobody noticed that this major
> performance improvement was effectively disabled for that long?

I'm not sure whom to address this to, but the just-committed release
notes for this issue reflect the original understanding that it only
applied when vacuum_freeze_min_age was lowered from its default.
Rather than the current understanding that it effects all old-enough
systems.

If the release notes are not already baked in, I would suggest this wording:

+ The main consequence of this mistake is that it
+ caused full-table vacuuming scans to occur much more frequently
+ than intended.

Cheers,

Jeff