When tuning the datasource connection pool, the following parameters should be considered
- the application's concurrency requirement,
- the average time taken to run a database query
- how many connections the database server can support
The goal for tuning the pool properties is to maintain a pool which is large enough to handle the peek load to the datasource but it should not utilize resources unnecessarily. The following properties are the highly used and important.
| Property Name | How to tune |
|---|---|
| Min. Idle | Configure this to expected minimum concurrency. The default value is 0. |
| Max. Idle | The value should be less that the Max. Active value. For high performance tune Max. Idle to the number of average concurrent requests to the pool. If this value is set to a large value, the pool will contain lot of idle connections unnecessarily. |
| Max. Active | If the Max. Active value = M The peek concurrency value = P Time taken to complete to process a query = t Then the maximum latency (approximately) = (P/M)t Therefore, by increasing the Max. Active value ( up to the expected highest number of concurrency), the time that requests wait in the queue for a connection to be released will decrease. But before increase the Max. Active value, consult the database administrator as it creates a tread off by taking up system resources unnecessarily. |
| Max. Wait | Adjust this value to a value slightly higher than the maximum latency for a request ie. Max. Wait = (P/M)t + buffer time |
| Validation Query | Set Validation Query to a simple test query like SELECT 1 |
| Test On Borrow | When the connection to the database is broken, the connection pool does not know that the connection has been lost. As a result, the connection pool still continue to distribute connections to the application until the application actually tries to use the connection. To resolve this problem set Test On Borrow to true and make sure that the Validation Query property is set |
| validationInterval | This parameter allows to control how frequently a given validation query is executed. By default it is set to 30 seconds. Deciding the value for the validationInterval property is bit tricky. If a larger values is set the frequency that the Validation Query is executed is low and hence we can gain better performance, while with smaller values a stale connection being presented will be identified quickly. Therefore, selecting a value for the this property is a trade-off and ultimately depends on what is acceptable for the application |
Apart from tuning the pool the following properties are used to diagnose the pool when there is an connection leak observed
| Property Name | How to configure |
|---|---|
| Remove Abandoned | Set this property to true if we wish to detect leaked connections |
| Remove Abandoned Timeout | The value should be set to the longest running query the applications might have |
| Log Abandoned | Set this property to true if we wish to log when the connection was abandoned. If this option is set to true, a stack trace is recorded during the dataSource.getConnection call and is printed when a connection is not returned. |