"out of memory" error
Hi list,
I am struggling with some "out of memory" errors in our PostgreSQL
database which I do not understand. Perhaps someone can give me a hint.
The application which causes the errors runs multi-threaded with 10
threads. Each of the threads performs several select statements on the
database. Some of the statements are rather complicated (joins over
mulitple tables etc.) From time to time some of the statements lead to
"out of memory" errors. The errors are not reproducable and if I run the
statements alone everything works fine.
When I watch the output of "free -m" while the application runs, the
used memory (without buffers) is always near 500 MB:
total used free shared buffers
cached
Mem: 3954 3410 543 0 0
2942
-/+ buffers/cache: 467 3486
Swap: 2055 556 1498
These are the current settings from the server configuration:
shared_buffers = 3GB
work_mem = 8MB
maintenance_work_mem = 256MB
max_stack_depth = 4MB
The output of "ulimit -a" is as follows:
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 38912
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) 3441565
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 38912
virtual memory (kbytes, -v) 4922720
file locks (-x) unlimited
The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4.
I can post the details about the memory status from the log file if it
is needed.
Are there any configuration parameters that influence the amount of
available memory (besides "shared_buffers" which seems to be high enough)?
Thanks for any help!
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-B�ckler-Stra�e 2 http://www.deriva.de
D-37079 G�ttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote:
These are the current settings from the server configuration:
shared_buffers = 3GB
this is *way* to much. i would suggest lowering it to 1gig *at most*.
max memory size (kbytes, -m) 3441565
this looks like too close to shared_buffers. again - lower it.
The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4.
is it by any chance i386 architecture?
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
hubert depesz lubaczewski wrote:
On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote:
These are the current settings from the server configuration:
shared_buffers = 3GBthis is *way* to much. i would suggest lowering it to 1gig *at most*.
Ok, I can do this, but why can more memory be harmful?
max memory size (kbytes, -m) 3441565
this looks like too close to shared_buffers. again - lower it.
What happens if I set shared_buffers higher than the ulimit?
The machine is a linux box with 4 GB memory running PostgreSQL 8.2.4.
is it by any chance i386 architecture?
Linux db2 2.6.18.8-0.3-default #1 SMP Tue Apr 17 08:42:35 UTC 2007
x86_64 x86_64 x86_64 GNU/Linux
Intel(R) Xeon(R) CPU 5130 @ 2.00GHz with 4 logical processors (2 physical)
vm.overcommit_memory = 2 # No memory overcommit.
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
=?UTF-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= <cs@deriva.de> writes:
hubert depesz lubaczewski wrote:
On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote:
These are the current settings from the server configuration:
shared_buffers = 3GBthis is *way* to much. i would suggest lowering it to 1gig *at most*.
Ok, I can do this, but why can more memory be harmful?
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.
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.
regards, tom lane
<!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.1187849631@sss.pgh.pa.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.1187849631@sss.pgh.pa.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>
db2:~ # free -m<br>
total used free shared buffers
cached<br>
Mem: 3954 3724 229 0
0 3097<br>
-/+ buffers/cache: 627 3326<br>
Swap: 2055 628 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>
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öckler-Straße 2 <a class="moz-txt-link-freetext" href="http://www.deriva.de">http://www.deriva.de</a>
D-37079 Gö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>
On Thu, Aug 23, 2007 at 08:30:46PM +0200, Christian Schröder wrote:
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.
You've got it completely wrong. By setting shared_buffers to 2GB it
means no-one can use it. It's not postgres that's running out of
memory, it's the rest of your system. Set it to something sane like
128MB or maybe smaller.
It's a cache, nothing more, small values does not mean you can't run big
queries.
The rest of Tom's comment was about how large shared_buffer is worse
because it eats away at your real disk cache and your performance will
completely tank.
Have a nice day,
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?
Postgresql shared_buffers is not "free". It should be around your
actually working set size, much bigger is counter productive.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote:
You've got it completely wrong.
Hm, you seem to be right. :(
I have now decreased the "shared_buffers" setting to 128 MB. I have also
found some tuning pages with warnings about not setting the value too
high. I'm sure that I have read these pages before, but I seem to have
been blind ...
Ok, many thanks for putting me right!
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-B�ckler-Stra�e 2 http://www.deriva.de
D-37079 G�ttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
On 8/23/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
You've got it completely wrong. By setting shared_buffers to 2GB it
means no-one can use it. It's not postgres that's running out of
memory, it's the rest of your system. Set it to something sane like
128MB or maybe smaller.
Isn't 128MB quite low considering the "current standard" of 25% - 50% of
total ram?
Regards
MP
On Fri, Aug 24, 2007 at 12:07:37PM +0300, Mikko Partio wrote:
On 8/23/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
You've got it completely wrong. By setting shared_buffers to 2GB it
means no-one can use it. It's not postgres that's running out of
memory, it's the rest of your system. Set it to something sane like
128MB or maybe smaller.Isn't 128MB quite low considering the "current standard" of 25% - 50% of
total ram?
Not sure about "current standard" but it depends heavily on your
system. On my laptop where postgres is among the least important
applications, even 16MB might be too much. If you have a server
dedicated to it, maybe a larger percentage would be helpful, but >50%
of memory is definitly counterproductive.
The basic issue is that everything postgres reads into shared_buffers
is going to be in the system-wide disk cache also. That's two copies of
everything -> wasted memory. I think it's probably more to do with your
write-traffic than read-traffic.
At the very least, if you're reaching the point where the kernel is
swapping shared memory to disk, you've lost. More is most definitly not
always better.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
<!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">
Mikko Partio wrote:
<blockquote type="cite">
<div>
<div>Isn't 128MB quite low considering the "current standard" of 25%
- 50% of total ram?<br>
</div>
</div>
</blockquote>
<br>
I had also read a statement about using this amount of memory as shared
buffers. Exactly that was the reason why I set it to such a high value,
but I am now convinced that this is wrong.<br>
<br>
<a class="moz-txt-link-freetext" href="http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf">http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf</a><br>
and<br>
<a class="moz-txt-link-freetext" href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html">http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a><br>
are quite clear about this.<br>
<br>
On the other hand,
<a class="moz-txt-link-freetext" href="http://edoceo.com/liber/db-postgresql-performance.php">http://edoceo.com/liber/db-postgresql-performance.php</a> says:<br>
<blockquote type="cite">
<dl>
<dt>Shared Memory</dt>
<dd>PostgreSQL uses lots of this, view <code>ipcs</code> to prove
it, the more shared memory the better as more data (tables) can be
loaded.
On a dedicated datbase server it's not uncommon to give half the memory
to the database.</dd>
</dl>
</blockquote>
and<br>
<blockquote type="cite">
<dl>
<dt>shared_buffers = N</dt>
<dd>Set anywhere from 1/4 to 1/2 physical memory, must set kernel
shared memory max first.
Will see noticeable difference.</dd>
</dl>
</blockquote>
Since the first links are also mentioned on the official PostgreSQL
website (<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/techdocs.2">http://www.postgresql.org/docs/techdocs.2</a>) I think they should
be trusted more.<br>
<br>
Regards,<br>
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öckler-Straße 2 <a class="moz-txt-link-freetext" href="http://www.deriva.de">http://www.deriva.de</a>
D-37079 Gö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>
Side note: Why does Thunderbird send HTML mails albeit being configured
for sending plain text mails? Sorry for that! And sorry for being off-topic.
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-B�ckler-Stra�e 2 http://www.deriva.de
D-37079 G�ttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer