Subject: Re: DB is slow until DB is reloaded
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Madison Kelly <linux@alteeve.com>
Cc: Gary Doades <gpd@gpdnet.co.uk>, pgsql-performance@postgresql.org
In-Reply-To: <4B425555.6020709@alteeve.com>
References: <4B423D22.7040803@alteeve.com>
	<4B424215.70605@pinpointresearch.com> <4B424FF6.40207@alteeve.com>
	<4B425222.7070905@gpdnet.co.uk>  <4B425555.6020709@alteeve.com>
Content-Type: text/plain
Date: Mon, 04 Jan 2010 16:00:56 -0500
Message-Id: <1262638856.29852.4.camel@bnicholson-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Mon, 2010-01-04 at 15:53 -0500, Madison Kelly wrote:
> Gary Doades wrote:
> >  From your queries it definitely looks like its your stats that are the 
> > problem. When the stats get well out of date the planner is choosing a 
> > hash join because it thinks thousands of rows are involved where as only 
> > a few are actually involved. Thats why, with better stats, the second 
> > query is using a loop join over very few rows and running much quicker.
> > 
> > Therefore it's ANALYZE you need to run as well as regular VACUUMing. 
> > There should be no need to VACUUM FULL at all as long as you VACUUM and 
> > ANALYZE regularly. Once a day may be enough, but you don't say how long 
> > it takes your database to become "slow".
> > 
> > You can VACUUM either the whole database (often easiest) or individual 
> > tables if you know in more detail what the problem is and that only 
> > certain tables need it.
> > 
> > Setting up autovacuum may well be sufficient.
> > 
> > Cheers,
> > Gary.
> 
> That explains things, thank you!
> 
> For the record; It was taking a few months for the performance to become 
> intolerable. I've added CLUSTER -> ANALYZE -> VACUUM to my nightly 
> routine and dropped the VACUUM FULL call. I'll see how this works.

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.

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.