쿼리 병목 분리하기

쿼리 최적화는 한 번에 쿼리의 한 부분만 바꾸고 안정적인 기준선과 결과를 비교하면 더 쉬워져요. 이 가이드에서는 쿼리를 점진적으로 단순화하고 실행 간 차이를 사용해 어떤 연산이 지속 시간에 가장 많이 기여하는지 식별하는 방법을 보여드릴게요. 그런 다음 최적화를 고르기 전에 의심되는 병목을 검증할 수 있어요.

출처: 문서

본문

시작하기 전에 (Before you begin)

조사하려는 반복되는 느린 쿼리 패턴부터 시작해요. 아직 식별하지 못했다면 느린 쿼리 진단이 그 과정을 안내해줘요. 이 가이드의 예제를 그대로 실행하려면 아직 안 했다면 nyc_taxi.trips_small_inferred 테이블을 만들고 데이터를 로드해주세요:

예제 데이터셋 설정하기

원본 Parquet 파일은 약 5.8 GB예요. 네트워크와 사용 가능한 리소스에 따라 로드에 몇 분이 걸릴 수 있어요.

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

예제 테이블은 ORDER BY ()를 사용하므로, 날짜 필터가 순서 키를 사용해 읽기 중 데이터를 제거할 수 없어요. 이 예제는 성능 목표가 아니라 비교 방법을 연습하는 데 사용해주세요.

어떻게 동작하나요 (How it works)

쿼리를 점진적으로 단순화하면 작업 단계를 제거하기 전후의 지속 시간을 비교할 수 있어요. 그 차이는 스캔·필터링, 그룹화, 집계 계산, 또는 정렬·출력 형식화 같은 이후 작업을 조사할지 결정하는 데 도움이 돼요:

원래 쿼리를 실행해 기준선 측정값을 확립해요.
GROUP BY를 유지하고, 쿼리의 집계 계산을 count로 바꾸고, 정렬·출력 형식화 같은 이후 작업을 제거해요.
그룹화를 제거하고 그룹 없는 count를 실행해서 스캔, 필터링, 조인이 남긴 작업을 근사해요.

이 단계는 일반적인 그룹 집계 쿼리에 직접 적용돼요. 더 복잡한 쿼리에서는 한 번에 하나의 SELECT 블록에 같은 원리를 적용해요: 동등한 데이터 소스와 필터를 보존하고, 한 번에 하나의 연산을 제거하며, 각 변경 후 실행 계획을 검증해요.

이 차이는 ClickHouse 실행 단계의 정확한 측정이 아니라 진단적 추정치예요. 쿼리를 바꾸면 실행 계획, 읽는 컬럼, 단계 간 전달되는 데이터가 바뀔 수 있어요. 결과를 사용해 가설을 세워요. 그런 다음 쿼리 로그와 EXPLAIN으로 검증해요.

반복 가능한 기준선 확립하기

측정값을 비교 가능하게 만들려면 다음 관행을 사용해요:

모든 비교가 같은 데이터와 시간 범위를 사용하도록 FROM, JOIN, PREWHERE, WHERE 절을 변경하지 않고 유지해요.
비슷한 시스템 부하에서 각 쿼리 버전을 여러 번 실행해요.
캐시 조건을 일관되게 유지해요. 측정값을 기록하기 전에 각 쿼리 버전을 실행하거나 아래 나열된 캐시를 비활성화해요. 캐시된 실행과 캐시되지 않은 실행을 비교하지 마세요.
가장 빠르거나 가장 느린 결과에 의존하지 말고 워밍업 실행 후 반복 실행의 중앙값 같은 대표 지속 시간을 기록해요.
특정 변경과 성능 차이를 연관 지을 수 있도록 한 번에 하나의 변수만 바꿔요.

캐시되지 않은 진단 비교를 위해 원격 데이터의 ClickHouse 파일시스템 캐시, 쿼리 캐시, 쿼리 조건 캐시를 비활성화해요. 암시적 프로젝션도 비활성화해서 실행 C의 count가 비교하려는 스캔을 우회하는 최적화된 실행 계획을 사용하지 않게 해요.

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

이 SET 문은 현재 세션에만 적용돼요. 모든 비교 쿼리를 그 세션에서 실행하거나, 모든 실행에 같은 설정을 적용해요. 파일시스템 캐시 설정이 운영체제 페이지 캐시나 모든 ClickHouse 캐시를 비활성화하지는 않아요. 끝나면 전용 세션을 닫거나 각 설정을 이전 값으로 복원해요.

이 워크플로는 통제된 쿼리 실행과 쿼리 로그의 측정값을 결합해요. 각 실행에 대한 측정값을 다음과 같이 수집해요:

모든 실행에 고유한 쿼리 ID를 할당하거나 쿼리 인터페이스가 생성한 ID를 기록해요. 예를 들어 반복 실행을 bottleneck-a-1, bottleneck-a-2, bottleneck-a-3으로 식별해요. clickhouse-client에서는 쿼리 실행 시 --query_id your-query-id를 전달해요.
같은 조건에서 각 비교 쿼리를 여러 번 실행해요. 워밍업 실행은 측정된 실행과 분리해주세요.
최근 완료된 쿼리를 조회하기 전에 쿼리 로그를 flush해요:

SYSTEM FLUSH LOGS;

SYSTEM FLUSH LOGS를 실행할 수 없으면 쿼리 로그가 자동으로 flush될 때까지 기다린 뒤 조회를 다시 시도해요. 레코드가 나타나지 않으면 쿼리 로깅이 활성화되어 있는지, system.query_log를 읽을 수 있는지, 쿼리를 실행한 노드를 조회하고 있는지 확인해요.

각 쿼리 ID의 완료 레코드를 조회해요. system.query_log는 완료된 쿼리에 대해 QueryStart와 QueryFinish 이벤트를 모두 기록해요. 최종 지속 시간, 읽은 행·바이트, 최고 메모리를 포함하는 QueryFinish로 필터링해요:

SELECT
    query_id,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_id = 'your-query-id'
ORDER BY event_time_microseconds DESC
LIMIT 1;

각 쿼리 버전에 대해 측정된 실행의 중앙값 지속 시간을 사용해요. 해당 중앙값에 가장 가까운 실행에서 read_rows, read_bytes, 최고 메모리를 기록해서, 측정값이 실제 실행에 묶이도록 해요.

분산 쿼리에서는 initiator 쿼리의 QueryFinish 레코드에 있는 memory_usage가 클러스터 전체 최고치가 아니에요. initial_query_id를 사용해 참여 노드의 자식 QueryFinish 레코드를 검사해요.

다음 같은 테이블을 사용해 대표 측정값을 정리해요. 필드와 설정에 대한 자세한 내용은 system.query_log를 참고하세요.

Run	Query version	Representative duration	`read_rows`	`read_bytes`	Peak memory
A	Original query
B	Grouped `count`
C	Ungrouped `count`

CSV (query-comparison.csv)

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

점진적으로 더 단순한 쿼리 실행하기

세 가지 비교를 모두 보여주기 위해 예제는 그룹화된 날짜 범위 워크로드를 사용해요. 이 방법을 워크드 예제를 따르지 않고 다른 쿼리에 적용할 수 있어요. 쿼리에 GROUP BY가 없으면 아래 설명대로 실행 B를 건너뛰어요.

1. 실행 A: 원래 쿼리 측정

필터, 그룹화, 집계 표현식, 정렬, 출력을 변경하지 않고 전체 쿼리를 실행해요. 이렇게 기준선 지속 시간, 읽은 행·바이트, 최고 메모리 사용량이 확립돼요.

이 쿼리는 지불 유형별로 여정을 그룹화하고 여러 집계 값을 계산해요:

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

쿼리의 측정값을 실행 A로 기록해요.

2. 실행 B: count로 그룹화 유지

쿼리의 FROM, JOIN, PREWHERE, WHERE, 그룹화 키를 보존해요. 집계 표현식을 그룹 count로 바꿔요. 원래 정렬과 출력 표현식을 포함해 집계 이후의 작업을 제거해요.

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

실행 B는 여전히 데이터를 스캔·필터링하고, 조인을 수행하고, 그룹을 구성해요. 지속 시간을 실행 A와 비교해 원래 집계 표현식과 집계 이후 작업의 기여도를 추정해요. read_bytes도 비교해요. 집계 표현식 제거가 읽기에서 컬럼을 제거할 수 있기 때문이에요.

원래 쿼리에 GROUP BY가 없으면 분리할 그룹화 단계가 없어요. 실행 B를 건너뛰고 원래 쿼리를 실행 C와 직접 비교해요.

3. 실행 C: 그룹화 제거

GROUP BY를 제거하고 단일 count를 반환해요. FROM, JOIN, PREWHERE, WHERE 절을 변경하지 않고 유지해서 남은 작업을 비교 가능하게 해요.

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

실행 C는 그 계획이 유지하는 연산의 기준선을 제공하지, 스캔이나 필터링의 고립된 측정은 아니에요. 실행 B와 비교해 그룹화의 기여도를 추정해요. 그룹화 키 제거가 읽는 컬럼을 줄일 수 있으므로 read_bytes도 비교해요. 반환된 count는 보존된 필터와 조인 후 집계에 도달하는 행 수를 보여줘요.

실행 C를 해석하기 전에 그 실행 계획이 의도한 데이터 소스를 읽고 보존된 필터를 적용하는지 확인해요. 프로젝션이나 메타데이터 기반 count는 수행되는 작업을 바꿀 수 있어요. 스캔 기반 기준선을 위해 세 실행 모두에서 계획에 표시된 최적화를 비활성화해요: 암시적 프로젝션이면 optimize_use_implicit_projections = 0, 명시적 프로젝션이면 optimize_use_projections = 0, 테이블 메타데이터에서 제공되는 필터 없는 count면 optimize_trivial_count_query = 0.

실행 C가 여전히 느리면 그 안에 유지된 연산(스캔·필터링부터 시작)을 조사해요. 쿼리를 바꾸기 전에 쿼리 로그와 EXPLAIN으로 의심되는 병목을 검증해요.

차이 해석하기

두 개의 개별 타이밍을 빼는 대신 반복 실행의 대표 지속 시간을 비교해요. 크고 일관된 차이는 다음 조사 지점을 가리켜요:

관찰	잠재적 병목	다음 조사
실행 A가 실행 B보다 훨씬 느림	집계 표현식, 정렬, 집계 이후의 다른 작업, 또는 추가로 읽은 컬럼	비싼 집계 함수, 표현식, `ORDER BY`, `read_bytes`, 최고 메모리 사용량 검사
실행 B가 실행 C보다 훨씬 느림	그룹화, 그룹 카디널리티, 또는 그룹화 키 읽기	그룹화 키, 그룹 수, `read_bytes`, 최고 메모리 사용량 검사
실행 C가 여전히 느림	스캔, 필터링, 조인, 또는 실행 C가 유지하는 다른 연산	읽은 행·바이트, 기본 키 사용, 데이터 스킵 인덱스, 실행 계획 검사 후 의심 병목 검증
세 실행 모두 비슷한 지속 시간	대기 시간의 원인이 세 버전 모두에 공통되거나, 단순화가 실행 계획을 바꿨을 수 있음	실행 간 `read_rows`, `read_bytes`, 최고 메모리 비교. 그것도 비슷하면 실행 C가 유지하는 연산 조사. 그렇지 않으면 실행 계획 차이 비교

읽은 행 수와 count 결과 비교하기

실행 C의 read_rows를 그 count가 반환한 값과 비교해요. 예를 들어 read_rows가 1억이고 count가 100만을 반환하면 ClickHouse는 세어진 행마다 약 100개의 원본 행을 스캔한 거예요. 이는 필터가 테이블에서 읽은 행의 대부분을 거부했다는 뜻이지만, 그 이유는 식별하지 못해요. 이 비율은 단순한 단일 테이블 스캔을 위한 것이에요. 여러 데이터 소스나 프로젝션이 있는 쿼리에서는 대신 실행 계획으로 read_rows를 해석해요. ClickHouse 25.9 이상에서는 인덱스 사용을 검사하기 전에 쿼리 조건 캐시와 데이터 스킵 인덱스의 동적 적용을 비활성화해요:

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

그런 다음 EXPLAIN indexes = 1을 사용해 ClickHouse가 어떤 인덱스를 사용했고 각 인덱스가 몇 개의 part와 granule을 제거했는지 확인해요. ClickHouse가 예상보다 더 많은 granule을 선택했다면 필터가 테이블의 순서 키와 정렬되는지, 파티션 프루닝이나 데이터 스킵 인덱스가 더 많은 granule을 제거할 수 있는지 검사해요. 계획에 Indexes 섹션이 없으면 EXPLAIN이 그 쿼리에 대해 인덱스 프루닝을 보고하지 않은 거예요. 반면 전체 테이블 분석 쿼리는 테이블의 대부분을 읽는 것이 정상이에요.

의심되는 병목 검증하기

비교가 가능성 있는 병목을 가리킨 뒤 스키마나 쿼리를 바꾸기 전에 검증해요. 의심되는 대기 시간 원인에 적합한 증거를 사용해요:

스캔·필터링 병목의 경우 위에서 설명한 설정과 함께 EXPLAIN indexes = 1을 사용해 ClickHouse가 사용하는 인덱스와 각 인덱스가 제거하는 part·granule 수를 확인해요. 계획이 예상 스캔 대신 암시적 프로젝션을 사용하는지 확인해요.
그룹화·집계 병목의 경우 관련 쿼리 프로필 이벤트와 최고 메모리 사용량을 검사해요.
실행 C가 여전히 느리고 조인이 포함되어 있으면, 한 번에 하나씩 조인을 제거하는 진단 쿼리와 비교해요. 지속 시간이 크게 줄면 제거된 조인이 상당한 작업을 기여한다는 뜻이에요. 조인 제거는 쿼리의 의미를 바꾸므로 이 비교는 타이밍을 분리하는 데만 사용하고 행 수 변화는 별도로 해석해요.
실행 C가 유지하는 다른 연산의 병목은 실행 계획과 관련 쿼리 프로필 이벤트를 검사해요.

EXPLAIN이 반환하는 인덱스 정보에 대한 자세한 내용은 느린 쿼리 진단 가이드를 참고하세요. 목표로 한 변경 하나를 적용한 뒤 같은 조건에서 실행 A, B, C를 반복해요. 변경이 의도한 작업을 줄였고 병목을 다른 곳으로 옮기지 않았는지 확인해요.

다음 단계 (Next steps)

최적화 접근법으로 계속해서 의심되는 병목을 하나 이상의 목표 변경과 대응시켜보세요.

쿼리 병목 분리하기