Message-ID: <65294283.050a0220.4c49b.0ac4@mx.google.com>
Sender: Michael Banck <mbanck@gmail.com>
Date: Fri, 13 Oct 2023 15:13:38 +0200
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
References: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/65294283.050a0220.4c49b.0ac4%40mx.google.com>
Precedence: bulk

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