Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL and the OOM killer: Why we use strict memory overcommit

ubicloud.com

Post reply on HN
Print view View original

11–20 of 140 posts

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#11

Post by furkansahin »

ozgune wrote: post #6

(Ozgun from Ubicloud) I agree with the blog post's technical contents, but I feel we came across too strong in the title. For Ubicloud as a managed Postgres provider, we use strict memory overcommit. Our experience with operating Postgres at scale taught us that it's better to enable this than going with the defaults. However, I can see many other scenarios, where using strict memory overcommit would have unanticipat…

(Furkan, submitter) Hmm, I haven’t thought about that. I updated the title to better reflect Ubicloud Postgres' position.

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#13

Post by guenthert »

hilariously wrote: post #7
leononame wrote: post #4

This has bitten me multiple times. The problem I have is that at work we deploy the application (written in Go) and PostgreSQL on the same machine. The backend app allocates a lot of virtual memory, and initially we had overcommit to 0 (heuristic). This caused crashes on big queries in PostgreSQL and we set it to 2. The whole system became a bit unstable because the backend would still allocate a lot of virtual memor…

Yes, it would. Basically every serious database tries to allocate everything and more - back in the day we'd just allocate VMs on the machine even with the overhead because knowing it cannot leave its constraints and would work within them was worth the cost.

There are many reasons to use a dedicated host (or VM) for a DB server, but if only the accessible memory needs to be limited a container is the simpler, more efficient tool. Said that, I would expect to be able to configure how much memory a DB process is allowed to allocate. I remember distinctly that PostgreSQL allows such. But of course both can be configured simultaneously, a belts&suspenders approach if you will.

Whether failed transactions are actually so much more desirable than a OOM-killed process isn't quite obvious, but it might be easier to troubleshoot.

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#14

Post by xyzzy_plugh »

leononame wrote: post #4

This has bitten me multiple times. The problem I have is that at work we deploy the application (written in Go) and PostgreSQL on the same machine. The backend app allocates a lot of virtual memory, and initially we had overcommit to 0 (heuristic). This caused crashes on big queries in PostgreSQL and we set it to 2. The whole system became a bit unstable because the backend would still allocate a lot of virtual memor…

I'm not sure if you are aware but there are relatively recent environment variables you can set to help contain Go memory to a fixed size.

GOMEMLIMIT works very well if you set it to around 90% of available memory as a rough heuristic. You should definitely profile your application to fine tune this number (e.g. if you link with C libraries that hold large memory pools then Go doesn't account for that) but also to identify sources of spikey/leaky allocations. For example, encoding/json is notorious for it's inner sync.Pool hanging on to outsized buffers. There's usually a lot of low hanging fruit.

In my experience Go can be extremely stable in terms of memory footprint at both small (~O(1MiB)) and large (~O(256GiB)) scales, and it takes only a small amount of effort.

As far as GC languages go, it is by far the easiest to work with.

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#15

Post by chiply314 »

Nothing worse than memory management on Hyperscaler VMs which do not use Swap :|

Took k8s ages to get Swap support.

We lost something when we accepted that Hyperscalers just tell you to use more moemory. It was shitty 5 years ago and today especially after the ram price increases

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#16

Post by nok22kon »

szmarczak wrote: post #3

I have disabled overcommit both on Windows and on Linux. I hate having random programs being killed. Unfortunately, many programs commit 2x memory than they actually use. Often I see ~32GB committed and ~16GB resident.

how exactly did you disabled it on Windows?

I dont think it has an option for that.

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#17

Post by Bender »

Bender wrote: post #2

They allude to this in the article but I would emphasize caution when using mode 2 especially if one has already adjusted overcommit ratios as one can prevent forks. Test this in a QA/Perf environment first, also testing the restart of all applications. Load test and do full QA tests before deploying to Production and even then when deploying to production I would just dynamically change the setting via app deploymen…

Correcting a rather significant typo: setting the oom_score_adj for programs as high as 1000 should be -1000 to be left alone. 1000 would make it a prime candidate for an OOM kill. Positive integers should be used on sacrificial superfluous programs. [1] As an example OpenSSH sets the sshd to -1000 by default.

[1] - https://man7.org/linux/man-pages/man5/proc_pid_oom_score_adj...

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#18

Post by ValdikSS »

chiply314 wrote: post #15

Nothing worse than memory management on Hyperscaler VMs which do not use Swap :| Took k8s ages to get Swap support. We lost something when we accepted that Hyperscalers just tell you to use more moemory. It was shitty 5 years ago and today especially after the ram price increases

My guess would be: it's because memory management before MGLRU was really not good and required different userspace solutions and tinkering. You either get killed with OOM (no swap) or got into thrashing (swap).

And now, with PSI + MGLRU, situation is much better, but there are still missing features/subsystems which would be nice to have. For example there's no simple way to lock memory mlockall-style to ensure that rarely used daemon would not face long no-cache-latency upon accessing the first time after long idle time.

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#19

Post by szmarczak »

nok22kon wrote: post #16
szmarczak wrote: post #3

I have disabled overcommit both on Windows and on Linux. I hate having random programs being killed. Unfortunately, many programs commit 2x memory than they actually use. Often I see ~32GB committed and ~16GB resident.

how exactly did you disabled it on Windows? I dont think it has an option for that.

Settings -> View advanced system settings -> Performance (Settings) -> Advanced -> Virtual memory (Change...) -> No paging file

Re: PostgreSQL and the OOM killer: Why we use strict memory overcommit

#20

Post by wongarsu »

nok22kon wrote: post #16
szmarczak wrote: post #3

I have disabled overcommit both on Windows and on Linux. I hate having random programs being killed. Unfortunately, many programs commit 2x memory than they actually use. Often I see ~32GB committed and ~16GB resident.

how exactly did you disabled it on Windows? I dont think it has an option for that.

Not overcommitting is Windows's default and only behavior

A memory allocator can implement overcommit, because you can separate reserving virtual memory and having it backed by physical memory into two different system calls. But from the point of view of the kernel, any time it promises to give you physical memory that memory is backed either by RAM or by space reserved in the swap file

Post reply on HN