Re: Visibility map and freezing

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com