요약
range_optimizer_max_mem_size는 옵티마이저가 range 접근 방법을 검토할 때 쓰는 메모리 상한이다 (기본 8MB).- 이 상한을 넘으면 range 접근을 버리고 풀 테이블 스캔 등 다른 방법으로 빠지며, 인덱스가 있어도 안 탄다.
- OR·IN 리스트가 아주 많은 쿼리에서 이 한도를 넘어 갑자기 느려질 수 있다.
본문
무엇을 제한하나
옵티마이저가 쿼리 계획을 세울 때 range 접근 방법(range access method)을 고려하면서 소비하는 메모리의 상한이다. 단위는 바이트, 기본값은 8388608(8MB). MySQL 5.7.12부터 8.0까지 동일하다.
상한을 넘으면 값이 0보다 클 때 옵티마이저는 range 최적화 중 소비 메모리를 추적한다. 한도를 넘길 것 같으면 range 접근을 포기하고 풀 테이블 스캔을 포함한 다른 방법을 대신 고른다. 즉 쓸 만한 인덱스가 있어도 안 타고 풀 스캔으로 빠진다. 이때 경고가 뜬다:
Warning 3170 Memory capacity of N bytes for
'range_optimizer_max_mem_size' exceeded. Range
optimization was not done for this query.
UPDATE/DELETE에서 sql_safe_updates가 켜져 있으면 경고가 아니라 에러가 난다. 어느 행을 바꿀지 정할 키가 없기 때문이다.
값 0의 의미
0은 무제한을 뜻한다. 상한을 두지 않고 range 최적화에 메모리를 자유롭게 쓴다.
언제 넘나 — OR·IN이 많은 쿼리 술어(predicate) 하나가 대략 이만큼을 먹는다:
- 단순 OR 술어: 약 230바이트/개
- AND 술어: 약 125바이트/개
IN()리스트: 원소 하나가 OR 술어 하나로 계산. 여러IN()리스트가 곱해지면 M × N 바이트로 폭증
그래서 OR·IN 항목이 수천 개인 쿼리에서 8MB를 넘기고, 인덱스가 있는데도 풀 스캔이 되어 성능이 급락하는 함정이 생긴다. 진단은 실행 계획이 인덱스를 안 탈 때 warning 3170 유무를 확인하는 것.
적용 사례 — 추천 쿼리의 거대 NOT IN 리스트
신규 유저 추천 슬로우 쿼리에서 실제로 밟은 함정이다. 쿼리 형태는 이랬다:
- 유저 테이블 INNER JOIN + 여러 필터(승인 상태·성별·생년월일 범위 등)
- 이미 스와이프한 유저를 빼는 거대한
NOT (id IN (...))제외 목록 — 원소 수백~수천 개 - 스와이프 카운트 서브쿼리로
ORDER BY후LIMIT 1
이 거대 NOT IN 리스트 하나가 두 옵티마이저 한도를 동시에 건드렸다:
eq_range_index_dive_limit(기본 200): IN 원소가 이 수를 넘으면 옵티마이저가 index dive를 포기하고 인덱스 통계로 어림 → 나쁜 plan.range_optimizer_max_mem_size(기본 8MB): 같은 리스트가 range 플래닝 메모리 한도를 넘겨 Warning 3170 → range 최적화 포기 → 풀 스캔.
조치: RDS 파라미터 그룹에서 두 값을 올렸다.
| 파라미터 | 기본값 | 상향 |
|---|---|---|
eq_range_index_dive_limit |
200 | 1000 |
range_optimizer_max_mem_size |
8388608 (8MB) | 50331648 (48MB) |
파라미터 그룹 변경은 새로 맺는 커넥션에만 반영되므로(커넥션 풀 recycle을 기다려야 함), 적용 후 슬로우로그 추이와 RDS CPU 부작용을 함께 모니터링해야 완결이다. 근본 해결은 거대 IN 리스트 자체를 조인·임시테이블로 바꾸는 것이고, 파라미터 상향은 그 전까지의 완화책이다.
관련 노트
- IN 리스트가 eq_range_index_dive_limit를 넘으면 옵티마이저가 index dive를 포기해 잘못된 plan을 고른다
- Range 쿼리 종류
- mysql 풀 테이블 스캔
- OR 연산은 인덱스의 연속 스캔을 방해해 비효율을 유발한다.