Engineering

DBA와 개발자가 모두 행복해지는 Hibernate의 in_clause_parameter_padding 옵션

2019년 12월 13일

Java ORM 기술의 표준 명세인 JPA가 소개된 지 참 오래되었지만, 국내 현실상 대규모 시스템에서 적용되어 사용된 운영 경험이 충분히 쌓이지 않고 공유되지도 않는 것 같습니다. 대부분 JPA를 사용한다고 하면 Hibernate를 구현체로 사용하게 됩니다. 제가 담당하는 서비스 역시 Spring Data JPA를 활용하고 있고 JPA 구현체로 Hibernate를 사용하고 있습니다. 개발과 서비스를 운영하면서 겪은 일 중에 SQL의 in 절과 관련해서 발생한 문제를 해결하면서 알게 된 옵션과 그로 인한 효과를 소개해 드리고자 합니다.

증상

운영 중인 서버가 이유 없이 응답이 느려집니다. 그리고 가비지 컬렉션을 너무 자주 하고 있습니다.
힙 덤프를 해보니 하이버네이트가 메모리의 60% 이상을 사용하고 있습니다. JVM기동할 때 xmx를 1024m으로 설정했는데 지금 이녀석이 약 650MB를 사용하고 있습니다.

심지어 GC를 강제로 시켜봐도 여유 heap 공간이 생기지 않습니다.
결국 얼마 가지 않아 OutOfMemoryError가 발생합니다.

원인

저는 JPA를 사용할 때, 과다한 join의 문제를 해결하기 위해서 in clause를 많이 사용합니다.
아래 코드는 spring-data-jpa를 적용한 코드입니다. findByIdIn 메서드 인자를 List로 처리하면 자동으로 in clause로 변경이 됩니다.

public interface SampleRepository extends CrudRepository<Sample, Integer>{
    List<Sample> findByIdIn(List<Integer> ids);
}

아래와 같이 SQL이 만들어지면서 호출이 될 것입니다.

select .... from Sample where id in (? ,? ,?)

그런데 여기서 문제가 있습니다. ids 는 고정된 크기를 가진 객체가 아닙니다. 대부분의 클라이언트가 특정 개수를 단위로 호출하게 되겠지만 항상 그 개수로 떨어지는 호출이 아닐 것입니다.
예를 들어 아래의 수를 ids에 할당하여 결과를 얻어내고 싶습니다.

1,2,3,4,5,6,7,8,9,10,11,12

숫자가 과다하게 많아질 것을 대비해서 5개씩 잘라서 호출하도록 개발했다고 가정합시다. (물론 5개씩 나눈 것은 실무적이지 않습니다. )

1,2,3,4,5
6,7,8,9,10
11,12

이렇게 반복하면서 호출하고 결과를 얻게 되겠지요.
이때 처리되는 SQL은 다음과 같이 호출되는데요. 계속 5개 인자로 query를 하다가 마지막에 in clause에 2개의 인자 query를 발생합니다.

select .... from Sample where id in (? ,? ,?, ?, ?);
select .... from Sample where id in (? ,? ,? ,? ,?);
select .... from Sample where id in (? ,? );

다음번 호출은 아래의 ids를 가지고 후출 합니다.
예를 들어 아래의 수를 ids에 할당할 것입니다. (13을 추가했습니다.)

1,2,3,4,5,6,7,8,9,10,11,12,13

그럼 예상대로 다음의 SQL이 호출됩니다.

select .... from Sample where id in (? ,? ,?, ?, ?);
select .... from Sample where id in (? ,? ,? ,? ,?);
select .... from Sample where id in (? ,?, ?);

이렇게 계속 서버를 운영하다 보면 5가지 종류의 SQL이 생성되고 호출됩니다.
일반적으로 서버 측에서 클라이언트는 통제할 수 없습니다.
그냥 제한 없이 호출하면 호출 때마다 다른 종류의 SQL이 생성될 확률이 높아집니다.
이 문제로 인해서 데이터베이스 관점에서 preparedStatement 효과를 못 누리게 되는 문제가 발생합니다.
더 심각한 것은 애플리케이션에서는 Hibernate도 Execution Plan Cache에 종류별로 query 데이터를 적재하게 됩니다. 이 Execution Plan Cache는 힙 메모리를 서서히 점유하게 됩니다.
제가 겪은 문제는 1000 개 단위로 데이터를 fetch 해 오는 것이었는데, 데이터 1000개를 잘라서 호출을 하다 보니 장기간 호출하다 보면 1000개의 다른 Execution Plan Cache를 발생시키는 문제가 있습니다.

해결방법

1) in_clause_parameter_padding

Hibernate ORM 5.2.18 이상을 사용하고 있다면 다음의 옵션을 설정하면 됩니다.

<property>
    name="hibernate.query.in_clause_parameter_padding"
    value="true"
</property>

물론 spring boot에서는 다음과 같이 application.properties에 설정해 주면 되겠죠.

spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

파라미터를 아래와 같이 호출한다고 해 봅시다.

1,2,3
1,2,3,4
1,2,3,4,5
1,2,3,4,5,6

Vlad Mihalcea의 설명에 따르면 2의 거듭제곱 단위로 Padding 합니다. 옵션이 없었다면 각기 다른 4개의 SQL을 호출했겠지만 2 종류 안에 머물러 있습니다.

select .... from Sample where id in (1 ,2 ,3, 3);
select .... from Sample where id in (1 ,2 ,3, 4);
select .... from Sample where id in (1 ,2 ,3, 4, 5, 5, 5, 5);
select .... from Sample where id in (1 ,2 ,3, 4, 5, 6, 6, 6);

만약 1~ 1000 개의 다른 파라미터 개수의 호출이 있다면 옵션이 없을 때는 1000개의 다른 SQL을 만들어 내겠지만 위의 옵션을 쓴다면 단지 10 종류의 SQL이 생성될 것입니다.
이렇게 되면 execution plan을 재사용 할 수 있게 되어 execution plan cache 가 힙을 가득 채워 버리는 문제도 해결될 것 입니다. 이 방안이 코드의 변경 없이 성능을 향상시키는 좋은 방법이라고 생각합니다.

2) padding programmatically

말 그대로 패딩을 프로그램으로 만들어 주는 것입니다.
문제는 이 메서드나 서비스의 클라이언트가 최대 몇 개 까지 보낼지 알 수 없기 때문에 패딩 단위 처리를 직접 구현해야 한다는 귀찮음이 있습니다.
배치 애플리케이션처럼 애플리케이션 내에서만 호출이 되고 클라이언트를 스스로 개발하는 경우에는 최적의 결과를 만들어 낼 수 있습니다.

3) execution plan cache 사이즈 조정

단순히 execution plan cache의 크기를 제한할 수도 있습니다.

<property
    name="hibernate.query.plan_cache_max_size"
    value="2048"
/>

2048개는 기본값이고 대부분의 small, medium-size 애플리케이션으로 충분한 값이라고 합니다.
이 충분한 값이 어떤 경우에는 문제를 일으키니까 2048 보다 줄여 가면서 메모리 상황을 보아야 합니다.
native query는 parameter metadata를 관리하는데 이것도 캐시의 크기를 제한할 수 있습니다.

<property
    name="hibernate.query.plan_parameter_metadata_max_size"
    value="128"
/>

DBA와 개발자가 모두 행복해지는 Hibernate의 in_clause_parameter_padding 옵션

증상

원인

해결방법

1) in_clause_parameter_padding

2) padding programmatically

3) execution plan cache 사이즈 조정

참고 링크