Data access tuning parameters
For better application performance, we can tune some data access resources through the WebSphere Application Server administrative console.
Tune these properties of data sources and connection pools to optimize the performance of transactions between the application and datastore.
Data source tuning
To view the administrative console page where we configure the following properties, click...
Resources > JDBC Providers > JDBC_provider > Data sources > data_source > WAS connection properties
- Enable JMS one phase optimization support
- If the application does not use JMS messaging, do not select this option. Activating this support enables the Java Message Service (JMS) to get optimized connections from the data source. Activating this support also prevents JDBC applications from obtaining connections from the data source. For further explanation of JMS one phase support, refer to the article entitled "Sharing connections to benefit from one phase commit optimization" in this information center.
- Statement cache size
- Number of statements that can be cached per connection.
The WAS data source optimizes the processing of prepared statements and callable statements by caching those statements that are not being used in an active connection. Both statement types help reduce overhead for transactions with backend data.
- A prepared statement is a precompiled SQL statement stored in a PreparedStatement object. Application Server uses this object to run the SQL statement multiple times, as required by the application run time, with values that are determined by the run time.
- A callable statement is an SQL statement containing a call to a stored procedure, which is a series of precompiled statements that perform a task and return a result. The statement is stored in the CallableStatement object. Application Server uses this object to run a stored procedure multiple times, as required by the application run time, with values that are determined by the run time.
In general, the more statements the application has, the larger the cache should be. Be aware, however, that specifying a larger statement cache size than needed wastes application memory and does not improve performance.
Determine the value for our cache size by adding the number of uniquely prepared statements and callable statements (as determined by the SQL string, concurrency, and the scroll type) for each application that uses this data source on a particular server. This value is the maximum number of possible statements that can be cached on a given connection over the life of the server. For more information about this setting, see the topic, WAS data source properties.
Default: For most databases the default is 10. Zero means there is no cache statement.
Connection pool tuning
To view the administrative console page where we configure the following properties, click...Resources > JDBC Providers > JDBC_provider > Data sources > data_source > Connection pool settings.
- Maximum connections
- Maximum number of physical connections that can be created in this pool. These are the physical connections to the backend datastore. When this number is reached, no new physical connections are created; requestors must wait until a physical connection that is currently in use is returned to the pool.
For optimal performance, set the value for the connection pool lower than the value for the web container threadpool size. Lower settings, such as 10 to 30 connections, might perform better than higher settings, such as 100. For more information about this setting, see the topic, Connection pool settings.
Default: 10
- Minimum connections
- Minimum number of physical connections to maintain. Until this number is exceeded, the pool maintenance thread does not discard physical connections.
If we set this property for a higher number of connections than the application ultimately uses at run time, we do not waste application resources. WAS does not create additional connections to achieve the minimum setting. Of course, if the application requires more connections than the value we set for this property, application performance diminishes as connection requests wait for fulfillment. For more information about this setting see the topic, Connection pool settings.
Default: 1
Related:
Connection pooling Data sources Configure a data source Configure new data sources using wsadmin Connection pool settings