쿼리 병목 현상 파악

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

분석하려는 반복적인 느린 쿼리 패턴을 준비하십시오. 아직 식별하지 못했다면 느린 쿼리 진단에서 절차를 안내합니다.

이 가이드의 예시를 그대로 실행하려면, 아직 생성 및 로드하지 않은 경우 nyc_taxi.trips_small_inferred 테이블을 생성하고 데이터를 로드하십시오.

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 ()를 사용하므로 날짜 필터에서 읽는 동안 순서 지정 키를 사용해 데이터를 제외할 수 없습니다. 이 예시는 성능 목표가 아니라 비교 방법을 연습하는 용도로 사용하십시오.

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

1. 원본 쿼리를 실행하여 기준 측정값을 설정합니다.
2. GROUP BY는 유지하고 쿼리의 집계 계산을 count로 대체한 다음, 정렬과 출력 포맷 지정 같은 후속 작업을 제거합니다.
3. 그룹화를 제거하고 그룹화하지 않은 count를 실행하여 스캔, 필터링 및 조인에 해당하는 작업량을 추정합니다.

이 단계는 일반적인 그룹화 집계 쿼리에 직접 적용할 수 있습니다. 더 복잡한 쿼리에서는 한 번에 하나의 SELECT 블록에 같은 원칙을 적용하십시오. 동등한 데이터 소스와 필터는 유지하고, 한 번에 하나의 작업을 제거한 뒤 변경할 때마다 실행 계획을 확인하십시오.

측정 결과를 비교 가능하게 하려면 다음 사항을 따르십시오:

• 모든 비교에 동일한 데이터와 시간 범위를 사용하도록 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;

이 워크플로는 통제된 쿼리 실행과 쿼리 로그의 측정값을 결합합니다.

다음과 같이 각 실행의 측정값을 수집합니다.

1. 실행마다 고유한 쿼리 ID를 할당하거나 쿼리 인터페이스에서 생성한 ID를 기록하십시오. 예를 들어 반복 실행은 bottleneck-a-1, bottleneck-a-2, bottleneck-a-3으로 식별합니다. clickhouse-client에서 쿼리를 실행할 때는 --query_id your-query-id를 전달하십시오.

2. 동일한 조건에서 각 비교 쿼리를 여러 번 실행하십시오. 워밍업 실행은 측정 대상 실행과 분리해 두십시오.

3. 최근 완료된 쿼리를 조회하기 전에 쿼리 로그를 플러시하십시오.

   SYSTEM FLUSH LOGS;

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

4. 각 쿼리 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;

5. 각 쿼리 버전에는 측정 대상 실행의 중앙 실행 시간을 사용하십시오. 측정값이 실제 실행과 연결되도록, 중앙값에 가장 가까운 실행의 read_rows, read_bytes, 피크 메모리를 기록하십시오.

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

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

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

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;

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;

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

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

실행 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를 반복하십시오. 변경으로 의도한 작업량이 줄었고 병목 현상이 다른 곳으로 이동하지 않았는지 확인하십시오.

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