Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 956601FBF225
	for <pgsql-performance@postgresql.org>;
	Tue, 27 Dec 2011 12:15:10 -0400 (AST)
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andrew@dunslane.net>) id 1RfZgB-0005oI-UY
	for pgsql-performance@postgresql.org; Tue, 27 Dec 2011 16:15:10 +0000
X-Originating-IP: 217.70.178.129
Received: from mfilter12-d.gandi.net (mfilter12-d.gandi.net [217.70.178.129])
	by relay4-d.mail.gandi.net (Postfix) with ESMTP id 76347172071;
	Tue, 27 Dec 2011 17:14:45 +0100 (CET)
X-Virus-Scanned: Debian amavisd-new at mfilter12-d.gandi.net
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by mfilter12-d.gandi.net (mfilter12-d.gandi.net [10.0.15.180])
	(amavisd-new, port 10024)
	with ESMTP id OrDw8Y3CH5D9; Tue, 27 Dec 2011 17:14:44 +0100 (CET)
X-Originating-IP: 98.27.54.176
Received: from [192.168.10.146] (cpe-098-027-054-176.nc.res.rr.com
	[98.27.54.176]) (Authenticated sender: adsend@dunslane.net)
	by relay4-d.mail.gandi.net (Postfix) with ESMTPSA id 60F4D17204B;
	Tue, 27 Dec 2011 17:14:41 +0100 (CET)
Message-ID: <4EF9EEF0.1070207@dunslane.net>
Date: Tue, 27 Dec 2011 11:14:40 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:8.0) Gecko/20111115 Thunderbird/8.0
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: Claudio Freire <klaussfreire@gmail.com>,
	Michael Smolsky <sitrash@email.com>, pgsql-performance@postgresql.org
Subject: Re: Exploring memory usage
References: <20111224192239.284500@gmx.com>
	<CAGTBQpZtS-2cTWV9meyimTtbzm-UkPAaeaM2vyt=h-GO-9-6nQ@mail.gmail.com>
	<CAOR=d=3z0BaSQjD0oZYemDScvnD1qLvYq5FU8ruDSbQq=Reu-Q@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=3z0BaSQjD0oZYemDScvnD1qLvYq5FU8ruDSbQq=Reu-Q@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201112/177
X-Sequence-Number: 45976



On 12/27/2011 11:00 AM, Scott Marlowe wrote:
> On Tue, Dec 27, 2011 at 8:33 AM, Claudio Freire<klaussfreire@gmail.com>  wrote:
>> On Sat, Dec 24, 2011 at 4:22 PM, Michael Smolsky<sitrash@email.com>  wrote:
>>> work_mem = 128MB (tried 257MB, didn't change anything)
>> This is probably your problem.
>>
>> Without an EXPLAIN output, I cannot be sure, but 'work_mem' is not the
>> total amount of memory a query can use, it's the amount of memory it
>> can use for *one* sort/hash/whatever operation. A complex query can
>> have many of those, so your machine is probably swapping due to
>> excessive memory requirements.
>>
>> Try *lowering* it. You can do so only for that query, by executing:
>>
>> set work_mem = '8MB';<your query>
> He can lower it for just that query but honestly, even on a machine
> with much more memory I'd never set it as high as he has it.  On a
> busy machine with 128G RAM the max I ever had it set to was 16M, and
> that was high enough I kept a close eye on it (well, nagios did
> anway.)



It depends on the workload. Your 16M setting would make many of my 
clients' systems slow to an absolute crawl for some queries, and they 
don't run into swap issues, because we've made educated guesses about 
usage patterns.

cheers

andrew