Mark Lonsdale wrote: > > 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 ) You likely run into issues with anything over 16384. I have never seen a benefit from shared_buffers over 12k or so with 7.3. > > Set my effective_cache_size to 125,000 ( ~25% of 4GB of RAM ) > > Set my sort_mem to 8192 :) Sincerely, Joshua D. Drake > > 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
