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