Re: Visibility map, partial vacuums

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:

> Hmm. It just occurred to me that I think this circumvented the anti-wraparound
> vacuuming: a normal vacuum doesn't advance relfrozenxid anymore. We'll need to
> disable the skipping when autovacuum is triggered to prevent wraparound. VACUUM
> FREEZE does that already, but it's unnecessarily aggressive in freezing.

Having seen how the anti-wraparound vacuums work in the field I think merely
replacing it with a regular vacuum which covers the whole table will not
actually work well.

What will happen is that, because nothing else is advancing the relfrozenxid,
the age of the relfrozenxid for all tables will advance until they all hit
autovacuum_max_freeze_age. Quite often all the tables were created around the
same time so they will all hit autovacuum_max_freeze_age at the same time.

So a database which was operating fine and receiving regular vacuums at a
reasonable pace will suddenly be hit by vacuums for every table all at the
same time, 3 at a time. If you don't have vacuum_cost_delay set that will
cause a major issue. Even if you do have vacuum_cost_delay set it will prevent
the small busy tables from getting vacuumed regularly due to the backlog in
anti-wraparound vacuums.

Worse, vacuum will set the freeze_xid to nearly the same value for all of the
tables. So it will all happen again in another 100M transactions. And again in
another 100M transactions, and again...

I think there are several things which need to happen here.

1) Raise autovacuum_max_freeze_age to 400M or 800M. Having it at 200M just
means unnecessary full table vacuums long before they accomplish anything.

2) Include a factor which spreads out the anti-wraparound freezes in the
autovacuum launcher. Some ideas:

. we could implicitly add random(vacuum_freeze_min_age) to the
autovacuum_max_freeze_age. That would spread them out evenly over 100M
transactions.

. we could check if another anti-wraparound vacuum is still running and
implicitly add a vacuum_freeze_min_age penalty to the
autovacuum_max_freeze_age for each running anti-wraparound vacuum. That
would spread them out without being introducing non-determinism which
seems better.

. we could leave autovacuum_max_freeze_age and instead pick a semi-random
vacuum_freeze_min_age. This would mean the first set of anti-wraparound
vacuums would still be synchronized but subsequent ones might be spread
out somewhat. There's not as much room to randomize this though and it
would affect how much i/o vacuum did which makes it seem less palatable
to me.

3) I also think we need to put a clamp on the vacuum_cost_delay. Too many
people are setting it to unreasonably high values which results in their
vacuums never completing. Actually I think what we should do is junk all
the existing parameters and replace it with a vacuum_nice_level or
vacuum_bandwidth_cap from which we calculate the cost_limit and hide all
the other parameters as internal parameters.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!