Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

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