9 ms·

PostgreSQL, Memory and the Cloud

211 points by bilalhusain 5y ago

yjftsjthsd-h 5y ago
So are there problems with disabling overcommit? Or is it really that simple (at least for dedicated db hosts)?
- jpeter 5y ago
  And if there are no problems, why is it not disabled by default?
  - derefr 5y ago
    If you mean “why isn’t it disabled by default on Linux installs”: most programs don’t expect malloc(2) to ever return NULL. Those programs will just assume the return value from malloc(2) is valid memory. In the best case, they’ll immediately write to it and protection-fault. In the worst case, they’ll hold onto this NULL pointer for a while, passing it around, until eventually something else somewhere distant in the program blows up some unknown amount of time later. Even those programs that are “malloc(2) error aware”, often do something stupid and counterproductive in response, like attempting to allocate more memory for an exception object / stack trace / error string. Programs that do something useful in response to a NULL malloc(2) return result — useful for the stability of the system as a whole, better than what the OOM killer gets you — are rare, even on servers. Usually it’s only stateful, long-running, DBMS-like daemons that 1. bother, and 2. have the engineering effort put into them to do the right thing.
    - mjw1007 5y ago
      I think your first paragraph is too pessimistic. You can get a decent idea of the behaviour of the programs you use when they run out of memory by running under 'ulimit -v' with a low limit. In my experience most (though far from all) of the programs I use managed a clean abort with an error message (as from a traditional xmalloc()).
      - derefr 5y ago
        Most of these were likely managed-language programs. Programs witten for managed language runtimes will have a language-runtime-level abort on malloc(2) fail, which usually is well written, in the sense that it will clean up language-runtime-level resources, and emit a language-runtime-level error message. But this language-runtime-level abort usually isn’t exposed to the application in any hookable way, so from the developer’s perspective, it’s basically the same as being OOM killed. There’s no option to clean up e.g. an individual transaction’s resources in order to keep going. There’s no hooks for libraries to use to e.g. properly send close messages on sockets (if the language runtime doesn’t do that itself as part of managing socket lifetimes.) Etc. These managed runtimes (e.g. the JVM) may expose a catchable exception for OOM errors, but these are for internal, language-runtime level OOM errors, triggered by the runtime itself under certain conditions, rather than in response to a syscall failure. When malloc(2) fails, it’s basically “too late” from these runtimes’ perspectives — they no longer have the resources required to allow the user to run any more code.
        mjw1007 5y ago
        « Most of these were likely managed-language programs. » Please don't guess. They weren't. It is true that a program that aborts as soon as malloc returns failure isn't doing any special cleanup or attempting to keep going. But that's not at all the same as « Those programs will just assume the return value from malloc(2) is valid memory. In the best case, they’ll immediately write to it and protection-fault. », which is what I'm informing you is too pessimistic.
        derefr 5y ago
        > Please don't guess. I'm not guessing. I'm just answering you by ignoring/disregarding your personal experience, and instead treating you as a random variable sampling the population of people who use all possible software, and then talking about what that random variable would look like. Why? Because we don't need anecdotes to know this particular thing — we have the data. We know what the random variable actually looks like. (How? Because people have downloaded "all of Github" or "the entire Debian package archive", and run Valgrind over it, and uploaded the resulting dataset to BigQuery!) By the Law of Large Numbers, we can actually do stats about, effectively, what "all software" looks like. By volume, the majority of POSIX software that calls malloc(2), is incompetently-written, with no checks on the return result of malloc(2). This is an objective, verifiable fact. By volume, the majority of POSIX software that has a call to malloc(2) that does check the return value, exists as the result of a managed-language compiler emitting a language-runtime-level check into the compiled binary, rather than as an explicit source-level check. Another objective, verifiable fact. ----- It so happens that the software making up the "backbone" of an OS / average LAMP server is more competently-written, because it's had a lot more attention and engineering time put into it. But the same "power law of features" from e.g. Microsoft Office applies here — there's a core set of stuff everyone uses, but every user also has some weird stuff they are in the small minority of users for. And that stuff is what breaks. As it happens, that lesser-used stuff is also usually mission-critical to the operation of a business; otherwise people wouldn't be driven to use such not-a-lot-of-engineering-effort-put-in software in the first place. People are using this stuff "in anger", if they're using it at all. Which means that, sadly — insofar as most developers creating business-process IPC pipelines, don't already have the hard-won experience to build in fault-tolerance for individual processes within that pipeline — we see production systems where these malloc(2) failures are Single Points of Failure for the entire system. The flakiness of these long-tail programs, drags down the reliability of most systems-as-a-whole.
  - AzN1337c0d3r 5y ago
    Because many program "unnecessarily" allocate a lot of virtual memory and then never actually page it in, disabling overcommit will start killing processes due to lack of memory even though most of the memory isn't been actually used.
  - ris 5y ago
    It's extremely useful to be able to map more virtual memory space than exists physical memory in your computer. This is what makes e.g. mmap'ed access to large files possible.
- Diggsey 5y ago
  Some programs allocate a lot of virtual memory and then don't use it. Also, linux's forking model can result in a lot of virtual memory being allocated if a heavy-weight program tries to fork+exec a lot of smaller programs, since fork+exec it not atomic and briefly doubles the virtual memory usage of the original program. I think there are better ways to spawn programs that don't suffer from this problem now... If you have programs that are written to allocate virtual memory sparingly (like postgres) then that should be fine. However, there is a second way you can be caught out: even if you disable overcommit, your program can still be OOM killed for violating cgroup limits, since cgroup limits always behave as though over-commit is enabled (ie. they allow you to allocate more than you are allowed, and then you get OOM killed when you try to use the allocated memory). This means you'd have to be really careful running eg. postgres inside a kubernetes pod. This behaviour really sucks IMO. I would like it if you could set overcommit on a per-program basis, so that eg. postgres can say "I know what I'm doing - when I allocate virtual memory I want you to really allocate it (and tell me now if you can't...)". I think you can somewhat achieve this with memory locking, but that prevents it from being paged out at all...
  - ithkuil 5y ago
    Wasn't vfork designed to solve this issue? Does it not work in practice?
    - Diggsey 5y ago
      It is certainly one way to solve that specific issue, assuming the program was written to take advantage of it. As mentioned, there are several other reasons a program may use a lot of virtual memory though.
  - lokedhs 5y ago
    The fork issue is solved by adding swap. Making sure you have plenty of swap solves these issues, and I'd like to argue that it is more reliable than using overcommit.
    - Diggsey 5y ago
- perlgeek 5y ago
  There are problems with disabling overcommit. Consider this scenario: a process runs a fork(), and shortly after it runs an exec(). Normally, the extra fork only uses a tiny amount of extra memory, because the memory is shared between the parent and the child, until one of them writes to it (copy-on-write). With overcommit disabled, the kernel must reserve enough space to copy the whole writable RAM of a process when it forks. So you have a 16GB machine, and an 8.1GB process cannot spawn any other program through the usual fork + exec routine (workarounds exist, like forking before allocating lots of memory and using IPC to instruct the low-memory fork to fork again and launch, but that's way more complicated than a simple fork + exec). So if you have a dedicated DB host and you know that your DB engine is very carefully engineered to work with disabled overcommit, you can disable it. On a general-purpose machine a disabled overcommit will waste lots of RAM that's sitting unused.
aeyes 5y ago
Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact with support since February. Unbelievable attitude towards fixing production critical problems of their platform affecting all customers.
- breakingcups 5y ago
  It exposes a very problematic communication pattern. The engineering team doesn't respond to the support team (accidentally or deliberately). The support team then just decides to close the issue instead of prodding the engineering team for an actual response (even if it's just "Yeah, we're not fixing it"). Now the issue is just in limbo and the only one who feels the pain is the customer.
  - perlgeek 5y ago
    Another "fun" interaction pattern: User reports a bug (or a feature request), several others subscribe to and/or vote for this to be solved, and then a service rep closes the issue because there wasn't any recent activity. I've observed with with Atlassian where I wanted to report a Jira bug, but found that it had already been opened some years before, more than a hundred people had subscribed, bug was still closed as "no activity, must not be relevant". I just found the exact same bug reported for Jira Cloud (I had observed it in the on-prem version): https://jira.atlassian.com/browse/JSWCLOUD-8865 https://jira.atlassian.com/browse/JSWCLOUD-8865 and it was closed there for the very same reason. I didn't leave a comment because the original report described the issue perfectly, and adding a "me too" comment is just noise in the bug tracker. Guess I'll be noise in future :-(
    - jaredsohn 5y ago
      I don't think a 'me too' on this bug would be noise right now. Last activity was in 2019; having someone state the problem is still real in 2021 could impact if it gets fixed.
thyrsus 5y ago
Are there recommendations for learning about Linux kernel memory management? Two anecdata: * I had some compute servers that were up for 200 days. The customers noticed that they were half as fast as identical hardware just booted. Dropping the file system cache ("echo 3 | sudo dd of=/proc/sys/vm/drop_cache") brought the speed back up to the newly deployed servers. WTF? File system caches are supposed to be zero cost discards as soon as processes ask for RAM - but something else is going on. I suspect the kernel is behaving badly with overpopulated RAM management data (TLB entries?), but I don't know how to measure that. * If that is actually the problem, then a solution might be to decrease data size by using non-zero hugepages ("cat /proc/sys/vm/nr_hugepages"). I'd love to see recommendations on when to use that.
- SteveNuts 5y ago
  Are you using any swap? If so, check the swappiness setting
  - thyrsus 5y ago
    No swap. These are large RAM (400G to 1000G) Kubernetes nodes.
    - sargun 5y ago
      This is likely due to a kernel bug that was caused by the way cgroup slab management is handled. Upgrade to 5.10 or later, and it should be fixed. I’d be interested to see if the problem continues.
- citrin_ru 5y ago
  I don’t remember details now, but I’ve seen a situation when a Java app was working slower and a box with more RAM (and probably a bigger heap size), compare to a box with the same CPU but 2x less RAM. I suspected that TLB cache was the reason, but didn’t have time to test this.
  - Tostino 5y ago
dkersten 5y ago
A metacomment about the page (rather than the content): the text in the white boxes is almost unreadable for me, the contrast is crazy low.
- meepmorp 5y ago
  I had this problem, too; there's a button to toggle the night mode theme, which fixed it for me.
  - dkersten 5y ago
    Oh. I didn't even see that. That does indeed make the text legible, thank you. Sadly it makes the entire page too bright, but at least its readable!
    - meepmorp 5y ago
      I usually assume it's just my eyes getting worse, so I'm low-key glad someone else complained.
Okkef 5y ago
Good article, but please check the spelling. 'Program' is with a single m at the end. Also, it's 'hundreds' instead of 'hunderts'.
mnahkies 5y ago
I recently managed to crash a GCP cloudsql postgres 12 host running an interactive query that was rather heavy (based on error logs OOM). It surprised me because I had never executed a query and caused the whole host to crash up until that point - now I'm wondering if this misconfiguration is the cause
shdh 5y ago
GCP CloudSQL has a lot of issues. There was one with query insights being enabled causing segfaults on `LEFT JOIN` operations. Its since been patched, but really shitty.
zingar 5y ago
I'd like to thank the author for their clear, simple explanation. I haven't had to think about allocating memory since university and am not practiced thinking about it in my software but now I feel like I have useful ways to think about why processes just disappear sometimes.
Winsaucerer 5y ago
Are there any good/recommended books or resources for someone who wants to learn how to run postgresql well? E.g, what defaults to change and when, settings for the host OS (such as in the parent linked article), overall tips/insights/recommendations.
- Proven 5y ago
  PostgreSQL docs. And google.
- mixmastamyk 5y ago
  Postgres up and running by o'reilly is decent. Reading the docs for the config files is necessary as well.
renewiltord 5y ago
Interesting. Also a problem with RDS: https://stackoverflow.com/questions/52148675/aws-rds-with-postgres-is-oom-killer-configured https://stackoverflow.com/questions/52148675/aws-rds-with-po...

211 points by bilalhusain 5y ago

yjftsjthsd-h 5y ago

So are there problems with disabling overcommit? Or is it really that simple (at least for dedicated db hosts)?

jpeter 5y ago
And if there are no problems, why is it not disabled by default?
- derefr 5y ago
  If you mean “why isn’t it disabled by default on Linux installs”: most programs don’t expect malloc(2) to ever return NULL. Those programs will just assume the return value from malloc(2) is valid memory. In the best case, they’ll immediately write to it and protection-fault. In the worst case, they’ll hold onto this NULL pointer for a while, passing it around, until eventually something else somewhere distant in the program blows up some unknown amount of time later. Even those programs that are “malloc(2) error aware”, often do something stupid and counterproductive in response, like attempting to allocate more memory for an exception object / stack trace / error string. Programs that do something useful in response to a NULL malloc(2) return result — useful for the stability of the system as a whole, better than what the OOM killer gets you — are rare, even on servers. Usually it’s only stateful, long-running, DBMS-like daemons that 1. bother, and 2. have the engineering effort put into them to do the right thing.
  - mjw1007 5y ago
    I think your first paragraph is too pessimistic. You can get a decent idea of the behaviour of the programs you use when they run out of memory by running under 'ulimit -v' with a low limit. In my experience most (though far from all) of the programs I use managed a clean abort with an error message (as from a traditional xmalloc()).
    - derefr 5y ago
      Most of these were likely managed-language programs. Programs witten for managed language runtimes will have a language-runtime-level abort on malloc(2) fail, which usually is well written, in the sense that it will clean up language-runtime-level resources, and emit a language-runtime-level error message. But this language-runtime-level abort usually isn’t exposed to the application in any hookable way, so from the developer’s perspective, it’s basically the same as being OOM killed. There’s no option to clean up e.g. an individual transaction’s resources in order to keep going. There’s no hooks for libraries to use to e.g. properly send close messages on sockets (if the language runtime doesn’t do that itself as part of managing socket lifetimes.) Etc. These managed runtimes (e.g. the JVM) may expose a catchable exception for OOM errors, but these are for internal, language-runtime level OOM errors, triggered by the runtime itself under certain conditions, rather than in response to a syscall failure. When malloc(2) fails, it’s basically “too late” from these runtimes’ perspectives — they no longer have the resources required to allow the user to run any more code.
      - mjw1007 5y ago
        « Most of these were likely managed-language programs. » Please don't guess. They weren't. It is true that a program that aborts as soon as malloc returns failure isn't doing any special cleanup or attempting to keep going. But that's not at all the same as « Those programs will just assume the return value from malloc(2) is valid memory. In the best case, they’ll immediately write to it and protection-fault. », which is what I'm informing you is too pessimistic.
        derefr 5y ago
        > Please don't guess. I'm not guessing. I'm just answering you by ignoring/disregarding your personal experience, and instead treating you as a random variable sampling the population of people who use all possible software, and then talking about what that random variable would look like. Why? Because we don't need anecdotes to know this particular thing — we have the data. We know what the random variable actually looks like. (How? Because people have downloaded "all of Github" or "the entire Debian package archive", and run Valgrind over it, and uploaded the resulting dataset to BigQuery!) By the Law of Large Numbers, we can actually do stats about, effectively, what "all software" looks like. By volume, the majority of POSIX software that calls malloc(2), is incompetently-written, with no checks on the return result of malloc(2). This is an objective, verifiable fact. By volume, the majority of POSIX software that has a call to malloc(2) that does check the return value, exists as the result of a managed-language compiler emitting a language-runtime-level check into the compiled binary, rather than as an explicit source-level check. Another objective, verifiable fact. ----- It so happens that the software making up the "backbone" of an OS / average LAMP server is more competently-written, because it's had a lot more attention and engineering time put into it. But the same "power law of features" from e.g. Microsoft Office applies here — there's a core set of stuff everyone uses, but every user also has some weird stuff they are in the small minority of users for. And that stuff is what breaks. As it happens, that lesser-used stuff is also usually mission-critical to the operation of a business; otherwise people wouldn't be driven to use such not-a-lot-of-engineering-effort-put-in software in the first place. People are using this stuff "in anger", if they're using it at all. Which means that, sadly — insofar as most developers creating business-process IPC pipelines, don't already have the hard-won experience to build in fault-tolerance for individual processes within that pipeline — we see production systems where these malloc(2) failures are Single Points of Failure for the entire system. The flakiness of these long-tail programs, drags down the reliability of most systems-as-a-whole.
- AzN1337c0d3r 5y ago
  Because many program "unnecessarily" allocate a lot of virtual memory and then never actually page it in, disabling overcommit will start killing processes due to lack of memory even though most of the memory isn't been actually used.
- ris 5y ago
  It's extremely useful to be able to map more virtual memory space than exists physical memory in your computer. This is what makes e.g. mmap'ed access to large files possible.
Diggsey 5y ago
Some programs allocate a lot of virtual memory and then don't use it. Also, linux's forking model can result in a lot of virtual memory being allocated if a heavy-weight program tries to fork+exec a lot of smaller programs, since fork+exec it not atomic and briefly doubles the virtual memory usage of the original program. I think there are better ways to spawn programs that don't suffer from this problem now... If you have programs that are written to allocate virtual memory sparingly (like postgres) then that should be fine. However, there is a second way you can be caught out: even if you disable overcommit, your program can still be OOM killed for violating cgroup limits, since cgroup limits always behave as though over-commit is enabled (ie. they allow you to allocate more than you are allowed, and then you get OOM killed when you try to use the allocated memory). This means you'd have to be really careful running eg. postgres inside a kubernetes pod. This behaviour really sucks IMO. I would like it if you could set overcommit on a per-program basis, so that eg. postgres can say "I know what I'm doing - when I allocate virtual memory I want you to really allocate it (and tell me now if you can't...)". I think you can somewhat achieve this with memory locking, but that prevents it from being paged out at all...
- ithkuil 5y ago
  Wasn't vfork designed to solve this issue? Does it not work in practice?
  - Diggsey 5y ago
    It is certainly one way to solve that specific issue, assuming the program was written to take advantage of it. As mentioned, there are several other reasons a program may use a lot of virtual memory though.
- lokedhs 5y ago
  The fork issue is solved by adding swap. Making sure you have plenty of swap solves these issues, and I'd like to argue that it is more reliable than using overcommit.
  - Diggsey 5y ago
perlgeek 5y ago
There are problems with disabling overcommit. Consider this scenario: a process runs a fork(), and shortly after it runs an exec(). Normally, the extra fork only uses a tiny amount of extra memory, because the memory is shared between the parent and the child, until one of them writes to it (copy-on-write). With overcommit disabled, the kernel must reserve enough space to copy the whole writable RAM of a process when it forks. So you have a 16GB machine, and an 8.1GB process cannot spawn any other program through the usual fork + exec routine (workarounds exist, like forking before allocating lots of memory and using IPC to instruct the low-memory fork to fork again and launch, but that's way more complicated than a simple fork + exec). So if you have a dedicated DB host and you know that your DB engine is very carefully engineered to work with disabled overcommit, you can disable it. On a general-purpose machine a disabled overcommit will waste lots of RAM that's sitting unused.

aeyes 5y ago

Wow, the title of this post is very calm compared to what is actually happening. CloudSQL Postgres is running with a misconfigured OS OOM killer, crashes Postmaster randomly even if memory use is below instance spec. GCP closes this bug report as "Won't fix". This is a priority 1 issue. Seeing a wontfix for this has completely destroyed my trust of their judgement. The bug report states that they have been in contact with support since February. Unbelievable attitude towards fixing production critical problems of their platform affecting all customers.

breakingcups 5y ago
It exposes a very problematic communication pattern. The engineering team doesn't respond to the support team (accidentally or deliberately). The support team then just decides to close the issue instead of prodding the engineering team for an actual response (even if it's just "Yeah, we're not fixing it"). Now the issue is just in limbo and the only one who feels the pain is the customer.
- perlgeek 5y ago
  Another "fun" interaction pattern: User reports a bug (or a feature request), several others subscribe to and/or vote for this to be solved, and then a service rep closes the issue because there wasn't any recent activity. I've observed with with Atlassian where I wanted to report a Jira bug, but found that it had already been opened some years before, more than a hundred people had subscribed, bug was still closed as "no activity, must not be relevant". I just found the exact same bug reported for Jira Cloud (I had observed it in the on-prem version): https://jira.atlassian.com/browse/JSWCLOUD-8865 https://jira.atlassian.com/browse/JSWCLOUD-8865 and it was closed there for the very same reason. I didn't leave a comment because the original report described the issue perfectly, and adding a "me too" comment is just noise in the bug tracker. Guess I'll be noise in future :-(
  - jaredsohn 5y ago
    I don't think a 'me too' on this bug would be noise right now. Last activity was in 2019; having someone state the problem is still real in 2021 could impact if it gets fixed.

thyrsus 5y ago

Are there recommendations for learning about Linux kernel memory management? Two anecdata: * I had some compute servers that were up for 200 days. The customers noticed that they were half as fast as identical hardware just booted. Dropping the file system cache ("echo 3 | sudo dd of=/proc/sys/vm/drop_cache") brought the speed back up to the newly deployed servers. WTF? File system caches are supposed to be zero cost discards as soon as processes ask for RAM - but something else is going on. I suspect the kernel is behaving badly with overpopulated RAM management data (TLB entries?), but I don't know how to measure that. * If that is actually the problem, then a solution might be to decrease data size by using non-zero hugepages ("cat /proc/sys/vm/nr_hugepages"). I'd love to see recommendations on when to use that.

SteveNuts 5y ago
Are you using any swap? If so, check the swappiness setting
- thyrsus 5y ago
  No swap. These are large RAM (400G to 1000G) Kubernetes nodes.
  - sargun 5y ago
    This is likely due to a kernel bug that was caused by the way cgroup slab management is handled. Upgrade to 5.10 or later, and it should be fixed. I’d be interested to see if the problem continues.
citrin_ru 5y ago
I don’t remember details now, but I’ve seen a situation when a Java app was working slower and a box with more RAM (and probably a bigger heap size), compare to a box with the same CPU but 2x less RAM. I suspected that TLB cache was the reason, but didn’t have time to test this.
- Tostino 5y ago

dkersten 5y ago

A metacomment about the page (rather than the content): the text in the white boxes is almost unreadable for me, the contrast is crazy low.

meepmorp 5y ago
I had this problem, too; there's a button to toggle the night mode theme, which fixed it for me.
- dkersten 5y ago
  Oh. I didn't even see that. That does indeed make the text legible, thank you. Sadly it makes the entire page too bright, but at least its readable!
  - meepmorp 5y ago
    I usually assume it's just my eyes getting worse, so I'm low-key glad someone else complained.

Okkef 5y ago

Good article, but please check the spelling. 'Program' is with a single m at the end. Also, it's 'hundreds' instead of 'hunderts'.

mnahkies 5y ago

I recently managed to crash a GCP cloudsql postgres 12 host running an interactive query that was rather heavy (based on error logs OOM). It surprised me because I had never executed a query and caused the whole host to crash up until that point - now I'm wondering if this misconfiguration is the cause

shdh 5y ago

GCP CloudSQL has a lot of issues. There was one with query insights being enabled causing segfaults on `LEFT JOIN` operations. Its since been patched, but really shitty.

zingar 5y ago

I'd like to thank the author for their clear, simple explanation. I haven't had to think about allocating memory since university and am not practiced thinking about it in my software but now I feel like I have useful ways to think about why processes just disappear sometimes.

Winsaucerer 5y ago

Are there any good/recommended books or resources for someone who wants to learn how to run postgresql well? E.g, what defaults to change and when, settings for the host OS (such as in the parent linked article), overall tips/insights/recommendations.

Proven 5y ago
PostgreSQL docs. And google.
mixmastamyk 5y ago
Postgres up and running by o'reilly is decent. Reading the docs for the config files is necessary as well.

renewiltord 5y ago

Interesting. Also a problem with RDS: https://stackoverflow.com/questions/52148675/aws-rds-with-postgres-is-oom-killer-configured https://stackoverflow.com/questions/52148675/aws-rds-with-po...