When tuning the datasource connection pool, the following parameters should be considered.
- the applicationThe application's concurrency requirement,.
- the The average time taken to run a database query.
- how many The number of connections the database server can support.
The goal for of tuning the pool properties is to maintain a pool which that is large enough to handle the peek peak load to the datasource but it should not utilize resources unnecessarilywithout unnecessarily utilising resources. The following properties are the highly used and , important .properties:
| Property Name | How to tune |
|---|---|
| Min. Idle | Configure this to match the 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 match the number of average, concurrent requests to the pool. If this value is set to a large value, the pool will contain lot of unnecessary idle connections unnecessarily.. |
| Max. Active | The maximum latency (approximately) = (P/M)T , where,
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 decreasebe decreases. But before increase increasing the Max. Active value, consult the database administrator, as it creates a tread off by taking takes up system resources unnecessarily. |
| Max. Wait | Adjust this value to a value slightly higher than the maximum latency for a request. ie. Max. That is, the 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 will continue to distribute connections to the application until the application actually tries to use the connection. To resolve this problem set Test , 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 a bit tricky. If a larger values value is set, the frequency that of executing the Validation Query is executed is low and hence we can gain better performance, while with smaller values low, which results in better performance. With a smaller value, a stale connection being presented will be identified quickly when it is presented. 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 a connection leak is observed.
| Property Name | How to configure |
|---|---|
| Remove Abandoned | Set this property to "true" if we you wish to detect leaked connections. |
| Remove Abandoned Timeout | The value should be set to the longest running query that the applications might have. |
| Log Abandoned | Set this property to "true" if we you 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. |