Message-ID: <4E6E5605.20803@squeakycode.net>
Date: Mon, 12 Sep 2011 13:57:09 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Robert Schnabel <schnabelr@missouri.edu>
CC: "sthomas@peak6.com" <sthomas@peak6.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Allow sorts to use more available memory
References: <4E6E426D.7060405@missouri.edu> <4E6E45CA.20606@squeakycode.net>
	<4E6E4826.8050002@peak6.com> <4E6E4DFB.2070605@missouri.edu>
In-Reply-To: <4E6E4DFB.2070605@missouri.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 9/12/2011 1:22 PM, Robert Schnabel wrote:
>
> On 9/12/2011 12:57 PM, Shaun Thomas wrote:
>> On 09/12/2011 12:47 PM, Andy Colson wrote:
>>
>>> work_mem is not the total a query can use. I believe each step can
>>> use that much, and each backend can use it for multiple bits. So if
>>> you had two backends, each doing 2 sorts, you'd use 2*2 = 4 * 2GB =
>>> 8GB.
>> Exactly. Find a big query somewhere in your system. Use EXPLAIN to
>> examine it. Chances are, that one query has one or more sorts. Each one
>> of those gets its own work_mem. Each sort. The query have four sorts? It
>> may use 4*work_mem. On a whim a while back, I doubled our 8MB setting to
>> 16MB on a test system. During a load test, the machine ran out of
>> memory, swapped out, and finally crashed after the OOM killer went nuts.
>>
>> Set this value *at your own risk* and only after *significant* testing.
>> Having it too high can have rather unexpected consequences. Setting it
>> to 1 or 2GB, unless you have VERY few threads, or a TON of memory, is a
>> very, very bad idea.
>>
> Yep, I know. But in the context of the data warehouse where *I'm the
> only user* and I have a query that does, say 4 large sorts like
> http://explain.depesz.com/s/BrAO and I have 32GB RAM I'm not worried
> about using 8GB or 16GB in the case of work_mem = 4GB. I realize the
> query above only used 1.9GB for the largest sort but I know I have other
> queries with 1 or 2 sorts that I've watched go to disk.
>
> Bob
>
>
>
>

Huge guess here, cant see select or ddl, but looks like all the tables 
are sequential scans.  It might help to add an index or two, then the 
table joins could be done much more efficiently with with a lot less 
memory.

-Andy