Message-ID: <4F501F9A.4080905@dunslane.net>
Date: Thu, 01 Mar 2012 20:17:14 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0) Gecko/20120131 Thunderbird/10.0
MIME-Version: 1.0
To: Claudio Freire <klaussfreire@gmail.com>
CC: Peter van Hardenberg <pvh@pvh.ca>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: PG as in-memory db? How to warm up and re-populate
	buffers? How to read in all tuples into memory?
References: 
 <CAFcOn28A3dkmCEGsu-7Chi0gH1qAPYcfbSp8op9gswFawS6ECA@mail.gmail.com>
	<4F4A4DD6.6090904@squeakycode.net>
	<CAFcOn2-n=fCmJEwB81V-SDo00RzTPrQbrh=GAgxoE1DWLqhtDw@mail.gmail.com>
	<4F4A861A.3000301@squeakycode.net>
	<CAFcOn28oCqoNJFVL8wdc6+QcMNuzLC5YuoamCJGF5npe8JT+WA@mail.gmail.com>
	<CAMkU=1wJ7ciYC_qX1es8oGQSc08jM7Vpvg52ahxU39ao93EC0w@mail.gmail.com>
	<CAFcOn29S7KMe+0qQY459OoGGZUEuF=zo4NPFw8=1CW=LB2yEGA@mail.gmail.com>
	<CAGTBQpY16=6GLsQG=O2FFh4zfBKof6u9r=C94YA0Em6vPL76zg@mail.gmail.com>
	<CAFcOn2-b2ZAi-Zy7v29sqfL+4s5mp8Zpnq1UpHoORQ7ctikNTA@mail.gmail.com>
	<CAMkU=1zUHzWzCWJWiUkq+rhC_yZuK8h6FaTC-wjPRPgZXTfzNA@mail.gmail.com>
	<CAFcOn2-bhKg3hbLMUDK1=_9FookxP6xbqeubzO9uwcP=dqnwyw@mail.gmail.com>
	<4F500163.9090200@dunslane.net>
	<CAGTBQpacHJrTKO9X3qC5_1q2iGz1g7LAc3JvMqMoL5WrZguF3w@mail.gmail.com>
	<CAAcg=kXps0_151vhQe4RYX++DUX9Z0TGVev-Gri19dZmDmnb9g@mail.gmail.com>
	<CAGTBQpbANDnewEMVBoFN0ZNtm6wvTUFH1nq6PdiQ7fnRMRjM1g@mail.gmail.com>
In-Reply-To: 
 <CAGTBQpbANDnewEMVBoFN0ZNtm6wvTUFH1nq6PdiQ7fnRMRjM1g@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


On 03/01/2012 07:58 PM, Claudio Freire wrote:
> On Thu, Mar 1, 2012 at 9:28 PM, Peter van Hardenberg<pvh@pvh.ca>  wrote:
>>> Setting work_mem to hundreds of MB in a 4G system is suicide. Tens
>>> even is dangerous.
>>>
>> Why do you say that? We've had work_mem happily at 100MB for years. Is
>> there a particular degenerate case you're concerned about?
> Me too.
>
> But I've analyzed the queries I'll be sending to the database and I've
> carefully bound the effective amount of memory used given the load
> I'll be experiencing.
>
> Saying that it should be set to 100M without consideration for those
> matters is the suicide part. work_mem applies to each sort operation.
> Suppose, just for the sake of argument, that each connection is
> performing 5 such sorts (ie, 5 joins of big tables - not unthinkable),
> then suppose you have your max_connections to the default of 100, then
> the system could request as much as 50G of ram.
>
> I set work_mem higher in my database system since I *know* most of the
> connections will not perform any merge or hash joins, nor will they
> sort the output, so they won't use work_mem even once. The ones that
> will, I have limited on the application side to a handful, hence I
> *know* that 50G theoretical maximum will not be reached.
>
> Can the OP say that? I have no reason to think so. Hence I don't
> suggest 100M is OK on a 4G system.

Well, obviously you need to know your workload. Nobody said otherwise.

cheers

andrew