[MySQL] (환경설정3️⃣) 정렬 성능 향상: sort_buffer_size 설정법

MySQL에서 ORDER BY, GROUP BY, DISTINCT 같은 정렬 작업은 sort_buffer라는 메모리 공간에서 수행된다.
이 공간이 부족하면 디스크에 임시 파일을 만들게 되고, 실행 속도는 급격히 느려진다.

즉, sort_buffer_size는 정렬 성능의 병목을 해소하는 핵심 키다.

🧠 sort_buffer_size란?

• 클라이언트 연결마다 개별적으로 할당되는 정렬용 메모리 공간
• ORDER BY, GROUP BY, DISTINCT, UNION, SQL_SMALL_RESULT 등의 연산 수행 시 사용
• 버퍼 크기가 작으면 Using temporary, Using filesort가 EXPLAIN에 자주 등장하게 된다

🔍 현재 설정값 확인

SHOW VARIABLES LIKE 'sort_buffer_size';

예시 결과:

sort_buffer_size	262144

= 약 256KB (기본값)

반응형

📏 적절한 설정값 가이드

환경	권장값
기본 테스트용	256KB ~ 512KB
정렬 쿼리 많은 환경	1MB ~ 2MB
대용량 정렬 쿼리 많은 환경	4MB ~ 8MB (주의 필요)

예시 설정 (my.cnf):

[mysqld]
sort_buffer_size = 2M

✅ 주의: 연결당 할당된다

• 이 값은 각 연결(Connection) 당 적용되므로 너무 크게 잡으면 RAM이 순식간에 소모된다
• 예: 100개 연결 시 2MB x 100 = 200MB

🔄 동적 조정 방법

SET GLOBAL sort_buffer_size = 1048576; -- 1MB

또는 현재 세션에만 적용:

SET SESSION sort_buffer_size = 1048576;

→ 영구 적용은 my.cnf에 설정 후 MySQL 재시작 필요

반응형

🔬 성능 영향 체크하기

✅ EXPLAIN 확인

EXPLAIN SELECT * FROM orders ORDER BY created_at;

• Extra 컬럼에 Using filesort가 나오면 정렬 성능 개선 대상

✅ 임시 파일 사용 여부 확인

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

또는

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

값이 계속 증가하면 정렬 시 디스크를 자주 사용하고 있다는 뜻
→ sort_buffer_size 확장 고려 필요

⚠️ 실무 튜닝 팁

• 정렬 쿼리가 빈번한 환경에서는 1MB 이상으로 설정 필요
• 서버 전체 연결 수 대비 총 메모리 사용량 계산 후 설정할 것
• RAM이 넉넉한 환경이라면 2MB 정도로 늘리는 것이 보편적
• 너무 크게 설정하면 메모리 부족 현상 발생하므로 주의
• 정렬이 많은 쿼리는 인덱스를 적극 활용하거나 LIMIT을 함께 사용

✅ 정리

• sort_buffer_size는 정렬 성능 향상의 핵심 파라미터다.
• 기본값은 너무 작아서 실무 환경에서는 반드시 조정이 필요하다.
• 너무 크게 설정하면 메모리 낭비가 심하므로 적절한 밸런스 유지가 중요하다.
• EXPLAIN과 STATUS 명령어로 튜닝 효과를 정기적으로 확인하는 것이 좋다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual – Server System Variables: sort_buffer_size