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. We can make a few observations: * For a table that otherwise goes unvacuumed, the interval between forced anti-wraparound vacuums will be critical_age - freeze_distance. Therefore, for large static tables there is value in being able to adjust this difference to be as large as possible. * The size of pg_clog is determined by the system-wide maximum of critical_age + number-of-transactions-needed-to-finish-vacuuming. Therefore, critical_age is the knob we must expose if we want to provide user control of pg_clog growth. * It might seem that there's no point in per-table adjustment of critical_age, since only the system-wide maximum means anything for resource consumption. I'm not so sure though --- for a really large table, the time needed to finish vacuuming it could be significant, meaning it would need a lower critical age than other tables. With the current one-process-at-a-time autovac infrastructure, this probably isn't very important, but we've been talking about allowing multiple parallel autovacuums specifically to deal with the problem of some tables being much larger than others. So it seems to me that an argument can be made for creating two new GUC variables and adding two columns to pg_autovacuum: vacuum_freeze_distance: number of transactions back from current that a VACUUM will use as the freeze cutoff point, ie, XIDs older than that will be replaced by FrozenXID, and the cutoff point will become the table's new relfrozenxid value. Valid range zero to perhaps 1 billion. VACUUM FREEZE is a shorthand for doing a vacuum with vacuum_freeze_distance = 0. autovacuum_freeze_limit: maximum age of relfrozenxid before autovacuum will force a vacuum for anti-wraparound purposes. Valid range perhaps 100 million to (2 billion - 100 million). pg_autovacuum.freeze_distance: per-table vacuum_freeze_distance setting for autovacuum to use. pg_autovacuum.freeze_limit: per-table autovacuum_freeze_limit for autovacuum to use. (I'm not wedded to these names, anyone have better ideas?) I'd propose default values of 200 million for autovacuum_freeze_limit and half that for vacuum_freeze_distance, resulting in a maximum pg_clog size of 50MB and forced autovacs about every 100 million transactions. One minor point is that while the values of these variables have to have sane relationships to each other, the GUC infrastructure doesn't really allow us to enforce such a constraint directly (the behavior would be too dependent on which variable got set first). I'd suggest making vacuum just silently limit the effective freeze_distance to not more than half of the system's autovacuum_freeze_limit, rather than trying to enforce any relationship within GUC. This is kind of a lot to be inventing in late beta, but if we want to have a really credible solution to the WAL-versus-freezing problem I think we need to do all of this. Comments? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match