Re: Visibility map, partial vacuums - Mailing list pgsql-hackers
From | Gregory Stark |
---|---|
Subject | Re: Visibility map, partial vacuums |
Date | |
Msg-id | 87tz9lqs2a.fsf@oxford.xeocode.com Whole thread Raw |
In response to | Re: Visibility map, partial vacuums (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>) |
Responses |
Re: Visibility map, partial vacuums
Re: Visibility map, partial vacuums Re: Visibility map, partial vacuums |
List | pgsql-hackers |
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!
pgsql-hackers by date: