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

On Fri, Mar 8, 2024 at 9:52 AM Robert Treat <rob(at)xzilla(dot)net> wrote:
> 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.

I agree with this.

I don't agree with Cary's statement that if you increase
max_connections you should increase shared_buffers as well. That seems
situation-dependent to me, and it's also missing Roberto's point,
which is that JUST increasing max_connections without doing anything
else uses more shared memory.

Similarly, I don't think we need to document a detailed testing
procedure, as proposed by Reid. If users want to know exactly how many
additional resources are used, they can test; either using the DEBUG3
approach, or perhaps more simply via the pg_shmem_allocations view.
But I think it's overkill for us to recommend any specific testing
procedure here.

Rather, I think that it's entirely appropriate to do what Roberto
suggested, which is to say, let users know that they're going to use
some extra resources if they increase the setting, and then let them
figure out what if anything they want to do about that.

--
Robert Haas
EDB: http://www.enterprisedb.com