Fwd: sensible configuration of max_connections - Mailing list pgsql-general

From Chris Ellis
Subject Fwd: sensible configuration of max_connections
Date
Msg-id CAF0QPmjW85wFZ=44C3rxBskNG4H9iqui7eqAdAatmNAmrcFCMA@mail.gmail.com
Whole thread Raw
In response to sensible configuration of max_connections  (Chris Withers <chris@withers.org>)
Responses Re: Fwd: sensible configuration of max_connections  (Chris Withers <chris@withers.org>)
Re: sensible configuration of max_connections  (Justin <zzzzz.graf@gmail.com>)
List pgsql-general
Hi Chris

On Fri, 7 Feb 2020, 08:36 Chris Withers, <chris@withers.org> wrote:
Hi All,

What's a sensible way to pick the number to use for max_connections?

Sensible in this context is some what variable.  Each connection in PostgreSQL will be allocated a backend process.  These are not the lightest weight of things.

Each connection takes up space in shared memory, as mentioned in the manual.


I'm looking after a reasonable size multi-tenant cluster, where the
master handles all the load and there's a slave in case of hardware
failure in the master.
The machine is used to host what I suspect are mainly django
applications, so lots of short web requests, not sure how much, if any,
django's orm does connection pooling.

I arbitrarily picked 1000 for max_connections and haven't had any
problems, but onboarding another app that handles a few million requests
per day  on Monday and thinking about potential problems related to the
number of available connections.

What's "too much" for max_connections? What happens when you set it to
high? What factors affect that number?

When sizing max_connections you need to trade off how many connections your application will use at peak vs how much RAM and CPU you have.

Each connection is capable of allocating work_mem and has a stack etc.

As such you don't want max_connections to be able to run your system out of RAM.

Given your situation I'd very seriously look at connection pooling using PgBouncer or similar.  That way you can run with a far smaller max_connections and still cope with applications configured with large usually idle connection pools.


cheers,

Chris

Regards,
Chris Ellis

pgsql-general by date:

Previous
From: Chris Withers
Date:
Subject: sensible configuration of max_connections
Next
From: Chris Withers
Date:
Subject: Re: Fwd: sensible configuration of max_connections