public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Alexandru Lazarev <[email protected]>
To: [email protected]
Subject: Re: work_mem RAM Accounting in PostgreSQL
Date: Thu, 14 Nov 2024 15:40:00 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
References: <CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
On Wed, 2024-11-13 at 21:09 +0200, Alexandru Lazarev wrote:
> I have some questions regarding how the "work_mem" parameter affects the overall RAM
> usage of PostgreSQL processes within a physical host or container.
>
> Each backend process during SQL execution may allocate N * "work_mem" simultaneously.
> For example, if "work_mem" is set to 32MB and N=5 (i.e. 5 simultaneous and/or sequential
> ORDER and hash operations), and the initial RAM usage (RSS - Resident Set Size) of the
> backend is 10MB, I would expect the backend process to use 160MB (32MB * 5) + 10MB,
> resulting in a total RAM usage of 170MB.
The limit for a hash is hash_mem_multiplier * work_mem.
> My questions are as follows:
>
> 1. What happens to the allocated "work_mem" after the execution of query nodes? Are
> these memory allocations freed?
Yes.
> 2. If they are freed, do they remain in the RSS of the PostgreSQL backend?
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.
> 3. From various sources, I understand that these allocations are freed after
> each node execution due to memory contexts, but they might remain in some sort
> of backend memory pool for future reuse. Is this correct?
I am not sure what you mean, but perhaps what I wrote above.
> 4. If so, will this memory be accounted for as used RAM on my Linux/Container
> system after the backend returns to an idle state (e.g., connection pooling)?
Certainly.
> Additionally: If the above is true, and my PostgreSQL host or container is limited
> to 16GB of RAM, what would happen if I have 100 pooled connections, each gradually
> allocating those 160MB? Will this memory be reclaimed (if I understood it correctly
> as a kind of inactive anon mem), or will the OOM Killer be triggered at some point
> (because it is real allocated memory)?
The backends won't keep that much memory allocated, so you need not worry.
100 connections are a lot. With efficient pooling, you could have fewer connections
and use your resources more efficiently.
Yours,
Laurenz Albe
view thread (3+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: work_mem RAM Accounting in PostgreSQL
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox