MariaDB SQL튜닝 사례-1

2020년 상반기에는 MariaDB 백업복구, 리플리케이션 테스트를 하면서 경험치를 쌓기 시작했다.

이 부분은 차차 업데이트를 하기로 하고...

 

오늘은 MariaDB SQL튜닝 사례에 대해 기록하려 한다.

 

작년 하반기에 기술세미나를 진행하면서 주제를 "MariaDB 성능개선 사례"로 선택했었는데...

혼자 Zabbix MariaDB 개선해보겠다고 야근까지 올려가며 노력했던 기억이 난다. ㅠㅠ

다시 생각해보니 조금 무모했지만...시간이 흐른 지금 돌아보니 피가 되고 살이 되었던 것 같다.

 

Oracle, Tibero에서는 SQL튜닝을 많이 해보았지만, MariaDB에서는 경험이 없어서 막막하기만 했다.

하지만, 사막 가운데서도 살아남는 생물체들이 있지 않는가!!

회사 내에 도움을 줄 수 있는 사람이 없었지만, 혼자 가설을 세우고 테스트해가며 증명해나가는 재미도 쏠쏠했던 것 같다 ㅎㅎ

 

기존 Oracle과 Tibero에서의 SQL튜닝 기준은 I/O(Logical Read)였는데, MariaDB에서의 SQL튜닝은 I/O로 잡기에는 좀 애매한 부분이 있었다.

 

엔진 아키텍쳐를 공부하다 보니 File per Table 방식으로 테이블별 파일이 따로 생성되는 것을 보며 I/O 기준의 튜닝은 의미가 없다는 가설을 내렸고...

커뮤니티에 조언을 구한 결과 역시, 구루 분들께서도 SQL수행시간 단축을 기준으로 튜닝하는게 맞다는 의견을 들었다.

 

따라서, performance_schema를 활용하여 Elapsed Time 기준으로 TOP 10 SQL을 추출하여 튜닝 대상을 선정하였고 SQL들 중, 빈번하게 수행되는 SQL부터 튜닝을 해보고자 하였다.

물론, slow_query에서도 빈번하게 기록되는 SQL을 기준으로 잡았다.

 

1.    튜닝대상선정이유

TOP SQL 1.

업무의 대부분을 차지하고 있는 SQL로 성능개선시 ZABBIX시스템 전반적인 개선이 될 것을 기대함

 

 

2.    튜닝전

 

SQL ID : 4042424527998719DBD693437B42F15B

DB : ZABBIXDB 수행스키마 : zabbix[zabbix] @ localhost []   SELECT * FROM history_str h WHERE h.itemid='268324' AND h.clock>1595701328 ORDER BY h.clock DESC LIMIT 3 ;   l  실행계획1

 

 

3.    문제점 및 개선사항

 

해당SQL은 OLTP성SQL로 수행시간 축소가 목표이다. 실행계획을 확인한 결과, 불필요한 미래의 테이블 파티션을 액세스하여 데이터 read가 추가적으로 발생하고 있었다.   Check) 파티션 프루닝이 동작하고 있는지 체크함 확인 결과, clock 컬럼의 timestamp 값 이상의 테이블 파티션들이 read되고 있는 것으로 확인됨. Check) 특정 timestamp 값 이상의 조건만 존재함으로 인해 범위에 대한 지정이 불명확한 것을 확인. 업무적으로 성능데이터를 조회하는 SQL이므로 clock컬럼에는 현재 시점까지의 timestamp 데이터만 존재함을 확인. between 조건으로 범위제한이 필요함을 판단. AND h.clock>1595701328 -> AND h.clock BETWEEN 1595701328 and unix_timestamp(CURRENT_TIMESTAMP)

 

 

4.    튜닝후

 

DB : ZABBIXDB 수행스키마 : zabbix[zabbix] @ localhost []   SELECT * FROM history_str h WHERE h.itemid='268324' AND h.clock BETWEEN 1595701328 and unix_timestamp(CURRENT_TIMESTAMP) ORDER BY h.clock DESC LIMIT 3

 

 

5.    튜닝결과 및 조치사항

 

튜닝전 (초)	튜닝후 (초)	개선율	조치사항
0.003527	0.001492	57.69%	Query Transformation을 통해 불필요한 테이블 파티션 액세스를 제어. 온전한 partition pruning이 되도록 조치

 

해당 SQL튜닝 케이스는 복잡한 튜닝 케이스는 아니지만...

수행횟수 TOP 10 SQL 중 하나로, 수행시간을 약간만 줄여도 전체적인 DB TIME을 줄일 수 있었다.

 

시작은 미약하나 그 끝은 창대하리라는 믿음으로 오늘도 노력해 본다.