Re: autovacuum not prioritising for-wraparound tables
От | Robert Haas |
---|---|
Тема | Re: autovacuum not prioritising for-wraparound tables |
Дата | |
Msg-id | CA+TgmobGVjY+GLav=rUwm_wCPcSL-MD1tawnA7Kt06f6Ze-9Xw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: autovacuum not prioritising for-wraparound tables (Andres Freund <andres@2ndquadrant.com>) |
Ответы |
Re: autovacuum not prioritising for-wraparound tables
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: autovacuum not prioritising for-wraparound tables (Jeff Janes <jeff.janes@gmail.com>) |
Список | pgsql-hackers |
On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund <andres@2ndquadrant.com> wrote: >> I think that to do this right, we need to consider not only the status >> quo but the trajectory. For example, suppose we have two tables to >> process, one of which needs a wraparound vacuum and the other one of >> which needs dead tuples removed. If the table needing the wraparound >> vacuum is small and just barely over the threshold, it isn't urgent; >> but if it's large and way over the threshold, it's quite urgent. >> Similarly, if the table which needs dead tuples removed is rarely >> updated, postponing vacuum is not a big deal, but if it's being >> updated like crazy, postponing vacuum is a big problem. Categorically >> putting autovacuum wraparound tables ahead of everything else seems >> simplistic, and thinking that more dead tuples is more urgent than >> fewer dead tuples seems *extremely* simplistic. > > I don't think the first part is problematic. Which scenario do you have > in mind where that would really cause adverse behaviour? autovacuum > seldomly does full table vacuums on tables otherwise these days so > tables get "old" in that sense pretty regularly and mostly uniform. I'm worried about the case of a very, very frequently updated table getting put ahead of a table that needs a wraparound vacuum, but only just. It doesn't sit well with me to think that the priority of that goes from 0 (we don't even try to update it) to infinity (it goes ahead of all tables needing to be vacuumed for dead tuples) the instant we hit the vacuum_freeze_table_age. One idea would be to give each table a "badness". So estimate the percentage of the tuples in each table that are dead. And then we compute the percentage by which age(relfrozenxid) exceeds the table age, and add those two percentages up to get total badness. We process tables that are otherwise-eligible for vacuuming in descending order of badness. So if autovacuum_vacuum_scale_factor = 0.2 and a table is more than than 120% of vacuum_freeze_table_age, then it's certain to be vacuumed before any table that only needs dead-tuple processing. But if it's only slightly past the cutoff, it doesn't get to stomp all over the people who need dead tuples cleaned up. The thing is, avoiding a full-cluster shutdown due to anti-wraparound vacuum is important. But, IME, that rarely happens. What is much more common is that an individual table gets bloated and CLUSTER or VACUUM FULL is required to recover, and now the system is effectively down for as long as that takes to complete. I don't want to make that case substantially more likely just to avoid a danger of full-cluster shutdown that, for most users most of the time, is really a very remote risk. There's some point at which an anti-wraparound vacuum should not only trump everything else, but probably also ignore the configured cost delay settings - but equating that point with the first point at which we consider doing it at all does not seem right to me. > I think a minimal version might be acceptable. Its a bug if the database > regularly shuts down and you need to write manual vacuuming scripts to > prevent it from happening. > > I don't think the argument that the pg_class order might work better > than anything holds that much truth - its not like thats something > really stable. I freely admit that if pg_class order happens to work better, it's just good luck. But sometimes people get lucky. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления: