Message-ID: <46618D36.3010901@kaltenbrunner.cc>
Date: Sat, 02 Jun 2007 17:31:02 +0200
From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Michael Fuhr <mike@fuhr.org>
CC: Douglas J Hunley <doug@hunley.homeip.net>,
	pgsql-performance@postgresql.org
Subject: Re: upgraded to pgsql 8.2.4, getting worse performance
 then 7.4.x
References: <200706020913.32241.doug@hunley.homeip.net>
	<20070602152141.GA38653@winnie.fuhr.org>
In-Reply-To: <20070602152141.GA38653@winnie.fuhr.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Michael Fuhr wrote:
> On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
>> Our 'esteemed' Engr group recently informed a customer that in their testing, 
>> upgrading to 8.2.x improved the performance of our J2EE 
>> application "approximately 20%", so of course, the customer then tasked me 
>> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 
>> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's 
>> been about a week now, and the customer is complaining that in their testing, 
>> they are seeing a 30% /decrease/ in general performance.
> 
> After the restore, did you ANALYZE the entire database to update
> the planner's statistics?  Have you enabled autovacuum or are you
> otherwise vacuuming and analyzing regularly?  What kind of queries
> are slower than desired?  If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
> 
> A few differences between the configuration files stand out.  The
> 7.4 file has the following settings:
> 
>   shared_buffers = 25000
>   sort_mem = 15000
>   effective_cache_size = 196608
> 
> The 8.2 config has:
> 
>   #shared_buffers = 32MB
>   #work_mem = 1MB
>   #effective_cache_size = 128MB
> 
> To be equivalent to the 7.4 config the 8.2 config would need:
> 
>   shared_buffers = 195MB
>   work_mem = 15000kB
>   effective_cache_size = 1536MB
> 
> With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
> (less if the entire database isn't that big) and effective_cache_size
> to 5GB - 6GB.  You might have to increase the kernel's shared memory
> settings before increasing shared_buffers.

some testing here has shown that while it is usually a good idea to set
effective_cache_size rather optimistically in versions <8.2 it is
advisable to make it accurate or even a bit less than that in 8.2 and up.


Stefan