Well, in the past we have seen many customers who tuned their connection pool improrely and got into troubles. So, in this blog
I will discuss few important connection pool parameters that would help in improving the application performance.
1) Set min/max poolsize parameters with cautions.
Setting very high value for maxPoolSize will allow more requests to go to database. If the database is not capable
of handling high load, then congestion will start building on connection pool and eventually the system will get
stuck. Then the only to recover from this situation is by restarting the application server process.
So, be aware of the ramifications of changing max poolsize. The maxPoolSize of "50" will do for most of the workloads. This setting, ideally,
should be less than the thread pool size given the fact that fraction of web request processing time is spent in jdbc while the rest is used
for executing the business logic.
Though this itself is not a rule, you can try and tune this parameter to get better performance without getting into any trouble.
The minPoolSize parameter ensures that at least those many connections are maintained in the pool. Since database connections
are very expensive, it is always wise to close idle connections which could otherwise be used by some other processes. You can do this by setting
a connection timeout which is the time after which an idle connection is claimed.
When connections are in freepool for a long period of time, there are always chances that these connections get dropped at network layer
(thanks to firewall and other network level settings) without the knowledge of the pool. Later, when this connection is put to use,
it will throw a famous "StaleConnectionException". To avoid this error, adjust agedTimeout so that when a connection is idle(i.e in freepool)
for very long time it will be claimed. This setting overrides the minimum connection poolsize in that this setting would make pool size fall
below minimum connection pool size.
This is another important parameter, tuned correctly, will enhance the application performance by a modest 20%-30%. This parameter will lead
the most used prepared statement objects to be cached so that they need not be recreated every time application requests for it. Since the
objects are cached, it will cut into heap space. And another important thing to be noted is the cache is maintained per connection. So, if you
have a poolsize of m and cache size of n, then the total cache size becomes 'm X n'.
Monitor the queries being executed against database from this server and count the queries that are executed frequently. This will help you
in tuning the size of the cache.