Re: Fwd: sensible configuration of max_connections

On Fri, Feb 7, 2020 at 5:36 AM Steve Atkins <steve(at)blighty(dot)com> wrote:

> What's a good number of active connections to aim for? It probably depends
> on whether they tend to be CPU-bound or IO-bound, but I've seen the rule of
> thumb of "around twice the number of CPU cores" tossed around, and it's
> probably a decent place to start, then run it under normal load and see how
> it behaves - cpu usage, RAM, IO, request latency and throughput.
>
>
> Back in the day of spinning media, when I was more active on the list and
postgresql 9 was just released, conventional wisdom for starting number was
2*cores + 1*spindles, if memory serves. You can set max_connections higher,
but that was the number you wanted to have active, and then adjust for
workload - OLTP vs warehouse, how much disk access vs buffer cache, etc.
Benchmarks, at the time, showed that performance started to fall off due to
contention if the number of processes got much larger. I imagine that the
speed of storage today would maybe make 3 or 4x core count a pretty
reasonable place to start. There will be a point of diminishing returns
somewhere, but you can probably construct your own benchmarks to determine
where that point is likely to be for your workload.

I was doing a lot of java development at the time, and tended to use a
connection pool per application server rather than an external connection
pool in front of postgresql, just for ease of administration, so I might
have more connections than the desired pool size, but I tried to keep the
number of active connections under that limit and set max_connections to a
value that prevented me from being locked out if each application server
was at max pool size. I'm not sure how well that strategy would work now
that autoscaling is so ubiquitous, since there is memory allocation
overhead associated even with idle connections and the sheer number of
per-server pools could get quite high