Re: Database-wide VACUUM ANALYZE

On 6/25/07, Jim Nasby <decibel(at)decibel(dot)org> wrote:
>
> If you set that to 2B, that means you're 2^31-"2 billion"-1000000
> transactions away from a shutdown when autovac finally gets around to
> trying to run a wraparound vacuum on a table. If you have any number
> of large tables, that could be a big problem, as autovac could get
> tied up on a large table for a long enough period that the table
> needing to be frozen doesn't get frozen in time.
>
> I suspect 1B is a much better setting. I probably wouldn't go past 1.5B.

From my understanding of the docs, for tables that are not otherwise
vacuumed, autovac will be invoked on it once every autovacuum_freeze_max_age
minus vacuum_freeze_min_age transactions. In our case that's 2 billion -
100 million = 1.9 billion transactions. So when an autovac finally kicks
off on an otherwise non-vacuumed table, we are (2^31 - 1.9 billion) - 1
million =~ 250 million transactions away from shutdown. (I guess that's
close to what you were saying.)

Most of our large (partitioned) tables are insert-only (truncated
eventually) so will not be touched by autovacuum until wraparound prevention
kicks in. However the tables are partitioned by timestamp so tables will
cross the 1.9 billion marker at different times (some not at all, as the
data will have been truncated).

Do you still think the 250 million transactions away from shutdown is
cutting it too close? Recall that the unintentional db-wide vacuum analyze
that was going on last week on our system took less than two days to
complete.

Steve