Thanks guys, I think we'll certainly look to get the app certified with 7.4 and 8.x but that may take a little while. In the interim, Im thinking of making the following changes then:- Change Shared_buffers from 393216 to 80,000 ( ~15% of 4GB of RAM. Server is 8GB but I want to leave space for App as well ) Set my effective_cache_size to 125,000 ( ~25% of 4GB of RAM ) Set my sort_mem to 8192 Do those numbers look a bit better? Will probably see if we can make these changes asap as the server is struggling a bit now, which doesn't really make sense given how much memory is in it. Really appreciate your help and fast turnaround on this Mark -----Original Message----- From: Joshua D. Drake [mailto:jd@xxxxxxxxxxxxxxxxx] Sent: 25 October 2006 22:17 To: Richard Huxton Cc: Mark Lonsdale; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: [PERFORM] Configuration Issue ? Richard Huxton wrote: > Mark Lonsdale wrote: >> >> Hi Josh >> >> Thanks for the feedback, that is most usefull. When you said one of the >> settings was likely killing us, was it all of the settings for >> max_fsm_relations, max_fsm_pages, and sort_mem or just the setting for >> sort_mem ? >> >> Can you explain why the setting would be killing me :-) > > The sort_mem is crucial. It's memory *per sort*, which means one query > can use several times that amount. Worse then that it is: ((sort memory) * (number of sorts)) * (number of connections) = amount of ram possible to use. Now... take the following query: SELECT * FROM foo JOIN bar on (bar.id = foo.id) JOIN baz on (baz.id = foo_baz.id) ORDER BY baz.name, foo.salary; Over 5 million rows... How much ram you think you just used? > >> The long and short is you need to upgrade to at least 7.4, preferrably >> 8.1. > > Joshua means this too. Upgrade to 7.3.16 within the next few days, then > test out something more recent. You should see some useful performance > gains from 8.1. Right. The reason I suggested 7.4 is that he gets VACUUM VERBOSE in a reasonable fashion but of course 8.1 is better. Sincerely, Joshua D. Drake > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
