C3P0 Configuration Connection Test

Original: http://www.mchange.com/projects/c3p0/#configuring_connection_testing

C3P0 provides a variety of methods for detecting failed connections, preventing programs from using invalid connections to complain. Connections fail for many reasons: JDBC driver actively shuts down long time connections, database or network reasons, insufficient resources, driver bugs, or other reasons.

C3P0 provides a number of flexible connectivity testing methods, with the following parameters:

Automatictesttable

Connecttesterclassname

Preferredtestquery

Idleconnectiontestperiod

Testconnectiononcheckin

Testconnectiononcheckout

Idleconnectiontestperiod,testconnectiononcheckin,testconnectiononcheckout is used to control when tests are performed, automatictesttable, Connecttesterclassname,preferredtestquery is used to control how to test.

When configuring a connection test, the first consideration is to reduce the cost per test. If you are using a JDBC driver that supports the JDBC4 API and uses c3p0-0.9.5 or later, these tasks can be delivered directly to the driver for you to complete. The JDBC4 connections contains a IsValid () method, and the JDBC4 implementation provides a fast, reliable connection test. C3P0 uses this method to test by default.

If your driver does not support the latest api,c3p0, the connection test will be done by default via the Gettables () method. This approach works for any database, but calling the Databasemetadata.gettables () method is slower than a normal query, which can greatly affect the performance of the connection pool.

The easiest way to use a driver below JDBC3 (or C3P0 version below 0.9.5) is to define a query statement through the Preferredtestquery parameter. However, if there is no corresponding table in the database, the query will cause errors. Queries that are not dependent on tables, such as Select 1, are ideal for detecting connections, depending on the database and JDBC drivers. If in addition to Preferredtestquery is not enough, you can set the parameter automatictesttable. C3P0 creates an empty table based on the name you provide and creates a simple query detection database connection.

Detection is the most reliable when Connectioin is checked out. But from the client's point of view, this is the most time-consuming way. The reliability requirements of most programs can be met by Idleconnectiontestperiod and Testconnectiononcheckin combination configuration. Both idle detection and check-in detection are performed asynchronously and can improve performance.

At some point, the high performance is more important than the risk of connection exceptions, you can use the default configuration, C3P0 does not perform any checks by default. You can set a longer idleconnectiontestperiod, and you can get higher performance when checking in and out.

Some suggestions for connection testing:

1. If the JDBC driver supports the JDBC4 Connection.isvalid () method and uses the version above c3p0-0.9.5, you do not need to set the preferredtestquery parameters. If the drive does not support the IsValid method, you can set the Referredtestquery to select 1 (for MySQL and Postgres).

2. The easiest way to do this is to set the Testconnectiononcheckout to true, which will ensure that the program works, but this can affect the client's connection overhead.

3. If you want to eliminate connectivity testing to improve client performance:

A. Setting up Testconnectiononcheckout=false

B. Setting up Testconnectiononcheckin=true

C. Set up idleconnectiontestperiod=30, start the program and observe. This is a more appropriate setting, and all connections are checked at 30s intervals in the check-in and thread pools. This allows the program to encounter very few connection failures, and the connection pool recovers quickly after the database restarts. But this will have a certain management costs.

D. Since database restarts are rare, fast recovery is not a major concern, and you can consider the frequency of detection, such as setting up idleconnectiontestperiod=300 to see if there is a connection failure on the client. Determine the most appropriate value for IDLECONNECTIONTESTPERIOD based on the test situation. Attempt to set Testconnectiononcheckin to false to reduce the cost of connection check-in. If you still have a connection failure problem, you can reduce the Idleconnectiontestperiod value and set Testconnectiononcheckin to True. These parameters do not have the most appropriate value: can only be set according to the actual situation.