MySQL JSON 정렬의 Out of sort memory — 버퍼를 늘리면 절벽이 옮겨갈 뿐입니다

MySQL 에서 JSON 컬럼이 든 행을 ORDER BY 하다 ERROR 1038, Out of sort memory 를 만나면 메시지는 sort_buffer_size 를 늘리라고 권합니다. 저도 그러면 끝나는 줄 알았습니다. 재현해 보니 버퍼를 올리자 통과는 했지만 여유가 없었고, 정렬 자체를 없애는 인덱스는 만들어 두어도 옵티마이저가 고르지 않았습니다.

재현 — 256KB 버퍼에서 1.4MB JSON 정렬이 ERROR 1038 로 멈췄습니다

출력은 모두 공식 Docker 이미지로 띄운 로컬 MySQL 8.4.11 에서 얻었고, 이 환경의 sort_buffer_size 는 262144(256KB)였습니다. JSON 컬럼 body 를 가진 테이블에 작은 행 300개는 owner_id 2~21 로, 1.4MB 짜리 JSON 12행은 owner_id = 1 로 넣었습니다.

CREATE TABLE documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  body JSON NOT NULL,
  KEY idx_owner_title (owner_id, title)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO documents (owner_id, title, body)
  WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 300)
  SELECT 2 + (n % 20), CONCAT('note-', n), JSON_OBJECT('n', n, 'text', 'small') FROM seq;
INSERT INTO documents (owner_id, title, body)
  WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 12)
  SELECT 1, CONCAT('big-', n), JSON_OBJECT('n', n, 'text', REPEAT('x', 1400000)) FROM seq;
ANALYZE TABLE documents;

큰 행의 LENGTH(body) 는 최대 1,400,021바이트였습니다. 이 12행을 id 순으로 가져온 결과는 아래와 같습니다. 에러 줄은 표준 에러로 나가 출력 파일에서 제자리를 벗어난 것이 있어서, at line N 의 스크립트 줄 번호에 맞춰 문장 옆으로 옮겼습니다.

-- 20번째 줄
SELECT id, LENGTH(body) FROM documents WHERE owner_id = 1 ORDER BY id LIMIT 5
ERROR 1038 (HY001) at line 20: Out of sort memory, consider increasing server sort buffer size

-- 21번째 줄 (LIMIT 없음)
SELECT id, LENGTH(body) FROM documents WHERE owner_id = 1 ORDER BY id
ERROR 1038 (HY001) at line 21: Out of sort memory, consider increasing server sort buffer size

-- 22번째 줄 (body 없음)
SELECT id, title FROM documents WHERE owner_id = 1 ORDER BY id
12 rows in set (0.00 sec)

LIMIT 5 도 소용이 없었습니다. 실행 계획에 limit input to 5 row(s) per chunk 라는 문구가 붙을 뿐 정렬 단계는 그대로였습니다.

눈에 걸린 것은 세 번째 쿼리입니다. SELECT 목록에서 body 를 빼자 같은 정렬이 256KB 로도 통과했습니다. 달라진 것은 정렬하는 동안 무엇을 들고 있느냐뿐이었습니다.

메시지대로 sort_buffer_size 를 8MB 로 올리면 — 통과했지만 버퍼를 꽉 채웠습니다

그래도 먼저 메시지를 따라 세션의 버퍼를 8MB 로 올리고, 옵티마이저 트레이스를 켠 채 두 번째 쿼리를 다시 실행했습니다.

SET SESSION sort_buffer_size = 8388608
SET optimizer_trace = 'enabled=on'
SELECT id, LENGTH(body) FROM documents WHERE owner_id = 1 ORDER BY id
12 rows in set (0.03 sec)
SELECT JSON_EXTRACT(TRACE, '$**.filesort_summary') AS filesort_summary FROM information_schema.OPTIMIZER_TRACE
[{"key_size": 8, "row_size": 4294967295, "sort_mode": "<fixed_sort_key, packed_additional_fields>", "num_rows_found": 12, "sort_algorithm": "std::sort", "memory_available": 8388608, "peak_memory_used": 8388640, "num_rows_estimate": 15, "max_rows_per_buffer": 0, "num_initial_chunks_spilled_to_disk": 3}]

여기서 멈췄다면 해결이라고 적었을 것입니다. 그런데 매뉴얼이 «정렬 중 한순간에 쓴 최대 메모리»라고 설명하는 peak_memory_used 가 8388608바이트 버퍼에서 8388640 이었습니다. 버퍼를 끝까지 쓴 셈입니다.

num_initial_chunks_spilled_to_disk 의 3 은 정의를 찾지 못해 계산으로 읽었습니다. 1.4MB 행은 8MB 버퍼에 다섯 개까지만 들어가니 12행은 세 덩어리가 되고, 메모리에 다 들어가지 않으면 filesort 가 임시 디스크 파일을 쓴다는 매뉴얼 설명과도 맞습니다.

여기서 생각이 뒤집혔습니다. 버퍼를 올려 고친 것이 아니라 이번 데이터가 8MB 안에 들어왔을 뿐이었습니다.

정렬 버퍼에 한 행조차 담지 못하면 이 에러가 나니, 한 행이 8MB 버퍼에도 들어가지 않는 날 같은 에러가 돌아올 것입니다. 버퍼를 올리는 일은 절벽을 없애는 게 아니라 뒤로 미는 일에 가까웠습니다. 8MB 를 넘는 행으로 확인하지는 않았으니 이 대목은 추론입니다.

JSON 은 왜 정렬 버퍼에 실리나 — MySQL 8.0.20 의 packed addon

filesort 가 정렬 버퍼에 담는 튜플은 두 모양입니다. <sort_key, rowid> 는 정렬 키와 row ID 만 담았다가 정렬 뒤에 행을 다시 읽고, <sort_key, additional_fields> 는 쿼리가 참조하는 컬럼 값까지 실어 두었다가 튜플에서 바로 꺼냅니다. packed_additional_fields 는 그 값을 고정 길이 대신 빽빽하게 채운 변형입니다.

MySQL 8.0.20 부터 JSON 과 GEOMETRY 가, 내부적으로 LONGBLOB 계열인데도 packed addon 으로 실립니다. TINYBLOB·BLOB(TINYTEXT·TEXT)은 그 전부터 addon 이었고, MEDIUMBLOB·LONGBLOB(MEDIUMTEXT·LONGTEXT)은 지금도 row ID 정렬로 빠집니다. 릴리스 노트는 부작용까지 적어 두었습니다.

One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large … JSON or GEOMETRY column values if the sort buffers are of insufficient size

정렬 버퍼에 한 행도 담지 못할 때 나는 것이 Out of sort memory, 곧 ER_OUT_OF_SORTMEMORY(1038, SQLSTATE HY001)입니다.

세 번째 쿼리가 통과한 이유도 여기서 풀립니다. addon 방식에서는 LENGTH(body) 처럼 표현식 안에서만 쓰인 컬럼도 참조된 컬럼이라 원본이 튜플에 실릴 수 있습니다. 숫자 하나가 필요했을 뿐인데 1.4MB 를 들고 정렬한 셈입니다. 정렬 앞에 임시 테이블을 거치는 계획이면 구성이 달라질 수 있다는 점은 남겨 둡니다.

확인 삼아 body 만 LONGTEXT 로 바꾼 사본에 같은 312행을 복사하고, 인덱스 구성을 처음과 같게 맞춘 뒤 256KB 로 같은 쿼리를 돌렸습니다. 12행이 그대로 돌아왔습니다.

항목	body JSON (트레이스는 8MB)	body LONGTEXT (256KB)
256KB 에서 같은 쿼리	ERROR 1038	12행 반환
sort_mode	<fixed_sort_key, packed_additional_fields>	<fixed_sort_key, rowid>
peak_memory_used	8388640	32896
num_initial_chunks_spilled_to_disk	3	0
unpacked_addon_fields	(항목 없음)	row_contains_blob

LONGTEXT 쪽은 정렬 키와 row ID 만 올려 32896바이트로 끝났고, row_contains_blob 은 BLOB 계열 컬럼 때문에 addon 을 쓰지 않았다는 표시로 읽힙니다. 같은 1.4MB 도 타입에 따라 정렬 버퍼에 실리기도 하고 실리지 않기도 합니다. 다만 이 비교는 해법이라기보다 원인을 드러내는 대조입니다.

정렬을 없애는 인덱스를 만들었지만 옵티마이저는 고르지 않았습니다

그렇다면 정렬 자체를 없애면 되겠다고 생각했습니다. WHERE owner_id = 1 ORDER BY id 는 (owner_id, id) 인덱스를 따라 읽기만 해도 id 순서이니, idx_owner_id (owner_id, id) 를 추가하고 ANALYZE TABLE 까지 돌렸습니다. 이번에야말로 끝났다고 생각했습니다.

쿼리는 모두 SELECT id, LENGTH(body) FROM documents WHERE owner_id = 1 ORDER BY id 이고, 힌트는 FROM documents 뒤에 붙였습니다. 힌트에 따른 실행 계획과 결과입니다.

-- 31번째 줄 · 힌트 없음 · EXPLAIN
-> Sort: documents.id  (cost=13.2 rows=12)
    -> Index lookup on documents using idx_owner_title (owner_id=1)  (cost=13.2 rows=12)
-- 32번째 줄 · 실행
ERROR 1038 (HY001) at line 32: Out of sort memory, consider increasing server sort buffer size

-- 33번째 줄 · FORCE INDEX (idx_owner_id) · EXPLAIN
-> Index lookup on documents using idx_owner_id (owner_id=1)  (cost=15.4 rows=14)
-- 34번째 줄 · 실행
12 rows in set (0.01 sec)

-- 35번째 줄 · FORCE INDEX FOR ORDER BY (idx_owner_id) · EXPLAIN
-> Index lookup on documents using idx_owner_id (owner_id=1)  (cost=13.2 rows=12)

인덱스가 있는데도 옵티마이저는 idx_owner_title 로 찾고 정렬하는 계획을 골랐고, 같은 1038 이 났습니다. 추정 비용은 기존 경로 13.2, FORCE INDEX 경로 15.4, FOR ORDER BY 경로 13.2 로, 정렬을 없애는 쪽이 더 싸 보이지 않았습니다. 매뉴얼에도 인덱스가 쿼리가 접근하는 모든 컬럼을 담지 않으면 다른 접근 방법보다 쌀 때만 쓰인다고 적혀 있고, body 를 읽어야 하는 이 쿼리에서 (owner_id, id) 는 커버링 인덱스가 아니었습니다.

돌이켜 보면 틀린 곳은 «정렬을 없앨 수 있는 인덱스가 있으면 옵티마이저가 알아서 고를 것»이라는 제 기대였습니다. 고른 계획이 256KB 에 들어가지 않는다는 사실은 실행하고서야 드러났습니다. 이 선택은 통계와 데이터 분포에 따라 달라질 수 있으니, 말할 수 있는 것은 이 12행 조건에서는 고르지 않았다는 데까지입니다.

FORCE INDEX 로 정렬은 사라졌지만 — 인덱스 힌트가 약속하지 않는 것

힌트를 주자 그제야 Sort 가 사라지고, 같은 256KB 에서 12행이 돌아왔습니다. 다만 FORCE INDEX 가 인덱스 사용을 보장하지는 않습니다. 매뉴얼의 정의는 USE INDEX 처럼 동작하되 테이블 스캔을 매우 비싸다고 가정한다는 것이고, FOR 절이 없으면 문장 전체에 걸립니다. 정렬만 겨냥하려면 FORCE INDEX FOR ORDER BY 로 좁힐 수 있는데, 재현에서는 이 형태를 EXPLAIN 으로만 봤습니다.

매뉴얼은 USE INDEX·FORCE INDEX·IGNORE INDEX 가 앞으로 deprecated 될 것으로 예상하라고 하고, 대신할 힌트 중 ORDER_INDEX 를 FORCE INDEX FOR ORDER BY 와 같다고 설명합니다. 이번에는 써 보지 않았습니다. body 없이 id 만 정렬해 얻고 본문은 기본 키로 따로 가져오는 2단계 조회도 검토할 만한 방향이지만, 역시 실행해 보지는 않았습니다.

RDS 에서 sort_buffer_size 를 올린다는 것 — 파라미터 그룹의 영향 범위

RDS for MySQL 에서는 aws rds describe-engine-default-parameters --db-parameter-group-family mysql8.4 로 엔진 기본 파라미터를 볼 수 있습니다. sort_buffer_size 항목은 다음과 같았고, mysql8.0 도 같았습니다.

필드	값	읽은 뜻
ParameterValue	(응답에 없음)	따로 정한 값 없음
Source	engine-default	엔진 기본값
ApplyType	dynamic	재부팅 없이 적용될 수 있음
IsModifiable	true	변경 가능

비어 있는 값은 MySQL 기본값에 맡긴 것으로 읽었습니다. 기본 파라미터 그룹이 엔진 기본값과 RDS 시스템 기본값을 담는다는 문서 설명, 그리고 조회 예시에서 engine-default 항목의 값 칸이 비어 있다는 점이 근거입니다. 다만 그렇게 못박은 문장은 찾지 못했고, 실제 인스턴스에서 SELECT @@sort_buffer_size 로 확인하지도 않았습니다.

기본 그룹은 값을 바꿀 수 없어 커스텀 그룹을 만들어 연결해야 합니다. 이미 연결된 그룹에서 dynamic 파라미터를 바꾸면 재부팅 없이 적용되지만, 새로 연결한 그룹의 값은 dynamic 이어도 재부팅 뒤에 적용됩니다. 그리고 그룹의 값은 그 그룹에 연결된 모든 DB 인스턴스에 걸립니다. SET SESSION 으로 한 연결에만 올린 재현과 달리 인스턴스 설정을 바꾸는 일이라, 문서도 잘못 설정한 파라미터는 성능 저하나 불안정을 부를 수 있으니 테스트 인스턴스에서 먼저 시험하라고 권합니다.

남는 것 — 에러 메시지는 막힌 자리를 알려 줄 뿐입니다

메시지는 틀리지 않았고, 버퍼를 올리자 정말로 통과했습니다. 다만 메시지가 알려 준 것은 막힌 자리였지 막힌 이유가 아니었습니다. 이유는 정렬하는 동안 무엇을 들고 있느냐에 있었고, 그것을 8.0.20 의 변경과 SELECT 목록과 컬럼 타입이 함께 정하고 있었습니다. 메시지가 가리키는 손잡이는 가장 가까운 손잡이일 뿐 가장 맞는 손잡이는 아닐 수 있다는 생각이 들었습니다.

인덱스를 만든 것과 그 인덱스가 쓰이는 것도 다른 일이었고, 그 사이를 보여 준 것은 EXPLAIN 한 번이었습니다. 통과한 쿼리도 마찬가지입니다. 12행이 돌아왔다는 사실만 보고 넘어갔다면 버퍼를 꽉 채운 트레이스는 보지 못했을 것입니다. 통과가 곧 여유는 아니었습니다.

확인하지 못한 것도 적어 둡니다. 8MB 를 넘는 한 행으로 실패를 재현하지 않았고, FORCE INDEX FOR ORDER BY 와 2단계 조회는 실행하지 않았으며, RDS 인스턴스의 실제 값도 조회하지 않았습니다. 모두 로컬 MySQL 8.4.11 한 대, 312행짜리 테이블에서 본 것입니다.

참고 문서

• MySQL 8.0.20 릴리스 노트
• MySQL 8.4 ORDER BY Optimization
• MySQL 8.4 Index Hints
• MySQL 8.4 Optimizer Hints
• RDS 파라미터 그룹 개요
• RDS 파라미터 그룹 수정
• RDS 파라미터 값 조회
• AWS CLI describe-engine-default-parameters