Date: Sat, 14 Sep 2024 22:11:57 +0200
From: Christoph Moench-Tegeder <cmt@burggraben.net>
To: Thomas Ziegler <thomas.ziegler@holmsecurity.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Failing to allocate memory when I think it shouldn't
Message-ID: <ZuXuDdq32fuzI8pp@elch.exwg.net>
References: <PAWPR07MB1009548159C0046E01170C6E6ED652@PAWPR07MB10095.eurprd07.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <PAWPR07MB1009548159C0046E01170C6E6ED652@PAWPR07MB10095.eurprd07.prod.outlook.com>
User-Agent: Mutt/2.2.13 (2024-03-09)
Archived-At: <https://www.postgresql.org/message-id/ZuXuDdq32fuzI8pp%40elch.exwg.net>
Precedence: bulk

Hi,

## Thomas Ziegler (thomas.ziegler@holmsecurity.com):

There's a lot of information missing here. Let's start from the top.

> I have had my database killed by the kernel oom-killer. After that I
> set turned off memory over-committing and that is where things got weird.

What exactly did you set? When playing with vm.overcommit, did you
understand "Committed Address Space" and the workings of the
overcommit accounting? This is the document:
https://git.kernel.org/pub/scm/linux/kernel/git/stable/linux.git/tree/Documentation/mm/overcommit-accounting.rst
Hint: when setting overcommit_memory=2 you might end up with way
less available adress space than you thought you would. Also keep
an eye on /proc/meminfo - it's sometimes hard to estimate "just off
your cuff" what's in memory and how it's mapped. (Also, anything
else on that machine which might hog memory?).

> I have `shared_buffers` at `16000MB`, `work_mem` at `80MB`, `temp_buffers`
> at `8MB`, `max_connections` at `300` and `maintenance_work_mem` at `1GB`.
> So all in all, I get to roughly 42GB of max memory usage
> (`16000+(80+8)*300=42400`).

That work_mem is "per query operation", you can have multiple of
those in a single query:
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM
Also, there's hash_mem_multiplier:
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-HASH-MEM-MULTIPLIER
Then I've seen that your query uses parallel workers, remember that
each worker requests memory.
Next, maintenance_work_mem is a per process limit, and depending on
what's running at any given time, that can add up.

Finally, there's this:
> 2024-09-12 05:18:36.073 UTC [1932776] LOG:  background worker "parallel worker" (PID 3808076) exited with exit code 1
> terminate called after throwing an instance of 'std::bad_alloc'
>   what():  std::bad_alloc
> 2024-09-12 05:18:36.083 UTC [1932776] LOG:  background worker "parallel worker" (PID 3808077) was terminated by signal 6: Aborted

That "std::bad_alloc" sounds a lot like C++ and not like the C our
database is written in. My first suspicion would be that you're using
LLVM-JIT (unless you have other - maybe even your own - C++ extensions
in the database?) and that in itself can use a good chunk of memory.
And it looks like that exception bubbled up as a signal 6 (SIGABRT)
which made the process terminate immediately without any cleanup,
and after that the server has no other chance than to crash-restart.

I recommend starting with understanding the actual memory limits
as set by your configuration (personally I believe that memory
overcommit is less evil than some people think). Have a close look
at /proc/meminfo and if possible disable JIT and check if it changes
anything. Also if possible try starting with only a few active
connections and increase load carefully once a steady state (in
terms of memory usage) has been reached.

Regards,
Christoph

-- 
Spare Space