DBCP 소개/설정 및 validationQuery 이슈

DB/Common 2012. 4. 30. 18:04

DBCP API를 이용하여 커넥션 풀을 사용하는 방법에 대해서 살펴본다.

커넥션 풀과 자카르타 DBCP API

DBCP API는 커넥션 풀 기능을 제공하는 API로서 자카르타의 또 다른 프로젝트인 Pool API에 기반하고 있다. DBCP API는 사용방법이 비교적 쉬우며, 파일을 통해서 커넥션 풀을 설정할 수 있고 또한 프로그램에서 직접 커넥션 풀을 설정할 수 있기 때문에 커넥션 풀을 사용하려는 개발자에게 매우 유용한 API이다.

커넥션 풀이란?

커넥션 풀에 대한 개념이 없는 사람을 위해 DBCP API를 이용한 커넥션 풀에 대해서 살펴보기 전에 간단하게 커넥션 풀이 무엇인지에 대해서 살펴보도록 하자. 커넥션 풀 기법이란 데이터베이스와 연결된 커넥션을 미리 만들어서 풀(pool)이란 저장소에 저장해 두고 있다가 필요할 때에 커넥션을 풀에서 가져다 쓰고 다시 풀에 반환하는 기법을 의미한다

커넥션 풀 기법에서는 위 그림과 같이 풀 속에 데이터베이스와 연결된 커넥션을 미리생성해놓고 있는다. 데이터베이스 커넥션이 필요할 경우, 커넥션을 새로 생성하는 것이 아니라 풀 속에 미리 생성되어 있는 커넥션을 가져다가 사용하게 된다. 다 사용한 커넥션은 다시 풀에 반환한다. 풀에 반환된 커넥션은 다음에 다시 사용된다.

커넥션 풀의 특징은 다음과 같다.

풀 속에 미리 커넥션이 생성되어 있기 때문에 커넥션을 생성하는 데 드는 연결 시간이 소비되지 않는다.
커넥션을 계속해서 재사용하기 때문에 생성되는 커넥션 수가 많지 않다.

커넥션을 생성하고 닫는 데 필요한 시간이 소모되지 않기 때문에 그 만큼 어플리케이션의 실행 속도가 빨라지며, 또한 한번에 생성될 수 있는 커넥션 수를 제어하기 때문에 동시 접속자수가 몰려도 웹 어플리케이션이 쉽게 다운되지 않는다.

커넥션 풀을 사용하면 전체적인 웹 어플리케이션의 성능 및 처리량이 높아지기 때문에 많은 웹 어플리케이션에서 커넥션 풀을 기본으로 사용하고 있다.

DBCP API의 사용방법

자카르타 프로젝트의 DBCP API를 사용할 때에는 다음과 같은 과정을 거치면 된다.

DBCP 관련 Jar 파일 및 JDBC 드라이버 Jar 파일 설치하기
커넥션 풀 관련 설정 파일 초기화하기
커넥션 풀 관련 드라이버 로딩하기
커넥션 풀로부터 커넥션 사용하기

이 네 가지 절차에 대해서 차례대로 살펴보도록 하자.

필요한 Jar 파일 복사

DBCP API를 사용하기 위해서는 다음과 같은 라이브러리가 필요하다.

DBCP API 관련 Jar 파일
DBCP API가 사용하는 자카르타 Pool API의 Jar 파일
Pool API가 사용하는 자카르타 Collection API의 Jar 파일

이들 라이브러리의 최신 버전은 http://jakarta.apache.org/site/binindex.cgi 에서 다운로드 받을 수 있으며, 이 글에서는 다음 버전을 사용하여 예제를 작성하였다.

DBCP 1.2.1 - commons-dbcp-1.2.1.zip
Pool 1.2 - commons-pool-1.2.zip
Collection 3.1 - commons-collections-3.1.zip

이들 파일의 압축을 풀면 다음과 같은 Jar 파일들을 발견할 수 있는데, 이들 Jar 파일들을 사용하면 된다.

commons-dbcp-1.2.1.jar
commons-pool-1.2.jar, commons-collections-3.1.jar

예제로 제공되는 파일에는 pool\WEB-INF\lib 폴더에는 이미 이들 Jar 파일들이 포함되어 있으므로 별도로 복사하지 않더라도 DBCP를 사용하는 본 장의 예제들을 실행할 수 있게 된다.

커넥션 풀 설정 파일 작성하기

DBCP를 사용하는 방법에는 소스 코드 상에서 커넥션 풀을 설정하는 방법과 설정 파일을 통해서 커넥션 풀을 설정하는 방법 두가지 존재하는데 본 장에서는 설정 파일을 이용한 커넥션 풀 설정 방법에 대해서 살펴보도록 하겠다.

DBCP Pool API에서 사용되는 커넥션 풀 설정 파일의 기본 골격은 아래 코드와 같다.

    파일명: pool\WEB-INF\classes\pool1.jocl
    <object class="org.apache.commons.dbcp.PoolableConnectionFactory"
        xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">
      <object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">
        <string value="jdbc:mysql://localhost:3306/.." />
        <string value="jspexam" />
        <string value="jspex" />
      </object>
      <object class="org.apache.commons.pool.impl.GenericObjectPool">
        <object class="org.apache.commons.pool.PoolableObjectFactory" null="true" />
      </object>

      <object class="org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory"
              null="true" />
            <string null="true" />
            <boolean value="false" />
            <boolean value="true" />
    </object>

위 코드에서 나머지 부분은 그대로 입력하고 다음 부분만 알맞게 변경하면 된다.

위 코드에는 세 개의 <string> 태그가 사용되는데, 이들 태그는 각각 순서대로 JDBC URL, 데이터베이스 사용자 계정, 암호를 나타낸다.

설정 파일의 위치

DBCP API는 클래스패스로부터 설정 파일을 읽어온다. 따라서 앞서 작성한 커넥션 풀 설정 파일은 클래스패스에 위치해 있어야 한다. 웹 어플리케이션에서 DBCP API와 관련된 설정 파일의 위치로 가장 좋은 곳은 WEB-INF\classes 폴더이다. 본 글의 예제에서 사용하는 커넥션 풀 설정 파일은 모두 WEB-INF\classes 폴더에 위치시켰다.

커넥션 풀 초기화

DBCP API를 통해서 커넥션 풀을 사용하기 위해서는 커넥션 풀과 관련된 JDBC 드라이버를 로딩해주어야 한다. DBCP API를 사용할 때에 로딩해주어야 할 JDBC 드라이버는 다음과 같다.

org.apache.commons.dbcp.PoolingDriver - DBCP API의 JDBC 드라이버
DBMS에 연결할 때 사용될 JDBC 드라이버

웹 어플리케이션 시작할 때 위에서 언급한 두 가지 형태의 JDBC 드라이버를 로딩하도록 하면 편리할 것이다. 웹 어플리케이션이 시작할 때 자동으로 시작되는 JDBC 드라이버를 로딩하도록 구현한 서블릿 클래스는 다음 코드와 같다.

    파일명: pool\WEB-INF\src\madvirus.jdbcdriver\DBCPInit.java
    package madvirus.jdbcdriver;

    import javax.servlet.http.HttpServlet;
    import javax.servlet.ServletConfig;
    import javax.servlet.ServletException;
    import java.util.StringTokenizer;

    public class DBCPInit extends HttpServlet {

        public void init(ServletConfig config) throws ServletException {
            try {
                String drivers = config.getInitParameter("jdbcdriver");
                StringTokenizer st = new StringTokenizer(drivers, ",");
                while (st.hasMoreTokens()) {
                    String jdbcDriver = st.nextToken();
                    Class.forName(jdbcDriver);
                }

                Class.forName("org.apache.commons.dbcp.PoolingDriver");
                System.setProperty("org.xml.sax.drvier",
                       "org.apache.crimson.parser.XMLReaderImpl");
            } catch(Exception ex) {
                throw new ServletException(ex);
            }
        }
    }

DBCPInit 서블릿은 "jdbcdriver" 초기화 파라미터로부터 로딩할 JDBC 드라이버를 입력받아 JDBC 드라이버를 차례대로 로딩한다. 그런 후, DBCP API의 JDBC 드라이버인 PoolingDriver 을 로딩한다. 마지막으로 설정 파일을 분석할 때 사용할 XML 파서를 지정한다. 위 코드는 Sun 사에서 배포한 JDK 1.4를 기준으로 XML 파서를 지정하였는데, 만약 다른 XML 파서를 사용한다면 알맞게 변경해주어야 한다.

WEB-INF\web.xml 파일에 DBCPInit 서블릿 클래스에 대한 설정 정보를 추가함으로써 웹 어플리케이션이 시작될 때 DBCPInit 서블릿 클래스가 시작될 수 있도록 할 수 있다. 예를 들면, 아래와 같은 코드를 web.xml 파일에 추가해주면 된다.

  <servlet>
     <servlet-name>DBCPInit</servlet-name>
     <servlet-class>madvirus.jdbcdriver.DBCPInit</servlet-class>
     <load-on-startup>1</load-on-startup>
     <init-param>
        <param-name>jdbcdriver</param-name>
        <param-value>com.mysql.jdbc.Driver</param-value>
     </init-param>
  </servlet>

위와 같이 코드를 web.xml 파일에 추가해주면 웹 어플리케이션이 시작할 때 DBCPInit 서블릿 클래스가 자동으로 시작되고 init() 메소드가 호출된다.

커넥션 풀로부터 커넥션 사용하기

커넥션 풀을 위한 JDBC 드라이버 및 DBMS에 연결할 때 사용할 JDBC 드라이버를 로딩하면 커넥션 풀로부터 커넥션을 가져와 사용할 수 있다. 커넥션 풀로부터 커넥션을 가져오는 코드는 별반 다르지 않으며, 다음과 같은 형태의 코드를 사용하면 된다.

    Connection conn = null;
    ....
    try {
        String jdbcDriver = "jdbc:apache:commons:dbcp:/pool1";
        conn = DriverManager.getConnection(jdbcDriver);
        ...
    } finally {
        ...
        if (conn != null) try { conn.close(); } catch(SQLException ex) {}
    }

위 코드를 보면 DBCP API 기반의 커넥션 풀을 사용한다고 해서 특별히 코드가 달라지는 부분이 없다는 것을 알 수 있다. 일반 경우와 마찬가지로 DriverManager.getConnection() 메소드를 사용해서 커넥션을 구해오고, 커넥션을 다 사용하면 close() 메소드를 사용하여 사용한 커넥션을 닫는다. 차이점이라면 JDBC URL이 다음과 같은 형태를 띈다는 점이다.

jdbc:apache:commons:dbcp:/[풀이름]

[풀이름]은 여러 개의 커넥션 풀 중에서 사용할 커넥션 풀의 이름을 나타내는 것으로서 커넥션 풀 설정 파일에서 확장자를 제외한 나머지 이름을 [풀이름]으로 사용한다. 예를 들어, 앞서 작성했었던 pool1.jocl 파일이 설정한 커넥션 풀을 사용하고 싶다면 다음과 같은 JDBC URL을 사용한다.

jdbc:apache:commons:dbcp:/pool1

실제로 커넥션 풀을 사용하는 완전한 예제는 다음 코드와 같다. (아래 코드를 여러분의 환경에 알맞게 변형시켜서 실행하기 바란다.)

    파일명: pool\usePool1.jsp
    <%@ page contentType = "text/html; charset=euc-kr" %>

    <%@ page import = "java.sql.DriverManager" %>
    <%@ page import = "java.sql.Connection" %>
    <%@ page import = "java.sql.Statement" %>
    <%@ page import = "java.sql.ResultSet" %>
    <%@ page import = "java.sql.SQLException" %>

    <html>
    <head><title>회원 목록</title></head>
    <body>

    MEMBMER 테이블의 내용
    <table width="100%" border="1">
    <tr>
        <td>이름</td><td>아이디</td><td>이메일</td>
    </tr>
    <%

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            String jdbcDriver = "jdbc:apache:commons:dbcp:/pool1";
            String query = "select * from MEMBER order by MEMBERID";
            conn = DriverManager.getConnection(jdbcDriver);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while(rs.next()) {
    %>
    <tr>
        <td><%= rs.getString("NAME") %></td>
        <td><%= rs.getString("MEMBERID") %></td>
        <td><%= rs.getString("EMAIL") %></td>
    </tr>
    <%
            }
        } finally {
            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (stmt != null) try { stmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
    %>
    </table>

    </body>
    </html>

위 코드에서 커넥션 풀에서 구한 Connection의 close() 메소드를 호출하면, 커넥션이 닫히는 것이 아니라 커넥션 풀로 반환된다. 이렇게 커넥션 풀에 커넥션을 반환하는 메소드를 close()로 지정한 이유는 기존의 코드를 최소한으로 변경하는 범위 내에서 커넥션 풀을 사용할 수 있도록 하기 위함이다. 물론, JDBC 프로그래밍의 코딩 형태를 동일하게 유지하기 위한 것도 close() 메소드를 사용하는 이유이다.

커넥션 풀 속성 지정하기

앞에서 살펴본 커넥션 풀 설정 파일인 pool1.jocl은 커넥션 풀과 관련된 속성을 지정하지 않고 있다. DBCP의 커넥션 풀은 최대 커넥션 개수, 최소 유휴 커넥션 개수, 최대 유휴 커넥션 개수, 유휴 커넥션 검사 여부 등의 속성을 지정할 수 있다. pool1.jocl을 보면 다음과 같은 코드가 있는데,

이 코드에 커넥션 풀과 관련된 속성 정보를 추가하면 된다. 예를 들면, 아래 코드와 같이 커넥션 풀 속성 정보를 추가하면 된다.

파일명: pool\WEB-INF\classes\pool2.jocl
    <object class="org.apache.commons.dbcp.PoolableConnectionFactory"
        xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">

      <object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">
        <string value="jdbc:mysql://localhost:3306/chap11?..." />
        <string value="jspexam" />
        <string value="jspex" />
      </object>

      <object class="org.apache.commons.pool.impl.GenericObjectPool">
        <object class="org.apache.commons.pool.PoolableObjectFactory" null="true" />
        <int value="10" />  
        <byte value="1" />  
        <long value="10000" /> 
        <int value="10" /> 
        <int value="3" /> 
        <boolean value="true" /> 
        <boolean value="true" /> 
        <long value="600000" /> 
        <int value="5" /> 
        <long value="3600000" /> 
        <boolean value="true" /> 
      </object>
      <object class="org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory"
          null="true" />

      <string null="true" />

      <boolean value="false" />

      <boolean value="true" />
    </object>

굵게 표시한 부분이 커넥션 풀의 속성과 관련된 부분인데, 각 속성의 값이 무엇을 의미하는 지 우측에 주석으로 표시하였다. 각 속성이 의미하는 것은 다음표와 같다.

속성	설명
maxActive	커넥션 풀이 제공할 최대 커넥션 개수
whenExhaustedAction	커넥션 풀에서 가져올 수 있는 커넥션이 없을 때 어떻게 동작할지를 지정한다. 1일 경우 maxWait 속성에서 지정한 시간만큼 커넥션을 구할 때 까지 기다리며, 0일 경우 에러를 발생시킨다. 2일 경우에는 일시적으로 커넥션을 생성해서 사용한다.
maxWait	whenExhaustedAction 속성의 값이 1일 때 사용되는 대기 시간. 단위는 1/1000초이며, 0 보다 작을 경우 무한히 대기한다.
maxIdle	사용되지 않고 풀에 저장될 수 있는 최대 커넥션 개수. 음수일 경우 제한이 없다.
minIdle	사용되지 않고 풀에 저장될 수 있는 최소 커넥션 개수.
testOnBorrow	true일 경우 커넥션 풀에서 커넥션을 가져올 때 커넥션이 유효한지의 여부를 검사한다.
testOnReturn	true일 경우 커넥션 풀에 커넥션을 반환할 때 커넥션이 유효한지의 여부를 검사한다.
timeBetweenEvctionRunsMillis	사용되지 않은 커넥션을 추출하는 쓰레드의 실행 주기를 지정한다. 양수가 아닐 경우 실행되지 않는다. 단위는 1/1000 초이다.
numTestsPerEvictionRun	사용되지 않는 커넥션을 몇 개 검사할지 지정한다.
minEvictableIdleTimeMillis	사용되지 않는 커넥션을 추출할 때 이 속성에서 지정한 시간 이상 비활성화 상태인 커넥션만 추출한다. 양수가 아닌 경우 비활성화된 시간으로는 풀에서 제거되지 않는다. 시간 단위는 1/1000초이다.
testWhileIdle	true일 경우 비활성화 커넥션을 추출할 때 커넥션이 유효한지의 여부를 검사해서 유효하지 않은 커넥션은 풀에서 제거한다.

몇몇 속성은 성능에 중요한 영향을 미치기 때문에 웹 어플리케이션의 사용량에 따라서 알맞게 지정해주어야 하는데, 다음과 같이 고려해서 각 속성의 값을 지정하는 것이 좋다.

maxActive - 사이트의 최대 커넥션 사용량을 기준으로 지정. 동시 접속자수에 따라서 지정한다.
minIdle - 사용되지 않는 커넥션의 최소 개수를 0으로 지정하게 되면 풀에 저장된 커넥션의 개수가 0이 될 수 있으며, 이 경우 커넥션이 필요할 때 다시 커넥션을 생성하게 된다. 따라서 커넥션의 최소 개수는 5개 정도로 지정해두는 것이 좋다.
timeBetweenEvctionRunsMillis - 이 값을 알맞게 지정해서 사용되지 않는 커넥션을 풀에서 제거하는 것이 좋다. 커넥션의 동시 사용량은 보통 새벽에 최저이며 낮 시간대에 최대에 이르게 되는데 이 두 시간대에 필요한 커넥션의 개수 차이는 수십개에 이르게 된다. 이때 최대 상태에 접어들었더가 최소 상태로 가게 되면 풀에서 사용되지 않는 커넥션의 개수가 점차 증가하게 된다. 따라서 사용되지 않는 커넥션은 일정 시간 후에 삭제되도록 하는 것이 좋다. 보통 10~20분 단위로 사용되지 않는 커넥션을 검사하도록 지정하는 것이 좋다.
testWhileIdle - 사용되지 않는 커넥션을 검사할 때 유효하지 않은 커넥션은 검사하는 것이 좋다.

관련링크:

자카르타 Commons DBCP

출처 - http://javacan.tistory.com/82

DB Connection Pool을 사용하기 위해서 Apache Commons의 dbcp를 많이 사용하고 있지만 매번 설정값에 대한 정확한 정의와 설정방법을 까먹는 경향이 있어 일반적인 설정값 가이드를 아주 간단히 정리하고자 한다.

maxActive (최대 active connection 개수??)
+ 그냥 최대 Connection 개수로 생각하면된다.
+ 기본값은 8이며, 적당히? 설정하면 된다.

minIdle
+ 사용되지 않고 풀에 저장될 수 있는 최소 커넥션 개수
+ 기본값은 0이며, 기본값을 사용하게 되면 connection pool이 비어버릴 수 있기 때문에 기본값 대신 적당한 설정이 필요하다.

maxIdle
+ 사용되지 않고 풀에 저장될 수 있는 최대 커넥션 개수
+ 기본값은 8이며, 일반적으로 maxActive 개수와 동일하게 설정하는게 맞는 것 같다

maxWait
+ connection 사용이 많아져서 connection pool이 비었을 때 대기시간 (단위 1/1000초)
+ 기본값은 -1(무한대)이며, 서비스 특성에 맞게 설정하면된다. 일반적으로 기본값을 사용해도 큰 문제는 안될 것 같다.

testOnBorrow
+ connection pool에서 connection을 가져올 때 해당 connection이 유효성 검사 여부
+ 기본값은 false이며, 일반적으로 기본값을 사용한다. true설정하게 되면 매번 validationQuery를 수행하기 때문에 약간의 성능저하를 감수해야 한다.

testOnRetrun
+ testOnBorrow와 비슷한데... 다만 유효성 검사 시점이 connection을 pool에 반환할때 이다.

timeBetweenEvictionRunsMillis
+ 놀고 있는 connection을 pool에서 제거하는 시간기준 (설정된 시간동안 놀고 있는 connection을 minIdle&maxIdel 설정값을 고려하여 제거한다.)
+ 기본값은 -1이며, 단위는 1/1000초이다. 개인적으로 10분정도의 설정이 적당한 것 같다.

testWhileIdle
+  놀고 있는 connection의 제거 여부를 검사할 때 해당 connection의 유효성 테스트 여부
+ 기본값은 false이며, 일반적으로 true로 설정하는 것이 좋은 것 같다.

validationQuery
+ connection 유효성 검사시에 사용할 쿼리문
+ DB 리소스를 최대한 적게 사용하는 쿼리를 사용하는게 좋다.
예제)
* Oracle : select * from dual
* MySql : select 1

위의 설정 이외에도 추가적인 설정이 있지만, 지금까지 경험으로 봐서는 그닥 사용할 일이 없는 것 같아서 생략한다. (Simple is best!!!)

출처 - http://dimdim.tistory.com/27

- 에러메시지 일부분

is longer than the server configured value of 'wait_timeout'.

- 문제 내용 및 해결책

IBATIS 커넥션 에러

J2EE 개발 관련/IBATIS && MYBATIS 2012/03/22 08:46

시나리오 : was에 war를 배포하고 몇일이 지난후 갑자기 화면에 에러를 뿜으며 아래와 같은 메세지를 보여줬다. 제길.. 멍미.

에러 메세지

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: SqlMapClient operation; SQL [];

--- The error occurred while applying a parameter map.

--- Check the aretias.category.selectChildCategorys-InlineParameterMap.

--- Check the statement (query failed).

--- Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 64,780,193 milliseconds ago. The last packet sent successfully to the server was 64,780,193 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred while applying a parameter map.

--- Check the aretias.category.selectChildCategorys-InlineParameterMap.

--- Check the statement (query failed).

org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:656)

org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)

javax.servlet.http.HttpServlet.service(HttpServlet.java:617)

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

root cause

org.springframework.dao.DataAccessResourceFailureException: SqlMapClient operation; SQL [];

--- The error occurred while applying a parameter map.

--- Check the aretias.category.selectChildCategorys-InlineParameterMap.

--- Check the statement (query failed).

--- The error occurred while applying a parameter map.

--- Check the aretias.category.selectChildCategorys-InlineParameterMap.

--- Check the statement (query failed).

org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)

org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)

org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:293)

com.aretias.jkholdings.repository.CategoryRepositoryImpl.selectChildCategorys(CategoryRepositoryImpl.java:22)

com.aretias.jkholdings.service.CategoryServiceImpl.getChild(CategoryServiceImpl.java:19)

com.aretias.jkholdings.controller.BaseController.getFirstDeptCategoryList(BaseController.java:42)

com.aretias.jkholdings.controller.UserController.goLoginPage(UserController.java:23)

sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

java.lang.reflect.Method.invoke(Method.java:597)

org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)

javax.servlet.http.HttpServlet.service(HttpServlet.java:617)

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

시나리오 환경
- TOMCAT4.5 / JDK 5.0 / MYBATIS / SPRING 3.0 / DBCP

원인
- 마지막으로 DB에 커넥션을 맺은후 사용이 없다 보니 데이터 베이스 커넥션이 끝겼다.

해결책
- 특정 시간마다 커넥션을 확인 하는 셋팅을 지정한다.

코드

</bean>

출처 - http://fbwotjq.tistory.com/entry/IBATIS-%EC%BB%A4%EB%84%A5%EC%85%98-%EC%97%90%EB%9F%AC

DB에 접속중인 세션을 오랫동안 사용하지 않거나, 네트워크에 문제가 발생하는 경우 DB 세션이 끊어질 수 있다.

DB 세션이 끊어지는 경우 App 입장에서는 재접속 처리를 해주어야 다시 정상적인 서비스가 가능해 진다.

재접속 처리 방법 중에 JDBC에 autoReconnect=true 옵션을 주는 방법이 있는데, 이 옵션을 잘못 사용하는 경우 얘기치 않은 문제가 발생할 수 있다.

autoReconnect 옵션은 쿼리를 수행한 다음 DB 세션에 문제가 있으면 단순히 SQLException 리턴 후 재접속 처리를 한다.

문제는 트랜잭션 구동 환경에서 수행중이던 트랜잭션은 롤백이 되어야 하고, 남은 트랜잭션은 수행이 되지 않아야 되는데, autoReconnect 옵션은 이런 처리를 해주지 않기 때문이다.

아래 예시는 3개의 쿼리로 이루어진 트랜잭션에서 첫번째 UPDATE 구문 실행후 DB 커넥션 종료 상황이다.

1개의 쿼리는 Rollback, 1개의 쿼리는 에러 리턴, 나머지 1개의 쿼리는 Commit 처리가 되면서 데이터 정합성이 깨지는결과를 초래하고 있다.

BEGIN;

UPDATE tb_work SET cnt = cnt+1 WHERE id = 10 ;

INSERT INTO tb_work_log VALUES (10, 20, 30) ; # 커넥션 에러 발생 및 SQLException 리턴 후 재접속 (기존 UPDATE 쿼리는 자동 롤백)

DELETE FROM tb_work_list WHERE id = 10 ; # 새로운 세션에서 새로운 트랜잭션으로 진행됨

COMMIT ;

위와 같은 문제를 막기 위해서 autoReconnect=true인 환경에서는 SQLException이 발생하는 경우에, 해당 트랜잭션이 더 이상 진행되지 않도록 App 단에서 직접 예외 처리를 해줘야 하고, MySQL 레퍼런스 메뉴얼에서도 autoRecoonect 옵션 사용은 권장하지 않고 있다.

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

Property Name	Definition	Default Value	Since Version
autoReconnect	Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.	false	1.1

트랜잭션을 사용하는 환경이라면 DB 세션의 재접속 처리는 JDBC의 autoReconnect 설정이 아닌 DBCP의 validationQuery 기능을 사용하는 것이 적합하다.

DBCP 설정 관련 apache 문서 : http://commons.apache.org/dbcp/configuration.html

출처 - http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70118975290

dbcp pooling설정에 validationQuery적용

tomcat dbcp를 이용하여 검색데이터 추출할 때, mysql 사용.

mysql wait_timeout 설정(기본값 28800 , 8시간) 에 의해 커넥션이 연결된 이후 해당

커넥션의 close 없이 8시간이 지나면 해당 커넥션을 종료.

▶issue : 종료된 커넥션을 dbcp의 connection pool 에선 여전히 가지고 있는 상태.

이런 상황에서 DB 관련 프로그램이 호출되면 커넥션 관련 에러가 발생.

▶solution : java에서 DB를 사용하기 전에 해당 connection 이 정상적인지 검사를 하도록 하는 것.
이 옵션이 validationQuery.

<-bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"->
<-property name="driverClassName" value="${jdbc.driverClassName}"/->
<-property name="url" value="${jdbc.url}"/->
<-property name="username" value="${jdbc.username}"/->
<-property name="password" value="${jdbc.password}"/->
<-property name="validationQuery" value="select 1"/->
<-/bean->

<-Resource name="jdbc/livesearch" auth="Container"
type="javax.sql.DataSource"
maxActive="10" maxIdle="30" maxWait="10000"
username="${jdbc.username}" password="${jdbc.password}"
driverClassName="com.mysql.jdbc.Driver"
url="${jdbc.url}"
validationQuery="select 1"
/>

와 같이 사용하면 된다.

<참고>
오라클의 경우 : validationQuery="select 1 from dual"

출처 : blog.hoonie.net

전에 기록한 "DBCP의 validationQuery는 약인가? 독인가?"에서 validationQuery는 필수가 아니라고 했었다. 그렇다면 만약 DBCP에서 바라보고 있는 DB 서버가 어떠한 이유로 사용 불가 상태에 들어갔다가 다시 사용 가능해 진다면 validationQuery가 없어도 DBCP는 Connection Pool을 제대로 복구할까?

DBCP 공식 사이트에서 관련된 내용을 검색해 보니 DBCP는 검증기능을 사용하지 않는 경우에도 DB서버가 셧다운 되었다가 다시 기동되면 자동으로 새로운 Connection 객체들을 Pool에 로딩한다 한다.

단, DBCP의 프러퍼티를 기본값으로 설정되어 있고, 사용하는 JDBC 드라이버가 select 등의 쿼리 실행시 발생하는 예외상황에 대해 SQLException을 제대로 보고한다는 전제하에 굳이 validationQuery를 사용하지 않아도 된다고 한다. 적어도 최근 버전의 DBCP에서는 validationQuery는 돌다리도 두드려보는 개념인것 같다.

다음은 DBCP사이트의 Wiki(DBCP공식사이트) 에서 확인한 내용이다.

Q: Without using validation of connections (testOnBorrow = false, testOnReturn = false, timeBetweenEvictionRunsMillis = -1) and after shutdown and restarting the database again, it looks like the pool is cleaning its old connections by itself. So it turns out that we always have valid connections. How can you explain this and when is explicit validation necessary?

A: During the connection activation (when borrowing a connection) the setAutoCommit and other connection init methods are called. If one of these methods throws a SQLException then the connection is also considered broken and removed from the pool.

So if you are using one of the "default*" properties and the JDBC driver correctly reports the SQLExceptions on the "set*" methods then you don't need an extra validationQuery.

참고 사이트 : Apache commons DBCP SITE

<참고 이미지>

Posted 12th October 2009 by 김형구

mysql wait_timeout 설정(기본값 28800 , 8시간) 에 의해 커넥션이 연결된 이후 해당

커넥션의 close 없이 8시간이 지나면 해당 커넥션을 종료 시키게 된다.

문제는 이렇게 종료된 커넥션을 dbcp의 connection pool 에선 여전히 가지고 있는 상태라는 것이다.

해결방법은 java에서 DB를 사용하기 전에 해당 connection 이 정상적인지 검사를 하도록 하는 것이다. 이 옵션이 validationQuery 파라메터이다.

http://blog.naver.com/PostView.nhn?blogId=pignbear&logNo=150037833102&redirect=Dlog&widgetTypeCall=true

운영툴 에러 발생.. 로그는 아래와 같다..

에러로그:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
...., which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

자상하게도 autoReconnect=true 로 바꾸거나 wait_timeout 을 수정하라고 한다..

wait_timeout 은 connection 증가로 그리 땡기진 않고..autoReconnection 을 추가하기로 했다.
설정은 jdbc 커넥션 설정 파일에서 url 에 "autoReconnection=true" 를 넣어주면 된다.

* url=jdbc:mysql://ip:port/dbname?useUnicode=true&characterEncoding=utf8&autoReconnect=true
* validationQuery="select 1"

그리고 이 외에도 validationQuery를 넣어주는 데 이유는 한번 커넥션이 끊어진다음 재시도를 하는 경우 문제가 있는 처음 한번의 시도는 에러가 나게 된다. 이때 넣어주는게 validationQuery로 모든 사용자쿼리를 실행 전에 한번 이 쿼리를 실행하게 된다.(그러므로 DB로서는 추가적인 부하가 됨) 쿼리가 한번 더 실행되는 것인 만큼 가장 간단한 쿼리여야 한다.

>validationQuery description

Parameter	Default	Description
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.

보통은 SELECT 1 을 많이 쓴다.(oraqle은 SELECT 1 FROM DUAL)
추가적인 쿼리인 만큼 access가 많은 곳에선 조심해서 써야 할듯?

jdbc 설정과 관련된것은 아래 링크를 참조함..
http://commons.apache.org/dbcp/configuration.html

출처 - http://netholic.tistory.com/137

DBCP의 validationQuery 약인가? 독인가?

출처 : http://www.hoonie.net/blog/64

현재 담당하고 있는 프로젝트와 Apache의 Commons에 있는 DBCP를 사용하여 구현된 Connection Pool 관리자로부터 Connection을 받아서 쓰고 있다. 그런데 DB 서버의 부하를 점검해 본 결과, 당연하게도 DBCP의 validationQuery로 할당한 SQL문의 호출이 가장 많았다.
솔루션의 성격상 DB 서버에 대한 Access가 빈번히 일어나고 그 응답 또한 빠른시간을 필요로하기 때문에 DB의 부하를 가능한 줄여야 하는데 DBCP에서 Connection 객체를 받아 올 때 마다 실행되는 validationQuery는 엄청난 부하를 야기하고 있었다. 값비싼 객체인 DB와의 Connection을 효율적으로 관리하여 DB 관련 프로세스의 효율을 높이고자 사용하는 DBCP가 아이러니하게도 DB 서버의 부담을 높이는 작용을 하고 있었던 것이다.
과연 validationQuery는 반드시 필요한가? DBCP의 API 문서를 확인한 결과, BasicDataSource의 Validation Query는 필수가 아닌 선택적인 기능으로서 할당되었을 경우에만 수행되는 것으로 나와 있다. 단, 이 기능을 사용하고자 할당한 경우에는 해당 쿼리의 실행결과 반드시 1개 이상의 resultset이 나와야 한다고 명시되어 있다.
만약 자신의 프로젝트에서 DBCP를 사용하고 있다면, 그리고 validationQuery를 설정하여 사용하고 있다면 해당 쿼리가 최경량의 SQL문을 사용하고 있는지 확인하는 것은 잊지 말아야 할 것이다.
추천되는 쿼리는 Oracle의 경우

select 1 from dual;

MySQL의 경우

select 1;

정도를 사용하면 좋을 것이다. 이외의 쿼리를 validationQuery로 사용 중 이라면 지금 당장 쿼리 실행 소요 시간을 비교해 보라.

[출처] DBCP의 validationQuery 약인가? 독인가?|작성자 빛나리

timeBetweenEvctionRunsMillis - 사용되지 않는 커넥션을 추출하는 쓰레드의 실행 주기를 지정

이값을 알맞게 지정해서 사용되지 않는 커넥션을 제거하는것이 좋다 보통 10~20분 단위 검사

testWhileIdle - true 일 경우 비활성화 커넥션을 추출할때 커넥션이 유효한지 여부를 검사해서 유효하지 않으면 제거

[출처] 트랜잭션 , 컨넥션 풀 관련|작성자 김상현

Batis, DBCP, MySQL 을 사용하는 웹어플리케이션 구동 중, 일정 시간 동안 컨넥션을 사용하지 않다가 사용을 하면 컨넥션이 이미 종료 되었다고 나타나는 문제가 발생했다.

Communications link failure due to underlying exception

뭐 사실 .. 어느 정도 사용을 하는 어플리케이션이라면 이런 문제는 발생하지 않을거다.
MySQL 은 기본적으로 컨넥션을 통해서 8시간 동안 request가 오지 않는다면, 강제적으로 컨넥션을 닫아버린다.
( 이는 물론 MySQL 의 설정을 변경함으로써 값을 바꿀 수는 있다. 하지만, 좋은 방법이라고 생각되지는 않는다. )

다음과 같은 iBatis 설정에서도 당연히 동일한 문제가 발생했다.

다음과 같이 설정값을 변경함으로써 해당 문제를 해결 할 수 있다.

이렇게 설정하면 컨넥션을 풀에서 가지고 올때도, validationQuery 를 통해서 유효성 검사를 진행하게 되는데,
뭐 퍼포먼스가 문제 될거라고 생각된다면 -_-aa .. testOnBorrow( default : true ) 값을 false 로 추가해주면 된다.

설정값의 테스트는 mysql 의 show processlist; 쿼리를 이용하여 정상적으로 구동되는지 알 수 있다.
show processlist 를 수행하면 현재 mysql 에서 수행되고 있는 프로세스의 리스트와 상태를 알 수 있는데,
웹어플리케이션을 구동하고, 컨넥션이 맺어지도록 한 후에 해당 프로세스가 command 필드가 sleep 상태에서 Time 필드 값이 timeBetweenEvictionRunsMillis/1000 보다 커지지 않는다면, 정상적으로 설정된 것이다. ( 뭐 물론 약간의 차이는 날 수 도 있겠으며, 해당 테스트 동안 컨넥션이 사용된다면 잘못 나타날 수 도 있다. ) 약 1,000 정도의 값으로 셋팅을 하고 테스트 후 적절한 값을 넣어서 사용하면 되겠다.

그 외에도 이상의 문제를 해결 할 수 있는 설정은 몇가지가 더 있다.
http://commons.apache.org/dbcp/configuration.html 를 참고 하면 된다.

출처 - http://blog.daum.net/iccaruss2/7501371

DBCP (Database connection pooling services)

1.1 DBCP configuration
1.2 connection validation check
문서에 대하여
1.1 DBCP configuration

DBCP configuration : http://commons.apache.org/dbcp/configuration.html

DBCP BasicDataSource	iBATIS SimpleDataSource	Option	description
driverClassName	JDBC.Driver	required	JDBC driver class
url	JDBC.ConnectionURL	required	DB Connection URL
username	JDBC.Username	optional	UserName
password	JDBC.Password	optional	Password
maxActive	Pool.MaximumActiveConnections	optional	최대 커넥션 수(Maximum Active Connections)
initialSize	Pool.MinimumIdleConnections	optional	최초 초기화 커넥션 수
maxIdle	Pool.MaximumIdleConnections	optional	사용되지 않고 풀에 저장될 수 있는 최대 커넥션 개수. 음수일 경우 제한이 없음.
minIdle	Pool.MinimumIdleConnections	optional	사용되지 않고 풀에 저장될 수 있는 최소 커넥션 개수
maxWait	Pool.MaximumWait	optional	최대 대기시간(milliseconds). 음수일 경우 제한이 없음
validationQuery	Pool.ValidationQuery	optional	Validation Query
testOnBorrow			true일 경우 커넥션을 가져올 때 커넥션이 유효한지의 여부를 검사.
testOnReturn			true일 경우 커넥션을 반환할 때 커넥션이 유효한지의 여부를 검사.
testWhileIdle			true일 경우 유효하지 않은 커넥션은 풀에서 제거
timeBetweenEvictionRunsMillis			사용되지 않은 커넥션을 추출하는 쓰레드의 실행주기를 지정. ( 음수이면 동작하지 않음, milliseconds)
numTestsPerEvictionRun			사용되지 않은 커넥션을 검증할 connection수 지정
minEvictableIdleTimeMillis			pool에 대기중인 시간이 설정된 값보다 크다면 validationQuery 와 관계없이 풀에서 제거

1.2 connection validation check

<validationQuery>select 1</validationQuery>

<testOnBorrow>false</testOnBorrow>

<testWhileIdle>true</testWhileIdle>

<numTestsPerEvictionRun>1</numTestsPerEvictionRun>

<timeBetweenEvictionRunsMillis>3000</timeBetweenEvictionRunsMillis>

출처 - http://hermeslog.tistory.com/132

'DB > Common' 카테고리의 다른 글

NoSQL 소개 (0)	2012.05.18
SQL 구문 기본 작성 가이드 (0)	2012.05.08
CREATE SCHEMA (0)	2012.04.12
join & view (0)	2012.04.10
제로보드로 유료 사이트 만들기 (결제 테이블) (0)	2012.04.09