Re: Database-wide VACUUM ANALYZE

On Jun 21, 2007, at 3:37 PM, Steven Flatt wrote:
> Thanks everyone. It appears that we had hacked the 502.pgsql
> script for our 8.1 build to disable the daily vacuum. I was not
> aware of this when building and upgrading to 8.2.

Much better to change stuff in a config file than to hack installed
scripts, for this very reason. :)

> So it looks like for the past two weeks, that 36 hour db-wide
> vacuum has been running every 24 hours. Good for it for being
> reasonably non-intrusive and going unnoticed until now. :)
>
> Although apparently not related anymore, I still think it was a
> good move to change autovacuum_freeze_max_age from 200 million to 2
> billion.

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.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)