Subject: Re: [DOC] Add detail regarding resource consumption wrt max_connections

From: Cary Huang <[email-masked]>
Date: Fri, 12 Jan 2024 22:14:38 +0000

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

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.

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. 

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.

thank you

-----------------------
Cary Huang
Highgo Software Canada
www.highgo.ca

---

From: Roberto Mello <[email-masked]>
Date: Sat, 13 Jan 2024 10:31:42 -0700

On Fri, Jan 12, 2024 at 3:15 PM Cary Huang <[email-masked]> 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.
>
> 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.
>
> 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.
>

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.

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.

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

Sincerely,

Roberto

---

From: [email-masked]
Date: Fri, 19 Jan 2024 17:37:33 -0500

On Sat, 2024-01-13 at 10:31 -0700, Roberto Mello 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.
> > 
> > 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. 
> > 
> > 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.
> > 
> 
> 
> 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. 
> 
> 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.
> 

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

start postgresql with varying max_connections to get
CreateSharedMemoryAndSemaphores() sizes to generate a rough equation

postgresql-12-main.log

max_connections=100
75:2024-01-19 17:04:56.544 EST [2762535] DEBUG: invoking
IpcMemoryCreate(size=149110784)
0.149110784GB

max_connections=10000
1203:2024-01-19 17:06:13.502 EST [2764895] DEBUG: invoking
IpcMemoryCreate(size=644997120)
0.64499712GB

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

max_connections=50000
2331:2024-01-19 17:07:27.716 EST [2767079] DEBUG: invoking
IpcMemoryCreate(size=2591490048)
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 = 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 = CalculateShmemSize(&numSemas);
186: elog(DEBUG3, "invoking IpcMemoryCreate(size=%zu)", size);
187-
188- /*
189- * Create the shmem segment
190- */
191- seghdr = 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- */

---

From: [email-masked]
Date: Mon, 22 Jan 2024 08:58:23 -0500

On Fri, 2024-01-19 at 17:37 -0500, [email-masked] wrote:
> > would be better if there was a "rule of thumb" guideline to go
> > along. I'm open to suggestions.
> > 

or maybe it would be sufficient to advise to set log_min_messages =
debug3 on a test DB and start/stop it with varying values of
max_connections and look at the differing values in 
DEBUG: invoking IpcMemoryCreate(size=...) log messages for themselves.