Re: Connection pooling - Number of connections

Sent from my iPhone
> On Mar 27, 2014, at 9:35 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> 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.
>
> 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.

In the end we've gone with a maxPoolSize + minIdle model where the default is that they are equal (fixed pool).

Though I won't dispute that such workloads (1000 active connections) exist, in that someone created it, I would love to hear their justification. Unless they have >128 CPU cores and solid state storage they are basically spinning their wheels.

> 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.

We do this, there is a maxLifeTime setting to rotate out connections.

-Brett