MySQL/MariaDB Sort Buffer

 

·       Version : MySQL,  MariaDB

 

MySQL/MariaDB에서는 데이터를 정렬(ORBER BY)하기 위해 별도의 메모리 공간을 할당하는데 이때 사용되는 메모리가 Sort buffer이다. 메모리는 정렬이 필요할 경우에만 할당되며, 쿼리 실행이 완료되면 시스템으로 즉시 반납된다. Soft buffer 크기는 시스템 설정 변수인 sort_buffer_size 조정 있다. sort_buffer_size byte단위로 표시된다.

show variables where Variable_Name like '%sort_buffer%';

 

 

정렬해야하는 데이터의 크기가 작은 경우에는 Sort buffer 만으로 충분할 수도 있지만, 정렬해야하는 데이터가 sort buffer보다 경우에는 정렬해야하는 데이터를 여러 조각으로 나누어서 처리되며, 임시 저장을 위해 디스크를 사용한다. 그리고 디스크에 저장된 데이터를 다시 메모리로 읽어 처리하고, 다시 디스크에 저장하는 행위를 반복한다.

 

 

여러  조각으로 나누어진 데이터를 정렬을 위해서 디스크에 쓰고 읽고를 반복하는 것을 Multi-Merge 라고 하는데,  Multi-Merge 횟수는  sort_merge_passes 라는 변수에 누적되기 때문에 모니터링이 가능하다. Multi-Merge 발생하게되면 디스크를 사용하는데, 디스크의 성능은 메모리보다 느리기 때문에 실제 쿼리 요청이 느려질 있다. 이러한 디스크 사용을 줄이기 위해 sort_buffer_size 증가 시킬수도 있지만, 특정 크기 이상 부터는 이상의 성능 향상을 기대하기 어렵다. 아래 그림의 테스트 자료를 보면 1MB 부터는 성능 향상이 없는것을 확인할 있다.

출처 : https://www.percona.com/blog/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/

 

sort_buffer_size 영역은 세션 마다 할당되기 때문에 너무 sort_buffer_size 자칫하면 메모리 부족현상을 겪을 있다. 또한 메모리가 부족할시 OS에서는 강제적으로 프로세스를 Kill 있으므로 MySQL/MariaDB 프로세스가 중지될 수도 있기 때문에 주의해야 한다.

 

 

2019-09-19 / 강성욱 / http://sungwookkang.com

 

MySQL, MariaDB, Sort Buffer, sort_buffer_size, sort_merge_passes, Multi-Merge

  1. 영화다시보기 2020.07.31 09:41

    잘 보고 갑니다~~

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, 정렬 메모리,



+ Recent posts