Re: Visibility map, partial vacuums

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +