Have you known enough about DBCP？
【本文如需转载，请注明作者及出处】
?
I think a lot of people have misunderstanding about DBCP. What makes them think so?
Cause' DBCP seems don't want to release database connection after use. Finally, all available connections got exhausted, and our application runs out of services.
DBCP is weak and can not bear production pressures, according to them. It’s worse that there are a lot of guys on the internet said so.
In fact, DBCP can absolutely take the job!
Firstly, its release number has reached 1.2.2. Base on the Apache Version Numbering Project's theory, any products whose version number has evolved up to 1, it is eligible to be used in production environment.
Second, I've run into such problem from my previous working experience, and my ex-colleague and I have made extensive tests on various Connection Pool products, such as Proxool, c3p0 and DBCP.
Result shows that DBCP behaves very well not only at responsiveness, stability, but also at performance. Once the network resume, it auto-reconnects instantly even after more than 8 hours network disconnection.
Third, I do the test again, but this time it’s only between DBCP and c3p0. The test starts from running 3000?to 5000 threads, which infinitely?execute a query lasting at least 1 second. Effort have been made to let DBCP and C3p0 start equally, like the same initial pool size 0, the same max pool size and the same time out, etc.
?
?
Finally, I try to give you a detail introduction on the configurations of DBCP, focusing on the important parameters.
?
?All information comes from http://commons.apache.org/dbcp/configuration.html
Part1, Besides the Normal parameters:
username????????????????????????? ?The connection username to be passed to our JDBC driver to establish a connection.
password???????????????????????? ? The connection password to be passed to our JDBC driver to establish a connection.
url???????????????????????????????????????The connection URL to be passed to our JDBC driver to establish a connection.
driverClassName??????????????? The fully qualified Java class name of the JDBC driver to be used.
Part2, the following are related to performances:
initialSize??????????????????????????? The initial number of connections that are created when the pool is started.
maxActive?????????????????????????? The maximum number of active connections that can be allocated from this pool at the same time, or non-positive for no limit.
maxIdle??????????????????????????????The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
minIdle???????????????????????????????The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
maxWait??????????????????????????? ?The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.
poolPreparedStatements??? Enable prepared statement pooling for this pool.
maxOpenPreparedStatements? The maximum number of open statements that can be allocated from the statement pool at the same time, or zero for no limit.
Part3, but setting the above parameters or combining only some of the them won’t help you get rid of exhaustion of connection problem due to poorly written code, maybe that's source of problems. The following configuration matters:
validationQuery??????????????????????? The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.
timeBetweenEvictionRunsMillis The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
minEvictableIdleTimeMillis???????? The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).
removeAbandoned Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection.
removeAbandonedTimeout Timeout in seconds before an abandoned connection can be removed. the above two parameter must be set at the same time to make connection pool manageable.If we set
validationQuery="SELECT SYSDATE FROM DUAL"
removeAbandoned=true
removeAbandonedTimeout=30
timeBetweenEvictionRunsMillis=500
minEvictableIdleTimeMillis=60000
then, we should interpreted these as:
1) If there’s poorly written code which forget to close connections after use, and it lasts for 30 seconds, it will be removed from the pool!
2) There’ll be an evictor thread up to monitor connection objects. If one of the connection objects becomes idle and last for 5000 milliseconds, it will be marked as removable. After another short nap of 500 milliseconds, that's at between 60000 and 60500 milliseconds, if the same connection is still found idle, it will be removed from the pool.
So a typical configuration for oracle would be like this:
driverClassName=driverclass
url=dburl
username=user
password=password
maxActive=100
maxWait=-1
defaultAutoCommit=true
minIdle=0
maxIdle=30
poolPreparedStatements=true
validationQuery="SELECT SYSDATE FROM DUAL"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle = "false"
timeBetweenEvictionRunsMillis=500
removeAbandoned=true
removeAbandonedTimeout=30
minEvictableIdleTimeMillis=60000
1) It means when the pool starts up, poolPreparedStatements ability is enabled, transaction will be auto committed and there is no connections in it.
2) Any connections will be created on demand. If a new connection is requested, after creation, the new one will be tested against "SELECT SYSDATE FROM DUAL" before returning it to user, if it failed validation, yielding no result, the connection will be dropped, and another connection will be created and validated again until it pass.
3) If suddenly, floods of requests come in, the pool will create as many connections as it can to serve the request until the size reach 100. If requests continue to arrive, they will have wait before some connections finished the previous serving.
4) If there’s poorly written code which forget to close connections after use, and it lasts for 30 seconds, it will be removed from the pool!
5) When the flood begins to ebb, some of the connections will become idle and have nothing to do. If any connection object becomes idle and last for 60000 milliseconds, it will be marked as removable. After another short nap of 500 milliseconds, that's at between 60000 and 60500 milliseconds, if the same connection is still found idle, it will be removed from the pool.
For commons-dbcp-1.2.2 and commons-pool-1.3 and
we have to set
minIdle=0
testWhileIdle=false
to avoid a potential deadlock as described by
https://issues.apache.org/jira/browse/DBCP-44
Here is the test:
Environment:
Hardware: AMD Turion(TM) 64 X2 Mobile 1.8G??? and 1.5G Memory
Operating System: Windows XP
Database:
mysql :?5.0.45-community-nt MySQL Community Edition (GPL)
Max_used_connections 100
?
?
Oracle: 10G default settings
?
Pool implementaions:
1)commons-dbcp-1.2.2(commons-pool-1.3)
2)c3p0-0.9.1.2
database??dbcp/c3p0???concurrent_threads?????maxActive/poolSize???10minutes queries executed
mysql??????? dbcp/c3p0??????????????? 5000?????????????????????100??????????????????????? 16200/1600
oracle?????? dbcp/c3p0??????????????? 3000????????????????????? 10???????????????????????? 3691/1900
oracle?????? dbcp/c3p0??????????????? 3000??????????????????????50???????????????????????? 20900/6500
oracle?????? dbcp/c3p0??????????????? 5000??????????????????????50???????????????????????? 8400/2000
oracle?????? dbcp/c3p0??????????????? 5000??????????????????????80???????????????????????? 8800/1800
?
oracle?????? dbcp/c3p0??????????????? 5000?????????????????????? 100???????????????????????? ORA-12519/very slow, but no ORA-12519
(ORA-12519, TNS:no appropriate service handler found)
But if you want performance gained from dbcp, you should increase the value of "PROCESSES" parameter in oracle
?
package com.cabernet.dbcp;import java.sql.SQLException;import javax.sql.DataSource;import com.cabernet.BaseTestCase;/** * * @author ginge * */public class TestPooling extends BaseTestCase{protected DataSourcedatasource;public void testConnection() throws SQLException, InterruptedException{for (int i = 1000; i > 0; i--){new QueryThread(this.datasource).start();log.debug("PoolingThread[" + i + "] is up and running.");}while (true)Thread.sleep(100000);}public DataSource getDatasource(){return datasource;}public void setDatasource(DataSource datasource){this.datasource = datasource;}@Overrideprotected String[] getConfigLocations(){// TODO Auto-generated method stubreturn new String[] { "classpath*:applicationContext-resources.xml", "classpath*:applicationContext-jbpm.xml","classpath*:applicationContext-hibernate.xml" };}}?
?
?
?
?
package com.cabernet.dbcp;import java.sql.Connection;import java.sql.SQLException;import java.util.Random;import javax.sql.DataSource;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;/** * * @author ginge * */public class QueryThread extends Thread{private Loglog= LogFactory.getLog(this.getClass());DataSourcedatasource;private Randomrandom= new Random();QueryThread(DataSource datasource){this.datasource = datasource;}@Overridepublic void run(){// TODO Auto-generated method stubConnection connection = null;int i = 0;while (true){if (i < Integer.MAX_VALUE)i++;elsei = 0;try{connection = datasource.getConnection();} catch (Exception e){log.error("getConnection exception :", e);}try{if (connection != null){String query = "select * from jbpm_log where id_ = " + i;connection.createStatement().execute(query);log.debug("Thread[" + this.getId() + "] executeing [" + query + "]");sleep(1000);this.close(connection);//this.forgetToCloseSomeConnections(connection);}} catch (Exception e){log.error("query exception :", e);}}}private void close(Connection connection){try{connection.close();} catch (SQLException e){// TODO Auto-generated catch blocke.printStackTrace();}}private void forgetToCloseSomeConnections(Connection connection){if (random.nextInt(20) != 9)try{connection.close();} catch (SQLException e){// TODO Auto-generated catch blocke.printStackTrace();}else{if (log.isWarnEnabled()){log.warn("Connection not closed.");}}}}?
?
?
1 楼 dboylx 2008-03-17 It does't release database connection after use which is not the excatly problem. It offten confuse us to identify the problem where it is , but we are also quite sure that it is not the problem. When it suddenly floods of requests come in , there is something wrong in the application and be hanged, Meanwhile, we have checked all the code in java(which is very simple code), it's fine, but not work. However,when we change the connection's pool implement like Websphere's pool , the problem is just go off.your said that above could not persuade me to use dbcp again .