MySQL/MariaDB Sort 모니터링

MySQL/MariaDB Sort 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL 뿐만 아니라 다른 제품의 RDB에서도 정렬(Sort)이 발생할 때 시스템 리소스가 추가적으로 발생하기 때문에 모니터링이 필요하다. Select 모니터링시 테이블 스캔이 발생하는지 또는 인덱스 를 잘 사용하는지에 대한 부분에 대해서 살펴 보았다면 이번에는 정렬과정에서 발생하는 비용에 대해서 살펴본다.

 

·       MySQL/MariaDB Select 모니터링  : http://sqlmvp.kr/221260775725

 

아래 스크립트를 실행 하면 Sort와 관련된 지표를 확인할 수 있다.

show status where variable_name like ‘sort%’

 

 

각 지표가 의미하는 내용은 아래 표를 참고 한다.

Variable Name	Comment
Sort_merge_passes	멀티 머지 처리 횟수
Sort_range	Index Range Scan결과에 대한 정렬 작업 횟수
Sort_rows	정렬한 전체 레코드 수
Sort_scan	Full table scan 결과에 대한 정렬 작업 횟수

 

일반적으로 ORDER BY, GROUP BY를 사용하면 정렬과정이 필요하다. 정렬을 하기 위해서는 우선 모든 레코드를 메모리에 적재하고 정렬을 시도한다. 이때 데이터 양이 sort_buffer_size 용량 보다 크다면 모든 데이터를 메모리에 적재할 수 없으므로  MySQL 엔진은 정렬해야 할 레코드를 여러 조각으로 나누어서 처리한다. 이때 중간 저장을 위한 임시 저장소로 디스크를 사용한다.

 

Sort_buffer_size 확인은 아래 스크립트로 확인할 수 있다.

show variables where variable_name like ‘sort%’

 

 

정렬시 발생하는 과정을 살펴보면 아래 그림과 같다. 메모리의 SORT BUFFER에서 정렬을 수행하고 임시로 디스크(TEMP FILE)에 저장한다. 그리고 모든 레코드에 대해서 앞의 과정을 반복한다. 각 버퍼크기 만큼 정렬되 레코드를 다시 병합하면서 정렬을 수행한다.

Sort 과정에서 데이터양이 많은 경우 디스크에서 임시 파일을 사용하게 되는데 디스크의 속도에 따라 처리 과정이 영향을 받는다. 따라서 sort_merge_passes 지표가 높게 나타난다면 sort_buffer_size를 조절하여 디스크의 사용을 줄일 수 있는 방안을 검토해야 한다.

 

 

 [참고자료]

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

 

 

2018-04-24 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, sort 모니터링, sort_buffer_size, sort merge, sort_merge_passes, 정렬 메모리,