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

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 of the other settings are the same between the configurations
but deserve discussion:

fsync = off

Disabling fsync is dangerous -- are all parties aware of the risk
and willing to accept it? Has the risk been weighed against the
cost of upgrading to a faster I/O subsystem? How much performance
benefit are you realizing by disabling fsync? What kind of activity
led to the decision to disable fynsc? Are applications doing
anything like executing large numbers of insert/update/delete
statements outside of a transaction block when they could be done
in a single transaction?

commit_delay = 20000
commit_siblings = 3

What kind of activity led to the above settings? Are they a guess
or were they determined empirically? How much benefit are they
providing and how did you measure that?

enable_mergejoin = off
geqo = off

I've occasionally had to tweak planner settings but I prefer to do
so for specific queries instead of changing them server-wide.

--
Michael Fuhr