Subject: Re: Performance query about large tables, lots of
	concurrent access
From: Mark Lewis <mark.lewis@mir3.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
In-Reply-To: <4677DC06.3000300@metacarta.com>
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>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Tue, 19 Jun 2007 06:50:30 -0700
Message-Id: <1182261030.8273.264.camel@archimedes>
Mime-Version: 1.0

On Tue, 2007-06-19 at 09:37 -0400, 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.
> 
> I will be experimenting with 8.1 to see how long it takes to complete a 
> vacuum under load conditions tonight.

The longer you wait between vacuuming, the longer each vacuum is going
to take.  

There is of course a point of diminishing returns for vacuum where this
no longer holds true; if you vacuum too frequently the overhead of
running the vacuum will dominate the running time.  But 6 days for a
busy database is probably way, way, way past that threshold.

Generally, the busier the database the more frequently you need to
vacuum, not less.  If your update/delete transaction rate is high enough
then you may need to vacuum multiple times per hour, at least on some
tables.  Playing with autovacuum might help you out here, because it can
look at how badly a vacuum is needed and adjust the vacuuming rate on
the fly on a per-table basis.  Be sure to look up some reasonable
autovacuum settings first; the 8.1 defaults aren't.

-- Mark