Re: Visibility map, partial vacuums

Heikki Linnakangas <heikki.linnakangas@enterprisedb.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
beforethey 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
outevenly over 100M     transactions.
 
   . we could check if another anti-wraparound vacuum is still running and     implicitly add a vacuum_freeze_min_age
penaltyto the     autovacuum_max_freeze_age for each running anti-wraparound vacuum. That     would spread them out
withoutbeing 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
thefirst 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
itseem 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
valueswhich results in their  vacuums never completing. Actually I think what we should do is junk all  the existing
parametersand replace it with a vacuum_nice_level or  vacuum_bandwidth_cap from which we calculate the cost_limit and
hideall  the other parameters as internal parameters.
 

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