Re: autovacuum not prioritising for-wraparound tables

On Mon, Jan 28, 2013 at 8:11 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> So I think we need to sort by age(relfrozenxid) in tables that are over
>> the anti-wraparound limit. Given your code that doesn't seem to be that
>> hard?
>
> I might also suggest that we think about changing the defaults for
> wraparound vacuum behavior. Partcularly, the fact that
> vacuum_freeze_min_age is 50% of autovacuum_freeze_max_age by default is
> optimal for absolutely nobody, and forces re-wraparound vacuuming of
> wraparound tables which were just recently wraparound-vacuumed. We
> should lower vacuum_freeze_min_age to something sane, like 1000000.

Currently, the defaults are 50m for vacuum_freeze_min_age, 150m for
vacuum_freeze_table_age, and 200m for autovacuum_freeze_max_age, which
doesn't seem like it matches what you're saying.
vacuum_freeze_min_age doesn't force anti-wraparound vacuuming anyway;
it just controls the tuple age at which opportunistic freezing starts.
There's possibly some advantage to lowering it anyway, in the hopes
of leaving less work for the eventual full-table scan to do, but
dropping it to as low as 1m seems like it might be too much of a good
thing - we might end up freezing a lot of tuples that would have
gotten removed before they hit the 50m freeze threshold we have today.

While we're talking about prioritizing vacuum, though, it would be
nice to have a high-priority anti-wraparound vacuum and a low-priority
anti-wraparound vacuum. When a table crosses the "low priority"
threshold, we work on freezing it *if there is no other vacuuming that
needs doing* - i.e. we're the only autovacuum process running, and no
other table needs autovacuuming either for dead tuples or for
wraparound. When it crosses the "high priority" threshold, then we
behave as now, or perhaps even more aggressively (move to front of
queue, raise or cost delay or ignore it outright, etc.). The "low
priority" anti-wraparound vacuum would abort if some other vacuuming
came to be required, if a lock conflict occurred, etc., and might also
run with a higher cost delay. I believe this would tend to spread the
anti-wraparound work out over a longer period of time, instead of
clumping it all together as often happens today, and reduce the effect
it has on foreground activities. It might not be going far enough in
that direction but at least it would be a start.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company