From: Michael Banck <mbanck@gmx.net>
To: Jean-Christophe Boggio <postgresql@thefreecat.org>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: One PG process eating more than 40GB of RAM and getting killed by OOM
Date: Fri, 13 Oct 2023 15:13:38 +0200
Message-ID: <65294283.050a0220.4c49b.0ac4@mx.google.com> (raw)
In-Reply-To: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org>
References: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org>
Hi,
On Fri, Oct 13, 2023 at 03:06:57PM +0200, Jean-Christophe Boggio wrote:
> On my dev laptop, I have ~40GB free RAM. When launching a heavy calculation
> in PostgreSQL (within a stored procedure), it consumes as much memory as is
> available and then gets killed by OOM. There is only one connected session.
>
> I have the following settings, which look reasonable (to me):
>
> shared_buffers = 512MB # min 128kB
That's not a lot.
> work_mem = 1GB # min 64kB
On the other hand, that's a lot. So if that query is run in parallel and
has a lot of different things it does, it might use work_mem several
times over, resulting in a lot of used memory.
So first thing to try would be to lower work_mem to someting much lower
like 64MB or 128MB.
> This is PostgreSQL 14.7 running on Ubuntu 23.04
The other thing you can try is to see whether turning jit off (or just
jit_inline_above_cost = -1) is helping.
Michael
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mbanck@gmx.net, postgresql@thefreecat.org, pgsql-admin@lists.postgresql.org
Subject: Re: One PG process eating more than 40GB of RAM and getting killed by OOM
In-Reply-To: <65294283.050a0220.4c49b.0ac4@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox