Thread: "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 = 3GB
>>
>
> this 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 = 3GB
>>
>> this 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
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 mentionedneeds 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 2GB. 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 /> Couldyou 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 isrunning 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 memoryused for buffers and caches can be thought of as free memory. Isn't this correct?<br /> Regarding the memory needsof 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 somany 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>
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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:
Isn't 128MB quite low considering the "current standard" of 25% - 50% of total ram?
Regards
MP
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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 asshared 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<dd>PostgreSQL uses lots of this, view <code>ipcs</code> to proveit, the more shared memory the better as more data (tables) can be loaded. On a dedicated datbase server it's not uncommonto give half the memory to the database.</dl></blockquote> and<br /><blockquote type="cite"><dl><dt>shared_buffers= N<dd>Set anywhere from 1/4 to 1/2 physical memory, must set kernel shared memory maxfirst. Will see noticeable difference.</dl></blockquote> Since the first links are also mentioned on the official PostgreSQLwebsite (<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 trustedmore.<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>
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