|
Problem |
Currently-published recommendations for setting Prepared
Statement Cache sizes for 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. |
|
Solution |
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 statements 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, but the application
may have 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 5.0, 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, the cache value is
this number of unique statements. WebSphere 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 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 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. 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, in the process of this investigation 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. |
|
|
|
|
|
|