mariadb partition table(파티션 테이블)을 join 사용할 때는 성능 이슈에 주의해야 한다

20분 내외면 수행이 끝났던 월배치 중 하나가

2시간이 넘게 걸리면 연관배치들이 지연처리되면 서비스에 문제가 발생하였다.

 

문제의 sql는 update 를 하는 구문이었고

 

이를 select로 변경하고 실행계획을 분석하던 중에 문제의 원인에 접근했다.

 

 

1.  슬로우쿼리 확인

 

 

#1) AS_IS

explain
select count(*) from TB_AAA T1
INNER JOIN TB_BBB T2            
ON T1.SEQ_NO = T2.SEQ_NO          
INNER JOIN TB_CCC T3 /* 24GB 파티션 테이블 */
ON T1.SEQ_NO = T3.SEQ_NO
WHERE T1.CHNG_DTTM BETWEEN DATE_FORMAT(CONCAT('202305', '01'),'%Y-%m-%d 00:00:00')                        
AND DATE_FORMAT(LAST_DAY(CONCAT('202305', '01')),'%Y-%m-%d 23:59:59')
;

value)
1	SIMPLE	T2	index	IDX_BBB_01		IDX_BBB_01	11			1314006		Using where; Using index
1	SIMPLE	T1	eq_ref	PRIMARY,IDX_AAA_06	PRIMARY		8	XXX.T2.SEQ_NO	1		Using where
1	SIMPLE	T3	ref	IDX_CCC_07		IDX_CCC_07	9	XXX.T2.SEQ_NO	1		Using index

 

# 인덱스 스캔 방법 중 index 란?
인덱스를 처음부터 끝까지 탐색하여 데이터를 찾는 방식 (Index Full scan)

-> index 라고 인덱스 스캔이 잘 된다고 생각하면 안된다 인덱스 스캔을 하는 방식 중에 성능이 매우 떨어지는 방식이다.

 

update문에서 where 조건절 T1.TB_AAA의 컬럼 CHNG_DTTM은

IDX_AAA_06 이라는 인덱스가 있는데

 

조건값 '2023-05-01 00:00:00 ~ 2023-05-31 23:59:59' 가 인덱스를 사용할 수 있는 범위에 해당함에도

이것을 옵티마이져가 제대로 활용하지 못하는 것을 알게 되었다.

 

 

 

옵티마이져가 정말 멍청했는지 직접 인덱스를 할당해서 확인해본다.

 

# TO_BE)

explain
select count(*) from TB_AAA T1 force index (IDX_AAA_06)
INNER JOIN TB_BBB T2            
ON T1.SEQ_NO = T2.SEQ_NO          
INNER JOIN TB_CCC T3 /* 24GB 파티션 테이블 */
ON T1.SEQ_NO = T3.SEQ_NO
WHERE T1.CHNG_DTTM BETWEEN DATE_FORMAT(CONCAT('202305', '01'),'%Y-%m-%d 00:00:00')                        
AND DATE_FORMAT(LAST_DAY(CONCAT('202305', '01')),'%Y-%m-%d 23:59:59')
;

value)
1	SIMPLE	T1	range	IDX_AAA_06	IDX_AAA_06	6			135160		Using index condition
1	SIMPLE	T2	ref	IDX_BBB_01	IDX_BBB_01	9	XXX.T1.SEQ_NO	1		Using index
1	SIMPLE	T3	ref	IDX_CCC_07	IDX_CCC_07	9	XXX.T1.SEQ_NO	1		Using index

 

실행계획으로 보았을 때는 인덱스가 range 스캔으로 풀리는 것으로 보아 더 나은 방식으로 보인다.

 

# 인덱스 스캔 방법 중 ragne 란?

특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출한다.

-> ref나 eq_ref보다는 느리지만 그래도 쓸만한 인덱스 검색방식이다. (range_optimizer_max_mem_size 변수와 연관있음)

 

하지만 옵티마이져는 최종 sql 실행시 가장 빠른 방식을 스스로 검색해서 실행계획을 세우기 때문에

반드시 위 인덱스 스캔이 더 빠르다고 판단할 수 없다.

 

실제로 select 문을 실행해 보아야한다.

 

#1) AS_IS 수행시간  ->  22초

#2) TO_BE 수행시간 -> 16초

 

음...

 

 

실제로 옵티마이져가 제대로 실행계획을 세우지 못한 것을 확인했다.

이번에는 옵티마이져가 영리하지 못했다.

 

 

 

2. 원인분석

 

 

그럼 왜 이런 일이 발생한 것일까?

 

그것은 update를 처리하는 테이블과 join 된 파티션 테이블의 용량이 문제였다

파티션 테이블에 별도의 조건값이 없어 파티션 푸르닝이나

파티션의 인덱스를 제대로 활용하지 못하고

통째로 join을 하다보니

 

버그인지는 모르겠지만 ( mariadb ver 10.4.18)
옵티마이져가 조건절의 값이 인덱스가 있음에도 제대로 활용하지 못하는 현상이 발생했다.

 

그럼 24GB나 되는 파티션 테이블을 조인해서 사용도 안하는데

 

왜 조인했나?

 

 

대부분의 화면에서 조회하는 sql은 보통 동적쿼리다.

 

이것이 DBA 입장에서는 상당히 머리가 아파질 수 있는 부분인데

 

예를 들어 신규 개발 SQL을 검수할 때 (동적쿼리라면)

서비스환경을 적대적으로 가정하고 그것에 대한 경우의 수를 생각해

실행계획이나 추가인덱스 설정등..SQL을 더 꼼꼼하게 확인해야하는 어려움이 있다.

 

이번 발생한 슬로우 쿼리 또한

웹페이지에서 조회시 TB_CCC(파티션 테이블)와 관련된 검색조건은 빼고 검색을 해도

조회가 되도록 되어있을 것이다.

 

 

 

3. 해결방법

 

 

 

가장 좋은 해결방법은 TB_CCC(파티션 테이블)에

파티션 푸르닝을 사용할 수 있게 조건값을 추가하거나

아니면 TB_CCC(파티션 테이블)의 용량 자체를 줄여서
옵티마이져가 멍때리지 않고 제대로 역할을 할 수 있게 환경을 만들어 주는 것

 

 

여기서 궁금한 게 생겨 테스트를 해봄!

Test) 

대용량 테이블(TB_CCC)의 파티션을 삭제하면서
언제쯤 옵티마이져가 정상적으로 조건절의 인덱스 값을 가져다 플랜을 만드는지 확인해 보았다.

월별 파티션을 하나씩 지우면서 플랜을 뜨다보니

TB_CCC의 용량이 24GB에서 22GB 로 줄었을 때!

드디어 옵티마이져가 where 조건절의 인덱스 IDX_AAA_06 가져다 실행계획을 세우는 것을 확인함

(아. 그래서 얼마전까지는 발생안했고 이번에 발생했구나...^^;)

 

explain)
1 SIMPLE T1 range	PRIMARY,IDX_AAA_06 	IDX_AAA_06 6 			134702 	Using where; Using index
1 SIMPLE T2 ref 	IDX_BBB_01 		IDX_BBB_01 9 	XXX.T1.SEQ_NO 	1 	Using index
1 SIMPLE T3 ref		IDX_CCC_07 		IDX_CCC_07 9 	XXX.T1.SEQ_NO 	1 	Using index

TB_CCC(파티션 테이블) 의 조인 컬럼 SEQ_NO 의 인덱스를 메모리에 올려 실행계획을 세울 때

옵티마이져가 판단하는데도 MAX 가 있는 것으로 보임

 

 24GB가 넘어가면서 문제가 생겼는데..
이것이 22GB 줄었을 때는 옵티마이저가 다시 재정신을 차림

 

검증은 끝났고 

화면에서 파티션 푸르닝 기능을 사용할 수 있는지 개발과 협의해 봐야겠고,

TB_CCC(파티션 테이블)의 데이터 용량은 삭제 주기를 정해서 6개월 이내의 데이터는 삭제하는 것으로 사업부서와 협의해봐야겠다.

force index까지는 사용하지 않고 끝났으면 좋겠다.

 

 

4.결론

 

대용량 파티션 테이블 join 사용법

1) 가능하면 파티션 푸르닝이 포함된 컬럼을 where 조건절에 넣는다.

2) 그것이 안되면 파티션 삭제주기를 정해서 주기적으로 파티션 테이블 용량을 관리한다.

3) 이것도 안될시에는 파티션 테이블을 사용하는 sql을 주기적으로 모니터링하면서
인덱스 스캔이 불가능해지면 forec index로 인덱스를 강제로 할당한다.

 

#파티션 푸르닝이란?
파티션 프루닝(pruning)은 MySQL 에서 WHEHE 구문에 사용된 조회 조건에 만족한 파티션만 추출하는 기능이다.
최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 배제하는 것 입니다.
파티션 프루닝은 SELECT, DELETE, UPDATE 구문에서만 가능합니다.

 

 

끝.