應知道的資料庫連接池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，老項目還是謹慎遷移（之前我遷移過是沒有問題的，不過還是謹慎）。

 

原文出自：http://mp.weixin.qq.com/s?__biz=MzIwODA4NjMwNA==&mid=2652897787&idx=1&sn=2725ee6a029901e026785522772f6c6b#rd