Re: autovacuum not prioritising for-wraparound tables

On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund <andres(at)2ndquadrant(dot)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