Re: Proposal: vacuum and autovacuum parameters to control freezing

On Sat, 2006-11-04 at 12:35 -0500, Tom Lane wrote:
> I wrote:
> > It's usually going to be the case that the oldest datvacuumxid is
> > template0's, meaning that it will never be possible to truncate clog
> > until autovacuum decides that template0 is at risk of wraparound and
> > goes and vacuums it. Shortening the freeze horizon will reduce the size
> > that pg_clog occupies just *after* that happens, but we're still going
> > to see pg_clog bloating up to something close to 256MB before autovacuum
> > kicks in.
>
> After further thought I see that there are actually two parameters
> involved in this process:
>
> 1. the critical age (currentXID - relfrozenxid) beyond which autovacuum
> will force a vacuum of a particular table to forestall XID wraparound.
> (Note: as the 8.2 code stands, the system will launch autovacuums even
> when autovac is nominally disabled in order to fix tables that have
> exceeded a hard-wired critical age.)
>
> 2. the freeze distance vacuum (whether auto or normal) uses to determine
> the new cutoff point, ie, the new relfrozenxid for the table.

I guess I don't fully understand the way you've described this, so I'd
like to put my own understanding to see if they both agree. Getting that
right is the key to understanding the proposal more fully (for me) - I
get the bit about 2 parameters...

If we take the current Xid as zero, we can go back in time to various
Xids using a timeline. That timeline can be divided into various Eras,
as with Geologic time (Jurassic, Triassic, Ice Ages etc). With
PostgreSQL, we used to have 5 eras:

1. In Progress Transactions 0 - OldestXmin
<---- limit is OldestXmin
2. Completed, normal Xids, status in clog
<---- limit is oldest Xid in clog (no specific name in code)
3. Completed, normal Xids, status marked on tuple
<---- limit is Freeze distance?
4. Frozen Xids, tuples frozen early to ensure nothing passes Wrap
<---- critical age? - practical last point to forestall Wrap
<---- limit is Wraparound
5. Frozen Xids (PreHistory)

now we have agreed to have only 4 eras, IIRC:

1. In Progress Transactions 0 - OldestXmin
<---- limit is OldestXmin
2. Completed, normal Xids, status in clog
<---- limit is Freeze distance?
3. Frozen Xids, tuples frozen early to ensure nothing passes Wrap
<---- critical age? - practical last point to forestall Wrap
<---- limit is Wraparound
4. Frozen Xids (PreHistory)

Perhaps you could edit the above if needed? I'm not making a separate
proposal, just trying to get it very clear.

If we can explain this simply now, then we stand a chance of other
people understanding it as well and setting these parameters correctly.
Otherwise we'll be doing the explanation hundreds of times on list/IRC.

AFICS freeze_limit and freeze_distance are both expressed in number of
Xids before current, so the "units" are the same for both. In that case,
maybe slightly more differentiated names would be appropriate.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com