메모리 초과 트러블슈팅 (Memory limit exceeded, 집계와 조인의 메모리)

증상

DB::Exception: Memory limit (for query) exceeded: would use 9.32 GiB
(attempt to allocate chunk of 4194304 bytes), current RSS 9.31 GiB, maximum: 9.31 GiB.
(MEMORY_LIMIT_EXCEEDED)

괄호 안이 어느 한도인지 알려 줍니다.

"would use 9.32 GiB" 가 쿼리가 쓰려던 양, "maximum: 9.31 GiB" 가 한도입니다. 둘이 거의 같으면 조금만 줄여도 되고, 한도의 몇 배라면 쿼리 구조를 바꿔야 합니다. 서버 전체 한도에 걸렸다면 내 쿼리가 아니라 옆 쿼리나 머지가 원인일 수도 있습니다. 한도 설정 자체는 서버 설정에 정리했습니다.

원인: 무엇이 메모리를 먹나

ClickHouse 는 스트리밍으로 처리하기 때문에 단순 필터와 투영은 데이터 크기와 무관하게 메모리를 조금만 씁니다. 메모리가 터지는 것은 거의 항상 "중간 상태를 전부 들고 있어야 하는" 연산입니다.

여기에 max_threads 가 곱해집니다. 스레드마다 자기 해시 테이블을 만들다가 마지막에 합치기 때문에, 스레드 32 개면 중간 메모리도 그만큼 늘 수 있습니다.

쿼리와 상관없이 서버 전체 한도에 걸리는 경우는 머지와 뮤테이션(백그라운드도 메모리를 씁니다), 딕셔너리 로딩, 마크 캐시와 비압축 캐시, 비동기 INSERT 버퍼, 그리고 동시 쿼리 수입니다.

확인 쿼리

지금 메모리를 많이 쓰는 쿼리입니다.

SELECT query_id, user, elapsed, formatReadableSize(memory_usage) AS mem,
       formatReadableSize(peak_memory_usage) AS peak, substring(query, 1, 80) AS q
FROM system.processes
ORDER BY memory_usage DESC;

지난 하루 동안 메모리 때문에 실패한 쿼리와 상위 쿼리입니다.

SELECT event_time, user, formatReadableSize(memory_usage) AS mem,
       exception_code, substring(query, 1, 120) AS q
FROM system.query_log
WHERE event_date >= today() - 1
  AND type = 'ExceptionWhileProcessing' AND exception_code = 241
ORDER BY event_time DESC LIMIT 20;

SELECT normalized_query_hash, count() AS n,
       formatReadableSize(max(memory_usage)) AS max_mem,
       formatReadableSize(quantile(0.5)(memory_usage)) AS p50_mem,
       any(substring(query, 1, 120)) AS sample
FROM system.query_log
WHERE event_date >= today() - 1 AND type = 'QueryFinish'
GROUP BY normalized_query_hash
ORDER BY max(memory_usage) DESC LIMIT 20;

서버 전체 상황입니다.

SELECT metric, formatReadableSize(value) AS v
FROM system.asynchronous_metrics
WHERE metric IN ('MemoryResident', 'OSMemoryTotal', 'MemoryCode',
                 'MarkCacheBytes', 'UncompressedCacheBytes');

SELECT metric, formatReadableSize(value) FROM system.metrics
WHERE metric IN ('MemoryTracking', 'MergesMutationsMemoryTracking');

MemoryTracking 이 MemoryResident 보다 훨씬 작은데도 서버 한도에 걸린다면 할당자(jemalloc)가 반납하지 않은 메모리가 원인일 수 있습니다. SYSTEM JEMALLOC PURGE 로 돌려받을 수 있습니다.

팁 system.query_log 의 ProfileEvents 맵에서 ExternalAggregationWritePart, ExternalSortWritePart 가 0 보다 크면 그 쿼리는 이미 디스크로 흘리고 있었다는 뜻입니다. 느린 이유가 메모리 부족일 때 이 숫자가 먼저 올라갑니다.

해결

1. 집계: 디스크로 흘리거나 근사로 바꿉니다

-- 한도의 절반쯤에서 디스크로 흘리기 시작
SET max_bytes_before_external_group_by = 4000000000;
-- 또는 한도 비율로 (최근 버전)
SET max_bytes_ratio_before_external_group_by = 0.5;

외부 집계는 느려지지만 끝까지 갑니다. 임시 파일은 tmp_path(Cloud 는 자동)에 쓰이니 디스크 여유를 봅니다.

근사 함수로 바꾸는 편이 대부분 더 낫습니다.

집계 함수의 종류와 정확도 차이는 집계 함수와 컴비네이터에 있습니다.

그룹 키가 정렬 키의 앞부분이면 optimize_aggregation_in_order = 1 로 해시 테이블 없이 흘려 가며 집계할 수 있습니다. 상위 N 만 필요하면 GROUP BY ... ORDER BY cnt DESC LIMIT 10 에 max_rows_to_group_by 와 group_by_overflow_mode = 'any' 를 붙여 그룹 수를 끊는 방법도 있습니다(결과가 근사가 됩니다).

2. 조인: 작은 쪽을 오른쪽에, 알고리즘을 바꿉니다

기본 해시 조인은 오른쪽 테이블을 메모리에 올립니다. 큰 테이블이 오른쪽에 있으면 바로 터집니다.

-- 큰 테이블 LEFT JOIN 작은 테이블 순서로
SELECT ... FROM big AS b LEFT JOIN small AS s ON b.k = s.k;

-- 둘 다 크면 디스크를 쓰는 알고리즘
SET join_algorithm = 'grace_hash';          -- 버킷으로 나눠 디스크에
SET join_algorithm = 'full_sorting_merge';  -- 정렬 병합, 메모리 예측 가능
SET join_algorithm = 'partial_merge';       -- 오래된 선택지

차원 테이블 조인은 조인 대신 딕셔너리로 바꾸면 메모리를 한 번만 쓰고 모든 쿼리가 공유합니다. 각 알고리즘의 특성은 JOIN 전략에 표로 있습니다. max_bytes_in_join 과 join_overflow_mode 로 상한을 걸어 두면 터지기 전에 끊거나 알아챌 수 있습니다.

3. 정렬과 결과 크기

• ORDER BY 에는 LIMIT 를 붙입니다. LIMIT 가 있으면 상위 N 개만 유지합니다.
• 정렬 키 순서로 정렬하면 optimize_read_in_order = 1(기본 켜짐)이 정렬을 생략합니다.
• 꼭 전체 정렬이 필요하면 max_bytes_before_external_sort 로 디스크를 씁니다.
• 결과를 통째로 클라이언트에 보내지 말고 INSERT INTO ... SELECT 로 테이블에 쓰거나 포맷을 바꿔 스트리밍합니다. max_result_rows 로 사고를 막습니다.

4. 스레드와 동시성

max_threads 를 절반으로 줄이면 중간 메모리도 대체로 비례해서 줄어듭니다. 느려지지만 끝나는 쪽이 낫습니다. 동시에 몰리는 대시보드 쿼리는 max_concurrent_queries_for_user 로 줄이고, 같은 쿼리가 반복되면 use_query_cache = 1 로 결과를 재사용합니다 (쿼리 최적화).

5. 한도 자체를 조정합니다

• 쿼리 한도가 서버 한도의 70% 이상이면 쿼리 두 개가 동시에 돌 수 없습니다. 보통 쿼리 한도는 서버 한도의 30~50% 로 둡니다.
• 메모리 오버커밋(memory_overcommit_ratio_denominator, memory_usage_overcommit_max_wait_microseconds)을 쓰면 여유가 있을 때 한도를 넘겨 쓰고, 부족해지면 가장 많이 넘긴 쿼리를 죽입니다. 한도가 소프트 리밋이 되는 셈입니다.
• 서버 한도에 자주 걸리면 캐시 크기(mark_cache_size, uncompressed_cache_size)를 줄이거나 메모리를 늘립니다. Cloud 는 복제본 크기를 올리면 됩니다 (컴퓨트와 스케일링).

6. 데이터 쪽에서 줄입니다

• 고카디널리티 문자열을 LowCardinality(String) 로 바꾸면 집계와 조인에서 정수 사전으로 처리되어 메모리가 크게 줄어듭니다 (데이터 타입 고르기).
• 자주 쓰는 집계는 머티리얼라이즈드 뷰로 미리 계산해 두면 쿼리 시점의 메모리가 사라집니다.
• SELECT * 대신 필요한 열만 읽습니다. 넓은 String 열 하나가 블록 메모리를 몇 배로 키웁니다.

재발 방지

1. 사용자 프로필에 max_memory_usage, max_bytes_ratio_before_external_group_by, max_execution_time 을 묶어 둡니다.
2. exception_code = 241(MEMORY_LIMIT_EXCEEDED) 건수를 대시보드에 올립니다.
3. 새 대시보드 쿼리는 EXPLAIN ESTIMATE 와 system.query_log 의 peak_memory_usage 로 리뷰합니다.
4. 서버 전체 한도에 걸린 날은 system.metric_log 로 그 시간대에 머지와 쿼리 중 무엇이 컸는지 되짚습니다.

공식 문서

• Memory limits 관련 설정 (max_memory_usage 등)
• Memory overcommit
• GROUP BY in external memory
• JOIN algorithms
• Server memory settings