Message-ID: <453FDA41.8030605@commandprompt.com>
Date: Wed, 25 Oct 2006 14:42:25 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 1.5.0.7 (X11/20060918)
MIME-Version: 1.0
To: Mark Lonsdale <mark.lonsdale@wysdm.com>
CC: Richard Huxton <dev@archonet.com>, pgsql-performance@postgresql.org
Subject: Re: Configuration Issue ?
References: <673ECBB77DA91F47AD4902A5FD73BCE4BA3AC6@mail3>
In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4BA3AC6@mail3>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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@commandprompt.com] 
> Sent: 25 October 2006 22:17
> To: Richard Huxton
> Cc: Mark Lonsdale; pgsql-performance@postgresql.org
> 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