Advertise with us

Search Mastertheboss

Our eBooks

WildFly Connection Pool configuration

WildFly uses a pool of Database Connection objects to  your applications. Think of a connection pool like a bucket pre-filled with a minimum number of ready-to-use connections for your application. There is also an upper bound limit that defines the maximum number of connections that the pool can hold. The default values for the minimum and maximum size of the pool are 0 and 20 respectively, with the prefill attribute set to false by default.

This means that when a Datasource is started up, its connection pool is created with 0 active and valid connections, and that it can hold up to 20 connections.

Before we move to the specific Database settings, please check this tutorial to learn how to configure a Datasource object, which is a Java Naming and Directory Interface (JNDI) object used to obtain a connection from a connection pool to a database: How to configure a Datasource with JBoss / WildFly

Configuring the Datasource attributes

Once created, the data source uses some default settings that might be good for an initial shot; in order to achieve optimal performance you should adjust these values based on your needs. Here is the list of attributes that you can configure with a short description:

  • min­-pool­-size: The minimum number of connections in the pool (default 0)
  • initial­-pool­-size: The initial number of connections to acquire from the database (New since WildFly 9)
  • max­-pool­-size: The maximum number of connections in the pool (default 20)
  • pool­-use­-strict­min: Whether idle connections below the min­pool­size should  be closed
  • pool­-prefill: Attempt to pre­fill the connection pool to the minimum number of connections. This will check your connections as soon as the Datasource is installed.
  • flush­-strategy: Specifies how the pool should be flushed in case of an error. The default one (FailingConnectionOnly) forces destroying only connections with error.
  • idle­-timeout­-minutes: Specifies the maximum time, in minutes, a connection may be idle before being closed. The actual maximum time depends also on the IdleRemover scan time, which is half of the smallest idle­timeout­minutes value of any pool.
  • track­-statements: Whether to check for unclosed statements when a connection is returned to the pool, result sets are closed, a statement is closed or return to the prepared statement cache. Valid values are: "false" ­ do not track statements, "true" ­ track statements and result sets and warn when they are not closed, "nowarn" ­track statements but do not warn about them being unclosed

So how these attributes can affect your applications ? Let's see it from the boot process.
When the application server starts, if you have configured an initial­-pool­-size, the datasource will be eventually filled up with that number of connections. Otherwise, you start with an empty pool. From now on, every time a Connection is requested to the datasource a check is made to see if any idle Connection is available. If not, the application server will attempt to acquire a new database Connection.

So, unless the max­-pool­-size has been reached, a new Connection will be created. When a Connection completes its job it becomes idle. A Connection can stay idle up to a maximum number of minutes as specified by the idle­-timeout­-minutes. After that, the Connection is returned to the pool.
The pool­-use-­strict­-min allows for a variation to this rule. If set to true, idle connections are not returned to the pool if you have hit the min-­pool­-size. They will just stay idle, ready to be used by your applications. Here is the min-­pool­-size and max-­pool­-size settings are applied to the MySQL datasource:

/subsystem=datasources/data-source=MySqlDS:write-attribute(name=min-pool-size,value=10)
/subsystem=datasources/data-source=MySqlDS:write-attribute(name=max-pool-size,value=50)

Monitoring Runtime attributes of the Datasource

In order to tune your applications, it is essential to monitor the runtime attributes of the Database Pool. This will help you to discover if the Connection pool has enough connections to meet your requirements and if any leak in Connection is detected. As a rule of thumb, you have to enable statistics on your Datasource first:

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

 Then, you can query the statistics=pool Node to investigate the current number of active Database connections:

/subsystem=datasources/data-source=PostgrePool/statistics=pool:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => 3,
        "AvailableCount" => 17,
        "AverageBlockingTime" => 0L,
        "AverageCreationTime" => 32L,
        "AverageGetTime" => 33L,
        "AveragePoolTime" => 0L,
        "AverageUsageTime" => 0L,
        "BlockingFailureCount" => 0,
        "CreatedCount" => 3,
        "DestroyedCount" => 0,
        "IdleCount" => 0,
        "InUseCount" => 3,
. . . .

 In this example, we have 3 active Database connections ("ActiveCount") but we also have 3 "InUseCount" Connections. This in turn can mean two things:

  • There are 3 Connection objects which are executing SQL statements right now.
  • There are active Connections which have completed their execution but they haven't been closed properly.

One way to detect Database connection leaks is using a combination of settings in the datasource and the jca subsystem. First, make sure that the Cached Connection Manager (use-ccm) is activated for the Datasource:

/subsystem=datasources/data-source=PostgrePool:read-attribute(name=use-ccm)
{
    "outcome" => "success",
    "result" => true
}

 The above should be "true" by default.  Then set to "debug" the Cached Connection Manager in the jca subsystem:

 /subsystem=jca/cached-connection-manager=cached-connection-manager:write-attribute(name=debug,value=true)

 That will cause connections to be closed automatically for you by the Cached Connection Manager and the following  information will be reported in your logs:

2020-08-21 13:04:54,946 INFO  [org.jboss.jca.core.api.connectionmanager.ccm.CachedConnectionManager] (default task-1) IJ000100: Closing a connection for you. Please close them yourself: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK82ba43757: java.lang.Throwable: STACKTRACE
	at org.jboss.ironjacamar.impl1.4.22.Final//org.jboss.jca.core.connectionmanager.ccm.CachedConnectionManagerImpl.registerConnection(CachedConnectionManagerImpl.java:308)
	at org.jboss.ironjacamar.impl1.4.22.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:819)
	at org.jboss.ironjacamar.jdbcadapters1.4.22.Final//org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)
	at org.jboss.as.connector20.0.0.Final//org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:64)

 Finally, consider that you can have Datasource runtime statistics printed in your server log file on each Connection usage by enabling this logger:

/subsystem=logging/logger=org.jboss.jca/:add(level=TRACE)

 That will print the full list of static and runtime attributes for your Datasource in the server logs:

Pool:
  Name: PostgrePool
  Class: org.jboss.jca.core.connectionmanager.pool.strategy.OnePool
  Object: 316a6237
  FIFO: false
PoolConfiguration:
  MinSize: 0
  InitialSize: 0
  MaxSize: 20
  BlockingTimeout: 30000
  IdleTimeoutMinutes: 30
  ValidateOnMatch: false
  BackgroundValidation: false
  BackgroundValidationMillis: 0
  StrictMin: false
  UseFastFail: false
  Decrementer: org.jboss.jca.core.connectionmanager.pool.capacity.TimedOutDecrementer
Available (0):
InUse (1):
  668c5c34 (NORMAL) (CheckedOut: 1598007894938) (Validated: 1598007794843) (Usage: 10)
Statistics:
  ActiveCount: 1
  AvailableCount: 19
  AverageBlockingTime: 0
  AverageCreationTime: 17
  AverageGetTime: 9
  AveragePoolTime: 100086
  AverageUsageTime: 9
  BlockingFailureCount: 0
  CreatedCount: 1
  DestroyedCount: 0
  IdleCount: 0
  InUseCount: 1
. . . . .

Configuring a Datasource with JBoss AS 4 and 5

In order to create a DataSource (so that you can use JDBC connectivity) you need to create a file ending with -ds.xml under the "deploy" directory of your server.

The default Datasource file

The default data source configured with JBoss 4.0 is the HypersonicDB data source. 
Here's the hsqldb-ds.xml that is shipped with jboss : 

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
    <local-tx-datasource>

        <jndi-name>DefaultDS</jndi-name>

        <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB
        </connection-url>

        <driver-class>org.hsqldb.jdbcDriver</driver-class>

        <user-name>sa</user-name>
        <password></password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>

        <idle-timeout-minutes>0</idle-timeout-minutes>

        <track-statements />

        <security-domain>HsqlDbRealm</security-domain>

        <prepared-statement-cache-size>32</prepared-statement-cache-size>

        <metadata>
            <type-mapping>Hypersonic SQL</type-mapping>
        </metadata>

        <depends>jboss:service=Hypersonic,database=localDB</depends>

    </local-tx-datasource>

    <mbean code="org.jboss.jdbc.HypersonicDatabase" name="jboss:service=Hypersonic,database=localDB">
        <attribute name="Database">localDB</attribute>
        <attribute name="InProcessMode">true</attribute>
    </mbean>

</datasources>

As you can see from this file, JDBC connectivity uses Connection pools to dispatch Connections. The initial size and the max size of the Connection pool can be configured with <min-pool-size> and <max-pool-size>.

With <idle-timeout-minutes> you can indicate the maximum time a connection may be idle before being closed and returned to the pool. If not specified it's 15 minutes.

<track-statements/> is a debugging feature: it checks that all statements are closed when the connection is returned to the pool: remember to disable it in production environment.

 <security-domain> tells to use the security domain defined in conf/login-config.xml : in our case:  

 <application-policy name="HsqlDbRealm">
    <authentication>
        <login-module
            code="org.jboss.resource.security.ConfiguredIdentityLoginModule"
            flag="required">
            <module-option name="principal">sa</module-option>
            <module-option name="userName">sa</module-option>
            <module-option name="password"></module-option>
            <module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS
            </module-option>
        </login-module>
    </authentication>
</application-policy>

<prepared-statement-cache-size> is the number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.

Enterprise datasources

I) Local Datasource

This is a sample Oracle local datasource configuration: a local DataSource is one that does not support two phase commit using a java.sql.Driver.

<datasources>
    <local-tx-datasource>
        <jndi-name>OracleDS</jndi-name>
        <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>

        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
        <user-name>x</user-name>
        <password>y</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>100</max-pool-size>
        <query-timeout>60</query-timeout>
        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
        </exception-sorter-class-name>

        <metadata>
            <type-mapping>Oracle9i</type-mapping>
        </metadata>
    </local-tx-datasource>

</datasources>

Notice the <query-timeout> tag which configures the maximum of seconds before a query times out ( avaliable since Jboss 4.0.3). The <exception-sorter-class-name> is used to Check the Oracle error codes and messages for fatal errors.

In order to be able to use a Datasource, you need to install a JDBC Driver. For JBoss AS 7/WildFly users check this tutorial: How to configure a Datasource with JBoss 7 . Of you are using JBoss AS 4/5 it's enough to drop the JDBC Driver in the lib folder of the application server.

II) XA Datasource

This is a sample XA Datasource: XA DataSources support two phase commit using a  javax.sql.XADataSource

<datasources>
    <xa-datasource>
        <jndi-name>XAOracleDS</jndi-name>
        <track-connection-by-tx></track-connection-by-tx>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource
        </xa-datasource-class>
        <xa-datasource-property name="URL">jdbc:oracle:oci8:@tc
        </xa-datasource-property>
        <xa-datasource-property name="User">scott
        </xa-datasource-property>
        <xa-datasource-property name="Password">tiger
        </xa-datasource-property>

        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
        </exception-sorter-class-name>

        <no-tx-separate-pools></no-tx-separate-pools>


        <metadata>
            <type-mapping>Oracle9i</type-mapping>
        </metadata>
    </xa-datasource>

    <mbean
        code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter"
        name="jboss.jca:service=OracleXAExceptionFormatter">
        <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager
        </depends>
    </mbean>

</datasources>

Notice the <isSameRM-override-value>  set to false to fix problems with Oracle. The element <track-connection-by-tx/>  can be omitted on JBoss 5 where it's enabled by default.
At last, the  <no-tx-separate-pools> means that Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa.

© 2020 mastertheboss.com. All Rights Reserved.

Please publish modules in offcanvas position.