Tuning JDBC Pool Configurations¶
If you have connected a JDBC user store to the Micro Integrator, you can apply the following datasource tuning recommendations in the deployment.toml
file (stored in the <MI_HOME>/conf
directory).
Info
The following recommendations also apply when you create a data service to expose data in an RDBMS. Find out more about RDBMS configurations in data services.
JDBC connection pool tuning recommendations¶
When a database operation is processed, the server spawns a database connection from an associated datasource. After using this connection, the server returns it to the pool of connections. The physical connection is not dropped with the database server unless it becomes stale or the datasource connection is closed. This is called datasource connection pooling and is a recommended way to gain more performance/throughput in the system. The goal of tuning the pool properties is to maintain an RDBMS connection pool that is large enough to handle peak load without unnecessarily utilizing resources.
The following parameters should be considered when tuning the connection pool for the database:
- The application's concurrency requirement.
- The average time used for running a database query.
- The maximum number of connections the database server can support.
The table below indicates some recommendations on how to configure the JDBC pool. See the complete list of JDBC configuration parameters that you can use in the deployment.toml
file. For more details about recommended JDBC configurations, see Tomcat JDBC Connection Pool.
Property | Description | Tuning Recommendations |
---|---|---|
pool_options.maxActive | The maximum number of active connections that can be allocated from the connection pool at the same time. The default value is 100. |
The maximum latency (approximately) = (P / M) * T , where,
|
pool_options.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, ff the maximum latency (approximately) = (P / M) * T , where,
|
pool_options.minIdle | 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. |
pool_options.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 property should also be used. |
pool_options.validationInterval | This parameter controls how frequently a given validation query is executed (time in milliseconds). The default value is 30000 (30 seconds). That is, if a connection is due for validation, but has been validated previously within this interval, it will not be validated again. | 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. |
pool_options.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. |
pool_options.MaxPermSize | The memory size allocated for the WSO2 product. |
The default memory allocated for the product via this parameter is as follows:
-Xms256m -Xmx512m -XX:MaxPermSize=256m
You can increase the performance by increasing this value in the -Xms2048m -Xmx2048m -XX:MaxPermSize=1024m
|
The following properties are used for detecting and removing the connections that get leaked from the connection pool:
Property Name | How to configure |
---|---|
pool_options.removeAbandoned | If this property is set to 'true', a connection is considered abandoned and eligible for removal if it has been in use for longer than the removeAbandonedTimeout value explained below. |
pool_options.removeAbandonedTimeout | The time in seconds that should pass before a connection that is in use can be removed. This is the time period after which the connection will be declared abandoned. This value should be set to the longest running query that the applications might have. |
pool_options.logAbandoned | Set this property to "true" if 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. |
Info
- When it comes to web applications, users are free to experiment and package their own pooling framework such BoneCP.
- If you are using an Oracle database, you may sometimes come across an error (ORA-04031) indicating that you have not allocated enough memory for the shared pool of connections. To overcome this, you can allocate more memory to the shared pool by adjusting the following parameters in the
/dbs/init .ora file of your Oracle database: SHARED_POOL_RESERVED_SIZE, SHARED_POOL_SIZE and LARGE_POOL_SIZE.