PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? - pgsql.performance

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
View parsed - Show only message text
Path: h9ni27459pbe.0!nntp.google.com!news2.google.com!goblin3!goblin1!goblin.stu.neva.ru!news.albasani.net!weretis.net!feeder4.news.weretis.net!news.hub.org!postgresql.org!pgsql-performance-owner+M46461
From: dcro...@gmail.com (Dave Crooke)
Newsgroups: pgsql.performance
Subject: Re: [PERFORM] PG as in-memory db? How to warm up and re-populate
 buffers? How to read in all tuples into memory?
Date: Thu, 1 Mar 2012 11:38:39 -0600
Organization: Hub.Org Networking Services
Lines: 101
Sender: n...@news.hub.org
Message-ID: <CALi4UpgqX2Ge3v6D01CzfPwaK6zRTXpS1zs9_b=js08W4ou6Lg@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>
	<CAOR=d=2+RXYOjL1Ko6E1v9cMo9+L4bhPbe_zy_qi8oZyL5ttUg@mail.gmail.com>
NNTP-Posting-Host: news.hub.org
Mime-Version: 1.0
NNTP-Posting-Date: Thu, 1 Mar 2012 17:39:04 +0000 (UTC)
Content-Type: multipart/alternative; boundary=0015175cd8263d1b5004ba31ee29

--0015175cd8263d1b5004ba31ee29
Content-Type: text/plain; charset=UTF-8

Just curious ... has anyone tried using a ram disk as the PG primary and
DRBD as the means to make it persistent?
On Mar 1, 2012 11:35 AM, "Scott Marlowe" <scott.marl...@gmail.com> wrote:

> On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:
> > On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkel...@gmail.com>
> wrote:
> >> 2012/2/28 Claudio Freire <klaussfre...@gmail.com>:
> >>>
> >>> In the OP, you say "There is enough main memory to hold all table
> >>> contents.". I'm assuming, there you refer to your current system, with
> >>> 4GB memory.
> >>
> >> Sorry for the confusion: I'm doing these tests on this machine with
> >> one table (osm_point) and one country. This table has a size of 2.6GB
> >> and 10 million tuples. The other machine has to deal with at least 5
> >> tables in total and will be hold more than one country plus routing
> >> etc..
> >
> > What is your shared_buffers set to?  2.6GB is uncomfortably close to
> > 4GB, considering the computer has other things it needs to use memory
> > for as well.
>
> The real danger here is that the kernel will happily swap ut
> shared_buffers memory to make room to cache more from the hard disks,
> especially if that shared_mem hasn't been touched in a while.  On a
> stock kernel with swappinness of 60 etc, it's quite likely the OP is
> seeing the DB go to get data from shared_buffers, and the OS is
> actually paging in for shared_buffers. At that point reading from
> kernel cache is MUCH faster, and reading from the HDs is still
> probably faster than swapping in shared_buffers.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--0015175cd8263d1b5004ba31ee29
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<p>Just curious ... has anyone tried using a ram disk as the PG primary and=
 DRBD as the means to make it persistent?</p>
<div class=3D"gmail_quote">On Mar 1, 2012 11:35 AM, &quot;Scott Marlowe&quo=
t; &lt;<a href=3D"mailto:scott.marl...@gmail.com">scott.marl...@gmail.com</=
a>&gt; wrote:<br type=3D"attribution"><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes &lt;<a href=3D"mailto:jeff.janes=
@gmail.com">jeff.ja...@gmail.com</a>&gt; wrote:<br>
&gt; On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller &lt;<a href=3D"mailto:s=
fkel...@gmail.com">sfkel...@gmail.com</a>&gt; wrote:<br>
&gt;&gt; 2012/2/28 Claudio Freire &lt;<a href=3D"mailto:klaussfreire@gmail.=
com">klaussfre...@gmail.com</a>&gt;:<br>
&gt;&gt;&gt;<br>
&gt;&gt;&gt; In the OP, you say &quot;There is enough main memory to hold a=
ll table<br>
&gt;&gt;&gt; contents.&quot;. I&#39;m assuming, there you refer to your cur=
rent system, with<br>
&gt;&gt;&gt; 4GB memory.<br>
&gt;&gt;<br>
&gt;&gt; Sorry for the confusion: I&#39;m doing these tests on this machine=
 with<br>
&gt;&gt; one table (osm_point) and one country. This table has a size of 2.=
6GB<br>
&gt;&gt; and 10 million tuples. The other machine has to deal with at least=
 5<br>
&gt;&gt; tables in total and will be hold more than one country plus routin=
g<br>
&gt;&gt; etc..<br>
&gt;<br>
&gt; What is your shared_buffers set to? =C2=A02.6GB is uncomfortably close=
 to<br>
&gt; 4GB, considering the computer has other things it needs to use memory<=
br>
&gt; for as well.<br>
<br>
The real danger here is that the kernel will happily swap ut<br>
shared_buffers memory to make room to cache more from the hard disks,<br>
especially if that shared_mem hasn&#39;t been touched in a while. =C2=A0On =
a<br>
stock kernel with swappinness of 60 etc, it&#39;s quite likely the OP is<br=
>
seeing the DB go to get data from shared_buffers, and the OS is<br>
actually paging in for shared_buffers. At that point reading from<br>
kernel cache is MUCH faster, and reading from the HDs is still<br>
probably faster than swapping in shared_buffers.<br>
<br>
--<br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e...@postgresql.org">pgsql-performa...@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" target=3D"=
_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</blockquote></div>

--0015175cd8263d1b5004ba31ee29--
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
