Message-ID: <4B425E93.1050207@alteeve.com>
Date: Mon, 04 Jan 2010 16:33:07 -0500
From: Madison Kelly <linux@alteeve.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Brad Nicholson <bnichols@ca.afilias.info>
Cc: Gary Doades <gpd@gpdnet.co.uk>, pgsql-performance@postgresql.org
Subject: Re: DB is slow until DB is reloaded
References: <4B423D22.7040803@alteeve.com>	
	<4B424215.70605@pinpointresearch.com>
	<4B424FF6.40207@alteeve.com>	 <4B425222.7070905@gpdnet.co.uk>
	<4B425555.6020709@alteeve.com>
	<1262638856.29852.4.camel@bnicholson-desktop>
In-Reply-To: <1262638856.29852.4.camel@bnicholson-desktop>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Brad Nicholson wrote:
> I think you are going down the wrong route here - you should be looking
> at preventative maintenance instead of fixing it after its broken.
> 
> Ensure that autovacuum is running for the database (assuming that you
> are on a relatively modern version of PG), and possibly tune it to be
> more aggressive (we can help).
> 
> This will ensure that the condition never comes up.
> 
> ps - if you do go with the route specify, no need to VACUUM after the
> CLUSTER.  CLUSTER gets rid of the dead tuples - nothing for VACUUM to
> do.
> 

   I wanted to get ahead of the problem, hence my question here. :) I've 
set this to run at night ('iwt' being the DB in question):

su postgres -c "psql iwt -c \"VACUUM ANALYZE VERBOSE\"

   I will keep an eye on the output for a little while (it appends to a 
log) and see what it says. Also, I read that CLUSTER can mess up back 
ups as it makes tables look empty while running. If the above doesn't 
seem to help, I will swap out the VACUUM and run a CLUSTER before the 
ANALYZE and see how that works.

Madi