2011-10-03

Tags: connection-pool , 程式語言

這陣子因工作需要，必需寫一些對mySQL database頻繁存取的程式。理所當然的就一定會選用Connection Pool來加速DB連線的取得與管理。當時選用了DBCP與BoneCP兩種，下面是針對BoneCP設置的SampleCode與心得筆記。

public class ConnectionManager {  
 private static final BoneCPDataSource dataSource; 
 
 static{
  try {
   Class.forName("com.mysql.jdbc.Driver");     
   dataSource = new BoneCPDataSource();
   dataSource.setJdbcUrl("dbConnectURL");
   dataSource.setUsername("dbUserName");
   dataSource.setPassword("dbPassword");
   dataSource.setPartitionCount(dbPoolPartitionCount); //connection pool的partition數量
   dataSource.setMaxConnectionsPerPartition(dbPoolMaxConnectionsPerPartition); //每個partition的最大連線數
   dataSource.setMinConnectionsPerPartition(dbPoolMinConnectionsPerPartition); //每個partition的最小連線數   
   dataSource.setIdleConnectionTestPeriodInMinutes(1); //檢查空閒連線的時間間隔
   dataSource.setConnectionTestStatement("SELECT 1");  //用來驗証連線是否還活著。MySQL用的語法是用"SELECT 1"，其它DB要設其它對應的語法；   
   dataSource.setIdleMaxAgeInMinutes(5); //空閒連線最大存活時間  
   dataSource.setConnectionTimeoutInMs(10000); //dataSource.getConnection()被呼叫時，超過多久時間未回應就回傳Timeout訊息
   //dataSource.setMaxConnectionAge(9, TimeUnit.MINUTES); //Sets the maxConnectionAge. Any connections older than this setting will be closed off whether it is idle or not.
                 //Connections currently in use will not be affected until they are returned to the pool.
                 //加入此項設定用來解決MySQL可能發生連線超時(SQLState = 08S01)的問題
   dataSource.setStatementsCacheSize(10);   
  }
  catch (Exception e) {
   throw new RuntimeException(e);
  }
 }   
  
    public static synchronized Connection getConnection(){
        try {
            return dataSource.getConnection();
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        }       
    }  
}

心得筆記

1. 在mySQL裡執行"show variables"查詢，找出"wait_timeout參數"的設置時間(單位為秒)。Connection Pool的參數配置要配合此參數進行合適的調整，否則有時會出現遇想不到的問題。
   
   之前遇到DB的參數值為"wait_timeout=600"時，Connection Pool常出現Communications link failure的問題。這問題的根源其實在於DB，DB端timeout時間很短，所以Connection Pool的參數裡，每個連線的存活時間要跟著調小，以免發生DB端已把連線斷掉，Connection Pool卻不知道，進而導致程式取到已被斷開的連線，然後程式發生連線異常的問題。 
2. BoneCP的JavaDoc API在此，可查相關的設定明細說明。
3. SampleCode裡的dataSource.setConnectionTestStatement(...)、dataSource.setIdleMaxAgeInMinutes(...)、dataSource.setMaxConnectionAge(...)跟每種不同DB特性的關聯較大，要視使用何種DB做對應調整。
4. DataSource本身並不是ThreadSafe，如果程式會開啟多個Thread同時取用連線，要加synchronized語法(如SampleCode所示)。
5. 目前如果遇到BoneCP取出的連線在執行資料存取時發生異常，會丟出 Database access problem. Killing off all remaining connections in the connection pool訊息，然後整個Connection Pool就掛掉了，目前還找不到真正的原因為何...囧rz
   
   目前為避免此問題，使用 dataSource.setMaxConnectionAge(...)強制設定Connection Pool裡每個Connection(不管是idle還是正被使用中)的生命周期，讓它在短時間內(e.g. 10分鐘)就要消滅，必需再從DB重新取得。

updated 2011/10/31
這儿天發現 「Database access problem. Killing off all remaining connections in the connection pool」問題的原因來自於程式本身的邏輯出錯，跟Connection Pool無關。當程式取出Connection後，完全不去用它，等到它超過Timeout的時間間隔後才去使用它，就有可能出現紅字的異常。一般的解決方式就是讓DAO功能的Class去綁定Connection，而不是BusineessObject去綁定Connection，進而達到Connection快速取得與釋放的目的。如果是寫WebApp，可以利用Filter來實作OpenSessionInView的機制，也可以達到同樣的效果，而且還省掉自己必需在程式碼中管理Connection取得與釋放的動作。

---