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

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

2016-06-15 张开涛 开涛的博客

 

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，老项目还是慎重迁移（之前我迁移过是没有问题的，不过还是慎重）。

 

===========================