8.5.1 Using connection pooling

The advantages of using connection pooling in the J2EE server and the functionality that can be used in relation to connection pooling are explained below:

Also, acquire a connection of DB Connector in Cosminexus JPA provider. For details about estimating the number of connections used in Cosminexus JPA provider, see 6.2.4 Estimating the number of connections of DB Connector in the uCosminexus Application Server Common Container Functionality Guide.

Organization of this subsection
(1) Advantages of using connection pooling
(2) Usable functionality

(1) Advantages of using connection pooling

Establishing a connection with an EIS such as a database is a process that involves excessive loading. The load can be reduced by using connection pooling. Connection pooling is a function that aims to enhance the processing efficiency by pooling the connections that are secured and generated by a J2EE server and reusing those connections. Connection pooling is effective in preventing the deterioration of performance rather than acquiring a connection each time the database is accessed.

The functionality that can be used differs depending on the method used for connecting with the EIS. For details, see 3.14.1 Connection pooling in the uCosminexus Application Server Common Container Functionality Guide.

By default, connection pooling is enabled.

(2) Usable functionality

The functionality that can be used in connection pooling and the guidelines for setup are explained below.

The following functionality can be used in the case of connection pooling:

#1
When this functionality is used, you can also set the timeout for checking the connections.
#2
This functionality is disabled by default. Use if required.
#3
This functionality can be used only when the database is Oracle10g or Oracle11g.
(a) Specify the minimum and maximum number of connections that can be pooled

The setup of connection pooling requires you to set the minimum and maximum number of connections that can be pooled. If you specify unlimited maximum number of connections, unlimited connections are established.

Set the minimum and maximum values by referring the values such as number of concurrently executed accesses to an EIS like the database, the number of transactions, and the number of concurrent executions of the business that occurs normally.

We recommend that the maximum number of connections to be pooled be set up in such a way so that the following relationship expression is satisfied between the number of concurrently executed threads and the Session Bean instance pool:

Maximum number of connections to be pooled = Session Bean instance pool = Number of concurrently executed threads

(b) Check the connections in the pool and delete unnecessary connections

Check that there is no failure in any connection in the pool when acquiring a connection or check for any failure in the connection pool at regular intervals and delete the connection in which a failure has occurred, from the pool (detect connection failure). This helps to prevent establishing a connection in which a failure has occurred at the point of time it was acquired and reduces the probability of connection failure. This functionality is effective in the case of connections for accessing the database using the DB Connector. This functionality cannot be used when direct is specified in the selectMethod property of the DB Connector.

The guidelines for deciding the timing for detecting the connection failure are given below. Decide according to the type of business.

Table 8-23 Guidelines for deciding the timing for detecting the connection failure

Type of businessTiming
  • Business that does not allow failures in connecting with the EIS
  • Business having low connection frequency
Hitachi recommends settings to detect any failure when acquiring a connection.
The processing time required for acquiring a connection is more as compared to when detection of any failure in acquiring a connection is not executed, but the risk of acquiring a connection in which a failure has occurred is reduced.
  • Business with high connection frequency
  • Business that allows connection to a certain extent even when the secured connection has a failure
Hitachi recommends settings to regularly check for any failures. You can prevent the performance from deteriorating by the process of detecting a connection failure, by prolonging the check interval to some extent. There is a risk of acquiring a connection in which a failure has occurred.

When detection of connection failure is performed, you can also set a timeout for detecting a connection failure. Sometimes, when there is no response from the resource due to a server failure and a network failure, it is likely that there may be no response for detecting a connection failure. Timeout is set so that the process of detecting a connection failure can be finished and the process can continue even when there is no response from the resource. In such a case, it is assumed that a connection failure has occurred.

Hint
  • When timeout is specified for detecting a connection failure, the connection management threads are generated according to the number of connections in the connection pool of the system. You need to be more careful because when timeout is specified for detecting a connection failure, a large amount of memory is consumed as compared to when a timeout is not specified. The number of connection management threads created is twice the maximum number of connections in the connection pool. Estimate the required memory appropriately.
  • Connection management threads are used along with the timeout of connection adjustment functionality. The timeout of connection adjustment functionality is also enabled when a timeout is specified for detecting a connection failure.
  • When detecting connection failure by enabling the timeout for detecting connection failure, the unused connections that are removed from the connection pool are not counted in the number of connections of connection pool. As a result, the sum of the connections in the connection pool and the unused connections that are removed from the connection pool might temporarily exceed the maximum number of connections specified for the connection pool.
(c) Retry connection when it fails

If an attempt to connect fails, it is no longer necessary to use a user program for retrying the connection. Set the retry connection so that even when there is decrease in the response of business processing, the business does not terminate due to the failure.

The guidelines for setting a retry frequency and retry interval are as follows:

(d) Automatically delete the unused connections from the connection pool by using a sweeper

The connections that remain idle for a given period of time are automatically deleted from the pool by using a sweeper. Set this functionality when it is likely that the failure will occur if the unused connections are pooled. This functionality can be set in the resource adaptor.

(e) Pool the connections in advance by using connection warming up

This is a functionality for securing the minimum number of connections in advance and pooling them when a resource adaptor or a J2EE server is started. With this functionality, you can enhance the response for the connection request from the application immediately after beginning to use the connection pool. The time required for processing is longer when the resource adaptor or the J2EE server is started.

(f) Enter a request for acquiring a connection in a queue when all connections are exhausted

This functionality enters a request for acquiring a connection in a queue, when there is a request for connection after all the pooled connections are exhausted.

The request for connection can be re-opened immediately when a used connection is released or when a connection is deleted and the number of connections falls below the maximum number of pooled connections. Additionally, the waiting time can be specified, and hence, an error might occur when the connection cannot be established after the specified period of time is elapsed.

(g) Reduce unnecessary connections from the connection pool gradually

This functionality reduces the number of connections in case there are unnecessary connections in the connection pool (Connection adjustment functionality).

Check the number of connection pools at regular intervals. With the maximum number of concurrently executed connections until the check is performed as the standard value, delete the excess connections from the pool if there are connections in the pool that exceed this standard value. This will reduce the connections in the pool to a number adequate for actual operational performance and enable economizing on resources and costs of generating a connection.

You can specify a timeout value for the deletion process of connections. Use connection management thread for timeout. Connection management thread is also used for the timeout of detecting a connection failure.

Hint
Connection management thread is used along with the timeout of detecting a connection failure. When a timeout has been set for the connection adjustment functionality, the timeout is also enabled for detecting a connection failure.
Also, if you use the connection adjustment functionality, the unused connections removed for adjusting the number of connections of connection pool are not counted in the number of connections of connection pool. As a result, the sum of the connections in connection pool and the unused connections that are removed from the connection pool might temporarily exceed the maximum number of connections specified for the connection pool.
(h) Clustering the connection pool

When a database has a cluster configuration, you can also set the cluster configuration for connection pools and maintain a connection pool for each database node having the cluster configuration (Cluster connection pool functionality). Database node and connection pool have a one-to-one relationship. If a failure occurs in the database node, then its corresponding connection pool is also stopped temporarily and becomes unavailable. This enhances the usability of the system since there is no risk of using a connection in which a failure has occurred.