DBCP Connection Pool Introduction

DBCP Connection Pool Introduction

-----------------------------

Currently, there are two versions of DBCP, 1.3 and 1.4, respectively.

The DBCP 1.3 version needs to run on JDK 1.4-1.5 and supports JDBC 3.

The DBCP 1.4 version needs to run on JDK 1.6 and supports JDBC 4.

1.3 and 1.4 are based on the same set of source code, containing all bug fixes and new features. So when choosing the DBCP version, it depends on what JDK version you are using.

DBCP1.2 version of the general performance, much worse than C3P0. DBCP1.4 and 1.3, with the jar package (dependent) Commons Pool 1.6, all aspects of function, performance to advance to a new peak. The relative 1.2 version is improved a lot. Beyond (or quite) c3p0. Recommended use of DBCP1.4 or 1.3 + commons pool 1.6

The DBCP connection pool is retained in TOMCAT7 to be compatible with existing applications. A new Tomcat JDBC pool is provided as an optional alternative to the DBCP. The new Tomcat JDBC pool, which is said to be better than DBCP 1.4, is not in touch and is not covered in this article.

DBCP Connection Pool configuration parameters explained

-----------------------------

One, the Apache official DBCP document gives the configuration example:

See also: http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

<Context>

<resource name= "Jdbc/testdb" auth= "Container" type= "Javax.sql.DataSource"

Maxactive= "maxidle=" maxwait= "10000"

Username= "Javauser" password= "Javadude" driverclassname= "Com.mysql.jdbc.Driver"

Url= "Jdbc:mysql://localhost:3306/javatest"/>

</Context>

Second, the common parameter description:

See also: http://elf8848.iteye.com/blog/337981

<resource

Name= the name of the "Jdbc/testdb" Jndi data source

Type= "Javax.sql.DataSource"

Driverclassname= "Com.mysql.jdbc.Driver" JDBC Driver class

Url= ""

Username= "" Access database user name

Password= "" Access the password for the database

Maxactive= "80" Maximum active connection

Initialsize= "10" to initialize the connection

maxidle= "60" Maximum idle connection

minidle= "10" Minimum idle connection

Maxwait= "3000" The maximum waiting time, in MS, to fetch connections from the pool.

Validationquery = "Select 1" verifies the SQL statement used

Testwhileidle = "True" indicates whether the connection is verified by the idle connection collector, if any. If the detection fails, the connection is removed from the pool.

Testonborrow = "false" do not test when lending a connection, otherwise it will affect performance

Timebetweenevictionrunsmillis = "30000" Runs idle connection collector every 30 seconds

Minevictableidletimemillis = connection in "1800000" pool is recycled after 30 minutes of inactivity

Numtestsperevictionrun= "3" The number of connections checked at run time for each idle connection collector thread (if any)

Removeabandoned= "true" connection leak reclaim parameter, executed only when the number of available connections is less than 3

removeabandonedtimeout= "180" connection leak recovery parameters, 180 seconds, the time-out value of the leaked connection can be deleted

/>

Self-detection of DBCP connection pool

-----------------------------

The default configuration of the DBCP connection pool, is not to test the connection in the pool, sometimes the connection has been broken, but the DBCP connection pool do not know, and thought the connection is good.

Application removing such a connection from the pool accessing the database must be an error. That's why so many people don't like dbcp.

Problem Example One:

MySQL8 hours problem, MySQL server default connection "Wait_timeout" is 8 hours, that is, a connection idle for more than 8 hours, MySQL will automatically disconnect the connection.

However, the DBCP connection pool does not know that the connection has been disconnected, and if the program happens to use the disconnected connection, the program will report an error.

Problem Example Two:

In the past, the Sybase database was used, and for some reason the database was restarted after it died, or after a network break.

After waiting for about 10 minutes, the connection in the DBCP connection pool is still not available (disconnected), Access data application has been error, and finally can only restart the Tomcat problem to solve.

Solution:

Scenario 1, timed to test the connection, the test failed to close the connection.

Scenario 2, control the idle time of the connection to n minutes, close the connection (and then create a new connection).

The above two scenarios can be solved by using either of two types of problems. If you use Scenario 2 only, suggest N <= for 5 minutes. The connection can be resumed up to 5 minutes after it has been disconnected.

You can also mix two scenarios and suggest N = 30 minutes.

Here is the DBCP connection pool, which uses the configuration configuration of the above two scenarios

Validationquery = "Select 1" verifies that the connection is available, using the SQL statement

Testwhileidle = "True" indicates whether the connection is verified by the idle connection collector, if any. If the detection fails, the connection is removed from the pool.

Testonborrow = "false" do not test when lending a connection, otherwise it will affect performance

Timebetweenevictionrunsmillis = "30000" Runs idle connection collector every 30 seconds

Minevictableidletimemillis = The connection in the "1800000" pool is reclaimed after 30 minutes of inactivity, and the default value is 30 minutes.

Numtestsperevictionrun= "3" The number of connections that are checked at runtime for each idle connection collector thread (if any), the default value is 3.

Explain:

After configuring Timebetweenevictionrunsmillis = "30000", The Idle Connection collector (Independent thread) is run every 30 seconds. And each check 3 connections, if the connection idle time more than 30 minutes to destroy. After the connection is destroyed, the number of connections is less, if less than the number of minidle, the new connection, maintenance quantity of not less than Minidle, over the line of new and old replacement.

Testwhileidle = "true" means every 30 seconds, remove 3 connections, test with SQL in validationquery = "Select 1", and destroy the connection if the test is unsuccessful. After the connection is destroyed, the number of connections is less, and if the number is less than minidle, a new connection is made.

Testonborrow = "False" must be configured, because its default value is true. False indicates that every time a connection is taken out of the connection pool, it is not necessary to perform a test with SQL Validationquery = "SELECT 1". If configured to True, performance has a very large impact, performance will be reduced by 7-10 times. The location must be configured to False.

Every 30 seconds, remove the Numtestsperevictionrun bar connection (This example is 3, also the default), issue a "SELECT 1" SQL statement to test, the test connection is not considered "used", is still idle. The connection will be destroyed after 30 minutes of inactivity.

DBCP Connection Pool Configuration parameter considerations

-----------------------------

The Maxidle value should be configured close to the Maxactive value.

Because, when the number of connections exceeds the Maxidle value, the connection that has just been used (just idle) is immediately destroyed. Instead of I want the idle m seconds before destroying a buffering effect. This dbcp may be different from what you might have imagined.

If the difference between Maxidle and maxactive, in a high-load system will lead to frequent creation, destruction of the connection, the number of connections between Maxidle and maxactive fast and frequent fluctuations, this is not what I want.

The Maxidle value of a high-load system can be set to be the same as maxactive or set to 1 (1 for unlimited), allowing the number of connections to buffer slow fluctuations between minidle and Maxidle.

Timebetweenevictionrunsmillis Recommended Setting values

Initialsize= "5" will create 5 connections at the start of Tomcat, which is ideal.

But at the same time we also configured the minidle= "10", that is, to maintain a minimum of 10 connections, then there are only 5 connections, when and when to create a few 5 connections?

1, and other business pressure comes up, DBCP will create a new connection.

2, configure timebetweenevictionrunsmillis= "time", DBCP will enable independent worker thread timing check, make up less 5 connections. The same is true for destroying redundant connections.

Logic for Connection Destruction

------------------------------

The number of DBCP connections varies between 0-minidle-maxidle-maxactive. The logical description of the change is as follows:

When InitialSize is not configured by default (the default is 0) and the Timebetweenevictionrunsmillis parameter, the number of connections is 0 when you just start Tomcat. When an app has a concurrent Access database, DBCP creates a connection.

The current number of connections has not reached Minidle, but DBCP does not automatically create new connections that have reached the number of minidle (without a separate worker thread to check and create).

With the increase of application concurrent Access database, the number of connections also increased, but are not related to Minidle value, and soon Minidle was surpassed, Minidle value a little use.

Until the number of connections reaches the Maxidle value, the connection is only increased. Further development, the number of connections increased and more than Maxidle, the use of the connection (just idle) will be closed immediately, the total number of connections is stable maxidle but not more than Maxidle.

However, the active connection (the connection in use) may have an instantaneous amount of more than maxidle, but never more than maxactive.

At this point, if the application business pressure is small, access to the database is less concurrency, the number of connections will not be reduced (not a separate thread to check and destroy), will remain in the number of maxidle.

InitialSize is not configured by default (the default value is 0), but when you start Tomcat, the number of connections is 0 when you configure the timebetweenevictionrunsmillis= "30000" (30 seconds) parameter. Immediately when you apply a concurrent Access database, DBCP creates a connection.

Currently the number of connections has not reached Minidle, every 30 seconds DBCP worker thread checks whether the number of connections is less than minidle, if less than create a new connection until the Minidle quantity is reached.

As the application concurrently accesses the database, the number of connections increases until the Maxidle value is reached. During this period, each 30-second DBCP worker thread checks if the connection is idle for 30 minutes, if it is destroyed. But at this time is the peak of the business, there will not be up to 30 minutes of idle connection, working thread check is also a white check, but it is working. The number of connections here has been a growing trend.

When the number of connections increases more than Maxidle, the used connections (just idle) are immediately closed and the total number of connections is stable at maxidle. The trend of growth has ceased. However, the active connection (the connection in use) may have an instantaneous amount of more than maxidle, but never more than maxactive.

At this time, if the application business pressure is small, access to the database is less concurrency, every 30 seconds dbcp worker thread Check the connection (default 3) is idle for 30 minutes (this is the default value), if the connection is idle for 30 minutes, the connection is destroyed. At this point, the number of connections decreased, a downward trend, will move from Maxidle to Minidle. When the value is less than Minidle, then DBCP creates a new connection that has stabilized the number of Minidle and has been replaced by new and old.

When you configure Initialsize= "10", Tomcat creates 10 connections as soon as it starts. Other ibid.

Minidle is useful for working with Timebetweenevictionrunsmillis, and using minidle alone will not work.

Configuring DBCP connection pooling in Tomcat

-----------------------------

Tomcat comes with a dbcp bag, which is $catalina_home/lib/tomcat-dbcp.jar.

Tomcat-dbcp.jar contains the contents of the Commons Pool, Commons dbcp two packages. However, only the classes associated with the connection pool are included.

Data source configuration in the Context.xml file, to put the JDBC driver package in the Tomcat Lib directory

Data source configuration in Server.xml host, you do not need to put the JDBC driver package in the Tomcat Lib directory, use only the JDBC driver package in the project

Jndi configuration: Change Tomcat's Server.xml or context.xml

The Global data Source:

If you need to configure global Resource, add Resource to the globalnamingresources node of server.xml and add ResourceLink configuration to the context node.

The global resource is just for reuse and facilitates all data source management for Web engineering under this tomcat, but if your tomcat does not load multiple Web projects at the same time, which means that a tomcat only loads a Web project, It is not necessary to configure the global resource.

One data source per Web project:

Add the resource configuration to the $catalina_home/conf/context.xml root node context. This configuration method, you configure a data source in Context.xml, but there are 5 projects in Tomcat running at the same time, then the bad thing, this data source was created at Tomcat start 5, each project 1 data sources. The number of connections will be 5 times times the parameters you configured.

You can configure the data source directly with Context.xml only if your tomcat is loading only one Web project.

<resource name= "Jdbc/testdb"//Specifies the Jndi name that will be used for the configuration of the spring data source bean and the configuration of the ResourceLink

Type= "Javax.sql.DataSource"//Data source bed type, using standard Javax.sql.DataSource

Driverclassname= "Com.mysql.jdbc.Driver"//jdbc Drive

Url= "Jdbc:mysql://localhost:3306/test"//Database URL address

Username= "TEST"//database user name

password= "TEST"//Database Password

Maxidle= "40"//maximum number of idle connections

maxwait= "4000"//when the pool's database connection is already occupied, the maximum wait time

Maxactive= the largest database connection in "40"//Connection Pool

Removeabandoned= "true"

removeabandonedtimeout= "180"

Logabandoned= "true"//Discarded database connection is logged for tracking

factory= "Org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"/>

The factory here refers to which data source configuration class is used by the resource configuration, which uses the standard data source resource configuration class of Tomcat, which can also be written by itself. Implement the Javax.naming.spi.ObjectFactory interface. In some places the use of the Commons-dbcp.jar in the org.apache.commons.dbcp.BasicDataSourceFactory, if you use this will need to commons-dbcp.jar and its dependent jar package, are placed in Tomcat Lib , it is not enough to put the light under the web-inf/lib of the project.

There are several configurations for ResourceLink:

1) Conf/context.xml in the Tomcat installation directory, the global resource is directly exposed to all Web projects under the tomcat and added to the context node:

<resourcelink global= "Jdbc/testmdb" name= "Jdbc/testmdb" type= "Javax.sql.DataSource"/>

It is not recommended to use <RESOURCELINK/> in this file, rather than using <Resource/> to configure the data source directly, as explained above.

2) Conf/server.xml in the Tomcat installation directory, this method can specify which source to bind to which Web project.

<!--New, the first behavior loads the project configuration, the second line is the ResourceLink configuration required for the project--

<context docbase= "/web/webapps/phoenix" path= "" reloadable= "false" >

<resourcelink global= "Jdbc/testmdb" name= "Jdbc/testmdb" type= "Javax.sql.DataSource"/>

</context>

You can also use the <Resource/> direct configuration data source in this file without using <resourcelink/>.

3) Set up an XML file under conf/localhost/in the installation directory, and the file name is <yourappname>.xml. For example, if the project is named Test, the XML name is Test.xml.

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

<Context>

<resourcelink global= "Jdbc/testmdb" name= "Jdbc/testmdb" type= "Javax.sql.DataSource"/>

</context>

You can also use the <Resource/> direct configuration data source in this file without using <resourcelink/>.

4) added in the \webapps\test\meta-inf\context.xml context node of the Tomcat installation directory:

<resourcelink global= "Jdbc/testmdb" name= "Jdbc/testmdb" type= "Javax.sql.DataSource"/>

You can also use the <Resource/> direct configuration data source in this file without using <resourcelink/>.

The contents of this article are tested on tomcat6.0, and the source code of Commons dbcp is downloaded, and the tracking log is added to verify the theory of this article.

Connection pool Ranking (pure personal view)

-----------------------------

Tomcat JDBC Pool

DBCP 1.4

C3P0 speed.

BONECP is a good speed, but it enables many additional threads to recycle and shut down work.

Proxool an exception when high concurrency occurs

DBCP 1.2

Dbpool worst, bottom.

Reference document "Java Connection Pool Assessment report"

DBCP Connection Pool Introduction