Re: "out of memory" error

From: Christian Schröder <cs(at)deriva(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: "out of memory" error
Date: 2007-08-23 18:30:46
Message-ID: 46CDD256.3030401@deriva.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid:18161(dot)1187849631(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<blockquote type="cite">
<pre wrap="">Ok, I can do this, but why can more memory be harmful?
</pre>
</blockquote>
<pre wrap=""><!---->
Because you've left no room for anything else? The kernel, the various
other daemons, the Postgres code itself, and the local memory for each
Postgres process all require more than zero space.
</pre>
</blockquote>
<br>
So does this mean that the stuff you mentioned needs more than 1 GB of
memory? I seem to have undererstimated the amount of memory that is
needed for these purposes. :(<br>
<br>
<blockquote cite="mid:18161(dot)1187849631(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">
Even more to the point, with such a large shared-buffer space, the
kernel probably will be tempted to swap out whatever parts of it seem
less used at the moment. That is far more harmful to performance than
not having had the buffer in the first place --- it can easily triple
the amount of disk I/O involved. (Thought experiment: dirty buffer is
written to disk, versus dirty buffer is swapped out to disk, then later
has to be swapped in so it can be written to wherever it should have
gone.)

Bottom line is that PG shared buffers are not so important as to deserve
3/4ths of your RAM.
</pre>
</blockquote>
<br>
Thanks for your tips! I have changed the "shared_buffers" setting back
to 2 GB. It was set to 2 GB before, but we also had "out of memory"
errors with this setting, so I raised it to 3 GB.<br>
Could you please help me understand what's happening? The server is a
dedicated database server. Few other demons are running, most of them
are system services that do not consume a considerable amount of
memory. No web server or similar is running on this machine.<br>
Moreover, the output of "free" confuses me:<br>
<br>
&nbsp;&nbsp;&nbsp; db2:~ # free -m<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; used&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; free&nbsp;&nbsp;&nbsp;&nbsp; shared&nbsp;&nbsp;&nbsp; buffers&nbsp;&nbsp;&nbsp;&nbsp;
cached<br>
&nbsp;&nbsp;&nbsp; Mem:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3954&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3724&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 229&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3097<br>
&nbsp;&nbsp;&nbsp; -/+ buffers/cache:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 627&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3326<br>
&nbsp;&nbsp;&nbsp; Swap:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2055&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 628&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1426<br>
<br>
Doesn't that mean that plenty of memory is unused? I always thought
that the memory used for buffers and caches can be thought of as free
memory. Isn't this correct?<br>
Regarding the memory needs of the PostgreSQL server itself: Is there
any estimation how much memory will be needed besides the shared
buffers? What exactly does "out of memory" mean? Who requested the
memory and why could this memory request not be fulfilled?<br>
I can post the memory overview from the log file, but I don't know if
it's considered impolite to post so many lines to this mailing list.<br>
<br>
Thanks a lot again for your help,<br>
&nbsp;&nbsp;&nbsp; Christian<br>
<pre class="moz-signature" cols="72">--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-B&ouml;ckler-Stra&szlig;e 2 <a class="moz-txt-link-freetext" href="http://www.deriva.de">http://www.deriva.de</a>
D-37079 G&ouml;ttingen

Deriva CA Certificate: <a class="moz-txt-link-freetext" href="http://www.deriva.de/deriva-ca.cer">http://www.deriva.de/deriva-ca.cer</a></pre>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 4.0 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-08-23 18:35:20 Re: PostgreSQL vs Firebird feature comparison finished
Previous Message Tom Lane 2007-08-23 18:27:08 Re: Argument type list