Configuring the Datasource Connection Pool Parameters

When the server processes a database operation, it spawns a database connection from an associated datasource. After using this connection, the server returns it to the pool of connections. This is called datasource connection pooling. It is a recommended way to gain more performance/throughput in the system. In datasource connection pooling, the physical connection is not dropped with the database server, unless it becomes stale or the datasource connection is closed.

RDBMS datasources in WSO2 products use Tomcat JDBC connection pool (org.apache.tomcat.jdbc.pool). It is common to all components that access databases for data persistence, such as the registry, user management (if configured against a JDBC userstore), etc.

You can configure the datasource connection pool parameters, such as how long a connection is persisted in the pool, using the datasource configuration parameters section that appears in the product management console when creating a datasource. Click and expand the option as shown below:

Following are descriptions of the parameters you can configure. For more details on datasource configuration parameters, see ApacheTomcat JDBC Connection Pool guide.

Parameter name	Description
Transaction isolation	The default `TransactionIsolation` state of connections created by this pool are as follows: TRANSACTION_UNKNOWN TRANSACTION_NONE TRANSACTION_READ_COMMITTED TRANSACTION_READ_UNCOMMITTED TRANSACTION_REPEATABLE_READ TRANSACTION_SERIALIZABLE
Initial Size (int)	The initial number of connections created, when the pool is started. Default value is zero.
Max. Active (int)	Maximum number of active connections that can be allocated from this pool at the same time. The default value is 100.
Max. Idle (int)	Maximum number of connections that should be kept in the pool at all times. Default value is 8. Idle connections are checked periodically (if enabled), and connections that have been idle for longer than `minEvictableIdleTimeMillis` will be released. (also see testWhileIdle )
Min. Idle (int)	Minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number, if validation queries fail. Default value is zero. For more information, see testWhileIdle.
Max. Wait (int)	Maximum number of milliseconds that the pool waits (when there are no available connections) for a connection to be returned before throwing an exception. Default value is 30000 (30 seconds).
Validation Query (String)	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 a SQLException. The default value is null. Example values are SELECT 1 (mysql), select 1 from dual (oracle), SELECT 1 (MS Sql Server).
Test On Return (boolean)	Used to indicate if objects will be validated before returned to the pool. The default value is false. For a true value to have any effect, the `validationQuery` parameter must be set to a non-null string.
Test On Borrow (boolean)	Used to indicate if objects will be validated before borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. Default value is false. For a true value to have any effect, the `validationQuery` parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval .
Test While Idle (boolean)	The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. The default value is false and this property has to be set in order for the pool cleaner/test thread to run. For more information, see timeBetweenEvictionRunsMillis . For a true value to have any effect, the validationQuery parameter must be set to a non-null string.
Time Between Eviction Runs Mills (int)	Number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It indicates how often we check for idle, abandoned connections, and how often we validate idle connections. The default value is 5000 (5 seconds).
Minimum Evictable Idle Time (int)	Minimum amount of time an object may sit idle in the pool before it is eligible for eviction. The default value is 60000 (60 seconds).
Remove Abandoned (boolean)	Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true, a connection is considered abandoned and eligible for removal, if it has been in use longer than the `removeAbandonedTimeout.` Setting this to true can recover database connections from applications that fail to close a connection. For more information, see logAbandoned. The default value is false.
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 application code which abandoned a connection. Logging of abandoned connections, adds overhead for every connection borrowing, because a stack trace has to be generated. The default value is false.
Auto Commit (boolean)	The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default. If not set, then the `setAutoCommit` method will not be called.
Default Read Only (boolean)	The default read-only state of connections created by this pool. If not set then the `setReadOnly` method will not be called. (Some drivers don't support read only mode. For example: Informix)
Default Catalog (String)	The default catalog of connections created by this pool.
Validator Class Name (String)	The name of a class which implements the `org.apache.tomcat.jdbc.pool`.Validates the interface and provides a no-arg constructor (may be implicit). If specified, the class will be used to create a `Validator` instance, which is then used instead of any validation query to validate connections. The default value is null. An example value is `com.mycompany.project.SimpleValidator`.
Connection Properties (String)	Connection properties that will be sent to our JDBC driver when establishing new connections. Format of the string must be `[propertyName=property;]*`. The default value is null. The `user` and `password` properties will be passed explicitly, so that they do not need to be included here.
Init SQL	Ability to run a SQL statement exactly once, when the connection is created.
JDBC Interceptors	Flexible and pluggable interceptors to create any customizations around the pool, the query execution and the result set handling.
Validation Interval (long)	To avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).
JMX Enabled (boolean)	Register the pool with JMX or not. The default value is true.
Fair Queue (boolean)	Set to true, if you wish that calls to `getConnection` should be treated fairly in a true FIFO fashion. This uses the `org.apache.tomcat.jdbc.pool.FairBlockingQueue` implementation for the list of the idle connections. The default value is true. This flag is required when you want to use asynchronous connection retrieval. Setting this flag ensures that threads receive connections in the order they arrive. During performance tests, there is a very large difference in how locks and lock waiting is implemented. When `fairQueue=true`, there is a decision making process based on what operating system the system is running. If the system is running on Linux (property `os.name=Linux`), then to disable this Linux specific behavior and still use the fair queue, simply add the property `org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true` to your system properties, before the connection pool classes are loaded.
Abandon When Percentage Full (int)	Connections that have been abandoned (timed out) will not get closed and reported up, unless the number of connections in use are above the percentage defined by `abandonWhenPercentageFull`. The value should be between 0-100. The default value is zero, which implies that connections are eligible for closure as soon as `removeAbandonedTimeout` has been reached.
Max Age (long)	Time in milliseconds to keep this connection. When a connection is returned to the pool, the pool will check to see if the current time when connected, is greater than the `maxAge` that has been reached. If so, it closes the connection rather than returning it to the pool. The default value is zero, which implies that connections will be left open and no age check will be done upon returning the connection to the pool.
Use Equals (boolean)	Set to true, if you wish the `ProxyConnection` class to use `String.equals,` and set to false when you wish to use `==` when comparing method names. This property does not apply to added interceptors as those are configured individually. The default value is true.
Suspect Timeout (int)	Timeout value in seconds. Default value is zero. Similar to to the `removeAbandonedTimeout` value, but instead of treating the connection as abandoned, and potentially closing the connection, this simply logs the warning if `logAbandoned` is set to true. If this value is equal or less than zero, no suspect checking will be performed. Suspect checking only takes place if the timeout value is larger than zero, and the connection was not abandoned, or if abandon check is disabled. If a connection is suspected, a warning message gets logged and a JMX notification will be sent.
Alternate User Name Allowed (boolean)	By default, the `jdbc-pool` will ignore the `DataSource.getConnection(username,password)` call, and simply return a previously pooled connection under the globally configured properties username and password, for performance reasons. The pool can however be configured to allow use of different credentials each time a connection is requested. To enable the functionality described in the `DataSource.getConnection(username,password)` call, simply set the property `alternateUsernameAllowed,` to true. If you request a connection with the credentials user1/password1, and the connection was previously connected using different user2/password2, then the connection will be closed, and reopened with the requested credentials. This way, the pool size is still managed on a global level, and not on a per-schema level. The default value is false.