Caching prepared/callable statements and significant memory use in EAP

Solution Verified - Updated

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 7
    • 6
  • <JBOSS_HOME>/docs/schema/jboss-as-datasources_1_*.xsd

Issue

  • High memory use for prepared/callable statements
  • High number of cached prepared statement misses
  • High number of cached prepared statement creations

Resolution

  • Multiply the prepared-statement-cache-size by the expected (typical) max connection pool size to determine a sustainable statement cache size
  • For EAP 6.4.0 CP02 (6.4.2) and later, the ironjacamar.filo_pool_behavior system property can be used to revert to a MRU connection selection algorithm which increases the potential for statement re-use and decreases the likelihood of "duplication" of statements across connections.

Root Cause

  • The datasource pool property prepared-statement-cache-size controls the number of prepared/callable statements which may be retained in a per-connection LRU cache
    • A large value for this setting can result in significant memory use because statements cannot be shared among connections and the number of prepared statements per-pool may reach prepared-statement-cache-size * max-pool-size (e.g. where cache size is 20 and max-pool-size is 200, there may be as many as 4000 prepared statements).
    • Note that other than the per-connection prepared-statement-cache-size limit there is no direct means of managing (reducing) the statement population and open statements <= the cache size will be closed only when the connection is eventually closed due to idle timeout, eviction due to external failure or shutdown of the pool.
  • In many cases, identical prepared/callable statements may be "duplicated" across several connections within a pool.
  • For a single connection, there are cases where cached prepared/callable statements cannot be (re-)used 1:
    • Cached statements cannot be re-used when the result set type or currency differ from another prepared statement with the same SQL
    • Cached statements cannot be re-used if in-use2 when the connection is in autocommit3 mode
    • Cached statements cannot be re-used if in-use when the connection is not in autocommit mode if share-prepared-statements is set to false
1

See Content from github.com is not included.BaseWrapperManagedConnection.prepareStatement(...), Content from github.com is not included.BaseWrapperManagedConnection.prepareCall(...) and Content from github.com is not included.BaseWrapperManagedConnection.canUse(...)
2: The statement close() operation re-sets the "in-use" flag for cached prepared/callable statements
3: The connection autocommit flag is true outside a managed JTA transaction and, for non-JTA pools, when explicitly set to true by client code using the JDBC connection (e.g. whenever a multi-statement block - a transaction - is not in-progress)

Diagnostic Steps

Verify the pool configuration (max-pool-size, prepared-statement-cache-size) from a heap dump

SELECT p.pool.poolName.toString() AS PoolName, p.maxSize AS MaxPoolSize,
    p.mcf.preparedStatementCacheSize.toString() AS PreparedStatementCacheSize
FROM org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool p

List the prepared statement SQL with the owning connection and retained heap size using one of the following OQL commands:

SELECT t4Connection.toString(), @retainedHeapSize, sqlObject.oracle_sql.toString()
FROM oracle.jdbc.driver.T4CPreparedStatement

SELECT cps, cps.ps.statement.t4Connection.toString() AS Connection, @retainedHeapSize,
    cps.ps.statement.sqlObject.oracle_sql.toString().hashCode() AS SqlHashCode,
    cps.ps.statement.sqlObject.oracle_sql.toString().length() AS SqlLength,
    cps.ps.statement.sqlObject.oracle_sql.toString() AS Sql
FROM org.jboss.jca.adapters.jdbc.jdk8.CachedPreparedStatementJDK8 cps
-- EAP 6.4 statement class is: org.jboss.jca.adapters.jdbc.jdk6.CachedPreparedStatementJDK6
-- Alternate to the below, can filter by SQL hash: (cps.ps.statement.sqlObject.oracle_sql.toString().hashCode() = <single_sql_hash_code_here>)
WHERE (cps.ps.statement.t4Connection.@objectId = <single_conn_id_here>)

Note that Eclipse MAT truncates long strings (e.g. SQL strings) so it may be difficult to distinguish between statements that may differ only in the number of bind parameters - toString() and hashCode() may not return accurate results for similar truncated strings (since they be identical up to the point of truncation). It may be necessary to inspect vendor specific statement data to distinguish between statements with truncated SQL.

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.