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
