Re: Visibility map and freezing

On Tue, Dec 23, 2008 at 4:24 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> Heikki Linnakangas wrote:
>>
>> Peter Eisentraut wrote:
>>>
>>> Heikki Linnakangas wrote:
>>>>
>>>> I think we need a threshold similar to autovacuum_freeze_max_age for
>>>> manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
>>>> relfrozenxid is older than vacuum_freeze_max_age, the visibility map is
>>>> ignored and all pages are scanned.
>>>
>>> Would one parameter to control both suffice? (i.e., rename
>>> autovacuum_freeze_max_age to vacuum_freeze_max_age)
>>
>> Imagine that you run a nightly VACUUM from cron, and have autovacuum
>> disabled. If autovacuum_freeze_max_age is the same as vacuum_freeze_max_age,
>> as soon as that age is reached, an anti-wraparound autovacuum launched. What
>> we'd want to happen is for the next nightly VACUUM to do the work instead.
>> So they need to be separate settings, with some space between them by
>> default.
>
> Attached is a proposed patch to handle freezing. In a nutshell:
>
> Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
> whole table and advance relfrozenxid, if relfrozenxid is older than
> vacuum_freeze_max_age.
>
> If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1
> million transactions, it's effectively capped at that value. It doesn't make
> sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age, because the
> manual VACUUM wouldn't have a chance to do the full sweep before the
> anti-wraparound autovacuum is launched. The "minus one million transactions"
> is to give some headroom.
>
> I'm lowering vacuum_freeze_min_age from 100 million transactions to 50
> million, so that the whole-table vacuum doesn't need to run as often. Note
> that since VACUUM normally only scans pages that need vacuuming according to
> the visibility map, tuples on skipped pages are not frozen any earlier even
> though vacuum_freeze_min_age is lower.
>
> To recap, here's the new defaults:
> autovacuum_freeze_max_age 200000000
> vacuum_freeze_max_age 150000000
> vacuum_freeze_min_age 50000000
>
> This means that with defaults, autovacuum will launch a whole-table vacuum
> every 150 million transactions (autovacuum_freeze_max_age -
> vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a
> whole-table vacuum every 100 million transactions.
>
> vacuum_freeze_max_age also affects autovacuums. If an autovacuum is launched
> on table to remove dead tuples, and vacuum_freeze_max_age has been reached
> (but not yet autovacuum_freeze_max_age), the autovacuum will scan the whole
> table. I'm not sure if this is desirable, to avoid having to launch separate
> anti-wraparound autovacuums even when there's not many dead tuples, or just
> confusing.
>
> If you set vacuum_freeze_max_age to 0, the visibility map is not used to
> skip pages, so you'll get the pre-8.4 old behavior.

It seems to be strange that "max" can be less than "min". Is it worth dividing
a parameter into two(min/max)?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center