PreparedStatement와 DB설정관리

요즘은 처음 자바 환경에서 개발 공부를 시작할때도 프레임워크 기반에서 시작해서 순수하게 JDBC 를 이용한 DB 접근 코드는 거의 작성할 일이 없는 것 같지만, JDBC 를 이용해서 코드를 작성하면 보통 이런 코드가 나온다.

Connection conn = DriverManager.getConnection(jdbcUrl, id, pw);
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM car WHERE car_id = ?");
pstmt.setLong(1);
ResultSet rs = pstmt.executeQuery();

rs.close();
pstmt.close();
conn.close();

DB의 Connection 객체를 가져와서 해당 객체를 이용해 PreparedStatement 객체를 얻고, 해당 객체로 SQL 을 실행한다. 요즘은 대부분 PreparedStatement 가 표준처럼 이용되는 것 같은데, JDBC 에는 Statement 라는 인터페이스도 있다.

Connection conn = DriverManager.getConnection(jdbcUrl, id, pw);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM car WHERE car_id = " + 1);

rs.close();
stmt.close();
conn.close();

사용법은 거의 유사한데 파라미터를 할당하는 방식이 다르다. PreparedStatement 는 이름에서부터 알 수 있듯이 뭔가가 준비된 상태라는건데 SQL 쿼리를 미리 파싱해놓고, ? 에 해당하는 파라미터만 교체해서 호출하게 된다. 당연히 동일한 쿼리가 여러번 호출되는 구조에서 성능 이점을 볼 수 있고 대표적인 취약점인 SQL 인젝션 공격을 쉽게 막을 수 있는 방법으로 처음에 공부한다.

 

이제 여기부터 헷갈려하거나 잘 모르는 분들이 많은데 prepared statement 는 JDBC 가 홀로 제공하는게 아니라 DBMS 에서 제공하고(MySQL 기준), JDBC 는 이를 호출하게 된다.

다만 JDBC 는 DBMS 의 추상화를 제공하는 인터페이스이기때문에 prepared statement 를 제공하지 않는 DBMS 가 있을 수 있고, 이 경우 해당 구현체에서 자체적으로 캐시하거나 캐시하지 않을 수 있다. 이 글은 MySQL 을 기준으로 진행한다.

 

JDBC 도 쓰지 않고, MySQL 에서 직접 prepared statement 를 이용하면 이렇게 작성할 수 있다.

// prepared statement 생성
PREPARE stmt1 FROM 'SELECT * FROM car WHERE car_id = ?';
PREPARE stmt2 FROM 'SELECT * FROM car WHERE car_name = ?';

// 생성된 prepared statement 갯수 조회
SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';

// prepared statement 제거
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;

살펴보기위해 DB 에서 직접 만드는 방식도 소개했지만 이렇게 쓸일은 없고, 애플리케이션 레벨에서 쓰게되는데 쿼리를 재사용할 수 있게 해준다니 잘 쓰면 좋을 것 같은 느낌이 든다. 그리고 다시 제일 처음에 봤던 JDBC 를 날로 사용하는 예제코드를 보면 pstmt.close() 를 볼 수 있다.

// prepared statement 제거
pstmt.close();

JDBC 코드로 검색해서 쉽게 찾을 수 있는 예제들은 보통 다 이렇게 쿼리를 한번 실행하고 preparedStatement 의 close() 를 호출한다. 이래버리면 재사용할 수 있다는 장점이 사라지지 않을까? 그리고 실제로 우리는 DBMS 를 추상화한 JDBC 를 직접 사용할 일이 거의 없다. DB 에 연결해서 쿼리를 실행시킬때도 매번 커넥션을 연결하는 경우도 없고, 보통은 Connection Pool 을 이용하게 된다. 이럴땐 어떻게 prepared statement 를 활용할 수 있을까?

 

# 설정

1. Hibernate

spring data jpa 를 이용해서 DB에 액세스하고 있다면 여러 추상화 계층에서 우리랑 가장 먼저 만나는 계층은 JPA 이며 구현체로 보통 Hibernate 를 사용하게 된다. 때문에 Hibernate 에서 뭔가 옵션을 줄 수 있는게 있을지 찾아봤는데 PreparedStatement 에 대한 내용은 없었다. 찾다보니 hibernate.query.plan_cache_enabled 라는 프로퍼티가 있긴 했는데 이건 JPQL 을 SQL 로 파싱하는거에 대한 캐시이고, 오늘 주제인 PreparedStatement 와는 무관했다.

 

2. HikariCP

한단계 더 들어가서 Connection Pool 쪽을 확인해보기로 했다. spring boot 기본 Connection Pool 라이브러리인 HikariCP 는 PreparedStatement 캐시는 지원하지 않고 있다. 좀 더 살펴보니 다른 Connection Pool 라이브러리에서는 지원하고 있는 것들도 있는 것 같다. HikariCP 이전 기본 라이브러리인 DBCP 는 설정 옵션이 있다고 한다. HikariCP 는 단순히 지원하지 않는걸 넘어서서 왜 지원하지 않고 있는지에 대한 설명도 잘 적어놨으니 참고해보면 좋겠다.

 

3. MySQL Connector

실제 JDBC 인터페이스들을 MySQL 용으로 구현하고 있는 MySQL Connector 까지 보게됐다. 그리고 prepared statement 에 대한 설정을 찾을 수 있었다.

 

- useServerPrepStmts (default false)

서버(mysql)에 prepared statement 를 만들도록한다. default 는 false 이다. 즉 이 속성을 활성화하지 않으면 위에서 알아본 DB 에서 prepared statement 를 만드는 구문이 실행되지 않는다.

- cachePrepStmts (default false)

JDBC 드라이버 수준에서 PreparedStatement 객체의 재활용 여부이다. 해당 속성이 true 이면 동일 쿼리에 대해 같은 객체를 사용하게 되고, false 이면 매번 새로 만든다.

- prepStmtCacheSize (default 25)

cachePrepStmts 속성이 true 라면 몇개의 객체를 캐시할건지 설정한다. 때문에 cachePrepStmts 속성이 false 이면 큰 의미없는 속성이다.

- prepStmtCacheSqlLimit (default 256)

캐시할 쿼리의 최대 바이트 지정. 이보다 큰 바이트의 쿼리가 들어올 경우 해당 쿼리는 캐시하지 않는다.

 

prepared statement 와 관련된 4개의 속성을 알아봤다. 일단 가장 중요한건 useServerPrepStmts 이 속성같은데 이 속성이 false 때 나머지 3개의 속성이 의미가 있을까? 라는 의문이 생겼는데 결론만 말하면 성능 관점에서 useServerPrepStmts 를 true 로 설정하고 나머지 속성들도 건드리는게 좋지만 useServerPrepStmts 가 false 여도 의미는 있다.

 

useServerPrepStmts 는 false 인데 cachePrepStmts 를 true 로 설정했을때를 살펴보자. MySQL Connector 에서는 PreparedStatement 인터페이스를 구현하고 있는데 구현체중에 ClientPreparedStatement 라는 구현체가 있다. 이 구현체는 클라이언트 측, MySQL 서버의 클라이언트가 되는 애플리케이션 서버에서 PreparedStatement 를 캐시하게된다. useServerPrepStmts 를 false 로 두게되면 MySQL Connector 는 클라이언트 구현체를 사용하게 되는데 이러면 DB 수준에서 prepared statement 를 생성하지는 않지만 JVM 내에서 PreparedStatement 를 캐시하게 되는것이다.

 

useServerPrepStmts 를 true 로 설정했을때 사용하는 구현체인 ServerPreparedStatement 가 있다. 해당 구현체는 예상하듯이 DB 에 prepare 명령을 날리는 구현체이며, 또한 ClientPreparedStatement 를 상속받고 있기 때문에 ClientPreparedStatement 에 구현된 내용들도 모두 포함한다.

 

prepStmtCacheSize 에 설정된 값을 초과해서 쿼리가 들어오게되면 캐시 evict 가 발생하는데 LRU 알고리즘 방식으로 처리된다.

 

해당 설정들을 잘 확인해서 mysql 연결시 적절한 속성값을 지정하면 된다.

jdbc:mysql://localhost:3306/app?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048

 

# 주의사항

# useServerPrepStmts 를 true 로 뒀을때

useServerPrepStmts 를 true 로 하게되면 MySQL 서버에도 prepared statement 를 생성하는걸 알아봤다. 하지만 MySQL 에서 생성할 수 있는 prepared statement 는 무한하지 않다. 때문에 서버에 설정된 값을 초과하여 생성하게되면 MySQL 을 에러를 일으키고 해당에러는 그대로 애플리케이션으로 전파된다. 이때 에러는 1461 코드로 발생한다. MySQL 에 설정되어있는 값은 아래 쿼리를 통해 알아볼 수 있다.

SHOW VARIABLES LIKE 'max_prepared_stmt_count';

 

# MySQL 에서 prepared statement 해제와 prepStmtCacheSize 의 적절한 값

그리고 MySQL 서버에서 생성하는 prepared statement 는 MySQL 글로벌에서 동일 쿼리당 1개가 아니고, 커넥션에서 쿼리당 1개이다. 특정 쿼리를 어떤 커넥션에서 실행시킬지는 개발자가 지정하지도 않고, 지정하고 싶지도 않은데 커넥션 풀 내 커넥션 수가 20개이고 이때 특정 쿼리를 10개의 커넥션에서 실행했다면 MySQL 에는 동일 쿼리에 대해 prepared statement 가 10개가 생성된다. 그러다가 11번째 커넥션이 실행하면 또 1개가 추가되는 것이다.

 

그럼 MySQL 서버에서 deallocate 명령으로 prepare statement 를 지우는건 언제일까? prepStmtCacheSize 값과 관련이 있는데 JVM 에서 prepStmtCacheSize 값을 초과해서 쿼리가 실행되면 LRU 알고리즘으로 가장 적게 사용된 PreparedStatement 를 close() 한다고 설명했다. 이때 ClientPreparedStatement 라면 그냥 객체수준에서만 캐시에서 제거되고, ServerPreparedStatement 라면 MySQL 서버에서도 지우게 된다. 그러면 이때부터 prepStmtCacheSize 에 대한 적절한 값을 고민해보게 되는데 동일 쿼리라도 커넥션당 1개씩 prepared statement 를 생성한다고 했으니

prepStmtCacheSize * 커넥션 수 <= MySQL 에 설정된 prepared statement 값

이라는 결과가 나온다. 여기에 엔터프라이즈 환경에서 1대의 JVM 만 구동하는 경우는 거의 없기 때문에 인스턴스 수까지 넣어줘야한다.

JVM 인스턴스 수 * prepStmtCacheSize * 커넥션 수 <= MySQL 에 설정된 prepared statement 값

이 조건을 만족해야 MySQL 서버에서 에러를 맞는일이 없을 것이다. 물론 useServerPrepStmts 를 false 로 둔다면 MySQL 에 prepare statement 자체를 만들지 않으니 위 공식은 아무의미 없다.

 

# useServerPrepStmts 와 cachePrepStmts 가 true 이고, prepStmtCacheSqlLimit 을 초과하는 쿼리가 실행될때

쿼리가 실행되면 MySQL 에 prepared statement 를 만드는 상태에서 prepStmtCacheSqlLimit 를 초과하는 쿼리가 들어온다면 어떻게 될까? 이땐 prepStmtCacheSqlLimit 를 값을 초과하기 때문에 MySQL Connector 에서 PreparedStatement 객체는 캐시하지 않지만 MySQL 에서는 prepared statement 를 생성한다. 오잉 그럼 캐시에 아예 안들어가기 때문에 위에서 얘기한 deallocate 시점이 존재하지 않게되고, 그대로 메모리릭이 나듯이 MySQL 에 prepared statement 릭이라도 나는걸까? MySQL Connector 에서 PreparedStatement 를 캐시하지 않기 때문에 생성 후 바로 close() 를 호출하게 되고, 이때문에 MySQL 에도 prepared statement 가 생성되지만 곧 제거된다.

 

이는 useServerPrepStmts 를 true 로 두고, cachePrepStmts 를 false 뒀을때도 마찬가지다. useServerPrepStmts 가 true 이기 때문에 MySQL 에 prepare statement 는 생성하지만 cachePrepStmts 가 false 이기 때문에 한번만 사용하고 바로바로 제거해서 prepared statement 를 써서 생기는 이점이 전혀 없다.

 

차라리 useServerPrepStmts false, cachePrepStmts true 라면 MySQL 측면에서의 prepare statement 는 없지만 JVM 에서 PreparedStatement 객체라도 캐시하지만 그 반대는 아무런 이점이 없다. 물론 말이 그렇다는거지 MySQL 수준의 prepare statement 없이 PreparedStatement 객체만 캐시하는 것도 굳이 이렇게 해서 얻는 이점을 찾기 어렵기 때문에 성능에 대한 고민때문에 이 글을 보고 있다면 useServerPrepStmts 와 cachePrepStmts 는 짝꿍처럼 true 로 하는게 좋다.

 

# 참고자료

- https://vladmihalcea.com/mysql-jdbc-statement-caching/

- https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

- https://github.com/brettwooldridge/HikariCP?tab=readme-ov-file#statement-cache