How to configure statement cache with Datasource in JBoss EAP?

Environment

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

Issue

• How are the following used in JBoss EAP datasource pools?
  • prepared-statement-cache-size
  • share-prepared-statements
  • track-statements
• How can prepared-statement-cache-size,share-prepared-statements,track-statements be configured using CLI ?

Resolution

Disclaimer: Links contained herein to external website(s) are provided for convenience only. Red Hat has not reviewed the links and is not responsible for the content or its availability. The inclusion of any link to an external website does not imply endorsement by Red Hat of the website or their entities, products or services. You agree that Red Hat is not responsible or liable for any loss or expenses that may result due to your use of (or reliance on) the external site or content.

Understanding the Prepared Statement Cache

In JBoss, by default, prepared statements¹ are not cached (i.e. the default value of prepared-statement-cache-size is 0, meaning no-cache). In some cases, caching prepared statements may have performance benefits. Prepared statements are stored in a per-connection cache (with a maximum of prepared-statement-cache-size statements per connection, evicted on a least recently used basis).

Prepared statements have two phases of execution:

• preparation of the statement
• execution of the statement

Statement preparation involves significant CPU load, so caching and re-use of prepared statements can improve throughput.

Configuration is done within the <statement>...</statement> block of each datasource pool.

• prepared-statement-cache-size :The maximum number of prepared statements that will be retained, in a Least Recently Used (LRU) cache, per connection.

  • This property enables caching and configures the maximum number of statements retained per connection.
  • Note that since each connection in the pool has its own cache, the number of prepared statements associated with a given pool is potentially equal to prepared-statement-cache-size x max-pool-size. A large pool of connections each with a large number of cached statements can have significant memory and resource utilization impact. Also, because connection selection is (by default) based on a least recently used algorithm, there may be minimal opportunities for reuse in many application scenarios.

• share-prepared-statements : Whether asking for the same statement twice without closing it uses the same underlying prepared statement. The default value is false.

  • A value of true permits re-use of a single prepared statement (within a transaction) for execution of multiple commands (e.g. concurrent queries for which the ResultSet objects have not been closed) without closing calling close on the statement (to return it to the cache). This may reduce memory requirements in a given transaction but this may be unsupported by some database drivers/servers.
  • Sharing of prepared statements is not required to leverage a prepared statement cache.

• track-statements: Whether to check for unclosed statements when a connection is returned to a pool and a statement is returned to the prepared statement cache. Valid values are:

  • true: statements and result sets are tracked, and a warning is issued if they are not closed.
  • false: neither statements or result sets are tracked.
  • nowarn: statements are tracked but no warning is issued. This is the default.
  • Since in-use statements are not re-used by default (see share-prepared-statements), verifying that the application does not leak prepared statements is useful and can be accomplished using this control.

Configuring the Cache

Use the following CLI commands for configuring prepared-statement-cache-size,shared-prepared-statements,track-statements :

Configure prepared-statement-cache-size:

[standalone@localhost:9999 /]/subsystem=datasources/data-source=ExampleDS:write-attribute(name=prepared-statements-cache-size,value=10)

Configure share-prepared-statements:

[standalone@localhost:9999 /]/subsystem=datasources/data-source=ExampleDS:write-attribute(name=share-prepared-statements,value=true)

Configure track-statements:

[standalone@localhost:9999 /]/subsystem=datasources/data-source=ExampleDS:write-attribute(name=track-statements,value=true)

See also: https://access.redhat.com/solutions/2350281

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.