Re: Visibility map, partial vacuums - Mailing list pgsql-hackers

Would someone tell me why 'autovacuum_freeze_max_age' defaults to 200M
when our wraparound limit is around 2B?

Also, is anything being done about the concern about 'vacuum storm'
explained below?

---------------------------------------------------------------------------

Gregory Stark wrote:
> 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 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!
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +