Re: autovacuum not prioritising for-wraparound tables

On 2013-01-28 08:15:29 -0800, Kevin Grittner wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2013-01-29 00:11:12 +1100, Josh Berkus 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.
> >
> > I have to admit, I fail to see why this is a good idea. There
> > isn't much of an efficiency bonus in freezing early (due to hint
> > bits) and vacuums over vacuum_freeze_table_age are considerably
> > more expensive as they have to scan the whole heap instead of
> > using the visibilitymap. And if you don't vacuum the whole heap
> > you can't lower relfrozenxid. So changing freeze_min_age doesn't
> > help at all to avoid anti-wraparound vacuums.
> >
> > Am I missing something?
>
> IMO, anything which changes an anti-wraparound vacuum of a
> bulk-loaded table from "read the entire table and rewrite nearly
> the complete table with WAL-logging" to rewriting a smaller portion
> of the table with WAL-logging is an improvement.

Yes, but the proposed changes make that *more* frequent, not less. 1mio
transactions will usually be *after* the next checkpoint and thus be
written twice.

Imnsho the solution to actually solve that problem is to have have
'freeze map' that marks blocks (say 16MB) of tables as already frozen so
it doesn't have to be reread/written every by (auto-)vacuum at all.
I would like to work on that sometime in the future, but it won't be all
that soon...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services