pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Andrew Dunstan <andrew@dunslane.net>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Peter van Hardenberg <pvh@pvh.ca>
Cc: 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?
Date: Thu, 01 Mar 2012 20:17:14 -0500
Message-ID: <4F501F9A.4080905@dunslane.net> (raw)
In-Reply-To: <CAGTBQpbANDnewEMVBoFN0ZNtm6wvTUFH1nq6PdiQ7fnRMRjM1g@mail.gmail.com>
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>
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
view thread (32+ messages)
Message-ID: <4F501F9A.4080905@dunslane.net> Permalink: ../4F501F9A.4080905@dunslane.net/ Also on: postgresql.org/message-id/4F501F9A.4080905@dunslane.net ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: andrew@dunslane.net, klaussfreire@gmail.com, pvh@pvh.ca Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? In-Reply-To: <4F501F9A.4080905@dunslane.net> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox