Skip site navigation (1) Skip section navigation (2)

Re: Database-wide VACUUM ANALYZE

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Steven Flatt <steven(dot)flatt(at)gmail(dot)com>
Cc: "Bill Moran" <wmoran(at)collaborativefusion(dot)com>, "Francisco Reyes" <lists(at)stringsutils(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Database-wide VACUUM ANALYZE
Date: 2007-06-26 00:00:20
Message-ID: FFD6DF28-B30D-47D0-BFB5-6485B32BF8AC@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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)



In response to

Responses

pgsql-performance by date

Next:From: Ed TyrrillDate: 2007-06-26 00:09:58
Subject: Re:
Previous:From: Stephen FrostDate: 2007-06-25 23:52:09
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group