DBCP ValidationQuery 이해 및 설정하기

회사 홈페이지의 서버를 이관해야 하는 일이 생겼다. 어려운 작업이 아니라 몇 시간 잡고 tomcat 서버를 통으로 복사하고, Mysql에서 MariaDB로 DB 이관 작업을 진행했다. 작업들을 진행한 후 해당 URL로 접속했을 때에는 정상적으로 동작했는데, 시간이 흐른 뒤 접속하니 아래와 같은 에러가 발생했다.

 

2024-11-21 17:36:19,302 ERROR [egovframework.com.cmm.EgovComOthersExcepHndlr] egovframework.let.cop.bbs.service.impl.EgovBBSManageServiceImpl.selectBoardArticles org.springframework.transaction.CannotCreateTransactionException:
Could not open JDBC Connection for transaction; nested exception is java.sql.SQLNonTransientConnectionException: (conn=43) Connection reset
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:289) ~[spring-jdbc-4.3.16.RELEASE.jar:4.3.16.RELEASE]

 

새로고침을 하니 다시 정상적으로 동작했고, 구글링 결과 DBCP ValidationQuery와 관련이 있다는 것을 알게 되었다. 따라서 오늘은 DBCP ValidationQuery에 대해 알아보고 해당 문제를 해결해보고자 한다.

 

---

🔎 발생원인

발생 원인을 이해하기 위해 필요로 하는 몇가지 이론에 대해 먼저 설명하겠다. 

DBCP(Database Connection Pool)

웹컨테이너(WAS)가 실행되면서 DB와 미리 Connection(연결)을 해놓은 객체들을 pool에 저장해두었다가, 클라이언트 요청이 오면 connection을 빌려주고, 처리가 끝나면 실행된 상태로 다시 connection을 반납 받아 pool에 저장하는 방식을 말한다.

 

만약 사용자가 요청을 보낼 때마다 커넥션 객체를 생성하고 연결하고 종료하는 것은 비효율적이다. 데이터베이스와 Connection을 맺는 작업 자체가 매우 느리며 자원을 많이 소모하기 때문이다. 이 문제를 해결하기 위해 커넥션풀(DBCP)를 통해 기존 pool에 존재하는 connection을 재사용한다.

 

• DB Connection 객체를 여러개 생성한 뒤 Pool에 담아 놓고 필요할 때 불러와서 사용
• 만약, 빌려줄 수 있는 Connection이 없다면 Connection 객체가 반환할 때까지 클라이언트는 대기 상태로 전환
• 사용이 끝난 Connection 객체는 다른 작업에서 재사용 가능하도록 Pool에 반환

 

DBCP ValidationQuery

ValidationQuery란 데이터베이스 연결이 유효한지 확인하기 위해 실행되는 SQL문이다. 데이터베이스 커넥션 풀링 라이브러리(Apache Commons DBCP, HikariCP 등)에서 관리되는 커넥션 객체가 정상적으로 동작하는지 확인하는 데 사용된다.

 

ValidationQuery 설정은 서버 환경과 애플리케이션 요구 사항에 따라 사용 여부가 달라지나 대부분의 환경에서는 ValidationQuery를 설정하는 것이 일반적이다. ValidationQuery 대신 Keep-Alive 옵션을 사용하기도 한다.

 

• ValidationQuery를 사용하는 경우
  • 유휴 시간이 긴 애플리케이션: 야간에 유휴 상태가 많고, 아침에 트래픽이 증가하는 서비스
  • 네트워크 환경이 안정적이지 않거나 DB 서버가 간헐적으로 재시작되는 경우
  • 대규모 트래픽을 처리하는 경우
  • 다중 데이터베이스 환경
• ValidationQuery를 사용하지 않는 경우
  • ValidationQuery는 추가 쿼리 실행 오버헤드를 유발하기 대문에 성능이 중요한 경우는 사용하지 않는다.
  • 애플리케이션과 DB가 같은 네트워크 내에서 동작하며, 연결이 안정적이고 문제가 드믈 경우 생략 가능
  • 커넥션이 짧은 주기로 재활용되거나 자주 새로운 커넥션이 생성되는 경우

 

ValidationQuery를 사용하는 이유

• 유휴 커넥션 문제 해결
  • 데이터베이스 연결이 오랜 시간 동안 사용되지 않으면 네트워크 문제, DB 서버 재시작, 세션 만료 등으로 인해 해당 연결이 끊어질 수 있다. 그러나 커넥션 풀은 여전히 이 연결을 ‘사용 가능’으로 간주할 수 있다. 이 상태에서 문제가 발생한 커넥션을 요청하면 오류가 발생한다.
• 안정적인 연결 보장
  • 풀링된 연결을 사용하기 전에 유효성을 검사하여 애플리케이션이 항상 정상적인 커넥션만 사용하도록 보장한다.

 

ValidationQuery 작동 방식

1. 커넥션 풀에서 커넥션을 제공하기 전에 설정된 ValidationQuery를 실행한다.
2. 이 쿼리가 성공적으로 실행되면 해당 커넥션은 ‘유효’한 것으로 간주한다.
3. 실패할 경우, 해당 커넥션은 폐기되며 새로운 커넥션을 생성한다.

위의 내용을 토대로 예측된 원인은 ‘유휴 연결 관리 문제’이다. 일반적인 환경에서는 잘 동작하지만, 일정 시간이 지나 유휴 상태로 있던 연결이 끊어진 후 재사용을 시도하면서 문제가 발생한 것으로 보인다.

 

이관한 서버는 아직 공개된 사이트가 아니므로 사용자가 없는 관계로 유휴시간이 8시간 이상으로 길어져 해당 문제가 발생한 것으로 보인다. 이대로 두어도 크게 문제가 되지 않을 것 같았으나 혹시 추후 사용자 접속자 수가 8시간 이내에 한 번도 안 들어올 가능성도 있기 때문에 해당 에러는 해결하고 넘어가는게 좋을 것 같다는 생각이 들었다.

 

#mysql/mariadb wait timeout 확인
SHOW VARIABLES LIKE 'wait_timeout';

#mysql/mariadb interactive timeout 확인
SHOW VARIABLES LIKE 'interactive_timeout';

 

이관한 MariaDB의 wait_time은 8시간 이었다.

즉, 정상 동작을 확인한 후 하루 이상 아무런 접속이 없었기 대문에 유휴 상태가 8시간 이상 지속되면서 세션이 종료된 것이다. 그러나 기존 커넥션 풀(DBCP)는 끊어진 연결을 여전히 유효하다고 간주하고 반환하였으나, 실제로는 연결이 끊어져 있어 Connection reset 오류가 발생한 것이다.

 

wait_timeout vs interactive_timeout

• wait_timeout
  • 적용 대상: 비대화형(non-interactive) 연결
  • 일반적으로 애플리케이션이나 백엔드 서비스가 데이터베이스와 연결을 유지할 때 사용
• interactive_timeout
  • 적용 대상: 대화형(interactive) 연결
  • 터미널 기반 클라이언트에서 사용자 입력을 기다리는 세션

 

항목	wait_timeout	interactive_timeout
적용 대상	비대화형 연결 (애플리케이션 등)	대화형 연결 (CLI, 터미널 등)
주요 예	JDBC, ODBC, 백엔드 서비스	MySQL CLI, MariaDB CLI
설정 우선순위	비대화형 클라이언트에만 적용	대화형 클라이언트에만 적용
기본값 (MariaDB)	600초	28800초 (8시간)
기본값 (MySQL)	28800초 (8시간)	28800초 (8시간)

 

• 추천 설정
  • 서버에 연결하는 애플리케이션이 많을 경우 비대화형 연결의 유휴 타임아웃을 낮춰 연결 낭비를 줄인다.
  • 개발 환경에서 CLI를 자주 사용하는 경우 interactive_timeout을 더 길게 설정하여 세션이 자주 끊기는 것을 방지한다.

---

🎁 해결방안

해결방안은 DB의 wait_timeout 설정을 늘리거나 Connection Pool 유효성 검사를 진행하여 문제를 해결할 수 있다.

 

그러나 첫번째 방법인 wiat_timeout을 8시간 이상으로 늘리게 되면, 장시간 유지되지 않는 연결이 많을 경우 시스템 자원을 낭비하거나 트래픽이 몰렸을 때 연결 부족 상황이 발생할 수 있다. 따라서 wait_timeout 설정은 건드리지 않고, ValidationQuery 설정과 끊어진 연결을 자동으로 복구할 수 있는 JDBC 드라이버의 autoRecoonect 옵션을 설정하는 방식으로 문제를 해결하였다.

 

DBCP Configuraion validationQuery 설정 추가

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

 

JDBC 자동 재연결 옵션 설정

• 자동 재연결 설정 없이 ValidationQuery 설정만 있는 경우 끊어진 연결을 폐기하고 새 연결을 생성하므로 복구하는데 속도가 비교적 느리다. 자동 재연결 옵션 설정을 할 경우 끊어진 연결을 복구하므로 즉시 복구가 가능하며, 애플리케이션이 연결을 사용 중일 때 연결이 끊어지는 경우에는 ValidationQuery가 작동하지 않는다. 자동 재연결을 통해 이러한 상황에서도 복구가 가능하다.
• 자사 홈페이지의 경우 트래픽 자체가 적고, 유휴 시간이 길기 때문에 ValidationQuery에 대한 오버헤드가 크지 않을거라 생각했고, 유휴 연결 상태를 확인하면서 끊어진 연결을 바로 복구 시키기 위해 두 설정을 모두 택하였다.

 

jdbc:mariadb://yourhost:3306/dbname?autoReconnect=true&testOnBorrow=true

설정	ValidationQuery만 있음	자동 재연결만 있음	두 설정 모두 있음
목적	유휴 연결 상태 확인	끊어진 연결 복구	유휴 상태 확인 + 끊어진 연결 복구
연결 끊김 복구	끊어진 연결을 폐기하고 새 연결 생성	자동으로 새 연결 생성	끊어진 연결 자동 복구, 유효성 확인 추가
오버헤드	쿼리 오버헤드(ValidationQuery 실행)	새로운 연결 생성 비용	쿼리 오버헤드 + 연결 생성 비용
복구 속도	비교적 느림(ValidationQuery 실패 후 새로운 연결 생성)	빠름(연결 끊김 시 즉시 복구)	즉시 복구 + 유효성 확인
적합한 환경	트래픽이 많고 유휴 연결이 잦은 환경	트래픽이 적고 연결이 장시간 유지되는 환경	안정성이 가장 중요한 환경(트래팩 적음, 유휴 시간 긴 경우)

 

---

참고자료

https://www.leafcats.com/237

https://www.egovframe.go.kr/home/qainfo/qainfoRead.do?menuNo=69&qaId=QA_00000000000021342

https://velog.io/@mooh2jj/커넥션-풀Connection-pool은-왜-쓰는가