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
