ClickHouse CPU 80% 진단

클릭하우스에서 CPU 부하 진단: 문제 쿼리 찾기

CPU 사용률이 80%에 도달하고 쿼리가 느려지면, 먼저 system.processes 테이블을 확인하세요. 이 테이블은 실행 중인 쿼리의 경과 시간, 메모리 사용량, 읽은 바이트 수, 읽은 행 수 등의 메트릭을 제공합니다.

SELECT
    elapsed,
    formatReadableSize(memory_usage) AS ram,
    formatReadableSize(read_bytes) AS read_size,
    read_rows,
    query
FROM system.processes
ORDER BY elapsed DESC;

문제가 있는 쿼리를 정지하려면 KILL QUERY WHERE query_id = 'xxx';를 사용하세요. 동기식 종료를 원하면 KILL QUERY WHERE query_id = 'xxx' SYNC;를 사용하면 됩니다. 이 방법은 대기 없이 피크 부하를 빠르게 제거할 수 있습니다.

query_log에서 완료된 쿼리 분석하기

system.query_log 테이블은 모든 완료된 쿼리의 지표를 기록합니다: 실행 시간, CPU, 메모리, 읽은 데이터 양 등입니다. 분석 전에 SYSTEM FLUSH LOGS;를 실행해 최신 데이터를 확보하세요.

지난 1시간 동안 리소스 소비가 가장 큰 상위 20개 쿼리를 찾는 기본 쿼리:

SELECT
    event_time,
    query_duration_ms / 1000 AS duration_sec,
    formatReadableSize(memory_usage) AS ram,
    formatReadableSize(read_bytes) AS read_size,
    read_rows,
    ProfileEvents['UserTimeMicroseconds'] / 1000000 AS user_cpu_sec,
    ProfileEvents['SystemTimeMicroseconds'] / 1000000 AS system_cpu_sec,
    round(user_cpu_sec + system_cpu_sec, 2) AS total_cpu_sec,
    substring(query, 1, 200) AS query_short
FROM system.query_log
WHERE event_date >= today()
  AND event_time >= now() - INTERVAL 1 HOUR
  AND type = 'QueryFinish'
  AND NOT has(tables, 'system.query_log')
ORDER BY duration_sec DESC
LIMIT 20;

type = 'QueryFinish' 필터를 통해 완전한 메트릭을 가진 완료된 쿼리만 포함합니다. 필요 시 ExceptionWhileProcessing을 제외할 수 있습니다. ProfileEvents는 SelectedMarks, SelectedParts, MergedRows와 같은 세부 카운터를 제공합니다. 높은 SelectedMarks 값은 비효율적인 WHERE 절을 의미합니다.

자원 유형별로 정렬하려면 다음과 같이 사용하세요:

• 느린 쿼리: ORDER BY duration_sec DESC
• 고 CPU 사용: ORDER BY total_cpu_sec DESC
• 고 메모리 사용: ORDER BY memory_usage DESC
• 고 디스크 I/O: ORDER BY read_bytes DESC

분석 범위를 제한하고 LIMIT을 활용해 MergeTree 테이블 자체에 과부하를 주지 않도록 하세요.

부하 원인 식별하기

여러 클라이언트가 존재하는 환경에서는 user, client_hostname, 또는 initial_user로 그룹화하세요:

SELECT
    user,
    client_hostname,
    count() AS queries,
    round(sum(query_duration_ms) / 1000, 1) AS total_sec,
    formatReadableSize(sum(read_bytes)) AS total_read
FROM system.query_log
WHERE event_date >= today()
  AND type = 'QueryFinish'
GROUP BY user, client_hostname
ORDER BY total_sec DESC
LIMIT 10;

에어플로우나 다그스터와 같은 오케스트레이션 도구를 사용한다면, 클라이언트 코드에 log_comment를 추가하세요:

client.execute(query, settings={'log_comment': 'dag:report/step:aggregate_daily'})

주석 태그별로 분석하세요:

SELECT
    log_comment,
    count() AS queries,
    round(sum(query_duration_ms) / 1000, 1) AS total_sec,
    formatReadableSize(sum(read_bytes)) AS total_read
FROM system.query_log
WHERE event_date >= today()
  AND type = 'QueryFinish'
  AND user = 'airflow'
GROUP BY log_comment
ORDER BY total_sec DESC
LIMIT 20;

대안으로 SQL 주석을 사용해 -- source:sales_dashboard처럼 표시하고, query ILIKE '%source:sales_dashboard%'로 검색할 수도 있습니다.

실행 계획 확인하기

쿼리를 실행하지 않고도 인덱스 효율성을 평가하려면 EXPLAIN indexes = 1을 사용하세요. 예: Granules: 10/1043 — ORDER BY 절과 일치해 데이터 스캔을 1%로 줄였습니다.

높은 전체 스캔(예: Granules: 95000/98400)은 WHERE 조건이나 정렬 키를 재설계해야 함을 의미합니다. 수동 추정: 1억 행 테이블에 N행이 있다면 약 N × 8192 행을 읽어야 합니다.

실제 사례 연구

CPU 사용률 80% 이상을 유발한 쿼리:

SELECT
    user_id,
    uniqExact(event_id),
    sum(amount)
FROM events final
WHERE event_date > '2024-01-01'
GROUP BY user_id
ORDER BY sum(amount) DESC
LIMIT 100;

테이블: ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). 문제점:

• 2년치 데이터를 넓은 날짜 범위로 스캔함.
• uniq 대신 더 자원을 많이 쓰는 uniqExact 사용.
• 조기 필터링을 위한 PREWHERE 누락.
• 정렬 순서 불량: (event_date, user_id, event_id)로 재정렬 필요.

최적화 전후 성능 지표를 비교하세요.

모니터링으로 전환하기

normalized_query_hash 기반의 일일 분석:

SELECT
    toDate(event_time) AS day,
    normalized_query_hash,
    count() AS executions,
    round(avg(query_duration_ms) / 1000, 2) AS avg_sec,
    round(max(query_duration_ms) / 1000, 2) AS max_sec,
    formatReadableSize(sum(read_bytes)) AS total_read,
    formatReadableSize(max(memory_usage)) AS peak_ram,
    substring(any(query), 1, 150) AS example
FROM system.query_log
WHERE event_date >= today() - 7
  AND type = 'QueryFinish'
GROUP BY day, normalized_query_hash
ORDER BY day DESC, sum(query_duration_ms) DESC
LIMIT 30;

이상치에 대한 알림을 설정하세요.

진단 체크리스트

• SYSTEM FLUSH LOGS
• system.processes 확인 — 실행 중인 쿼리
• system.query_log 검토 — 리소스 소비가 큰 쿼리
• EXPLAIN indexes = 1 실행 — 실행 계획 확인
• WHERE 조건 및 ORDER BY 키 검증
• uniqExact, JOIN, 날짜 범위 최적화
• 수정 후 성능 비교 및 측정

핵심 요약

• 문제 쿼리는 system.processes를 통해 즉시 종료 가능.
• query_log와 ProfileEvents는 CPU, RAM, 디스크 사용량을 종합적으로 파악할 수 있음.
• 멀티테넌트 환경에서는 log_comment가 필수적.
• EXPLAIN indexes = 1은 비효율적인 정렬 키를 드러냄.
• 단발적 디버깅에서 쿼리 해시 기반 일일 분석으로 전환하세요.

— Editorial Team