|
Problem(Abstract) |
Currently-published recommendations for setting Prepared
Statement Cache sizes for IBM® WebSphere® Application Server might result
in very large Application Server memory footprints and in extreme cases,
OutOfMemory errors or crashes of the Java™ process.
These are memory utilization considerations needed for applications with
very large prepared statement cache sizes. |
|
|
|
Resolving the
problem |
Overview of Prepared Statement Caching
WebSphere Application Server provides a mechanism for caching and reusing
prepared statement objects. Caching prepared statements improves overall
response times because an application can reuse a PreparedStatement on a
connection if it exists in that connection's cache, bypassing the need to
create a new PreparedStatement.
When an application creates a PreparedStatement on a connection, the
connection's cache is first searched to determine if a PreparedStatement
with the same SQL string already exists. If a match is found, the cached
PreparedStatement is returned for use. If it is not, a new
PreparedStatement is created and returned to the application. When a
prepared statement is closed by the application, it is returned to the
connection's cache of statements, rather than being completely closed.
Elements are removed from the connection's cache of prepared statements
only when the number of currently cached prepared statements exceeds the
configured statement cache limit for the connection. When the cache limit
is reached, the least-recently-used statement is removed from the cache to
make room for the new statement. Because in-use prepared statements are
not considered part of the prepared statement cache, the total number of
statements open per connection can actually be slightly higher than the
configured statement cache size. For example, if the prepared statement
cache is configured to ten statements per connection and the application
has up to two prepared statements in use at a time, the true upper limit
on the prepared statements per connection is twelve.
The number of prepared statements to hold in the cache is configurable on
the DataSource. Each cache must be tuned according to the application's
need for prepared statements.
- In WebSphere Application Server V5.0, V6.0, V6.1, the
prepared statement cache value is the number of prepared statements to
cache for each connection; to get the total number of prepared statements
that are cached, multiply this value by the maximum number of connections
in your connection pool.
- In WebSphere Application Server V4.0 and V3.5.6 or later,
the statement cache size property on the DataSource object specifies how
many statements can be cached for the entire connection pool. At runtime,
this number is divided by the maximum size of the connection pool to
determine how many statements each connection can cache.
Memory utilization for Prepared Statement Caching
Recommendations are made in several WebSphere Application Server
documents on the value for the prepared statement cache. They all
recommend estimating the number of unique SQL statements an application
prepares and using this number to set the number of prepared statements to
be cached for each connection:
- In WebSphere Application Server V5.0, V6.0, V6.1, the
cache value is the number of unique statements. Application Server then
creates one cache of this size for each connection.
- In WebSphere Application Server V4.0, multiply the number
of unique statements by the maximum size of your connection pool to get
the Statement Cache Size value.
The connection manager then divides this number by the number of maximum
database connections and creates a cache of this size for each connection.
These formulas work well when the number of unique prepared statements
and maximum connections are relatively small; however, these formulas do
not take into account the possible memory consumption of the cached
prepared statements, particularly when the total number of statements
being cached is large. What is considered a small or large prepared
statement caches depends on the database vendor in use.
Each prepared statement object consumes some amount of memory. The actual
amount is variable, based on the database vendor in use, as well as the
number and size of the parameter data for the statement. When prepared
statement caches are configured to large values, it is possible to outgrow
the amount of memory available to the cache, resulting in unexpected
behavior. Depending on the type of JDBC™ driver, the memory consumption
might be from the Java heap or from the JVM™ native heap. In a 32-bit JVM,
which includes the supported WebSphere Application Server JVMs, the limit
for the combination of the Java heap and the native heap is 2 GB. For more
details on the different heaps and how they are configured and allocated,
please check the documentation for the JVM for your particular platform.
Both the JDBC type 3 and 4 drivers are pure Java code within the JVM.
This means that the memory consumed in the JVM is allocated from the Java
heap. The Java heap is a bounded memory space, the size of which can be
configured by setting JVM parameters within WebSphere Application Server.
If the Java heap is exhausted, java.lang.OutOfMemory exceptions begin to
occur in the Application Server. It is also important to note that with a
type 3 driver, there might also be memory consumption in the JDBC provider’
s server middleware component corresponding to the cached prepared
statements, which might require tuning of the prepared statement cache.
Cached prepared statements in a JDBC type 1 or 2 driver would likely
consume most of their memory in native objects, which are allocated from
the JVM native memory heap. The size of this heap is not directly
configurable. When the native heap is consumed, a java.lang.OutOfMemory
error might occur, or the JVM itself might crash.
Tuning the Prepared Statement Cache
As with any other tuning exercise, tuning of the prepared statement cache
should be done before the application is put into production. If you are
experiencing memory growth in your JVM to the point where one of your
heaps is exhausted, there are procedures and tools available for
investigating which of the heaps is growing and what might be causing this
growth. Refer to the documentation provided by your particular JVM, or
http://java.sun.com for more
information on debugging Java memory utilization.
If you believe that the problem might be due to a very large prepared
statement cache, consider the following possibilities:
- If the memory growth is taking place in the Java heap, and
there appears to be memory available in the native heap, try tuning the
size of your maximum Java heap.
- Decrease the size of your prepared statement cache. A good
model might be to cut the cache in half and analyze the memory usage at
this level. If this halved-cache setting causes a leveling-off of memory,
consider whether there is available memory in the JVM. If so, begin tuning
the cache upward until you reach the optimal memory utilization. If the
available memory is still being consumed with the halved cache, try
cutting the cache size in half again.
If you choose to decrease the size of your prepared statement cache, some
cycling of the statement cache could occur, as the least recently used
statements are closed to make room for more recently used statements. It
can be worthwhile to analyze the usage pattern of the prepared statements
in your application. If some prepared statements are executed
infrequently, the penalty in consumed resources might outweigh the
advantage of the caching mechanism. These infrequently-used statements
might be better suited to the java.sql.Statement interface, rather than
the java.sql.PreparedStatement interface. Statement objects are not cached
by the Application Server and will not consume memory beyond the scope in
which they are used. |
|
|
|
|
Cross Reference information |
Segment |
Product |
Component |
Platform |
Version |
Edition |
Application Servers |
Runtimes for Java Technology |
Java SDK |
|
|
|
|
|
|