Re: One PG process eating more than 40GB of RAM and getting killed by OOM

On Fri, Oct 13, 2023 at 10:53 AM Jean-Christophe Boggio <
postgresql(at)thefreecat(dot)org> wrote:

> Le 13/10/2023 à 15:12, MichaelDBA a écrit :
>
> Turn off the OOM killer so you would get a nicer me ssage in PG log file
> instead of crashing the PG service.
>
> vm.overcommit_memory=2
>
> Did this and now the process dies much quicker (without seemingly consume
> all the available memory)... Also I can not launch thunderbird anymore with
> this setting...
>

Yes, turning off overcommit doesn't play with graphical environments, in my
experience. But a production database probably shouldn't be running on a
system like that. On non-prod systems, you can either turn it off
temporarily, or you could try to catch the problem before it becomes fatal
and get the log with pg_log_backend_memory_contexts.

> Anyway, I also reduced work_mem to 128Mb
>
> You can find the corresponding logs attached.
>
We can see what the problem is (over 137,000 concurrent tuple sorts), but
we can't tell what is ultimately causing it. You will need to dig into, or
disclose, the contents of the procedure.

Cheers,

Jeff

>