3

We have a postgres 9.0 instance that is running on a pretty beefy machine (96G of RAM/24 cores). IN the recent weeks we have experienced crashes due to child processes of postgres being killed with out of memory by OOM killer. It seems that due to using connection pooling, those child processes are long-lived (which makes sense, since opening and closing the connections takes time), the problem is that they are gradually growing in memory consumption to reach even 9Gigs/process. As you may imagine, having 10 of those, fills up the available ram and oom-killer kicks in.

The questions are:

  1. if we are using the default config parameter, how is it possible for the process to allocate so much memory?
  2. why are those processes not freeing the memory ever?

For reference, settings that can influence memory:

max_connections = 950
shared_buffers = 32MB

All other settings are not overridden, meaning we are using defaults.

Bartosz Radaczyński
  • 131
  • 1
  • 5
  • PostgreSQL uses around `shared_buffers + max_connections * work_mem * N` of memory at max where `N` is the amount of tables to sort during one query. I would guess that the original issue was that `work_mem` had been set too high. – Mikko Rantalainen Jun 29 '20 at 14:37

1 Answers1

5

The first thing to consider would be to increase shared_buffers way way up, at least to 1GB and possibly much more, up to 25% of the main memory. That's what the doc says.

At only 32MB, of which ~18Mb will already be used just to cope with max_connections to 950, the child processes have access to barely enough shared memory to share anything.

This may or may not mitigate the memory consumption problem for your specific workload and situation, but anyway that's a step in the right direction. The current value is insanely low.

About the OOM specifically, you may want to consider the workarounds offered in the Linux Memory Overcommit section of the doc. Note however that it's a short paragraph for a complex issue. There's more for example in this blog post and its comments with pointers to understand the context and the trade-offs involved in limiting or disabling the OOM.

Aside from that, a memory leak in Postgres is always possible. You need to make sure that you're running the latest minor version in case it's already fixed. The release notes that come with each bugfix-release definitely mention memory leaks from time to time.

Daniel Vérité
  • 2,740
  • 14
  • 19