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

On Mon, Jan 22, 2024 at 8:58 AM <reid(dot)thompson(at)crunchydata(dot)com> wrote:
> On Fri, 2024-01-19 at 17:37 -0500, reid(dot)thompson(at)crunchydata(dot)com wrote:
> > On Sat, 2024-01-13 at 10:31 -0700, Roberto Mello wrote:
> > >
> > > 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
> >
> > 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
> >
>
> 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.
>
>

I'm of the opinion that advice suggestingDBA's set things to DEBUG 3
is unfriendly at best. If you really want to add more, there is an
existing unfriendly section of the docs at
https://www.postgresql.org/docs/devel/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
that mentions this problem, specifically:

"If PostgreSQL itself is the cause of the system running out of
memory, you can avoid the problem by changing your configuration. In
some cases, it may help to lower memory-related configuration
parameters, particularly shared_buffers, work_mem, and
hash_mem_multiplier. In other cases, the problem may be caused by
allowing too many connections to the database server itself. In many
cases, it may be better to reduce max_connections and instead make use
of external connection-pooling software."

I couldn't really find a spot to add in your additional info, but
maybe you can find a spot that fits? Or maybe a well written
walk-through of this would make for a good wiki page in case people
really want to dig in.

In any case, I think Roberto's original language is an improvement
over what we have now, so I'd probably recommend just going with that,
along with a similar note to max_prepared_xacts, and optionally a
pointer to the shared mem section of the docs.

Robert Treat
https://xzilla.net