Re: Connection pooling - Number of connections

On 25/03/14 02:27, Brett Wooldridge wrote:
> Hi, Brett Wooldridge here, one of the principals of HikariCP. I
> thought I'd wade into the conversation pool a little myself if you
> guys don't mind.
>
> Speaking to David's point...
> >> Reaching the maxPoolSize from the minPoolSize means creating the
> >> connections at the crucial moment where the client application is in the
> >> desperate need of completing an important query/transaction which the
> >> primary responsibility since it cannot hold the data collected.
>
> 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.
>
> As I think Tom noted is a slidestack I read somewhere, there is a
> "knee" in the performance curve beyond which additional connections
> cause a drop in TPS. While users often think it is a good idea to
> have maxPoolSize of 100, the reality is they can retire/reuse
> connections faster with a much smaller pool. I didn't see a pool of a
> 2 or 3 dozen connections actually impacting performance much when half
> of them are idle and half are executing transactions (ie. the idle
> ones don't impact the overall performance much).
>
> Finally, one of my contentions was, either your database server has
> resources or it doesn't. Either it has enough memory and processing
> power for N connections or it doesn't. If the pool is set below,
> near, or at that capacity what is the purpose of releasing connections
> in that case? Yes, it frees up memory, but that memory is not really
> available for other use given that at any instant the maximum capacity
> of the pool may be demanded.
> Instead releasing resources only to try to reallocate them during a
> demand peak seems counter-productive.
>
> I'd appreciate any shared thoughts on my presuppositions.
>
> -Brett
>
Surely no code changes are required, as one can simply set the min and
max pool sizes to be the same?

Cheers,
Gavin