Message-ID: <be5fcc3e5d6e75721c97263e59891c8a0f560406.camel@cybertec.at>
Subject: Re: work_mem RAM Accounting in PostgreSQL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alexandru Lazarev <alexandru.lazarev@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Date: Mon, 18 Nov 2024 19:57:18 +0100
In-Reply-To: <CAL93h0GSqz99bJmdxSZgSsqLLj_=HkpUvfHSxSbEBe1Zep-+ag@mail.gmail.com>
References: 
	<CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
	 <cec733623111083dbd4d35ddc3950be9c02968cf.camel@cybertec.at>
	 <CAL93h0GSqz99bJmdxSZgSsqLLj_=HkpUvfHSxSbEBe1Zep-+ag@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/be5fcc3e5d6e75721c97263e59891c8a0f560406.camel%40cybertec.at>
Precedence: bulk

On Mon, 2024-11-18 at 18:11 +0200, Alexandru Lazarev wrote:
> Highly appreciate=C2=A0your response. But I have some additional question=
s (inline)
>=20
> On Thu, Nov 14, 2024 at 4:40=E2=80=AFPM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> > On Wed, 2024-11-13 at 21:09 +0200, Alexandru Lazarev wrote:
> >=20
> > > My questions are as follows:
> > >=20
> > > 1. What happens to the allocated "work_mem" after the execution of qu=
ery nodes? Are
> > > =C2=A0 =C2=A0 these memory allocations freed?
> >=20
> > Yes.
> >=20
> > > 2. If they are freed, do they remain in the RSS of the PostgreSQL bac=
kend?
> >=20
> > They may, because the C library can choose not to actually free all the=
 memory,
> > but retain some to serve future malloc() requests more efficiently.
>=20
> This part is important for me to understand (my apologies, I am not a C p=
rogrammer
> and for me it is difficult reading PG sources :)): I wanted to understand=
 if in this part
> there isn't some kind of allocated memory pooling in postgres. So, since =
some memory is
> freed then it is up to C Library and underlying OS if it will be returned=
 back to OS or
> will stay somehow reserved - did I get it correctly? If so, then most pro=
bably this
> "reserved" memory=C2=A0should be reclaimed under memory pressure conditio=
ns (other backends
> processes try greedely allocate big chunks of memory)?

Idle PostgreSQL backends won't keep moch memory allocated under normal cond=
itions:
the catalog cache, prepared statements, private memory to buffer temporary =
tables,
potentially a materialized WITH HOLD cursor.

Yes, it is up to the C library to keep some free'd memory allocated for reu=
se.
This memory won't be reclaimed when there is memory pressure, since it belo=
ngs to
the process.

> >=20
> >=20
> > The backends won't keep that much memory allocated, so you need not wor=
ry.
>=20
> Let me reformulate the use-case a bit differently - I would highly apprec=
iate=C2=A0any community inputs:=C2=A0
> 1) Let say 60 connections did some intensive memory consuming operations,=
 each one
>    allocating up to 200MB of work_mem, then they finished and returned to=
 'idle' state.

The lion's share of that memory will be freed when processing is done.

> 2) After that the rest of 40 connections starting doing "work_mem" consum=
ing operations
>    in parallel, each one allocating up to 300MB, then will N*"work_mem" a=
llocated RAM
>    from step#1 be reclaimed at point of step#2 when multiple backend need=
 to allocate
>    aggressively=C2=A0K*"wor_mem" memory?

Most of the memory is already reclaimed when the first cconnections are don=
e, so you
shouldn't be in trouble.
All this is somehow vague, since it depends on the C library implementation=
.

I recommend that you start tuning with a value for work_mem that is low eno=
ugh
to be very certain that you won't go out of memory.
Then monitor temporary files.  If too many get created, increase work_mem, =
all the
while watching your memory consumption to avoid going OOM.
As soon as only few temporary files are created, you are probably good.
If you cannot get there, try to tune your queries.

The king's way to avoid problems in this area is to use a moderately sized
connection pool.  Then connections don't sit idle for long and cannot hog m=
uch
memory.

Yours,
Laurenz Albe