- 浏览: 208639 次
- 性别:
- 来自: 杭州
最新评论
-
李嘉图0624:
第一段代码就出错了,少了g.close();不关闭的话,也没有 ...
Jackson 入门 -
daichangfu:
不光是生僻字需要,正常的也需要啊。
解决Java应用在Linux下无法正常水印生僻字 -
yaoweinan:
不错
JGroup配置笔记 -
learnworld:
grandboy 写道我想json的数据多一些对于数据绑定回j ...
Jackson 入门 -
grandboy:
我想json的数据多一些对于数据绑定回javabean时不会出 ...
Jackson 入门
【本文如需转载,请注明作者及出处】
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
processes that can simultaneously connect to Oracle. Its value should
allow for all background processes such as locks, job queue processes,
and parallel execution processes.
Jdbc.properties
cabernet.jdbc.driverClassName=com.mysql.jdbc.Driver
cabernet.jdbc.url=jdbc:mysql://g-mobile:3306/jbpm?useUnicode=true&characterEncoding=utf8&autoReconnect=true
cabernet.jdbc.username=root
cabernet.jdbc.password=root
#dbcp
cabernet.jdbc.maxActive=100
##max.Wait means waiting until one is available
cabernet.jdbc.maxWait=-1
cabernet.jdbc.defaultAutoCommit=true
cabernet.jdbc.minIdle=0
cabernet.jdbc.maxIdle=30
cabernet.jdbc.removeAbandoned=true
cabernet.jdbc.removeAbandonedTimeout=30
cabernet.jdbc.poolPreparedStatements=true
cabernet.jdbc.validationQuery=select current_date();
cabernet.jdbc.testOnBorrow=true
cabernet.jdbc.testOnReturn=false
cabernet.jdbc.testWhileIdle=false
cabernet.jdbc.timeBetweenEvictionRunsMillis=500
cabernet.jdbc.minEvictableIdleTimeMillis=16000
cabernet.jdbc.logAbandoned=true
#c3p0
cabernet.jdbc.acquireIncrement=5
cabernet.jdbc.idleConnectionTestPeriod=3000
cabernet.jdbc.maxIdleTime=60
###checkoutTimeout=0 means waiting until one is available
cabernet.jdbc.checkoutTimeout=0
cabernet.jdbc.maxPoolSize=100
cabernet.jdbc.minPoolSize=0
cabernet.jdbc.maxStatements=1000
cabernet.jdbc.maxStatementsPerConnection=1000
cabernet.jdbc.initialPoolSize=0
cabernet.jdbc.autoCommitOnClose=true
Test Code:
package com.cabernet.dbcp; import java.sql.SQLException; import javax.sql.DataSource; import com.cabernet.BaseTestCase; /** * * @author ginge * */ public class TestPooling extends BaseTestCase { protected DataSource datasource; 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; } @Override protected String[] getConfigLocations() { // TODO Auto-generated method stub return 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 Log log = LogFactory.getLog(this.getClass()); DataSource datasource; private Random random = new Random(); QueryThread(DataSource datasource) { this.datasource = datasource; } @Override public void run() { // TODO Auto-generated method stub Connection connection = null; int i = 0; while (true) { if (i < Integer.MAX_VALUE) i++; else i = 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 block e.printStackTrace(); } } private void forgetToCloseSomeConnections(Connection connection) { if (random.nextInt(20) != 9) try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } else { if (log.isWarnEnabled()) { log.warn("Connection not closed."); } } } }
- logs.rar (383.5 KB)
- 描述: the test logs
- 下载次数: 19
评论
your said that above could not persuade me to use dbcp again .
发表评论
-
开发新手通用代码问题
2011-06-04 06:32 13170最近辅导黄埔的同学进行开发,发现了一些问题,隐约看到当年自己写 ... -
jmockit 的使用系列
2010-12-13 21:30 94171. 入门 2. 完整的Mock步骤 ... -
jmockit 的使用示例-02完整的Mock步骤
2010-12-13 21:11 4876package jmockit.sample; impo ... -
jmockit 的使用示例-03全部mock的?
2010-12-13 21:11 3644package jmockit.sample; impo ... -
jmockit 的使用示例-04静态部分mock示例
2010-12-13 21:10 3863package jmockit.sample; impo ... -
jmockit 的使用示例-06私有成员的Mock
2010-12-13 21:10 7652目标测试代码 package jm ... -
jmockit 的使用示例-07私有静态成员Mock
2010-12-13 21:09 4690目标测试代码 package jmockit.target ... -
jmockit 的使用示例-09构造方法也可以Mock?基于状态的Mock?
2010-12-13 21:07 10376目标测试代码 package jmockit.target ... -
jmockit 的使用示例-10另一种基于状态的Mock,随穿随脱?
2010-12-13 21:07 2705目标测试代码 package jm ... -
jmockit 的使用示例-11不是吧,还能访问实际被Mock的对象?
2010-12-13 21:07 3231目标测试代码 package jmockit.target ... -
jmockit 的使用示例-07私有静态成员Mock
2010-12-13 20:40 5889目标测试代码 package jmockit.target ... -
jmockit 的使用示例-02完整的Mock步骤
2010-12-13 20:31 1462package jmockit.sample; impo ... -
开放的心态
2010-10-02 13:56 982今天看李笑来的《把时间当作朋友》,看到一句话,“我们的大脑 ... -
Jackson 入门
2010-09-15 07:17 6492同事的一些测试结果看来,Jackson在处理Json方面性能相 ... -
解决Java应用在Linux下无法正常水印生僻字
2010-04-10 13:46 5616昨天接到一个投诉,说是他的水印是一个方框。刚开始还以为是程序出 ... -
同学们,秒投简历啦!
2010-03-12 23:51 221公司业务急速扩张,急需有才能的同学加入到我们的行列中, ... -
反编译工具
2009-12-31 20:54 1132追查JVM崩溃过程中使用到的反编译工具,其中beanutils ... -
追查JVM崩溃
2009-12-31 20:25 2241JDK升级到1.6后,服务器集群经常崩溃,后来把VM的参 ... -
普通用户下实现Apache 2.2.14与jboss-5.1.0.GA集成
2009-11-29 23:05 2767Apache与jboss集成,实际是apache与tomcat ... -
swt 笔记
2009-11-27 00:24 891http://www.ibm.com/developerwor ...
相关推荐
dbcp jar包 一个是dbcp的包, 一个是pool包, 两者都导入工程
开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发工具 commons-dbcp2-2.1.1开发...
创建dbcp连接,dbcp(Spring)
DBCP(DataBase Connection Pool)是 apache common上的一个 java 连接池项目,也是 tomcat 使用的连接池组件,依赖 于Jakarta commons-pool 对象池机制,DBCP可以直接的在应用程序中使用。 使用DBCP会用到commons-...
DBCP的依赖Jar包,完整的,亲测能用,欢迎下载!DBCP的依赖Jar包,完整的,亲测能用,欢迎下载!
DBCP连接池DBCP和C3P0配置,可以对数据源进行各种有效的控制
commons-dbcp-1.2.2 commons-dbcp-1.2.2 commons-dbcp-1.2.2
SpringMVC链接mysql数据库,配置需要用到的两个包class="org.apache.commons.dbcp.BasicDataSource
commons中的DBCP连接池jar,用于利用dbcp链接数据库
赠送jar包:commons-dbcp-1.4.jar; 赠送原API文档:commons-dbcp-1.4-javadoc.jar; 赠送源代码:commons-dbcp-1.4-sources.jar; 赠送Maven依赖信息文件:commons-dbcp-1.4.pom; 包含翻译后的API文档:commons-...
DBCP连接池介绍 ----------------------------- 目前 DBCP 有两个版本分别是 1.3 和 1.4。 DBCP 1.3 版本需要运行于 JDK 1.4-1.5 ,支持 JDBC 3。 DBCP 1.4 版本需要运行于 JDK 1.6 ,支持 JDBC 4。 1.3和1.4基于...
DBCP
commons-dbcp.jar dbcp数据库连接池驱动,包括pool、logging两个依赖
commons-dbcp-1.4,到官网下载慢到抽筋,传这里来了。
dbcp连接池
开源数据库连接池dbcp及其文档
DBCP1.4.chm帮助文档,更好地理解和掌握dbcp数据库连接池。
commons-dbcp-1.3-javadoc.jar, commons-dbcp-1.3-RC1.jar, commons-dbcp-1.3-sources.jar, commons-dbcp-1.3.jar, commons-dbcp-1.4-javadoc.jar, commons-dbcp-1.4-sources.jar, commons-dbcp-1.4.jar, commons-...
DBCP 数据库连接池 DBCP 数据库连接池 DBCP 数据库连接池 里面是DBCP的jar,导进去就可以用了
commons-dbcp2