应知道的数据库连接池DBCP配置及JDBC超时设置

应知道的数据库连接池DBCP配置及JDBC超时设置

2014年618前夕的某个晚上的某个系统的sql执行时报错了：

<!--添加同步数据--><insert id="insert" parameterClass="order">  INSERT INTO aa(ID,ORDERID,CREATEDATE)  VALUES  (seq.Nextval,#orderId#,#createDate#)  <selectKey resultClass="java.lang.Long">    SELECT seq.CURRVAL FROM DUAL  </selectKey></insert>
会抛出800多条如下错误
Caused by: java.sql.SQLException: ORA-01013: 用户请求取消当前的操作at Oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)at oracle.jdbc.driver.T4CTTIoer.PRocessError(T4CTTIoer.java:331)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
 
原因是sql执行时间太长，jdbc驱动主动去取消了操作。
 
建议：
1、看一下该sql的平均执行时间，对该sql设置一个超时时间。（执行时间太长会对占用着连接，造成其他人拿不到连接）
2、找DBA咨询下有没有办法优化下该sql，比如能不能并行插入。或者能不能做分区。
 
1、数据源配置
如果使用apache dbcp时，且可能遇到连接数瓶颈时，可以调整如下配置：
 
<!—建议以下值尽量一样，没必要频繁的过期空闲连接（除非比如连接池资源紧缺，可以考虑） --><property name="maxIdle" value="80" /><property name="minIdle" value="80" /><property name="initialSize" value="80"/><property name="maxActive" value="80" />
 
<!—这个是等待获取连接池连接时间，也不要太大，比如设置在500毫秒 --><property name="maxWait" value="500" />
<!-- 移除无引用连接（那些没有close的连接）此处设置为false，需要保证程序中连接一定释放 -->
<property name="removeAbandoned" value="false"></property><property name="removeAbandonedTimeout" value="300000"></property><!-- 一个连接空闲多久从池中移除，此处不做判断--><property name="minEvictableIdleTimeMillis" value="-1" /><!-- 过期时循环测试多少次（0 就相当于关闭定时器） --><property name="numTestsPerEvictionRun" value="0" /><!-- expire connection 定时器周期 --><property name="timeBetweenEvictionRunsMillis" value="120000" />
<!-- 当连接空闲时是否测试，即保持连接一直存活，配合expire connection 定时器使用 -->
<property name="testWhileIdle" value="false"></property>
 
如果是MySQL库，可能存在8小时问题，可以考虑开启过期定时器(numTestsPerEvictionRun=1)，定期过期一下连接，timeBetweenEvictionRunsMillis时间可以设置在8小时左右.
 
另外可以通过如下配置来配置socket连接/读超时：
<property name="connectionProperties"
value="oracle.net.CONNECT_TIMEOUT=2000;oracle.jdbc.ReadTimeout=2000"></property>
（此处的连接和读取超时时间，请根据自己业务来考虑大小）
 
更多配置可参考http://www.importnew.com/2466.html
 
2、ibatis配置
**项目使用的是ibatis-sqlmap-2.3.4.726.jar版本，而从2.3.1起：
o Removed maxTransactions, maxRequests, maxsessions from configuration, all are now controlled by the resource providers。（即已经移除了maxTransactions, maxRequests, maxSessions配置）
  
因此我们只需要如下配置：
<settings cacheModelsEnabled="false" enhancementEnabled="true"
lazyLoadingEnabled="false" errorTracingEnabled="true" maxRequests="32"
defaultStatementTimeout="2"/>
 
defaultStatementTimeout单位是秒；根据业务配置。
  
如果想只设置某个Statement的超时时间，可以考虑：<insert ……timeout="2">
 
之前线上报如下错误，原因就是statement执行超时了。
Cause:java.sql.SQLException:ORA-01013:用户请求取消当前的操作
 
3、spring事务管理器配置
提供全局的事务级别的超时时间：
<bean id="oracleTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="oracleDataSource" />
  <property name="defaultTimeout" value="2"/>
</bean>
 
总结：
超时设置主要有以下几个：
1、连接超时
2、读数据超时
3、Statement超时
4、事务级别的超时=N* Statement超时 + GC 暂停time
 
之前总结过事务超时的一些问题，有兴趣可以参考下：
http://jinnianshilongnian.iteye.com/blog/1986023
http://www.importnew.com/2466.html
 
另一个数据库连接池需要注意的点：
<bean id="msqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
如果没有加destroy-method ，而且重启次数太频繁，造成重启tomcat 旧的数据库连接池的连接不释放，这样会有好多数据库连接占着一段时间不释放；所以最好加上destroy-method。
//////////////////////乱七八糟///////////////////////
当超过最大的连接数目的时候，会删除连接。
if (config != null && config.getRemoveAbandoned() && (getNumIdle() < 2) && (getNumActive() > getMaxActive() - 3) ) {
  removeAbandoned();
}
这段代码的作用是失效孤儿连接，即有人拿到连接但是没有close的。 
 
1、网络阻塞/不稳定时的级联效应（比如我现在写的ssdb-client 在网络出现故障（网络不可用）时 我会设置一个时间，在这个时间内的请求全部tiemout）
连接池内部应该根据当前网络的状态（比如超时次数太多），对于一定时间内的（如100ms）全部timeout，根本不进行await(maxWait)。
还有一个就是当前等待连接池的人数，比如现在等待1000个，那么接下来的等待是没有意义的，这样还会造成滚雪球（ssdb-client采用了这种策略）。
2、等待超时应该尽可能小点（除非很必要），即使返回错误页，也比等待强。
dbcp的比较容易出问题的地就是 设置的超时时间太长，造成大量的TIMED_WAIT，线程阻塞，而且是滚雪球，一旦出问题很难立即回复，而且这个可以通过[1]说的解决。 
 
大部分数据库client都会有一个取消statement执行的功能（即假设我们设置QueryTimeout=2秒，如果2秒内没返回信息，那么有个任务会主动发送一个取消的sql去取消当前statement的执行）
1、mysql每个连接会创建一个Timer（每个Timer会创建一个Thread）
2、每创建一个Statement会提交一个TimerTask(每个Task在执行时会创建一个Thread）
也就是说假设我们500个连接池，每个连接执行1个statement，最坏的情况下会创建：
500*1+500*1=1000个线程。
假设一个应用中有三个mysql库，那么最坏情况下有：
1000*3=3000个线程创建。
如果我们数据库采用了分库分表或者读写分离，可想而知。在压力大的时候。
假设os对线程释放不是特别快的话，cancel掉的线程可能并不是立即可用（我不确定，熟悉的同学可解释下）。
 
而oracle采用不同的策略：
1、每个ClassLoader一个watchdog 线程（类似于mysql的timer）；
2、每个Statement一个Task，而线程是在watchdog需要取消时去触发的，即watchdog发现该Statement需要cancel时，调用其某个方法，该方法快速创建线程并运行；
也就是说假设我们500个连接池，每个连接执行1个statement，最坏的情况下会创建：
1+500*1=501个线程。
假设一个应用中有三个mysql库，那么最坏情况下有：
1 + 500*3=1501个线程创建。
解决方案：
1、最好的方案是改mysql实现。
2、修改底层系统支持的线程数。
//////////////////////乱七八糟///////////////////////
 
另外dbcp 1.x使用的是commons-pool 1.x，高并发下性能不是很好；考虑升级2.x或者如果新项目可以考虑使用druid或proxool，老项目还是慎重迁移（之前我迁移过是没有问题的，不过还是慎重）。
 
===========================