MySQL Filesort 진단: sort merge pass와 sort buffer 해석
MySQL Filesort의 메모리 정렬과 디스크 병합 경로를 이해하고 Sort_merge_passes와 sort_buffer_size를 운영 관점에서 해석한다.
EXPLAIN의 Using filesort는 쿼리가 실패했다는 뜻도, 반드시 디스크에 임시 파일을 썼다는 뜻도 아니다. MySQL이 원하는 행 순서를 인덱스 순서만으로 만들지 못해 별도의 정렬 단계를 수행한다는 실행 계획 신호다. 이 정렬은 메모리 안에서 끝날 수도 있고, 정렬 결과를 여러 run으로 나눈 뒤 디스크를 거쳐 병합할 수도 있다.
운영자는 Using filesort 한 문구만 보고 sort_buffer_size를 크게 올리기보다 다음 질문을 순서대로 답해야 한다.
- 어떤 쿼리와 어떤
ORDER BY또는GROUP BY가 정렬을 만들었는가? - 읽은 행과 실제 반환 행 사이의 차이가 큰가?
- 정렬이 메모리에서 끝났는가, merge pass가 발생했는가?
- 인덱스로 정렬 자체를 제거할 수 있는가?
- 버퍼를 늘릴 때 동시 접속 수와 전체 메모리 위험은 감당할 수 있는가?
이 글은 Filesort의 실행 경로와 Sort_merge_passes, Sort_rows, sort_buffer_size의 관계를 설명하고, 재현 가능한 MySQL 8.0 예제로 진단 절차를 정리한다.
1. Filesort는 무엇을 의미하는가
MySQL Optimizer는 정렬된 결과를 만드는 방법을 크게 두 가지로 비교한다.
- 인덱스 순서 읽기: B+Tree의 키 순서를 그대로 따라가며 행을 반환한다.
- Filesort: 후보 행을 읽은 뒤 정렬 키를 별도 작업 공간에서 정렬한다.
이름에 file이 들어가지만 Filesort가 항상 디스크 I/O를 발생시키는 것은 아니다. 정렬 대상이 sort_buffer_size 안에서 처리되면 메모리 정렬로 끝날 수 있다. 버퍼에 담기 어려우면 MySQL은 정렬된 run을 만들고, 필요에 따라 여러 run을 병합해 최종 순서를 만든다. Sort_merge_passes는 이 외부 정렬 경로를 의심하게 하는 핵심 누적 지표다.
flowchart TD
A[후보 행 읽기] --> B{인덱스 순서가 ORDER BY를 충족하는가}
B -->|예| C[인덱스 순서로 반환]
B -->|아니오| D[Filesort용 정렬 레코드 구성]
D --> E{정렬 작업이 메모리에서 끝나는가}
E -->|예| F[메모리 정렬 후 반환]
E -->|아니오| G[정렬된 run 생성]
G --> H[run 병합]
H --> I[최종 순서로 반환]
Filesort 여부와 임시 테이블 여부도 구분해야 한다. Using temporary와 Using filesort가 함께 나타날 수 있지만 서로 같은 작업은 아니다. 전자는 중간 결과를 담는 임시 테이블이 필요하다는 신호이고, 후자는 행 순서를 만들기 위한 별도 정렬 단계가 있다는 신호다.
2. 내부 실행 경로: 수집, 정렬, 병합
2.1 후보 행과 정렬 레코드 수집
Executor는 접근 경로에 따라 테이블 또는 인덱스에서 후보 행을 읽고 WHERE 조건을 적용한다. 정렬에 필요한 키와 결과를 식별할 정보를 Filesort 작업 공간에 기록한다. 정렬 레코드의 폭이 커질수록 같은 버퍼에 들어가는 행 수는 줄어든다.
정렬 비용은 단순히 반환 행 수로 결정되지 않는다. 다음 쿼리가 20행만 반환하더라도, 적절한 인덱스가 없다면 수십만 행을 읽고 정렬 후보로 만든 뒤 상위 20행을 골라야 할 수 있다.
SELECT id, created_at
FROM orders
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
LIMIT이 작으면 MySQL이 priority queue 기반 Top-N 최적화를 선택할 수 있다. 이 경우 모든 후보를 완전히 정렬하지 않고 필요한 상위 N개를 유지할 수 있다. 따라서 Using filesort가 보여도 비용은 정렬 후보 수, 정렬 레코드 폭, LIMIT 크기와 실행 전략에 따라 크게 달라진다.
2.2 메모리 정렬
sort_buffer_size는 정렬이 필요한 세션에 할당되는 세션 단위 작업 버퍼다. 전역값을 크게 지정하면 모든 연결이 서버 시작 시 그만큼을 즉시 소비하는 구조는 아니지만, 여러 세션이 동시에 큰 정렬을 수행하면 총 메모리 사용량이 빠르게 증가할 수 있다.
MySQL 8.0 계열은 정렬 버퍼를 필요한 만큼 점진적으로 할당하는 방향으로 동작하지만, 운영 용량 계획에서는 여전히 다음 근사식을 고려해야 한다.
동시 정렬 메모리 위험 ≈ 동시 Filesort 수 × 세션별 sort_buffer_size
+ join/read/tmp 등 다른 세션 버퍼
+ Buffer Pool과 서버 고정 메모리
그러므로 sort_buffer_size는 Buffer Pool처럼 “크면 대체로 좋다”는 전역 캐시가 아니다. 특정 쿼리의 정렬 spill을 줄일 수 있는 대신 동시성 높은 서버의 메모리 여유를 줄이는 교환 관계가 있다.
2.3 run 생성과 merge pass
정렬 레코드가 메모리에 충분히 들어가지 않으면 MySQL은 정렬 가능한 단위로 run을 만들고 이를 병합한다. run 수가 많거나 병합 fan-in으로 한 번에 합치지 못하면 추가 merge pass가 발생할 수 있다.
Sort_merge_passes가 증가했다는 사실은 Filesort 과정에 병합 작업이 있었다는 강한 신호다. 다만 이 값만으로 디스크에 쓴 정확한 바이트 수, 특정 SQL, 응답시간 기여도를 알 수는 없다. 누적 상태값이므로 반드시 관측 구간과 쿼리를 함께 좁혀야 한다.
3. 정렬 관련 변수와 상태값 읽기
다음 쿼리는 현재 세션의 정렬 버퍼 설정과 세션 누적 정렬 상태를 함께 확인한다. 세션 상태를 사용하면 다른 연결의 작업이 섞이는 것을 피할 수 있다.
SELECT @@session.sort_buffer_size AS sort_buffer_bytes,
@@session.max_sort_length AS max_sort_length_bytes;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.session_status
WHERE VARIABLE_NAME IN (
'Sort_merge_passes',
'Sort_range',
'Sort_rows',
'Sort_scan'
)
ORDER BY VARIABLE_NAME;
실행 결과(MySQL 8.0.x):
mysql> SELECT @@session.sort_buffer_size AS sort_buffer_bytes,
-> @@session.max_sort_length AS max_sort_length_bytes;
+-------------------+-----------------------+
| sort_buffer_bytes | max_sort_length_bytes |
+-------------------+-----------------------+
| 262144 | 1024 |
+-------------------+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
-> FROM performance_schema.session_status
-> WHERE VARIABLE_NAME IN (
-> 'Sort_merge_passes',
-> 'Sort_range',
-> 'Sort_rows',
-> 'Sort_scan'
-> )
-> ORDER BY VARIABLE_NAME;
+-------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------+----------------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+----------------+
4 rows in set (0.01 sec)
주요 상태값의 해석은 다음과 같다.
| 상태값 | 의미 | 운영 해석 |
|---|---|---|
Sort_scan |
테이블 스캔 계열 접근 후 수행한 정렬 횟수 | 인덱스 없이 넓게 읽고 정렬하는 쿼리를 의심한다. |
Sort_range |
range 접근 후 수행한 정렬 횟수 | 필터 인덱스는 사용하지만 정렬 순서는 충족하지 못할 수 있다. |
Sort_rows |
정렬한 행의 누적 수 | 정렬 횟수와 함께 보아 회당 규모를 추정한다. |
Sort_merge_passes |
정렬 run 병합 횟수 | 증가하면 메모리 내 정렬을 넘은 외부 병합 경로를 조사한다. |
Sort_rows / (Sort_scan + Sort_range) 같은 비율은 대략적인 회당 정렬 규모를 보는 보조값일 뿐이다. 서로 다른 쿼리와 동시 세션이 섞인 전역 누적값에서는 평균이 실제 병목 쿼리를 숨길 수 있다. 재현 세션, Performance Schema statement digest, 슬로우 로그를 함께 사용해야 한다.
4. 재현 1: 인덱스가 정렬을 제거하는 조건
다음 예제는 status만 인덱싱했을 때와 (status, created_at, id) 복합 인덱스를 사용했을 때의 계획을 비교한다. 첫 번째 계획은 필터에는 인덱스를 쓰지만 created_at 순서를 제공하지 못해 Filesort가 필요하다.
SET SESSION cte_max_recursion_depth = 25000;
DROP TABLE IF EXISTS tech_filesort;
CREATE TABLE tech_filesort (
id BIGINT NOT NULL PRIMARY KEY,
status ENUM('PENDING', 'DONE', 'FAILED') NOT NULL,
created_at DATETIME NOT NULL,
sort_key VARCHAR(192) NOT NULL,
INDEX idx_status (status)
) ENGINE=InnoDB;
INSERT INTO tech_filesort (id, status, created_at, sort_key)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 20000
)
SELECT n,
ELT(MOD(n, 3) + 1, 'PENDING', 'DONE', 'FAILED'),
TIMESTAMP('2026-01-01 00:00:00') + INTERVAL MOD(n * 37, 500000) SECOND,
CONCAT(
SHA2(CONCAT('a-', n), 256),
SHA2(CONCAT('b-', n), 256),
SHA2(CONCAT('c-', n), 256)
)
FROM seq;
ANALYZE TABLE tech_filesort;
EXPLAIN
SELECT id, created_at
FROM tech_filesort FORCE INDEX (idx_status)
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
실행 결과(MySQL 8.0.x):
준비 DDL/DML과 첫 번째 실행 계획에서 핵심 출력만 발췌했다.
mysql> CREATE TABLE tech_filesort (..., INDEX idx_status (status)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tech_filesort ...
Query OK, 20000 rows affected (0.14 sec)
Records: 20000 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE tech_filesort;
+-------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------------+---------+----------+----------+
| mysql_tech_note.tech_filesort | analyze | status | OK |
+-------------------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> EXPLAIN
-> SELECT id, created_at
-> FROM tech_filesort FORCE INDEX (idx_status)
-> WHERE status = 'PENDING'
-> ORDER BY created_at DESC
-> LIMIT 20;
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | tech_filesort | NULL | ref | idx_status | idx_status | 1 | const | 6666 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+---------------------------------------+
1 row in set (0.00 sec)
FORCE INDEX는 두 접근 경로의 차이를 안정적으로 보여주기 위한 교육용 장치다. 실제 운영에서는 강제 힌트부터 넣지 말고 통계와 비용 모델이 선택한 계획을 먼저 확인해야 한다.
이제 WHERE 절의 동등 조건 컬럼 뒤에 정렬 컬럼을 배치한 복합 인덱스를 추가한다. id까지 포함했기 때문에 예제 SELECT는 인덱스만으로 필요한 값을 얻을 수 있다.
ALTER TABLE tech_filesort
ADD INDEX idx_status_created (status, created_at DESC, id);
EXPLAIN
SELECT id, created_at
FROM tech_filesort FORCE INDEX (idx_status_created)
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
실행 결과(MySQL 8.0.x):
mysql> ALTER TABLE tech_filesort
-> ADD INDEX idx_status_created (status, created_at DESC, id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN
-> SELECT id, created_at
-> FROM tech_filesort FORCE INDEX (idx_status_created)
-> WHERE status = 'PENDING'
-> ORDER BY created_at DESC
-> LIMIT 20;
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | tech_filesort | NULL | ref | idx_status_created | idx_status_created | 1 | const | 6600 | 100.00 | Using where; Using index |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
1 row in set (0.00 sec)
인덱스가 Filesort를 제거하려면 단순히 ORDER BY 컬럼이 인덱스 어딘가에 존재하는 것으로 충분하지 않다. 선행 키 컬럼에 대한 조건, 정렬 방향, 여러 ORDER BY 컬럼의 순서, 범위 조건이 시작되는 위치가 모두 맞아야 한다. 또한 정렬 제거를 위해 지나치게 넓은 인덱스를 만들면 쓰기 비용과 Buffer Pool 점유가 증가하므로 읽기 이득과 함께 평가해야 한다.
5. 재현 2: 작은 sort buffer에서 merge pass 관측하기
다음 예제는 앞에서 만든 20,000행의 넓은 sort_key를 작은 세션 정렬 버퍼로 정렬한다. 전역 설정을 바꾸지 않고 현재 검증 세션에만 32 KiB를 적용한다. 파생 테이블의 큰 LIMIT은 전체 정렬 결과를 구체화하도록 두었고, 바깥 쿼리는 대량 행을 화면에 출력하지 않고 행 수와 checksum만 확인한다.
SET SESSION sort_buffer_size = 32768;
SET @passes_before := (
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Sort_merge_passes'
);
SET @rows_before := (
SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Sort_rows'
);
SELECT COUNT(*) AS sorted_rows,
SUM(id) AS id_checksum
FROM (
SELECT id
FROM tech_filesort
ORDER BY sort_key, id
LIMIT 20000
) AS ordered_rows;
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED) - @passes_before AS merge_passes_delta
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Sort_merge_passes';
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED) - @rows_before AS sort_rows_delta
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Sort_rows';
실행 결과(MySQL 8.0.x):
mysql> SET SESSION sort_buffer_size = 32768;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @passes_before := (
-> SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
-> FROM performance_schema.session_status
-> WHERE VARIABLE_NAME = 'Sort_merge_passes'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SET @rows_before := (
-> SELECT CAST(VARIABLE_VALUE AS UNSIGNED)
-> FROM performance_schema.session_status
-> WHERE VARIABLE_NAME = 'Sort_rows'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) AS sorted_rows,
-> SUM(id) AS id_checksum
-> FROM (
-> SELECT id
-> FROM tech_filesort
-> ORDER BY sort_key, id
-> LIMIT 20000
-> ) AS ordered_rows;
+-------------+-------------+
| sorted_rows | id_checksum |
+-------------+-------------+
| 20000 | 200010000 |
+-------------+-------------+
1 row in set (0.04 sec)
mysql> SELECT
-> CAST(VARIABLE_VALUE AS UNSIGNED) - @passes_before AS merge_passes_delta
-> FROM performance_schema.session_status
-> WHERE VARIABLE_NAME = 'Sort_merge_passes';
+--------------------+
| merge_passes_delta |
+--------------------+
| 63 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT
-> CAST(VARIABLE_VALUE AS UNSIGNED) - @rows_before AS sort_rows_delta
-> FROM performance_schema.session_status
-> WHERE VARIABLE_NAME = 'Sort_rows';
+-----------------+
| sort_rows_delta |
+-----------------+
| 20000 |
+-----------------+
1 row in set (0.00 sec)
여기서 중요한 것은 특정 merge pass 숫자를 모든 서버의 기준값으로 외우는 것이 아니다. 값은 MySQL minor version, 정렬 레코드 구성, 데이터 분포, LIMIT 최적화, 버퍼 크기에 따라 달라진다. 같은 쿼리를 같은 세션에서 실행하면서 버퍼 또는 인덱스 변경 전후의 증가량과 응답시간을 비교해야 한다.
재현을 마친 뒤 테스트 객체와 세션 설정을 정리한다.
SET SESSION sort_buffer_size = DEFAULT;
DROP TABLE tech_filesort;
실행 결과(MySQL 8.0.x):
mysql> SET SESSION sort_buffer_size = DEFAULT;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE tech_filesort;
Query OK, 0 rows affected (0.00 sec)
6. 실무 진단 절차
6.1 1단계: 누적값의 증가율 확인
서버 전체 상태는 순간값 한 번보다 시간 구간의 증가율이 중요하다. 다음 쿼리는 MySQL 8.0의 performance_schema.global_status에서 정렬 관련 누적값을 조회한다.
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
'Sort_merge_passes',
'Sort_range',
'Sort_rows',
'Sort_scan'
)
ORDER BY VARIABLE_NAME;
실행 결과(MySQL 8.0.x):
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
-> FROM performance_schema.global_status
-> WHERE VARIABLE_NAME IN (
-> 'Sort_merge_passes',
-> 'Sort_range',
-> 'Sort_rows',
-> 'Sort_scan'
-> )
-> ORDER BY VARIABLE_NAME;
+-------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------+----------------+
| Sort_merge_passes | 63 |
| Sort_range | 0 |
| Sort_rows | 20004 |
| Sort_scan | 2 |
+-------------------+----------------+
4 rows in set (0.00 sec)
모니터링 시스템에서는 1분 또는 5분 간격의 delta/rate를 저장한다. 재시작으로 counter가 초기화될 수 있으므로 단순 차감 시 음수가 생기지 않도록 처리한다. Sort_merge_passes가 늘어도 서버 부하와 응답시간이 안정적이면 즉시 튜닝할 이유는 없다. 반대로 증가율이 작아도 특정 온라인 요청의 tail latency를 크게 늘린다면 우선순위가 높다.
6.2 2단계: statement digest로 발생 SQL 좁히기
누적 status는 어떤 SQL이 정렬했는지 알려주지 않는다. Performance Schema의 statement summary, 슬로우 쿼리 로그, 애플리케이션 trace를 이용해 ORDER BY, GROUP BY, DISTINCT, window function이 포함된 고비용 SQL을 좁힌다.
statement summary에서는 다음 항목을 함께 본다.
- 실행 횟수와 총/평균/최대 지연시간
- 검사 행 수와 반환 행 수
- 디스크 임시 테이블 사용 여부
- 정렬 행 수와 정렬 관련 집계값
- digest가 여러 리터럴 실행을 합친 결과라는 점
Performance Schema consumer와 instrument 설정, minor version에 따라 수집 열과 보존 범위가 달라질 수 있으므로 실제 서버의 DESCRIBE performance_schema.events_statements_summary_by_digest 결과를 먼저 확인하는 것이 안전하다.
6.3 3단계: 실제 계획과 cardinality 확인
후보 SQL에는 EXPLAIN만 보지 말고, 안전한 검증 환경에서 EXPLAIN ANALYZE를 사용해 실제 읽은 행과 소요시간을 비교한다. 운영의 변경 쿼리에 무심코 EXPLAIN ANALYZE를 실행해서는 안 되며, SELECT라도 큰 부하를 만들 수 있으므로 복제본이나 스테이징에서 먼저 검증한다.
다음 관계를 중점적으로 본다.
- WHERE로 좁히기 전에 읽는 행이 지나치게 많은가?
- 반환 행은 적지만 정렬 후보 행은 매우 많은가?
- LIMIT가 작아 Top-N 최적화 여지가 있는가?
- 복합 인덱스가 필터와 정렬 순서를 동시에 충족하는가?
- 인덱스 추가 후 쓰기 비용과 인덱스 크기는 감당 가능한가?
6.4 4단계: 세션 단위 실험 후 변경
인덱스로 정렬을 제거하기 어렵고 merge pass가 실제 병목으로 확인됐을 때만 sort_buffer_size 실험을 고려한다. 먼저 해당 배치 세션이나 제한된 연결 풀에서 세션값을 변경해 전후를 비교한다.
SET SESSION sort_buffer_size = 1048576;
위 문장은 운영 환경에 맞게 조정해야 하는 설정 예시이므로 검증 SQL fence가 아닌 텍스트로 제시했다. 값 하나를 복사해 전역 적용하는 방식은 피해야 한다. 충분한 동시성 부하 시험 없이 전역값을 수십 MiB로 올리면 메모리 압박, swap, OOM 위험을 키울 수 있다.
7. 흔한 오해와 실패 패턴
7.1 Using filesort는 무조건 제거해야 한다
정렬 대상이 작고 응답시간이 짧다면 Filesort가 인덱스 추가보다 저렴할 수 있다. 정렬을 없애려고 넓은 복합 인덱스를 여러 개 만들면 DML 지연, redo 증가, 페이지 분할, Buffer Pool 경쟁이 더 큰 문제가 될 수 있다.
7.2 Sort_merge_passes가 0이 아니면 sort buffer가 부족하다
누적값이 0보다 큰 사실만으로 현재 병목을 판단할 수 없다. 서버 가동 기간이 길수록 과거 배치 작업의 흔적이 남는다. 시간 구간 delta, 쿼리 digest, latency와 함께 봐야 한다.
7.3 sort_buffer_size를 전역으로 크게 올리면 해결된다
이 버퍼는 정렬 작업을 수행하는 세션별로 사용된다. 동시 정렬 수가 많은 서버에서는 작은 상향도 큰 총량 변화가 될 수 있다. 정렬 레코드가 넓거나 데이터가 매우 크면 버퍼를 늘려도 merge가 완전히 사라지지 않을 수 있다.
7.4 반환 행이 적으므로 정렬 비용도 작다
LIMIT 10은 출력 행 수일 뿐이다. 인덱스가 필터와 순서를 지원하지 않으면 매우 많은 후보를 읽은 뒤 10행을 고를 수 있다. rows examined, 실제 iterator row 수, 정렬 후보 규모를 확인해야 한다.
7.5 Filesort와 디스크 임시 테이블을 같은 것으로 본다
두 작업은 원인과 튜닝 지점이 다르다. Using temporary는 중간 결과 materialization을, Using filesort는 정렬 단계를 가리킨다. 둘이 함께 나타나도 임시 테이블 변수만 조정해서 Filesort가 없어지는 것은 아니다.
8. Aurora MySQL에서의 운영 해석
Aurora MySQL도 MySQL 호환 Optimizer와 세션 정렬 버퍼 개념을 사용하므로 기본 진단 원리는 같다. 다만 운영에서는 다음 차이를 함께 고려한다.
- 인스턴스별 관측: writer와 reader는 각자의 CPU, 메모리, Performance Schema 상태를 가진다. 읽기 트래픽이 분산되면 한 인스턴스의 전역 status만으로 클러스터 전체를 판단할 수 없다.
- Parameter Group: 전역 기본값 변경은 DB parameter group과 적용 방식의 영향을 받는다. 재부팅 필요 여부와 적용 범위를 변경 전에 확인한다.
- 메모리 여유: 관리형 서비스라도 세션 버퍼의 동시 할당 위험은 사라지지 않는다. 인스턴스 클래스의 메모리, 연결 수, reader별 부하를 함께 본다.
- Performance Insights와 로그: 상위 SQL과 대기, 부하 분포를 이용해 정렬 status 증가 구간의 SQL을 좁힌다. 특정 지표 이름과 제공 범위는 Aurora MySQL 버전과 활성화 설정에 따라 확인해야 한다.
- 스토리지 구조 차이: Aurora의 분산 스토리지는 InnoDB 로컬 스토리지와 다르지만, Filesort를 무조건 저렴하게 만들지는 않는다. 정렬 작업의 CPU, 메모리, 임시 작업 공간 비용은 여전히 실행 인스턴스 관점에서 평가해야 한다.
Aurora에서도 첫 번째 해법은 무작정 큰 parameter를 적용하는 것이 아니라 SQL·인덱스·트래픽 배치로 정렬 작업량 자체를 줄이는 것이다.
9. 변경 판단 체크리스트
증상 확인
-
Sort_merge_passes
원인 SQL 식별
-
Using filesort와Using temporary
개선안 검토
배포 후 검증
-
Sort_rows와Sort_merge_passes
10. 정리
Filesort는 인덱스 순서로 결과를 만들 수 없을 때 사용하는 정상적인 정렬 경로다. 핵심은 Using filesort를 없애는 것 자체가 아니라, 정렬 후보 수와 레코드 폭, merge pass, 응답시간, 동시성을 함께 이해하는 데 있다.
Sort_merge_passes 증가는 외부 병합 정렬을 조사할 출발점이지만 단독 결론은 아니다. 세션 또는 짧은 관측 구간에서 증가량을 측정하고, digest와 실행 계획으로 SQL을 식별한 뒤, 먼저 인덱스와 쿼리 구조로 정렬 작업량을 줄인다. sort_buffer_size는 그 다음에 제한된 범위에서 검증할 보조 수단이다.
다음 정렬 관련 분석에서는 GROUP BY, DISTINCT, window function, 임시 테이블이 Filesort와 결합할 때 실행 계획과 메모리 사용이 어떻게 달라지는지 연결해서 살펴볼 수 있다.