Database connection Pool optimization configuration (DRUID,DBCP,C3P0)

This paper mainly describes the criteria of database connection pool parameter configuration, and gives the recommended configuration for common database connection pool (C3P0,DBCP,DRUID).

Factors to consider

1: Size of current connection DB 2: Concurrency Scenario 3: Response time for DB execution

Configuration considerations

1: Initialize connection: Consider setting to 3 connections. Consider setting it to 1 for a particularly large db size. Avoid too long start-up time;

2: Minimum Connection: The setting of this value can be considered consistent with the initialization of the connection;

3: Maximum connection: For a larger DB size, do not set the maximum connection too large, avoid the local maintenance of the db too large. If the number of concurrent numbers that correspond to the data source is too high, consider increasing the maximum number of connections.

4: Gets the time-out for the connection: If the connection is all occupied, the time to wait. Can be determined according to the current system response time, if the tolerance is high, can be larger. Low tolerance, set small dots.

5: When getting connections and releasing connection heartbeat detection: It is recommended to close all, otherwise each database access instruction will produce an additional two heartbeat detection instructions to the database, increasing the load on the database. The check for connection validity is replaced by a background idle connection check.

6: Connection Validity detection time: This value needs to be set in conjunction with the Wait_timeout,interactive_timeout value of the database. If the database is 120s, the heartbeat detection time within 120s larger the better. If it is too small, heartbeat detection time will be more frequent. The recommended setting is 90s.

7: Maximum idle time: If the connection has not been used for more than that time, it will be close off. The value is not too small to avoid frequent connection closing connections. Don't be too big, cause it can't be closed.

8: Heartbeat Check SQL statement: To use the ping command as much as possible, the ping performance is higher than the query statement. The ping command is called when most database connection pools do not have a query statement configured.

9:preparestatement cache: You can decide whether to turn on or off according to your business. The impact on performance depends on the specific business and concurrency situation. You may consider temporarily not opening.

10: Connection Use timeout: The business gets a connection, if it is not returned after the specified time, whether the connection is to be recycled. Time-outs and other specific business associations. It is recommended that you do not open it temporarily.

The following is mainly given: druid,dbcp,c3p0 three kinds of connection pool recommended configuration

Druid Configuration

Description: Https://github.com/alibaba/druid

Recommended configuration:

InitialSize	3	Initialize configuration
Minidle	3	Minimum number of connections
Maxactive	15	Maximum number of connections
Maxwait	5000	Gets the connection time-out (unit: MS)
Timebetweenevictionrunsmillis	90000	Connection Validity detection Time (unit: MS)
Testonborrow	False	Get connection detection
Testonreturn	False	Return connection detection
Minevictableidletimemillis	1800000	Maximum idle time (unit ms)
Testwhileidle	True	Determine if you want to check the connection space time after getting the connection

Configuration Description:

1:minevictableidletimemillis (Maximum idle time): Default is 30 minutes, the configuration is not set.

2:testonborrow, Testonreturn is off by default and can be set to Not configured.

3:testwhileidle (determines whether to check for connection idle time after getting the connection). The default is true. Configuration is no longer set.

Process Description:

1: initialsize initialization is performed when the connection is first called.

2: Heartbeat detection Time thread, will hibernate timebetweenevictionrunsmillis time, and then only the thread (without borrow thread minus minidle) is checked, Close if the idle time is greater than minevictableidletimemillis.

3:testwhileidle must be set to true, after acquiring the connection, check testonborrow before determining Testwhileidle, if the connection idle time is greater than Timebetweenevictionrunsmillis, Heartbeat detection is performed.

4: Do not need to configure Validationquery, if not configured, will go ping command, higher performance.

5: The connection is saved in the array, gets the connection, gets the last digit of the array. Check the validity of the connection before Timebetweenevictionrunsmillis.

DBCP Configuration

Description: http://commons.apache.org/proper/commons-dbcp/configuration.html

Recommended configuration:

Parameters	Configuration	Description
InitialSize	3	Initialize configuration
Minidle	3	Minimum number of connections
Maxidle	15	Maximum idle connection
Maxtotal	15	Maximum number of connections
Maxwaitmillis	5000	Get connection time-out (in units of MS)
Timebetweenevictionrunsmillis	90000	Heartbeat detection Time (unit ms)
Minevictableidletimemillis	1800000	Maximum idle time (unit ms)
Testonborrow	FALSE	Get connection detection
Testonreturn	FALSE	Return connection detection
Numtestsperevictionrun	-1	Number of idle connection checks
Testwhileidle	TRUE	Whether to turn on check for idle connections

Configuration Description:

1: About Maxidle and maxtotal try to stay consistent.

The 2:numtestsperevictionrun is set to-1, which means that all connections are checked. The default value is 3. -1 represents the validity of the connection check for all idle. Otherwise, it is possible that the validity of the partial connection has not been checked.

3:testwhileidle must also be true, representing the need to check for validity.

The default value of 4:minevictableidletimemillis is 30 minutes and can be set without setting.

5:testonreturn the default value is False, you can not set it. However, the Testonborrow must be set to False, and the default value is true.

6:validationquery do not configure the default go ping command

Process Description:

1: initialsize initialization is performed when the connection is first called.

2: Do not need to configure Validationquery, if not configured, will go ping command, higher performance.

3: The connection is saved in the Linkedblockingdeque. To do concurrency control.

4: The backend will have a timed task at intervals of Timebetweenevictionrunsmillis, first determining how many threads need to be detected (Numtestsperevictionrun control), Then determine whether to exceed Minevictableidletimemillis, if more than the close off. If the Testwhileidle is true, the heartbeat check is not exceeded. If the check fails, the connection is closed.

5: When the return connection is determined Maxidle, if the current idle connection is greater than maxidle, the connection will be closed off.

C3P0 Configuration

Description: http://www.mchange.com/projects/c3p0/

Recommended configuration:

Parameters	Recommended values	Description
Initialpoolsize	3	Initialize configuration
Minpoolsize	3	Minimum number of connections
Maxpoolsize	15	Maximum number of connections
Acquireincrement	1	Number of each fetch
Checkouttimeout	5000	Get connection time-out (in units of MS)
Idleconnectiontestperiod	90	Heartbeat detection Time (unit s)
MaxIdleTime	1800	Maximum idle time (unit s)
Testconnectiononcheckout	FALSE	Get connection detection
Testconnectiononcheckin	FALSE	Return connection detection
Numhelperthreads	1

Configuration Description:

1:testconnectiononcheckout and Testconnectiononcheckin default to False, no configuration is necessary

2:preferredtestquery does not have to be configured, the default ping command.

3:numhelperthreads default is to turn on 3 threads. If there are more data sources, there will be more threads here. This is set to 1, to avoid the case of more threads.

Process Description:

1: initialpoolsize initialization is performed when the connection is first called.

2: Heartbeat detection is performed on all idle connections during heartbeat detection. If the total connection is found to be less than the minimum number of connections, the connection is created and the minimum number of connections is maintained.

Original link: 51861015

Database connection Pool optimization configuration (DRUID,DBCP,C3P0)