Date: Tue, 19 Jun 2007 09:46:37 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Message-ID: <20070619134637.GG4265@alvh.no-ip.org>
References: <4676DD57.7070304@metacarta.com> <14291.1182221811@sss.pgh.pa.us>
	<4677B831.8060704@metacarta.com> <4677D298.2010109@metacarta.com>
	<20070619132643.GC4265@alvh.no-ip.org>
	<4677DC06.3000300@metacarta.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4677DC06.3000300@metacarta.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

Karl Wright wrote:
> Alvaro Herrera wrote:
> >Karl Wright wrote:
> >
> >>This particular run lasted four days before a VACUUM became essential. 
> >>The symptom that indicates that VACUUM is needed seems to be that the 
> >>CPU usage of any given postgresql query skyrockets.  Is this essentially 
> >>correct?
> >
> >Are you saying you weren't used to run VACUUM all the time?  If so,
> >that's where the problem lies.
> 
> Postgresql 7.4 VACUUM runs for so long that starting it with a cron job 
> even every 24 hours caused multiple instances of VACUUM to eventually be 
> running in my case.  So I tried to find a VACUUM schedule that permitted 
> each individual vacuum to finish before the next one started.  A vacuum 
> seemed to require 4-5 days with this particular database - or at least 
> it did for 7.4.  So I had the VACUUM schedule set to run every six days.

How large is the database?  I must admit I have never seen a database
that took 4 days to vacuum.  This could mean that your database is
humongous, or that the vacuum strategy is wrong for some reason.

You know that you can run vacuum on particular tables, right?  It would
be probably a good idea to run vacuum on the most updated tables, and
leave alone those that are not or little updated (hopefully the biggest;
this would mean that an almost-complete vacuum run would take much less
than a whole day).

Or maybe vacuum was stuck waiting on a lock somewhere.

> I will be experimenting with 8.1 to see how long it takes to complete a 
> vacuum under load conditions tonight.

You can also turn autovacuum on in 8.1, which might help quite a bit
with finding a good vacuum schedule (you would need a bit of tuning it
though, of course).

In any case, if you are struggling for performance you are strongly
adviced to upgrade to 8.2.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"No single strategy is always right (Unless the boss says so)"
                                                  (Larry Wall)