Tuning connection pools

 

+

Search Tips   |   Advanced Search

 

WAS enables administrators to establish a pool of backend connections that applications can share on an appserver.

Creating connections is expensive in terms of resources. Connection pools use existing connections without spinning resources to create new connections with each requrest. Web apps can realize performance improvements of up to 20 times the normal results.

 

Application clients

Application clients do not have a connection pools, but we can configure JDBC provider settings in the client deployment descriptors. The application client calls the database directly and does not go through a data source.

The connection is established directly between the application client and the database.

 

Prevent a connection deadlock.

Deadlock can occur if the application requires more than one concurrent connection per thread, and the database connection pool is not large enough for the number of threads. Suppose each of the application threads requires two concurrent database connections and the number of threads is equal to the maximum connection pool size.

Deadlock can occur when both of the following are true:

• Each thread has its first database connection, and all are in use.

• Each thread is waiting for a second database connection, and none would become available since all threads are blocked.

To prevent the deadlock in this case, increase the maximum connections value for the database connection pool by at least one.

This will allow at least one of the waiting threads to obtain a second database connection and avoid a deadlock scenario.

For general prevention of connection deadlock, code your applications to use only one connection per thread.

If we code applications to require C concurrent database connections per thread, the connection pool must support at least the following number of connections, where T is the maximum number of threads:

T * (C - 1) + 1

 

DB settings

Connection pool settings are directly related to the number of connections that the database server is configured to support.

If we increase the maximum number of connections in the pool and the corresponding settings in the database are not increased accordingly, the application might fail.

One of the most common causes of connection deadlock is the use of the same connection pool by both servlets and by EJBs, and where the servlet directly or indirectly invokes the bean. For example, a servlet that obtains a JMS connection from the connection pool, sends a message to a Message Driven Bean and waits for a reply.

The MDB is configured to use the same connection pool as the servlet, therefore, another connection from the pool is required for the MDB to send a reply to the servlet. Servlets and enterprise beans do not share the same connection pool.

This is a classic case of concurrent (C) threads, where C=2 and T is the maximum size of the servlet and EJB thread pools.

 

Disable connection pooling.

For relational resource adapters, add the disableWASConnectionPooling custom property for the data sources...

JDBC | Data sources | data_source | Additional Properties | Custom properties | New

...and set...

disableWASConnectionPooling = true

For other resource adapters, consult with the binding specifications for that resource adapter to configure the applications to disable connection pooling.

1. Programmatically disable connection pooling through the resource adapter.

2. The appserver leverages the following code to detect the...

   javax.resource.NotSupportedException

   ....exception and disable connection pooling...

   
   // 169059 174269      managedFactory.matchManagedConnections(s,subject,cri); 
   }  catch(javax.resource.NotSupportedException e)
   {
   

 

Enable deferred enlistment.

In the appserver environment, deferred enlistment refers to the technique in which the appserver waits until the connection is used before the connection is enlisted in the application's unit of work (UOW) scope.Consider the following illustration of deferred enlistment:

• An application component that uses deferred enlistment calls the getConnection method from within a global transaction.

• The application component does not immediately use the connection.

• When the application issues the call for initial use of the connection, the transaction manager intercepts the call.

• The transaction manager enlists the XA resource for the connection and calls the XAResource.start method.

• The connection manager associated with the XA resource sends the call to the database.

Given the same scenario, but the application component does not use deferred enlistment, the component container immediately enlists the connection in the transaction. Thus the appserver incurs, for no purpose, an additional load of all of the overhead associated with that transaction. For XA connections, this overhead includes the two phase commit (2PC) protocol to the resource manager.

Deferred enlistment offers better performance in the case where a connection is obtained, but not used, within the UOW scope. The technique saves the cost of transaction participation until the UOW in which participation must occur.

Check with the resource adapter provider if we need to know if the resource adapter provides this functionality. The appserver relational resource adapter automatically supports deferred enlistment.

 

Incorporating deferred enlistment in your code:

The Java EE Connector Architecture (JCA) V1.5 spec calls the deferred enlistment technique lazy transaction enlistment optimization. This support comes through a marker interface (LazyEnlistableManagedConnection) and a new method on the connection manager (LazyEnlistableConnectionManager()):

 package javax.resource.spi;  import javax.resource.ResourceException;  import javax.transaction.xa.Xid;   interface LazyEnlistableConnectionManager 
{ 
    
// appserver void 
    lazyEnlist(ManagedConnection) throws ResourceException; 
}   interface LazyEnlistableManagedConnection 
{ 
    
// resource adapter 
} 

 

Control connection pool sharing.

Part of Fix Pack 3.

Use the defaultConnectionTypeOverride, or globalConnectionTypeOverride connection pool custom property for a particular connection factory or datasource to control connection sharing , or to globally set a datasource connection type.

The defaultConnectionTypeOverride property changes the default sharing value for a connection pool. This property enables you to control connection sharing for direct look-ups. If resource references are configured for this datasource/connection factory they will take precedence over this property and the resource ref settings will be used. For example: if an application is doing direct look-ups, and you do not want the connections pool shared. set this property to unshared.

The value specified for the globalConnectionTypeOverride custom property takes precedence over all of the other connection sharing settings. For example if we set this property to unshared, all connection requests are unshared for both direct look-ups, and resource reference lookups. This property provides you with a quick way to test the consequences of moving all connections for a particular datasoure/connection factory to unshared or shared without changing the resource reference setting in all the deployment descriptors for an application. The globalConnectionTypeOverride property also enables you to move between shared, and unshared connections for a particular datasource or connection factory without having to change any resource references.

If we specify values for both the defaultConnectionTypeOverride and the globalConnectionTypeOverride properties, only the values specified for the globalConnectionTypeOverride property is used to determine connection sharing type.

To add these new custom properties to the settings for a datasource or connection factory connection pool, a new custom property on the connection pool, in the admin console click JDBC providers > DB2 Universal JDBC Driver Provider > Data sources > DB2 Universal JDBC Driver DataSource > Connection pools > Custom Properties > New . Then specify defaultConnectionTypeOverride or globalConnectionTypeOverride in the Name field, and shared or unshared in the Value field.

 

Related concepts

Connection pooling
Transaction type and connection behavior

 

Related tasks

Set Java EE Connector connection factories in the admin console
Disable statement pooling
Set a data source

 

Related

Connection pool settings
Connection and connection pool statistics