Diagnostyka CPU 80% w ClickHouse

Diagnozowanie obciążenia CPU w ClickHouse: identyfikacja problematycznych zapytań

Przy 80% wykorzystaniu CPU i spowolnieniu zapytań zacznij od tabeli system.processes. Pokazuje ona aktualnie wykonywane zapytania z metrykami elapsed, memory_usage, read_bytes i read_rows.

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

Zidentyfikowane zapytanie można zatrzymać poleceniem KILL QUERY WHERE query_id = 'xxx';. Aby zakończyć działanie synchronicznie, użyj KILL QUERY WHERE query_id = 'xxx' SYNC;. Pozwala to szybko usunąć szczytowe obciążenie bez oczekiwania.

Analiza zakończonych zapytań w query_log

Tabela systemowa system.query_log rejestruje metryki wszystkich zakończonych zapytań: czas trwania, zużycie CPU, pamięć, objętość odczytu. Przed analizą wykonaj SYSTEM FLUSH LOGS;, aby upewnić się, że dane są aktualne.

Podstawowe zapytanie do wyświetlania top 20 najbardziej intensywnych zapytań w ciągu godziny:

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;

Filtr type = 'QueryFinish' wybiera tylko wpisy z metrykami. W razie potrzeby możesz wykluczyć ExceptionWhileProcessing. ProfileEvents zawierają szczegółowe liczniki: SelectedMarks, SelectedParts, MergedRows. Wysokie wartości SelectedMarks wskazują na nieefektywne warunki WHERE.

Do sortowania według zasobów:

• Zawieszenia: ORDER BY duration_sec DESC
• CPU: ORDER BY total_cpu_sec DESC
• RAM: ORDER BY memory_usage DESC
• Dysk: ORDER BY read_bytes DESC

Ogranicz okno analizy i użyj LIMIT, aby nie przeciążyć samej tabeli MergeTree.

Identyfikacja źródeł obciążenia

Przy wielu klientach grupuj dane po 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;

Dla orkiestratorów (Airflow, Dagster) dodaj log_comment w kliencie:

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

Analiza po etykietach:

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;

Alternatywa — komentarze SQL: -- source:sales_dashboard, wyszukiwanie za pomocą query ILIKE '%source:sales_dashboard%'.

Sprawdzanie planu wykonania

EXPLAIN indexes = 1 ocenia skuteczność indeksów bez uruchamiania zapytania. Przykład: Granules: 10/1043 — filtr ogranicza dane do 1% dzięki trafieniu w ORDER BY.

Wysokie pełne skanowanie (Granules: 95000/98400) wymaga przeanalizowania warunku WHERE lub klucza sortowania. Ocena ręczna: dla N wierszy w tabeli o 100M — odczyt N × 8192 wierszy.

Analiza rzeczywistego przypadku

Zapytanie z 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;

Tabela: ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). Problemy:

• Szeroki zakres dat czyta dane z dwóch lat.
• uniqExact zamiast uniq – oszczędność zasobów.
• PREWHERE do wcześniejszego filtrowania.
• Przepisanie ORDER BY na (event_date, user_id, event_id).

Porównaj metryki przed i po optymalizacji.

Przejście do monitoringu

Analiza dzienna po 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;

Skonfiguruj alerty na odchylenia.

Checklist diagnostyki

• SYSTEM FLUSH LOGS
• system.processes – aktywne zapytania
• system.query_log – top zasobów
• EXPLAIN indexes = 1 – plan odczytu
• Sprawdzenie WHERE w ORDER BY, PREWHERE
• uniqExact, JOIN, daty – optymalizacja
• Poprawa i porównanie metryk przed i po

Co jest ważne

• Używaj system.processes do natychmiastowego zatrzymania problematycznych zapytań.
• query_log z ProfileEvents daje kompletną wizję zużycia CPU, RAM i dysku.
• log_comment ma kluczowe znaczenie w środowiskach multi-tenant.
• EXPLAIN indexes = 1 wykrywa nieefektywne klucze sortowania.
• Przejdź od diagnostyki jednorazowej do codziennej analizy po hash.

— Editorial Team