쿼리 병목 현상 파악

한 번에 쿼리의 한 부분만 변경하고 결과를 안정적인 기준값과 비교하면 쿼리 최적화가 수월해집니다. 이 가이드에서는 쿼리를 점진적으로 단순화하고 실행 결과 간의 차이를 통해 어떤 작업이 실행 시간에 가장 큰 영향을 미치는지 파악하는 방법을 설명합니다. 그런 다음 최적화를 선택하기 전에 의심되는 병목 현상을 검증할 수 있습니다.

시작하기 전에

분석하려는 반복적인 느린 쿼리 패턴을 준비하십시오. 아직 식별하지 못했다면 느린 쿼리 진단에서 절차를 안내합니다. 이 가이드의 예시를 그대로 실행하려면, 아직 생성 및 로드하지 않은 경우 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 ()를 사용하므로 날짜 필터에서 읽는 동안 순서 지정 키를 사용해 데이터를 제외할 수 없습니다. 이 예시는 성능 목표가 아니라 비교 방법을 연습하는 용도로 사용하십시오.

작동 방식

쿼리를 점진적으로 단순화하면 작업 단계를 제거하기 전후의 실행 시간을 비교할 수 있습니다. 이러한 차이를 통해 스캔 및 필터링, 그룹화, 집계 계산 또는 정렬과 출력 포맷 지정 같은 후속 작업 중 어느 부분을 조사해야 할지 판단할 수 있습니다.

원본 쿼리를 실행하여 기준 측정값을 설정합니다.
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를 전달하십시오.
동일한 조건에서 각 비교 쿼리를 여러 번 실행하십시오. 워밍업 실행은 측정 대상 실행과 분리해 두십시오.
최근 완료된 쿼리를 조회하기 전에 쿼리 로그를 플러시하십시오.
SYSTEM FLUSH LOGS;
SYSTEM FLUSH LOGS를 실행할 수 없다면 쿼리 로그가 자동으로 플러시될 때까지 기다린 후 다시 조회하십시오. 레코드가 계속 나타나지 않으면 쿼리 로깅이 활성화되어 있는지, 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, 피크 메모리를 기록하십시오.

분산 쿼리에서는 시작 쿼리의 QueryFinish 레코드에 있는 memory_usage가 클러스터 전체의 피크 메모리를 나타내지 않습니다. initial_query_id를 사용하여 참여 노드의 하위 QueryFinish 레코드를 확인하십시오.

다음 표를 사용하여 대표 측정값을 정리하십시오. 필드와 구성에 관한 자세한 내용은 system.query_log를 참조하십시오.

테이블
CSV

실행	쿼리 버전	대표 실행 시간	`read_rows`	`read_bytes`	피크 메모리
A	원본 쿼리
B	그룹화된 `count`
C	그룹화되지 않은 `count`

query-comparison.csv

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

점차 단순화한 쿼리 실행

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

실행 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로 기록합니다.

실행 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와 직접 비교합니다.

실행 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을 사용해 의심되는 병목 현상을 확인합니다.

차이점 해석

개별 측정 시간 2개를 빼기보다 반복 실행에서 얻은 대표 소요 시간을 비교합니다. 크고 일관된 차이는 다음으로 조사할 지점을 보여 줍니다.

관찰 결과	잠재적 병목 현상	다음 조사
실행 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가 사용한 인덱스와 각 인덱스가 제외한 파트 및 그래뉼 수를 확인합니다. ClickHouse가 예상보다 많은 그래뉼을 선택했다면 필터가 테이블의 순서 지정 키와 일치하는지, 파티션 프루닝 또는 데이터 스키핑 인덱스를 통해 더 많은 그래뉼을 제외할 수 있는지 확인합니다. 계획에 Indexes 섹션이 없으면 EXPLAIN이 해당 쿼리의 인덱스 프루닝 정보를 보고하지 않은 것입니다. 반면 전체 테이블을 대상으로 하는 분석 쿼리는 테이블 대부분을 읽는 것이 정상입니다.

의심되는 병목 현상 검증

비교 결과 병목 현상이 의심되면 스키마나 쿼리를 변경하기 전에 이를 검증하십시오. 의심되는 지연 시간의 원인에 맞는 근거를 사용하십시오.

스캔 또는 필터링 병목 현상의 경우, 위에서 설명한 설정과 함께 EXPLAIN indexes = 1을 사용하여 ClickHouse가 사용하는 인덱스와 각 인덱스가 제외하는 파트 및 그래뉼 수를 확인하십시오. 계획에서 예상한 스캔 대신 암시적 프로젝션을 사용하는지도 확인하십시오.
그룹화 또는 집계 병목 현상의 경우, 관련 쿼리 프로필 이벤트와 피크 메모리 사용량을 확인하십시오.
실행 C가 여전히 느리고 조인을 포함한다면, 한 번에 하나씩 조인을 제거한 진단용 쿼리와 비교하십시오. 소요 시간이 크게 줄어들면 제거한 조인이 상당한 작업을 유발한다는 의미입니다. 조인을 제거하면 쿼리의 의미가 바뀌므로, 이 비교는 시간 측정 요인을 분리하는 용도로만 사용하고 행 수 변화는 별도로 해석하십시오.
실행 C에 남아 있는 다른 작업이 병목 현상을 일으킨다면, 실행 계획과 관련 쿼리 프로필 이벤트를 확인하십시오.

EXPLAIN이 반환하는 인덱스 정보에 관한 자세한 내용은 느린 쿼리 진단 가이드를 참조하십시오. 대상 변경을 하나 적용한 후 동일한 조건에서 실행 A, B, C를 반복하십시오. 변경으로 의도한 작업량이 줄었고 병목 현상이 다른 곳으로 이동하지 않았는지 확인하십시오.

다음 단계

최적화 접근 방식을 계속해서 살펴보고, 의심되는 병목 지점에 맞는 하나 이상의 개선 방법을 찾아보십시오.

ClickHouse guides

Use case guides

시작하기 전에

작동 방식

재현 가능한 기준선 설정

점차 단순화한 쿼리 실행

차이점 해석

읽은 행 수와 count 결과 비교

의심되는 병목 현상 검증

다음 단계

​시작하기 전에

​작동 방식

​재현 가능한 기준선 설정

​점차 단순화한 쿼리 실행

​차이점 해석

​읽은 행 수와 count 결과 비교

​의심되는 병목 현상 검증

​다음 단계

시작하기 전에

작동 방식

재현 가능한 기준선 설정

점차 단순화한 쿼리 실행

차이점 해석

읽은 행 수와 count 결과 비교

의심되는 병목 현상 검증

다음 단계