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 1rQxW8-004TMH-Io for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Jan 2024 22:39:17 +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 1rQxW5-00BSng-U1 for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Jan 2024 22:39:13 +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 1rQxW5-00BSnY-BI for pgsql-hackers@lists.postgresql.org; Fri, 19 Jan 2024 22:39:13 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rQxVx-002Ntb-Mo for pgsql-hackers@lists.postgresql.org; Fri, 19 Jan 2024 22:39:11 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-5fa4b03f3f5so13086627b3.1 for ; Fri, 19 Jan 2024 14:39:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=crunchydata.com; s=google; t=1705703945; x=1706308745; darn=lists.postgresql.org; h=user-agent:mime-version:date:references:in-reply-to:cc:to:from :subject:message-id:from:to:cc:subject:date:message-id:reply-to; bh=U/MsLMt3yNFvxfntgkh1DiVKuUhsSuvNat4TELqbug0=; b=c8a7k/w9qKXb3zlDJQnh72PTTaf5yi9XZF8AIpdLVlmIwWsBYiGbOYd2oPZT6tKz0f A7lLJO66G4r3NEH0uI379J+TFV5KDgQHEZYezgGrVMEvJUvGDg+pcv6XFxJ4WMTs7trF 5lwdg82u3+VWVzp2F0RciWu+x5gJIM3QYjKjjEJmLYddmUaIHQnzRJmsIGzIPznT/HGx TI/Bfjzf3pncD9ODuyxF2208N8iCtXTS4OG3F0l7w43lRt0J2h2QBU7s4d3oGS4gpzz0 a7qtAx44ssXVImL62A98UEBNJ99waTauYusLhBVSvSKz3/Q0OgTgtNU+E4CO2lIrZqJ6 wDfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705703945; x=1706308745; h=user-agent:mime-version:date:references:in-reply-to:cc:to:from :subject:message-id:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=U/MsLMt3yNFvxfntgkh1DiVKuUhsSuvNat4TELqbug0=; b=nat+NyPc3AyUdNaa++U2Jq3P+tFfWAd4EOLP3qA43VsBrtwE3t001Q5pog2puu630Z vM/l2TVR6cGN865srPdhFaFcJy00xMNRurgWEKvp8KGq3k6wloPF0jTBmt4SjnYhnRa0 OZSGXhvZiRV4hJxLecMCm3jdwYHD/ntSM54W7u9EYuOV1WczzKmmlx3sC3dCcmaV9OOk c9kuIQ/7dGIbVrl3Tve5qKwM8ihAUGxZMNYI2AKP/wVgQDuGowYS3LCl85Pa2e6sBlVq ij2t1zbqUsvdYt7aQ6U6J542p4gF08RNM73zU11RB51Q+rqDY2AcJR5He2fHgxOHdhJ/ A1EA== X-Gm-Message-State: AOJu0Yw2CNbLv+21TunjoA4babTtEIus6F6KI5+YduqSmnCcu3YRHU4e 70GRB1NiswLluWHFfwwXtM1Bu2XgKORS4stLlYZmwci46fDoIKFb+IRn/WTqgYM= X-Google-Smtp-Source: AGHT+IGotxUdd4nOykTP2VDuIjFaQECMjF8qwAoXwlG8eUM+GFNNq0AWakEp8fvCufzg1dYhC8XfOg== X-Received: by 2002:a25:ef11:0:b0:dc2:4433:f9d3 with SMTP id g17-20020a25ef11000000b00dc24433f9d3mr565305ybd.66.1705703944848; Fri, 19 Jan 2024 14:39:04 -0800 (PST) Received: from raker.lan (024-163-071-136.res.spectrum.com. [24.163.71.136]) by smtp.gmail.com with ESMTPSA id 10-20020a25060a000000b00dc2328c28ebsm64595ybg.51.2024.01.19.14.39.04 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 19 Jan 2024 14:39:04 -0800 (PST) Message-ID: <17c31b30041187a577eb5b45090a58c5fae6418c.camel@crunchydata.com> Subject: Re: [DOC] Add detail regarding resource consumption wrt max_connections From: reid.thompson@crunchydata.com To: Roberto Mello , Cary Huang Cc: pgsql-hackers@lists.postgresql.org In-Reply-To: References: <170509767861.1131.15083983626399995184.pgcf@coridan.postgresql.org> Content-Type: multipart/alternative; boundary="=-tm+clwpdLr5i9ZsDbArW" Date: Fri, 19 Jan 2024 17:37:33 -0500 MIME-Version: 1.0 User-Agent: Evolution 3.44.4-0ubuntu2 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-tm+clwpdLr5i9ZsDbArW Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, 2024-01-13 at 10:31 -0700, Roberto Mello wrote: > On Fri, Jan 12, 2024 at 3:15=E2=80=AFPM Cary Huang > wrote: > > I think it is good to warn the user about the increased allocation > > of memory for certain parameters so that they do not abuse it by > > setting it to a huge number without knowing the consequences. > >=20 > > It is true that max_connections can increase the size of proc array > > and other resources, which are allocated in the shared buffer, > > which also means less shared buffer to perform regular data > > operations. I am sure this is not the only parameter that affects > > the memory allocation. "max_prepared_xacts" can also affect the > > shared memory allocation too so the same warning message applies > > here as well. Maybe there are other parameters with similar > > effects.=20 > >=20 > > Instead of stating that higher max_connections results in higher > > allocation, It may be better to tell the user that if the value > > needs to be set much higher, consider increasing the > > "shared_buffers" setting as well. > >=20 >=20 >=20 > Appreciate the review, Cary. >=20 > My goal was to inform the reader that there are implications to > setting max_connections higher. I've personally seen a user > mindlessly set this to 50k connections, unaware it would cause > unintended consequences.=20 >=20 > I can add a suggestion for the user to consider increasing > shared_buffers in accordance with higher max_connections, but it > would be better if there was a "rule of thumb" guideline to go along. > I'm open to suggestions. >=20 > I can revise with a similar warning in max_prepared_xacts as well. >=20 > Sincerely, >=20 > Roberto Can a "close enough" rule of thumb be calculated from: postgresql.conf -> log_min_messages =3D debug3 start postgresql with varying max_connections to get CreateSharedMemoryAndSemaphores() sizes to generate a rough equation postgresql-12-main.log max_connections=3D100 75:2024-01-19 17:04:56.544 EST [2762535] DEBUG: invoking IpcMemoryCreate(size=3D149110784) 0.149110784GB max_connections=3D10000 1203:2024-01-19 17:06:13.502 EST [2764895] DEBUG: invoking IpcMemoryCreate(size=3D644997120) 0.64499712GB max_connections=3D20000 5248:2024-01-19 17:24:27.956 EST [2954550] DEBUG: invoking IpcMemoryCreate(size=3D1145774080) 1.14577408GB max_connections=3D50000 2331:2024-01-19 17:07:27.716 EST [2767079] DEBUG: invoking IpcMemoryCreate(size=3D2591490048) 2.591490048GB from lines 184-186 $ rg -B28 -A35 'invoking IpcMemoryCreate' backend/storage/ipc/ipci.c 158-/* 159- * CreateSharedMemoryAndSemaphores 160- * Creates and initializes shared memory and semaphores. 161- * 162- * This is called by the postmaster or by a standalone backend. 163- * It is also called by a backend forked from the postmaster in the 164- * EXEC_BACKEND case. In the latter case, the shared memory segment 165- * already exists and has been physically attached to, but we have to 166- * initialize pointers in local memory that reference the shared structures, 167- * because we didn't inherit the correct pointer values from the postmaster 168- * as we do in the fork() scenario. The easiest way to do that is to run 169- * through the same code as before. (Note that the called routines mostly 170- * check IsUnderPostmaster, rather than EXEC_BACKEND, to detect this case. 171- * This is a bit code-wasteful and could be cleaned up.) 172- */ 173-void 174-CreateSharedMemoryAndSemaphores(void) 175-{ 176- PGShmemHeader *shim =3D NULL; 177- 178- if (!IsUnderPostmaster) 179- { 180- PGShmemHeader *seghdr; 181- Size size; 182- int numSemas; 183- 184- /* Compute the size of the shared-memory block */ 185- size =3D CalculateShmemSize(&numSemas); 186: elog(DEBUG3, "invoking IpcMemoryCreate(size=3D%zu)", size); 187- 188- /* 189- * Create the shmem segment 190- */ 191- seghdr =3D PGSharedMemoryCreate(size, &shim); 192- 193- InitShmemAccess(seghdr); 194- 195- /* 196- * Create semaphores 197- */ 198- PGReserveSemaphores(numSemas); 199- 200- /* 201- * If spinlocks are disabled, initialize emulation layer (which 202- * depends on semaphores, so the order is important here). 203- */ 204-#ifndef HAVE_SPINLOCKS 205- SpinlockSemaInit(); 206-#endif 207- } 208- else 209- { 210- /* 211- * We are reattaching to an existing shared memory segment. This 212- * should only be reached in the EXEC_BACKEND case. 213- */ 214-#ifndef EXEC_BACKEND 215- elog(PANIC, "should be attached to shared memory already"); 216-#endif 217- } 218- 219- /* 220- * Set up shared memory allocation mechanism 221- */ --=-tm+clwpdLr5i9ZsDbArW Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
On Sat, 2024-01-13 at 10:31 -0700, Roberto Me= llo wrote:
On Fri, Jan 12, 2024 at 3:15=E2=80=AFPM Cary Huang <cary.huang@highgo.ca> wrote:
I think it is good to warn= the user about the increased allocation of memory for certain parameters s= o that they do not abuse it by setting it to a huge number without knowing = the consequences.

It is true that max_connections can inc= rease the size of proc array and other resources, which are allocated in th= e shared buffer, which also means less shared buffer to perform regular dat= a operations. I am sure this is not the only parameter that affects the mem= ory allocation. "max_prepared_xacts" can also affect the shared memory allo= cation too so the same warning message applies here as well. Maybe there ar= e other parameters with similar effects.

Instead of stat= ing that higher max_connections results in higher allocation, It may be bet= ter to tell the user that if the value needs to be set much higher, conside= r increasing the "shared_buffers" setting as well.

=

Appreciate the review, Cary.
My goal was to inform the reader that there are implications to= setting max_connections higher. I've personally seen a user mindlessly set= this to 50k connections, unaware it would cause unintended consequences. <= br>

I can add a suggestion for the user to conside= r increasing shared_buffers in accordance with higher max_connections, but = it would be better if there was a "rule of thumb" guideline to go along. I'= m open to suggestions.

I can revise with a similar= warning in max_prepared_xacts as well.

Sincerely,=

Roberto
Can a "close enough" rule of thumb be calculated from:
postgresql.conf -> log_min_messages =3D debug3

start postgresql with varying max_connections to get CreateSharedMemoryA= ndSemaphores() sizes to generate a rough equation

= postgresql-12-main.log

max_connections=3D100<= /div>
75:2024-01-19 17:04:56.544 EST [2762535] DEBUG: invoking Ip= cMemoryCreate(size=3D149110784)
0.149110784GB

max_connections=3D10000
1203:2024-01-19 17:06:13= .502 EST [2764895] DEBUG: invoking IpcMemoryCreate(size=3D644997120)
<= div>0.64499712GB

max_connections=3D20000
5248:2024-01-19 17:24:27.956 EST [2954550] DEBUG: invoking= IpcMemoryCreate(size=3D1145774080)
1.14577408GB

max_connections=3D50000
2331:2024-01-19 17:07:27.716 EST [2767079] DEBUG: invoking IpcM= emoryCreate(size=3D2591490048)
2.591490048GB

=

from lines 184-186

$ rg -B28 -= A35 'invoking IpcMemoryCreate'
backend/storage/ipc/ipci.c
158-/*
159- * CreateSharedMemoryAndSemaphores
160- *= Creates and initializes shared memory and semaphores.
161- *
162- * This is called by the postmaster or by a standalone backend.<= /div>
163- * It is also called by a backend forked from the postmaster = in the
164- * EXEC_BACKEND case. In the latter case, the shared m= emory segment
165- * already exists and has been physically attac= hed to, but we have to
166- * initialize pointers in local memory= that reference the shared structures,
167- * because we didn't i= nherit the correct pointer values from the postmaster
168- * as w= e do in the fork() scenario. The easiest way to do that is to run
169- * through the same code as before. (Note that the called routines mos= tly
170- * check IsUnderPostmaster, rather than EXEC_BACKEND, to = detect this case.
171- * This is a bit code-wasteful and could be= cleaned up.)
172- */
173-void
174-CreateShar= edMemoryAndSemaphores(void)
175-{
176- PGShmemHeader *s= him =3D NULL;
177-
178- if (!IsUnderPostmaster)
179- {
180- PGShmemHeader *seghdr;
181- Size size;
182- int numSemas;
183-
184- /* Compute the si= ze of the shared-memory block */
185- size =3D CalculateShmemSize= (&numSemas);
186: elog(DEBUG3, "invoking IpcMemoryCreate(size= =3D%zu)", size);
187-
188- /*
189- * Create t= he shmem segment
190- */
191- seghdr =3D PGSharedMemory= Create(size, &shim);
192-
193- InitShmemAccess(segh= dr);
194-
195- /*
196- * Create semaphores
197- */
198- PGReserveSemaphores(numSemas);
199= -
200- /*
201- * If spinlocks are disabled, initialize = emulation layer (which
202- * depends on semaphores, so the order= is important here).
203- */
204-#ifndef HAVE_SPINLOCKS=
205- SpinlockSemaInit();
206-#endif
207- }
208- else
209- {
210- /*
211- * We a= re reattaching to an existing shared memory segment. This
212- * = should only be reached in the EXEC_BACKEND case.
213- */
214-#ifndef EXEC_BACKEND
215- elog(PANIC, "should be attached t= o shared memory already");
216-#endif
217- }
= 218-
219- /*
220- * Set up shared memory allocation mec= hanism
221- */

<= br>
--=-tm+clwpdLr5i9ZsDbArW--