Re: Connection pooling - Number of connections

On 03/24/2014 06:27 AM, Brett Wooldridge wrote:
> This was one of the reasons I was proposing the fixed pool design. In my
> experience, even in pools that maintain a minimum number of idle
> connections, responding to spike demands is problematic. If you have a
> pool with say 30 max. connections, and a 10 minimum idle connection goal, a
> sudden spike demand for 20 connections means the pool can satisfy 10
> instantly but then is left to [try to] establish 10 connections before the
> application's connectionTimeout (read acquisition timeout from the pool) is
> reached. This in turn generates a spike demand on the database slowing
> down not only the connection establishments themselves but also slowing
> down the completion of transactions that might actually return connections
> to the pool.

So what makes sense really depends on what your actual connection
pattern is. Idle connections aren't free; aside from PostgreSQL
lock-checking overhead, they hold on to any virtual memory allocated to
them when they were working. In the aggregate, this can add up to quite
a bit of memory, which can then cause the OS to decide not to cache some
data you could really use.

Now, if your peak is 100 connections and your median is 50, this doesn't
signify. But I know more than a few workloads where the peak is 1000
and the median is 25, and in that case you want to drop the idle
connections gradually. The key is to keep enough of a buffer of ready
connections to deal with the next peak when it comes in.

That also means that even if the pool is a fixed size, you want to
rotate in and out the actual sessions, so that they don't hang onto
maximum virtual memory indefinitely.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com