Chapter 6. Datasource and Resource Adapter Tuning

Connection pools are the principal tool that JBoss EAP uses to optimize performance for environments that use datasources, such as relational databases, or resource adapters.

Allocating and deallocating resources for datasource and resource adapter connections is very expensive in terms of time and system resources. Connection pooling reduces the cost of connections by creating a 'pool' of connections that are available to applications.

Before configuring your connection pool for optimal performance, you must monitor the datasource pool statistics or resource adapter statistics under load to determine the appropriate settings for your environment.

6.1. Monitoring Pool Statistics

6.1.1. Datasource Statistics

When statistics collection is enabled for a datasource, you can view runtime statistics for the datasource.

6.1.1.1. Enabling Datasource Statistics

By default, datasource statistics are not enabled. You can enable datasource statistics collection using the management CLI or the management console.

Enable Datasource Statistics Using the Management CLI

The following management CLI command enables the collection of statistics for the ExampleDS datasource.

Note

In a managed domain, precede this command with /profile=PROFILE_NAME. 

/subsystem=datasources/data-source=ExampleDS:write-attribute(name=statistics-enabled,value=true)

Reload the server for the changes to take effect.

Enable Datasource Statistics Using the Management Console

Use the following steps to enable statistics collection for a datasource using the management console.

  1. Navigate to Configuration → Subsystems → Datasources & Drivers → Datasources.
  2. Select the datasource and click View.
  3. Click Edit under the Attributes tab.
  4. Set the Statistics Enabled field to ON and click Save. A popup appears indicating that the changes require a reload in order to take effect.

  5. Reload the server.

    • For a standalone server, click the Reload link from the popup to reload the server.
    • For a managed domain, click the Topology link from the popup. From the Topology tab, select the appropriate server and select the Reload drop down option to reload the server.

6.1.1.2. Viewing Datasource Statistics

You can view runtime statistics for a datasource using the management CLI or management console.

View Datasource Statistics Using the Management CLI

The following management CLI command retrieves the core pool statistics for the ExampleDS datasource.

Note

In a managed domain, precede these commands with /host=HOST_NAME/server=SERVER_NAME. 

/subsystem=datasources/data-source=ExampleDS/statistics=pool:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => 1,
        "AvailableCount" => 20,
        "AverageBlockingTime" => 0L,
        "AverageCreationTime" => 122L,
        "AverageGetTime" => 128L,
        "AveragePoolTime" => 0L,
        "AverageUsageTime" => 0L,
        "BlockingFailureCount" => 0,
        "CreatedCount" => 1,
        "DestroyedCount" => 0,
        "IdleCount" => 1,
        ...
}

The following management CLI command retrieves the JDBC statistics for the ExampleDS datasource. 

/subsystem=datasources/data-source=ExampleDS/statistics=jdbc:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "PreparedStatementCacheAccessCount" => 0L,
        "PreparedStatementCacheAddCount" => 0L,
        "PreparedStatementCacheCurrentSize" => 0,
        "PreparedStatementCacheDeleteCount" => 0L,
        "PreparedStatementCacheHitCount" => 0L,
        "PreparedStatementCacheMissCount" => 0L,
        "statistics-enabled" => true
    }
}
Note

Since statistics are runtime information, be sure to specify the include-runtime=true argument.

See Datasource Statistics for a detailed list of all available statistics.

View Datasource Statistics Using the Management Console

To view datasource statistics from the management console, navigate to the Datasources subsystem from the Runtime tab, select a datasource, and click View.

See Datasource Statistics for a detailed list of all available statistics.

6.1.2. Resource Adapter Statistics

You can view core runtime statistics for deployed resource adapters. See the Resource Adapter Statistics appendix for a detailed list of all available statistics.

Enable Resource Adapter Statistics

By default, resource adapter statistics are not enabled. The following management CLI command enables the collection of statistics for a simple resource adapter myRA.rar with a connection factory bound in JNDI as java:/eis/AcmeConnectionFactory:

Note

In a managed domain, precede the command with /host=HOST_NAME/server=SERVER_NAME/. 

/deployment=myRA.rar/subsystem=resource-adapters/statistics=statistics/connection-definitions=java\:\/eis\/AcmeConnectionFactory:write-attribute(name=statistics-enabled,value=true)
View Resource Adapter Statistics

Resource adapter statistics can be retrieved from the management CLI. The following management CLI command returns statistics for the resource adapter myRA.rar with a connection factory bound in JNDI as java:/eis/AcmeConnectionFactory.

Note

In a managed domain, precede the command with /host=HOST_NAME/server=SERVER_NAME/. 

deployment=myRA.rar/subsystem=resource-adapters/statistics=statistics/connection-definitions=java\:\/eis\/AcmeConnectionFactory:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => "1",
        "AvailableCount" => "20",
        "AverageBlockingTime" => "0",
        "AverageCreationTime" => "0",
        "CreatedCount" => "1",
        "DestroyedCount" => "0",
        "InUseCount" => "0",
        "MaxCreationTime" => "0",
        "MaxUsedCount" => "1",
        "MaxWaitCount" => "0",
        "MaxWaitTime" => "0",
        "TimedOut" => "0",
        "TotalBlockingTime" => "0",
        "TotalCreationTime" => "0"
    }
}
Note

Since statistics are runtime information, be sure to specify the include-runtime=true argument.

6.2. Pool Attributes

This section details advice for selected pool attributes that can be configured for optimal datasource or resource adapter performance. For instructions on how to configure each of these attributes, see:

  • Configuring Datasource Pool Attributes

  • Configuring Resource Adapter Pool Attributes

    Minimum Pool Size

    The min-pool-size attribute defines the minimum size of the connection pool. The default minimum is zero connections. With a zero min-pool-size, connections are created and placed in the pool when the first transactions occur.

    If min-pool-size is too small, it results in increased latency while executing initial database commands because new connections might need to be established. If min-pool-size is too large, it results in wasted connections to the datasource or resource adapter.

    During periods of inactivity the connection pool will shrink, possibly to the min-pool-size value.

    Red Hat recommends that you set min-pool-size to the number of connections that allow for ideal on-demand throughput for your applications.

    Maximum Pool Size

    The max-pool-size attribute defines the maximum size of the connection pool. It is an important performance parameter because it limits the number of active connections, and thus also limits the amount of concurrent activity.

    If max-pool-size is too small, it can result in requests being unnecessarily blocked. If max-pool-size is too large, it can result in your JBoss EAP environment, datasource, or resource adapter using more resources than it can handle.

    Red Hat recommends that you set the max-pool-size to at least 15% higher than an acceptable MaxUsedCount observed after monitoring performance under load. This allows some buffer for higher than expected conditions.

    Prefill

    The pool-prefill attribute specifies whether JBoss EAP will prefill the connection pool with the minimum number of connections when JBoss EAP starts. The default value is false.

    When pool-prefill is set to true, JBoss EAP uses more resources at startup, but there will be less latency for initial transactions.

    Red Hat recommends to set pool-prefill to true if you have optimized the min-pool-size.

    Strict Minimum

    The pool-use-strict-min attribute specifies whether JBoss EAP allows the number of connections in the pool to fall below the specified minimum.

    If pool-use-strict-min is set to true, JBoss EAP will not allow the number of connections to temporarily fall below the specified minimum. The default value is false.

    Although a minimum number of pool connections is specified, when JBoss EAP closes connections, for instance, if the connection is idle and has reached the timeout, the closure may cause the total number of connections to temporarily fall below the minimum before a new connection is created and added to the pool.

    Timeouts

    There are a number of timeout options that are configurable for a connection pool, but a significant one for performance tuning is idle-timeout-minutes.

    The idle-timeout-minutes attribute specifies the maximum time, in minutes, a connection may be idle before being closed. As idle connections are closed, the number of connections in the pool will shrink down to the specified minimum.

    The longer the timeout, the more resources are used but requests might be served faster. The lower the timeout, the less resources are used but requests might need to wait for a new connection to be created.

6.3. Configuring Pool Attributes

6.3.1. Configuring Datasource Pool Attributes

Prerequisites

  • Install a JDBC driver. See JDBC Drivers in the JBoss EAP Configuration Guide.
  • Create a datasource. See Creating Datasources in the JBoss EAP Configuration Guide.

You can configure datasource pool attributes using either the management CLI or the management console:

  • To use the management console, navigate to Configuration → Subsystems → Datasources & Drivers → Datasources, select your datasource, and click View. The pool options are configurable under the datasource Pool tab. Timeout options are configurable under the datasource Timeouts tab.

  • To use the management CLI, execute the following command: 

    /subsystem=datasources/data-source=DATASOURCE_NAME/:write-attribute(name=ATTRIBUTE_NAME,value=ATTRIBUTE_VALUE)

    For example, to set the ExampleDS datasource min-pool-size attribute to a value of 5 connections, use the following command: 

    /subsystem=datasources/data-source=ExampleDS/:write-attribute(name=min-pool-size,value=5)

6.3.2. Configuring Resource Adapter Pool Attributes

Prerequisites

You can configure resource adapter pool attributes using either the management CLI or the management console:

  • To use the management console, navigate to Configuration → Subsystems → Resource Adapters, select your resource adapter, click View, and select Connection Definitions in the left menu. The pool options are configurable under the Pool tab. Timeout options are configurable under the Attributes tab.

  • To use the management CLI, execute the following command: 

    /subsystem=resource-adapters/resource-adapter=RESOURCE_ADAPTER_NAME/connection-definitions=CONNECTION_DEFINITION_NAME:write-attribute(name=ATTRIBUTE_NAME,value=ATTRIBUTE_VALUE)

    For example, to set the my_RA resource adapter my_CD connection definition min-pool-size attribute to a value of 5 connections, use the following command: 

    /subsystem=resource-adapters/resource-adapter=my_RA/connection-definitions=my_CD:write-attribute(name=min-pool-size,value=5)