On 6/25/07, Jim Nasby <decibel@decibel.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