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:
> Hello great gurus of performance:
> 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. Of course, our Engr
> group is being less than responsive, and I have a feeling all they're doing
> is googling for answers, so I'm turning to this group for actual
> assistance :)
> I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had
> spent the better part of their 2 years as a customer tuning and tweaking
> setting. I've attached the file that was in place at the time of upgrade. I
> did some cursory googling of my own, and quickly realized that enough has
> changed in v8 that I'm not comfortable making the exact same modification to
> their new config file as some options are new, some have gone away, etc. I've
> attached the existing v8 conf file as well.
> I'd really like it if someone could assist me in determining which of the v8
> options need adjusted to be 'functionally equivalent' to the v7 file. Right
> now, my goal is to get the customer back to the previous level of
> performance, and only then pursue further optimization. I can provide any and
> all information needed, but didn't know what to include initially, so I've
> opted to include the minimal :)
> The DB server in question does nothing else, is running CentOS 4.5, kernel
> 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
> 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
>
> Thank you in advance for any and all assistance you can provide.
> --
> Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
> http://doug.hunley.homeip.net
>

Douglas,

If these are the current config files, it is no wonder that the performance
is worse. Here are the things that need to be changed right from the start.
The old 7.x is on the left and the 8.2 value is on the right. Make them
the same to start and see how it looks then.

setting           7.x         current 8.2
------------------------------------------------------
shared_buffers = 25000 / 32MB (=3906)
sort_mem/work_mem = 15000/ 1MB (=122)
vacuum_mem/maint_work_mem = 100000 / 16MB (=1950)
effective_cache = 196608 / 128MB (=15600) should start between 200k-500k

These changes alone should get you back to the performance point you are
expecting. It would also be worth re-evaluating whether or not you should
be disabling enable_mergehashjoin in general, and not just for specific
problem queries. I would also tend to start with an effective_cache at
the higher end on a dedicated DB server. Good luck with your tuning. If
the 8.2 config file you posted is the one that has been in use, these few
changes will restore your performance and then some.

Ken