ClickHouse 대규모 데이터 정렬 성능 최적화: 엔진 교체와 분할 쿼리 전략

수억 건 이상의 데이터를 다루는 ClickHouse 환경에서 ORDER BY 성능 문제는 흔한 병목 지점입니다. 이 글에서는 실제 사례를 통해 엔진 교체와 쿼리 분할 기법을 적용하여 37초에서 2.7초로 성능을 개선한 방법을 설명합니다.

문제 상황: 수억 건 정렬의 느린 실행

3억 건 규모의 디바이스 로그 테이블을 가정합니다. 스키마는 다음과 같습니다:

CREATE TABLE device_logs (
    device_id UInt64,
    payload String,
    event_time DateTime,
    category UInt8,
    priority UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(event_time)
PRIMARY KEY (event_time, device_id)
ORDER BY (event_time, device_id)
SETTINGS index_granularity = 256;

3개월치 데이터(약 1.5억 건)에서 특정 카테고리 필터링 후 priority 기준 정렬이 필요한 쿼리:

SELECT count() FROM (
    SELECT event_time, 
           toUInt32(device_id) AS device_id, 
           payload 
    FROM device_logs
    WHERE category = 5
    ORDER BY priority DESC
);

실행 결과:

+---------+
| count() |
+---------+
| 1262100 |
+---------+
1 row in set (37.50 sec)

같은 조건에서 ORDER BY를 제거하면:

+---------+
| count() |
+---------+
| 1262100 |
+---------+
1 row in set (1.74 sec)

단순히 정렬 추가만으로 20배 이상의 성능 저하가 발생합니다.

실행 계획 분석

정렬 없는 쿼리의 실행 흐름

Expression ((Projection + Before ORDER BY))
  Aggregating
    Expression ((Before GROUP BY + Projection))
      SettingQuotaAndLimits
        Union
          Expression ((Convert block structure + Before ORDER BY))
            SettingQuotaAndLimits
              ReadFromStorage (MergeTree)
          ReadFromPreparedSource (remote replica)

단순한 3단계: 스토리지 읽기 → 노드 간 병합 → 집계

정렬 포함 쿼리의 실행 흐름

Expression ((Projection + Before ORDER BY))
  Aggregating
    Expression ((Before GROUP BY + Projection))
      MergingSorted (Merge sorted streams for ORDER BY)
        SettingQuotaAndLimits
          Union
            Expression (Convert block structure)
              FinishSorting
                Expression (Before ORDER BY)
                  SettingQuotaAndLimits
                    Expression (Remove unused columns)
                      Union
                        MergingSorted (Merge sorting mark ranges)
                          Expression (Calculate sorting key prefix)
                            ReadFromStorage (MergeTree with order)
                        MergingSorted (Merge sorting mark ranges)
                          Expression (Calculate sorting key prefix)
                            ReadFromStorage (MergeTree with order)
                        ... (동일 패턴 반복)

MergeTree는 정렬 키가 PRIMARY KEY와 다를 때 다중 단계 외부 정렬을 수행합니다. 각 파티션별로 부분 정렬 후 전역 병합을 반복하며, 이 과정에서 대량의 메모리-디스크 왕복이 발생합니다.

최적화 1: 엔진 교체

MergeTree는 집계 함수와 정렬 키가 다른 경우 비효율적입니다. AggregatingMergeTree 계열 엔진으로 교체하면 정렬 과정이 단순화됩니다.

새로운 테이블 구조:

CREATE TABLE device_logs_optimized (
    device_id UInt64,
    payload SimpleAggregateFunction(any, String),
    event_time DateTime,
    category UInt8,
    priority UInt32
) ENGINE = ReplicatedAggregatingMergeTree(
    '/clickhouse/tables/{shard}/logs/optimized', 
    '{replica}'
)
PARTITION BY toYYYYMMDD(event_time)
PRIMARY KEY priority
ORDER BY priority
SETTINGS index_granularity = 8192;

주요 변경사항:

PRIMARY KEY와 ORDER BY를 정렬 대상 컬럼(priority)으로 통일
index_granularity를 256에서 8192로 증가하여 인덱스 율 향상
payload를 SimpleAggregateFunction으로 정의하여 병합 시 중복 제거

데이터량을 3.5억 건으로 증가시킨 후 동일 쿼리 테스트:

조건	결과	소요시간
ORDER BY 없음	3,290,193	3.58 sec
ORDER BY 포함	3,290,193	4.16 sec

데이터량이 2배 이상 증가했으나 정렬 쿼리가 4초 내외로 안정화되었습니다.

최적화된 실행 계획

Expression ((Projection + Before ORDER BY))
  Aggregating
    Expression ((Before GROUP BY + Projection))
      MergingSorted (Merge sorted streams for ORDER BY)
        SettingQuotaAndLimits
          Union
            Expression (Convert block structure)
              MergingSorted (Merge sorted streams for ORDER BY)
                MergeSorting (Merge sorted blocks for ORDER BY)
                  PartialSorting (Sort each block for ORDER BY)
                    Expression (Before ORDER BY)
                      SettingQuotaAndLimits
                        ReadFromStorage (MergeTree)
            ReadFromPreparedSource (remote replica)

계이 14단계로 축소되었고, 핵심 차이는 PartialSorting → MergeSorting → MergingSorted의 선형 흐름입니다. PRIMARY KEY와 ORDER BY가 일치하면 ClickHouse는 데이터가 이미 물리적으로 정렬되어 있다고 판단하여 복잡한 재정렬을 생략합니다.

최적화 2: 시간 구간 분할 쿼리

엔진 교체로 충분한 경우도 있으나, 극단적인 데이터량이나 복잡한 필터 조건에서는 쿼리 레벨 분할이 효과적입니다.

단일 구간 쿼리:

SELECT count() FROM (
    SELECT event_time, toUInt32(device_id) AS device_id, payload 
    FROM device_logs_optimized
    WHERE category = 5 
      AND event_time BETWEEN '2021-01-01' AND '2021-03-31'
    ORDER BY priority DESC
);

월별 분할 쿼리:

SELECT count() FROM (
    SELECT event_time, toUInt32(device_id) AS device_id, payload 
    FROM device_logs_optimized
    WHERE category = 5 
      AND event_time BETWEEN '2021-01-01' AND '2021-01-31'
    ORDER BY priority DESC
    
    UNION ALL
    
    SELECT event_time, toUInt32(device_id) AS device_id, payload 
    FROM device_logs_optimized
    WHERE category = 5 
      AND event_time BETWEEN '2021-02-01' AND '2021-02-28'
    ORDER BY priority DESC
    
    UNION ALL
    
    SELECT event_time, toUInt32(device_id) AS device_id, payload 
    FROM device_logs_optimized
    WHERE category = 5 
      AND event_time BETWEEN '2021-03-01' AND '2021-03-31'
    ORDER BY priority DESC
);

분할 쿼리의 성능:

+---------+
| count() |
+---------+
| 3290193 |
+---------+
1 row in set (2.74 sec)

최종 성능 비교

최적화 단계	데이터량	소요시간	개선율
초기 상태 (MergeTree)	1.5억 건	37.50 sec	기준
엔진 교체만	3.5억 건	4.16 sec	90% ↓
엔진 + 분할 쿼리	3.5억 건	2.74 sec	93% ↓

핵심 원리 정리

PRIMARY KEY와 ORDER BY 정렬 키 일치: 물리적 정렬 순서가 쿼리 정렬 요구와 같으면 런타임 정렬 최소화
index_granularity 튜닝: 넓은 granule은 정렬 작업의 메모리 효율을 높임 (단, 필터링 쿼리에는 주의)
시간/파티션 기준 분할: 독립적인 정렬 작업을 병렬화하여 전역 병합 부담 감소
UNION ALL 활용: 각 구간이 정렬된 상태로 반환되므로 최종 병합만 필요

실제 적용 시 데이터 분포와 쿼리 패턴에 따라 granule 크기와 분할 구간을 조정하는 것이 중요합니다.

태그: ClickHouse MergeTree AggregatingMergeTree ORDER BY 쿼리 최적화

6월 27일 22:56에 게시됨

괴물 클럽