Re: Connection pooling - Number of connections

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 think it is a good idea to have 10 idle connections but a
maxPoolSize of 100, the reality is, they can retire/reuse connections faster
with a much smaller maxPoolSize. And I didn't see a pool of a few 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.

-Brett

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-Number-of-connections-tp5797025p5797135.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.