Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tD4Ld-00FeCM-79 for pgsql-general@arkaria.postgresql.org; Mon, 18 Nov 2024 16:11:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tD4Lb-005rIZ-2d for pgsql-general@arkaria.postgresql.org; Mon, 18 Nov 2024 16:11:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tD4La-005rIQ-Is for pgsql-general@lists.postgresql.org; Mon, 18 Nov 2024 16:11:31 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tD4LX-002Y2Y-Te for pgsql-general@lists.postgresql.org; Mon, 18 Nov 2024 16:11:29 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-e382661fb79so2645103276.0 for ; Mon, 18 Nov 2024 08:11:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731946287; x=1732551087; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=r9itXnZL0w3zrODzmYXX37910ZiC+Hy4HgAxlF4zbME=; b=NIa/zzPsdQ8YA2RUazAUvHno2/l2iADymTl1afqzZ+HW9+oOdWuK5yHRVaCaZ0T44z S7cHeAGljbl7IUqyT5YV3R6WNWlDtl+Td1Uhj7g0i2o11AUubPFC5R3xtl3LV5Swltpz VuMef+4tb/UwuOCnwZHoZNxlaUeVqM3RWA5gIVrZ8DFGvSwG+wVIz3LSYRiZRe3cdfzD 2vBwsd8g9qNttmoHI4o2JXJZK8PfYZCi7CNRFPdhZd2+PsR87LDV4FXHWueHds5rb3NM yunNdY45Jjsx2sTU4gg6NatQrbylu99qvVuv1iwAaT2LSqie0n0THxf+A16emz99w0Hy plyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731946287; x=1732551087; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=r9itXnZL0w3zrODzmYXX37910ZiC+Hy4HgAxlF4zbME=; b=KFW+uJDeAmq2Aq8+Ek8WNgYetKtQlu40KR/x+hghV/yOK7vUgYm/9R91er/VoL2PLD AOpsu2f+qveu3X5xKMNYd4nwpXnrbqUeGCloRsh8OaqGaRdqHGOXbhH1CcoywaOI745Y RrqENOlHGej5UMQ7sqweRrmQd9DDX8ZKgDNprQr87ch/M3YEAgOOBSzw7kAA9UdG1rnh ifOUv3WNLqsWuIEA/lKd4aLwm/Ud5WIrHykup4gljCMROt5rRuGU9sejQDJ72DutJ2FZ 0+vQGjr1Kbcs5dLCiPx4CdaipISaMvr0/kmRsazfVbw8JFzEy4BMlVvVGvtfTXyBh8AJ a2gQ== X-Gm-Message-State: AOJu0Yw3x8hTrAmnQGl5FfQqmM3wGsOHzX4G66DPmwx5YIc8Sy3rblGM 8orCDJjYbc4WUu+3YinuZuIY44QR1yAZrgfCk0MN2BIiolYhHJbbNMuTVIZG4nKjIfRU2Y1Vxtr Wv/ZYfuvgy4C/YD2sC4RxzAIEsyg= X-Google-Smtp-Source: AGHT+IFJYIvD0QbSZV7DqcJQpWj6ETpFTv6aM1qMtPnvIznsubPnl9VeDQzhNN5NuIUm0bvSzGDCsxVHPs7/hLnIFVM= X-Received: by 2002:a05:690c:7309:b0:6e3:156e:a917 with SMTP id 00721157ae682-6ee55bf0685mr135015847b3.14.1731946286507; Mon, 18 Nov 2024 08:11:26 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Alexandru Lazarev Date: Mon, 18 Nov 2024 18:11:15 +0200 Message-ID: Subject: Re: work_mem RAM Accounting in PostgreSQL To: Laurenz Albe Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002d6f800627322b72" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002d6f800627322b72 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi @Laurenz Albe & PG Community, Highly appreciate your response. But I have some additional questions (inline) On Thu, Nov 14, 2024 at 4:40=E2=80=AFPM Laurenz Albe wrote: > On Wed, 2024-11-13 at 21:09 +0200, Alexandru Lazarev wrote: > > I have some questions regarding how the "work_mem" parameter affects th= e > 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=3D5 (i.e. 5 simultaneou= s > 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. > Yes, I know, I considered it above in "N". > > > My questions are as follows: > > > > 1. What happens to the allocated "work_mem" after the execution of quer= y > 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. > This part is important for me to understand (my apologies, I am not a C programmer 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 probably this "reserved" memory should be reclaimed under memory pressure conditions (other backends processes try greedely allocate big chunks of memory)? > > > 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. > I repeated myself, so Yes, You 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, eac= h > 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= . > Let me reformulate the use-case a bit differently - I would highly appreciate any community inputs: 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. 2) After that the rest of 40 connections starting doing "work_mem" consuming operations in parallel, each one allocating up to 300MB, then will N*"work_mem" allocated RAM from step#1 be reclaimed at point of step#2 when multiple backend need to allocate aggressively K*"wor_mem" memory? Thank You > > 100 connections are a lot. With efficient pooling, you could have fewer > connections > and use your resources more efficiently. > > Yours, > Laurenz Albe > --0000000000002d6f800627322b72 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0@Laurenz Al= be=C2=A0& PG Community,=C2=A0
Highly appreciate=C2=A0your= response. But I have some additional questions (inline)

On Thu, Nov 14, 202= 4 at 4:40=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-11-13 at 21:09 +0200, Alexand= ru 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=3D5 (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.

Yes, I know, I considered it above in "N".
=
=C2=A0

> My questions are as follows:
>
> 1. What happens to the allocated "work_mem" after the execut= ion of query nodes? Are
>=C2=A0 =C2=A0 these memory allocations freed?

Yes.

> 2. If they are freed, do they remain in the RSS of the PostgreSQL back= end?

They may, because the C library can choose not to actually free all the mem= ory,
but retain some to serve future malloc() requests more efficiently.

This part is important for me to understand (m= y apologies, I am not a C programmer and for me it is difficult reading PG = sources :)): I wanted to understand if in this part there isn't some ki= nd 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 mos= t probably this "reserved" memory=C2=A0should be reclaimed under = memory pressure conditions (other backends processes try greedely allocate = big chunks of memory)?
=C2=A0

> 3. From various sources, I understand that these allocations are freed= after
>=C2=A0 =C2=A0 each node execution due to memory contexts, but they migh= t remain in some sort
>=C2=A0 =C2=A0 of backend memory pool for future reuse. Is this correct?=

I am not sure what you mean, but perhaps what I wrote above.

I repeated myself, so Yes, You wrote above.=C2=A0
=C2=A0

> 4. If so, will this memory be accounted for as used RAM on my Linux/Co= ntainer
>=C2=A0 =C2=A0 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 containe= r is limited
> to 16GB of RAM, what would happen if I have 100 pooled connections, ea= ch 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 a= t some point
> (because it is real allocated memory)?

The backends won't keep that much memory allocated, so you need not wor= ry.

Let me reformulate the use-case a b= it differently - I would highly appreciate=C2=A0any community inputs:=C2=A0=
1) Let say 60 connections did some intensive memory consuming op= erations, each one allocating up to 200MB of work_mem, then they finished a= nd returned to 'idle' state.=C2=A0
2) After that the rest= of 40 connections starting doing "work_mem" consuming operations= in parallel, each one allocating up to 300MB, then will N*"work_mem&q= uot; allocated RAM from step#1 be reclaimed at point of step#2 when multipl= e backend need to allocate aggressively=C2=A0K*"wor_mem" memory?<= /div>

Thank=C2=A0You
=C2=A0

100 connections are a lot.=C2=A0 With efficient pooling, you could have few= er connections
and use your resources more efficiently.

Yours,
Laurenz Albe
--0000000000002d6f800627322b72--