Message-ID: <453FD447.1050206@commandprompt.com>
Date: Wed, 25 Oct 2006 14:16:55 -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: Richard Huxton <dev@archonet.com>
CC: Mark Lonsdale <mark.lonsdale@wysdm.com>, pgsql-performance@postgresql.org
Subject: Re: Configuration Issue ?
References: <673ECBB77DA91F47AD4902A5FD73BCE4BA3A94@mail3>
	<453FD351.7000408@archonet.com>
In-Reply-To: <453FD351.7000408@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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