Skip site navigation (1) Skip section navigation (2)

Re: Configuration Issue ?

From: "Mark Lonsdale" <mark(dot)lonsdale(at)wysdm(dot)com>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>,"Richard Huxton" <dev(at)archonet(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Configuration Issue ?
Date: 2006-10-25 21:31:29
Message-ID: 673ECBB77DA91F47AD4902A5FD73BCE4BA3AC6@mail3 (view raw or flat)
Thread:
Lists: pgsql-performance

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


Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2006-10-25 21:42:25
Subject: Re: Configuration Issue ?
Previous:From: Joshua D. DrakeDate: 2006-10-25 21:16:55
Subject: Re: Configuration Issue ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group