MySQL eq_range_index_dive_limit 를 활용한 쿼리 수행시간 개선 및 실무에서 발생할 수 있는 연관된 문제

배경

의도치 않게 쿼리 수행시간이 증가하거나 이슈가 발생하는 부분 중 하나인 쉽게 발생하지만 원인파악이 쉽지 않은 부분에 대한 내용입니다.

IN절로 인한 쿼리 수행시간이 증가하는 것에 따른 원인파악과 해결방법에 대해서 다룹니다. IN절에 들어가는 개수에 따라 쿼리 수행시간이 급격하게 나빠지는 경우가 존재하기 때문입니다.

설정값

eq_range_index_dive_limit

eq_range_index_dive_limit

IN 절에 길게 중첩되었을때 쿼리 속도를 높이는데 사용되는 값이다. 이 값 이상이라면 옵티마이저는 인덱스 다이빙을 수행하지 않고 기존 인덱스 통계를 사용한다. 0으로 설정하면 항상 인덱스 다이빙을 수행한다.

 

아래와 같은 쿼리가 있을 때,

col_name IN(val1, ..., valN) 
col_name = val1 OR ... OR col_name = valN

옵티마이저는 아래와 같은 규칙으로 비용을 추정한다.

• col_name에 unique index가 있다면 최대 1개의 유일값이기 때문에 각각의 range비용은 1이다.
• col_name에 index가 unique 하지 않으면 행수를 추정하기 위해서 다이빙을 수행하거나 인덱스 통계로 row count를 추정한다.

 

인덱스 다이브를 사용한다면 옵티마이저는 범위의 각 끝에서 다이빙을 하고 범위의 행 수를 추정치로 사용한다. 예를들어, col_name in (10, 20, 30)은 세 번의 동등비교가 있고 옵티마이저는 행 추정치를 구하기 위해 두 번의 다이빙을 수행한다. 다이빙의 양쪽은 주어진 범위내에서 추정치를 산출하게 된다.

(동등비교에 대한 내용은 https://pjh3749.tistory.com/287 지난 글에 정리)

 

인덱스 다이빙은 정확한 행 추정치를 구해주지만 비교하는 횟수가 늘수록 옵티아미저는 추정치를 구하는데 시간이 오래걸린다. 인덱스 통계를 사용한다면 다이빙 방식보다는 덜 정확하지만 개수가 많은 리스트에서는 추정치를 구하는 시간이 더 빠르다.

 

eq_range_index_dive_limit 시스템 변수는 옵티마이저가 옵티마이저가 행 추정치를 계산하는데 어떤 전략을 사용할지 선택하게 한다.

즉 시간이 조금 걸려도 정확한 행 추정치를 원한다면 해당 시스템변수값을 조절해야 한다는 의미이다.

 

N개의 행에 대해서 인덱스 다이빙을 허용하려면 eq_range_index_dive_limit를 N+1값으로 설정하여야 한다. 통계를 아예 사용하지 않고 항상 인덱스 다이빙을 수행하려면 N의 값과 상관없이 eq_range_index_dive_limit를 0으로 설정한다.

 

최상의 추정치를 위해서 테이블 인덱스 통계를 업데이트 하려면 ANALYZE TABLE을 사용하면 된다고한다.

https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

MySQL 5.6부터 innoDB에서는 통계정보가 주기적으로 갱신된다고 한다. (이부분은 더 서치해보지 않았다)

Range Optimization시 메모리 사용량 제한하기

range_optimizer_max_mem_size

range 옵티마이징 수행시 메모리 사용량을 조절하려면 range_optimizer_max_mem_size 시스템 변수를 활용하면 된다. eq_range_index_dive_limit 을 활용한 계산 시 이용되는 메모리 사용량으로 보면 될 것 같다.

• 0값은 제한 없음을 의미한다.
• 0보다 큰 값은 옵티아미저가 range 접근시 메모리 사용량을 추적한다. 설정한 리미트 값을 넘으려고할때 range 접근 메서드는 버려지고, full table scan을 포함한 다른 방법이 고려된다. 그렇기 때문에 최적화가 덜 될 수 있다. 이게 발생되면 아래와 같은 에러가 발생한다.

 

Memory capacity of N bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

 

참고)

추가적으로 update, delete 구문에서 옵티마이저가 풀테이블 스캔으로 폴백하면서 sql_safe_updates옵션이 켜져있다면 warning이 아니라 error를 밷는다. 왜냐하면 행 수정에 있어서 아무 키가 쓰이지 않기 때문이다.

sql_safe_updates를 찾아보았는데, (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates) 이 변수가 enabled되어있다면 update, delete시 where과 limit 구문에서 키를 사용하지 않는다면 에러를 뱉는다. 이렇게 한다면 update, delete시 키가 적절히 사용되지 않아 많은 행수를 건드리는 것을 캐치할 수 있게 해준다. 디폴트 값은 off이다.

내 생각엔 키를 명시하지 않는 delete행위는 실수로 간주하고 키를 강제로 명시하기 위해서 사용하는게 아닐까 생각이 든다. 일종의 안전장치로 보인다.

 

다시 돌아와서 위에 지정한 메모리를 초과하고 옵티마이저가 덜 최적화 된 계획으로 대체되는 개별 쿼리의 경우 range_optimizer_max_mem_size 값을 늘리면 성능이 향상 될 수 있다. (당연히 대충 늘리거나 0으로 설정하기보다 사내 dba가 있다면 가용리소스가 얼마나 되는지 정확히 파악후에 액션을 취해야 될 것 같다)

실습

간단한 테이블을 하나 만들었다.

id 와 position, no 라는 필드를 가지고 position에 인덱스가 걸려있다. (이전 글 https://pjh3749.tistory.com/287 의 테이블과 같다)

 

 

분포도는 아래와 같다. (특별한 이유는 없고 막 넣은것)

 

 

분포도를 보았을 때 a04, a05의 조건을 주고 조회를 했을때는 position에 걸려있는 인덱스를 활용하므로 효율적일거라고 예상한다. 차지하는 비율이 적기 때문.

하지만 a01처럼 테이블의 상당부분을 차지하는 컬럼으로 조회할 시 인덱스보다는 full scan을 탈 확률이 높다.

그러면 eq_range_index_dive_limit 설정으로 인해 옵티마이저가 어떻게 실행계획을 계산하는지 살펴보자.

show variables like 'eq_range_index_dive_limit';

 

해당 값을 조회를 해보면 200이 나온다. 200은 디폴트 값인데 버전마다 다르니 문서를 참고해보면 된다.

업무환경은 5.7이므로 로컬에서도 5.7로 테스트를 해보았다. 5.7은 아래와 같이 200이 기본값이다.

 

 

위 테이블 컬럼 분포도에서 a04, a05가 차지 하는 비율이 매우 낮으므로 인덱스를 활용하기에 적절해보인다. 실행계획은 아래와 같이 확인하였다. (a100은 없지만 테이블에 insert를 더하기가 귀찮아서 일단 넣었다)

 

explain select * from JAY_TECH where position in ('a04', 'a05', 'a100');

 

type에 range가 나오고 my_idx를 (이 인덱스가 position) 사용할거다라고 계산한것을보니 의도한 대로 동작할것같다.

 

분포도가 좋지 않은경우 (a01 같은 컬럼으로 조회시) 에는 인덱스를 활용하는것보다 풀스캔이 낫다고 판단한다.

explain select * from JAY_TECH where position in ('a01', 'a02', 'a03');

 

여기까지는 기본적인 인덱스 내용이다.

 

이제 저 eq_range_index_dive_limit 옵션을 변경해보자.

set session eq_range_index_dive_limit=2;

 

테스트의 편의성을 위해서 2로 설정을 하였다. 다시 이야기하면 in절의 개수가 2가 넘어가면 옵티마이저가 인덱스 통계를 활용하여 계산하고 그 밑이면 인덱스 다이빙을 통해 계산하게 될 것이다.

 

show variables like 'eq_range_index_dive_limit';

* 분포도가 좋지 않은 경우

explain select * from JAY_TECH where position in ('a01', 'a02', 'a03');

 

eq_range_index_dive_limit를 변경하기 전과 차이가 없다.

 

 

* 분포도가 좋은 경우

explain select * from JAY_TECH where position in ('a04', 'a05', 'a100');

 

..??

 

in절에 2개가 넘어버려서 인덱스 통계를 활용하여 계산한다고 했는데 계산결과가 매우 좋지 않다. 인덱스 다이빙을 통해 계산하였다면 보다 정확한 실행계획 (3개의 row만을 읽는)을 계산할텐데 통계를 기반으로 계산하니 매우 비효율적인 계획이 나왔다.

문서상에는 인덱스 통계를 활용한것이 다이빙보다는 부정확하다고 되어있긴한데 아예 최악으로 치달을 수 있을것으로 보인다.

결론

인덱스 통계를 활용한 비용계산은 부정확할 수 있기 때문에, 혹은 의도한대로 실행계획이 타지 않을 수 있기 때문에 in 절의 개수에 얼마나 들어가는지 유의해야 한다.

또한 비지니스적으로 200개 이상씩 들어가는일이 종종 생긴다면 eq_range_index_dive_limit 이 값을 적절히 상향조정해 사용해야 의도치않은 풀스캔으로 인한 쿼리 타임아웃을 막을 수 있다.

실무에서 접할 수 있는 연관된 문제

jpa를 사용했을때 의도치 않게 쿼리가 느려지는 경우가 발생할 수 있다. eq_range_index_dive_limit과 관련이 있는데 어떤 문제인지 살펴보자.

hibernate사용시 IN 절에 들어가는 파라미터값을 캐싱하는 옵션을 사용하라고 권고한다.

hibernate.query.in_clause_parameter_padding=true

 

쿼리플랜을 캐싱하는것인데 이것을 사용하지 않는다면 메모리 효율이 떨어지게된다.

아래와 같은 쿼리들이 실행된다고 생각해보자.

select * from JAY_TECH where no in (1,2,3,4,5);
select * from JAY_TECH where no in (1,2,3,4,5,6,7,8);
select * from JAY_TECH where no in (1,2,3,4,5,6,7,8,9,10);

인자의 개수가 달라지면서 쿼리가 날아가게되면 해당 종류마다 쿼리플랜에 캐싱한다. 종류가 다양해질 수록 쿼리플랜캐싱의 양이 점점 늘어나면서 힙 메모리를 점유해버린다.

 

자세한 내용은 https://meetup.toast.com/posts/211 여기에 잘 설명이 되어있으니 참고하면 좋을 것 같다.

(참고로 저 링크에서 해결방안 2번, 3번은 좋지 않다고 생각한다. 2번은 직접구현의 문제, 3번은 캐싱의 의미가 변질되기 때문)

 

위에 언급한 padding 옵션을 true로 주면 in 절에 들어가는 파라미터 개수를 캐싱하는데 2의 제곱수를 사용한다. 즉 in절에 3개라면 4개를 보내고 5개 혹은 6개라면 8개를 보낸다. 그러면 내가 in절에 분명히 6개를 보냈는데 실제 쿼리를 보니 8개가 보내지는것이다.

 

eq_range_index_dive_limit과 연관되어 생각해보면, 디폴트값이 위에서 200이라고 했는데 그럼 내가 200개 조금 안되게 보냈는데 실제로 in절에 200개가 넘게 들어갈 수 있는 것이다. 분명히 200개 밑이면 인덱스 다이빙을 통해서 실행계획을 계산하는데, 실제로는 200개가 넘으므로 인덱스 통계를 사용해버리면서 쿼리가 느려지는 상황이 발생할 수 있다. 찾기가 어려울 수 있으니 jpa를 사용한다면 이점을 유의해야될거같다.

 

참고

https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html

http://small-dbtalk.blogspot.com/2016/02/ (실습할때 좋은 참고자료가 되었습니다)

https://kwonnam.pe.kr/wiki/java/hibernate/performance (권남님 블로그)