Re: Visibility map, partial vacuums
От | Magnus Hagander |
---|---|
Тема | Re: Visibility map, partial vacuums |
Дата | |
Msg-id | 4936939C.9080001@hagander.net обсуждение исходный текст |
Ответ на | Re: Visibility map, partial vacuums (Gregory Stark <stark@enterprisedb.com>) |
Список | pgsql-hackers |
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. How about a way to say that only one (or a config parameter for <n>) of the autovac workers can be used for anti-wraparound vacuum? Then the other slots would still be available for the small-but-frequently-updated tables. > 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. It would certainly be helpful if it was just a single parameter - the arbitraryness of the parameters there now make them pretty hard to set properly - or at least easy to set wrong. //Magnus
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: snapshot leak and core dump with serializable transactions