Hi,
I have already read a few threads here and the wM documentation too regarding the performance issues when Minimum Pool Size is set to 0 or 1 when creating a new JDBC connection for an Oracle DB, say.
My question is,
Is there any performance issue,
Case 1: when value is set to 0
Case 2: when value is set to 1
Could anyone please elaborate more (performance-wise but not just the definition please) as i’m a bit confused.
Thanks,
Hope.
Performance-wise a min connection > 0 will be “better” for the first set of transactions that is processed in IS using those connections. That is because the connections to the DB will already be established–the time cost of connecting and logging in was incurred during start-up rather than when a JDBC service was invoked.
With a min size of 0, the first JDBC service that is invoked will be delayed a bit while the connection is made and log-in accomplished. After that, there is no overhead (for that connection–each connection up to max pool size will connect and log-in when multiple connections are needed).
An idle pool will drop the connections back to 0 at some point, and then the next JDBC services will incur the connect/log-in overhead again.
For busy pools, the actual connections will not go back to 0. They will be maintained and connection/log-in overhead avoided.
The biggest problem with a min connection > 0 is the potential for stale sessions. This can cause a JDBC service failure if a session is kept and is idle long enough for the DB to close the session or if the DB is restarted.
As usual, it is a trade-off. You want to strike a balance between keeping too many idle connections (wasteful and risks stale connections) and establishing connections too often (time delay and impact on the DB server). It is likely rare that a value other than 0 should/must be used.
That’s an awesome description Rob. Thank you.
Hope.