0

i'm currently investigating a situation where my PostgreSQL 9.3 instance was killed by the Linux OOM Killer and went to recovery mode. The machine is EC2 Server with 16 GB of RAM and has rather few connections and is used for Business Analsys purposes only.

My assumption is that the reason for the out of memory situation is on one hand a very huge statement but also the fact that work_mem variable is set to 2GB while at the same time 100 connections are allowed.

And here are my questions:

  1. is my assumption correct
  2. if so, how can i force postgres to fill the session memory in a controlled manner to make the situation repeatable

I hope you guys can help me soon, thanks in advance and have a good week.

Improve this question
3
  • Well, lower your work_mem setting, as it's obviously much too high? (And allow fewer concurrent connections?) And don't use cryptic abbreviations like "BI" in your question. This is supposed to be publicly readable.
    – Erwin Brandstetter
    Jul 25, 2016 at 17:40
  • @ErwinBrandstetter BI is Business Intelligence, I think - it is very common in the DB world, so for me this is one of the rare cases where the abbreviation is OK. YMMV ;)
    – András Váczi
    Jul 27, 2016 at 14:16
  • work_mem 'Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files' - so write some huge sort operations, start them from several connections and there you are. Anyway, what are the kernel overcommit settings on your instance?
    – András Váczi
    Jul 27, 2016 at 14:18

1 Answer 1

Reset to default
1

You definitely have too high memory settings. What you can do in your situation: 1) Lower work_mem 2) Lower max_connections

If you want to completely get rid of OOM-killer coming to your postgres server, then you should set kernel parameter vm.overcommit = 2 is sysctl.conf. This will forbid kernel to allocate more memory than it really have.

Important notes about this parameter:

1) Tune our memory-related settings. If something will try to allocate memory when it is full, you'll get Can't allocate memory problem. This can end even in no possibility of launch any bash command from command line because of no memory enough to fork().

2) Create relatively small swap area and set vm.swappiness to some low value (like 5 maybe, default is 60). This will save some nerves in case of trouble.

Improve this answer
1
  • So, they are specifically asking about how to reproduce the issue... Otherwise, you are right.
    – András Váczi
    Jul 27, 2016 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.