From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
To: Michael Fuhr <mike@fuhr.org>
Cc: Douglas J Hunley <doug@hunley.homeip.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
Date: Sat, 02 Jun 2007 17:31:02 +0200
Message-ID: <46618D36.3010901@kaltenbrunner.cc> (raw)
In-Reply-To: <20070602152141.GA38653@winnie.fuhr.org>
References: <200706020913.32241.doug@hunley.homeip.net>
<20070602152141.GA38653@winnie.fuhr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stefan@kaltenbrunner.cc, mike@fuhr.org, doug@hunley.homeip.net
Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
In-Reply-To: <46618D36.3010901@kaltenbrunner.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox