I have a specific question about how you
I have a specific question about how you manage postgres connexions and out of memory.
If one connection eats too much memory, more than provisioned, how does neon reacts ?
1. Does it kill postgres postmaster processes and postgres need restart
2. It kills the process related to the connexion only, so the request fails but postgres does not need a restart
3. Other ?
4 Replies
fascinating-indigo•this hour
Hey!
When the OS runs out of virtual memory, the postmaster may be targeted by the OOM killer. There are some modifications possible, though I'm not sure of the Neon specifics. If the postmaster is killed, existing database connections will continue to function normally but no new connections will be accepted, and you need to restart postres to recover. You can read more about it here : https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
However, with Neon's autoscaling, if you set your max CU to an adequate value you should avoid this altogether. Likewise, using Neon's pooled connecting you can multiplex many client connections to fewer postgres backends which can help a lot given postgres connections are not the cheapest.
PostgreSQL Documentation
18.4. Managing Kernel Resources
18.4. Managing Kernel Resources # 18.4.1. Shared Memory and Semaphores 18.4.2. systemd RemoveIPC 18.4.3. Resource Limits 18.4.4. Linux Memory Overcommit 18.4.5. …
frail-apricot•23h ago
In the same document you mentioned, it is said :
in the PostgreSQL startup script just before invoking postgres. Note that this action must be done as root, or it will have no effect; so a root-owned startup script is the easiest place to do it. If you do this, you should also set these environment variables in the startup script before invoking postgres: export PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj export PG_OOM_ADJUST_VALUE=0 These settings will cause postmaster child processes to run with the normal OOM score adjustment of zero, so that the OOM killer can still target them at need. You could use some other value for PG_OOM_ADJUST_VALUE if you want the child processes to run with some other OOM score adjustment. (PG_OOM_ADJUST_VALUE can also be omitted, in which case it defaults to zero.) If you do not set PG_OOM_ADJUST_FILE, the child processes will run with the same OOM score adjustment as the postmaster, which is unwise since the whole point is to ensure that the postmaster has a preferential setting.Is it the case on neon databases ?
xenial-black•19h ago
Postgres on neon can get OOM-killed if that is what you are asking. The Linux OOM killer is not very smart. It may kill your connection process, or it may very well kill the postmaster.
frail-apricot•19h ago
Won't it try to kill the connection process first ? Because of pg_oom_adjust_value ?
We have a quite intensive app with various queries. And I discovered some big JSON payload can make postgres processes use unrealistic amount of memory.
I want to find a robust solution, or at least a solution that will minimize the impact of a single request, or help to find the culprit.
Any details about how it would be managed on neon ?