MySQL sort_buffer_size 변화에 따른 정렬 성능
컨텐츠 정보
- 1,359 조회
본문
MySQL에서 인덱스 없이 ORDER BY, GROUP BY 처리를 하기 위해서는 filesort 알고리즘을 사용해야 한다.
* filesort 알고리즘 : http://seuis398.blog.me/70039224614
filesort 처리를 하는 경우 sort_buffer_size 파라미터로 지정한 단위별로 먼저 정렬을 수행하고 그 결과를 다시 합치게 되므로, sort_buffer_size 파라미터가 쿼리 속도를 결정하는 중요한 기준점이 된다.
하지만 최근 MySQL 5.5 => MySQL 5.6 버전으로 버전 업그레이드가 되면서 sort_buffer_size 파라미터의 기본값이 2MB에서 256KB로 변경이 되었다.
최근 시스템 성능이 좋아지면서 대부분의 파라미터들이 조금씩 늘어나는 것에 반해, 왜 sort_buffer_size는 기본 설정이 줄어든 것일까?
결론부터 얘기하자면, sort_buffer_size가 커봐야 별 이득이 없기 때문이다.
간단하게 아래와 같은 시나리오로 MySQL 5.5 버전에서 sort_buffer_size 변화에 따른 filesort 쿼리 성능을 측정해 보았다.
테스트에 사용한 테이블은 평균 레코드 사이즈가 66byte, 데이터 건수는 대략 6천만건이 저장된 테이블이며, 각각 6천만건, 10만건을 물리 정렬하는 쿼리를 사용하였다.
[query 1] select * from tb_filesort_test order by col1 desc
[query 2] select * from tb_filesort_test where PK < 100000 order by col2 asc
|
sort_buffer_size |
256KB |
1MB |
2MB |
4MB |
8MB |
16MB |
|
query 1 |
38.17 초 |
39.39 초 |
39.94 초 |
46.97 초 |
52.72 초 |
57.55 초 |
|
query 2 |
0.06 초 |
0.06 초 |
0.06 초 |
0.08 초 |
0.08 초 |
0.09 초 |
MySQL 메뉴얼 상에서는 Linux 환경에서 sort_buffer_size에 특정 임계치 이상의 메모리 할당 요청시 성능이 떨어질 수 있다는 경고가 존재한다.
메뉴얼에서 언급된 임계치는 아마도 메모리 동적 할당/반환시 malloc 내부의 풀에서 메모리를 할당하지 않고 nmap 시스템콜이 사용이 되거나, 메모리 반환시 sbrk 시스템콜이 사용되는 등의 임계치를 언급한 것으로 보인다. 하지만 간단하게 256KB ~ 16MB 단위로 수억회 malloc / free를 반복 호출해 봤을 떄 메모리 블럭 사이즈에 따른 성능 차이는 거의 존재하지 않았다.
|
On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. |
또한 MySQL이 정렬시 사용하는 quick sort의 알고리즘 복잡도는 평균 Θ(n log n) 정도로, 정렬 단위가 커짐에 따라 정렬 부하는 기하급수적으로(exponentially) 늘어나게 된다. 혹시 이 부분이 위와 같은 성능 저하를 일으킨 원인은 아닐까 의심도 해 보았지만, 위와 같은 sort_buffer_size 변화에 따른 성능 저하 현상은 MySQL 5.5 버전까지만 재현이 되고, MySQL 5.6 버전애서는 재현이 되지 않았다.
명확한 원인은 알 수 없지만, 결과적으로 볼 때 sort_buffer_size 사이즈를 키워도 물리적인 정렬 성능에는 별로 도움이 되지 않는다.
그렇기 때문에 그냥 MySQL의 default 사이즈로 사용해도 크게 문제가 없을 것으로 보인다.
관련자료
-
이전
-
다음
