For better application performance, you 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 your application
and datastore.
Data source tuning
To view the administrative console
page where you configure the following properties, click
Resources >
JDBC
Providers >
JDBC_provider >
Data sources >
data_source >
WebSphere Application
Server connection properties.
- Enable JMS one phase optimization support
- If your 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
- Specifies the number of statements that can be cached per connection.
The WebSphere Application
Server 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 that is stored in
a PreparedStatement object. Application Server uses this
object to run the SQL statement multiple times, as required by your application
run time, with values that are determined by the run time.
- A callable statement is an SQL statement that contains 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 your application run time, with values that are determined
by the run time.
In general, the more statements your 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 your 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, WebSphere Application
Server 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 you configure the following properties, click
Resources >
JDBC
Providers >
JDBC_provider >
Data sources >
data_source >
Connection
pool settings.
- Maximum connections
- Specifies the 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
- Specifies the minimum number of physical connections to maintain. Until
this number is exceeded, the pool maintenance thread does not discard physical
connections.
If you set this property for a higher number of connections
than your application ultimately uses at run time, you do not waste application
resources. WebSphere Application
Server does not create additional connections to achieve your minimum setting.
Of course, if your application requires more connections than the value you
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