...
| Property | Description | Tuning Recommendations |
|---|---|---|
| maxActive | The maximum number of active connections that can be allocated from the connection pool at the same time. The default value is | The maximum latency (approximately) = (P / M) * T , where,
Therefore, by increasing the maxActive 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 increasing the Max. Active value, consult the database administrator, as it will create up to maxActive connections from a single node during peak times, and it may not be possible for the DBMS to handle the accumulated count of these active connections. Note that this value should not exceed the maximum number of requests allowed for your database. |
| maxWait | The maximum time that requests are expected to wait in the queue for a connection to be released. This property comes into effect when the maximum number of active connections allowed in the connection pool (see maxActive property) is used up. | Adjust this to a value slightly higher than the maximum latency for a request, so that a buffer time is added to the maximum latency. That is, If the maximum latency (approximately) = (P / M) * T , where,
then, the maxWait = (P / M) * T + buffer time. |
| minIdle | The minimum number of connections that can remain idle in the pool, without extra ones being created. The connection pool can shrink below this number if validation queries fail. Default value is 0. | This value should be similar or near to the average number of requests that will be received by the server at the same time. With this setting, you can avoid having to open and close new connections every time a request is received by the server. |
| maxIdle | The maximum number of connections that can remain idle in the pool. | The value should be less than the maxActive value. For high performance, tune maxIdle to match the number of average, concurrent requests to the pool. If this value is set to a large value, the pool will contain unnecessary idle connections. |
| testOnBorrow | The indication of whether connection objects will be validated before they are borrowed from the pool. If the object validation fails, the connection is dropped from the pool, and there will be an attempt to borrow another connection. | 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 will 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 "ValidationQuery" property is set. To increase the efficiency of connection validation and to improve performance, |
| validationInterval | This parameter controls how frequently a given validation query is executed (time in milliseconds). The default value is | Deciding the value for the "validationInterval" depends on the target application's behavior. Therefore, selecting a value for this property is a trade-off and ultimately depends on what is acceptable for the application. If a larger value is set, the frequency of executing the Validation Query is low, which results in better performance. Note that this value can be as high as the time it takes for your DBMS to declare a connection as stale. For example, MySQL will keep a connection open for as long as 8 hours, which requires the validation interval to be within that range. However, note that the validation query execution is usually fast. Therefore, even if this value is only large by a few seconds, there will not be a big penalty on performance. Also, specially when the database requests have a high throughput, the negative impact on performance is negligible. For example, a single extra validation query run every 30 seconds is usually negligible. If a smaller value is set, a stale connection will be identified quickly when it is presented. This maybe important if you need connections repaired instantly, e.g. during a database server restart. |
| validationQuery | The SQL query used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw an SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server). | Specify an SQL query, which will validate the availability of a connection in the pool. This query is necessary when testOnBorrow property is true. |
| Remove Abandoned (boolean) | Flag to remove abandoned connections if they exceed the removeAbandonedTimout. The default value is 'false'. | If this flag is set to 'true', a connection is considered abandoned and eligible for removal, if it has been in use for longer than the removeAbandonedTimeout. This Setting this property to 'true' can recover database connections from applications that fail to close a connection. Also see logAbandoned. |
| Remove Abandoned Timeout (int) | Timeout in seconds before an abandoned (in use) connection can be removed. The default value is 60 (60 seconds). | The value should be set to the longest running query that your applications might have. |
| Log Abandoned (boolean) | Flag to log stack traces for the application code that abandoned a connection. The default value is 'false'. | Note that logging of abandoned connections add overhead for every connection borrowing, because a stack trace has to be generated. |
...