Diagnostika CPU 80 % v ClickHouse

Diagnostika zatížení CPU v ClickHouse: hledání problémových dotazů

Při zatížení CPU na 80 % a zpomalení dotazů začněte tabulkou system.processes. Obsahuje aktuální spuštěné dotazy s metrikami elapsed, memory_usage, read_bytes a 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;

Zjištěný dotaz zastavíte příkazem KILL QUERY WHERE query_id = 'xxx';. Pro synchronní ukončení použijte KILL QUERY WHERE query_id = 'xxx' SYNC;. Tím rychle odstraníte špičkové zatížení bez čekání.

Analýza dokončených dotazů v query_log

Systémová tabulka system.query_log zachycuje metriky všech dokončených dotazů: dobu trvání, využití CPU, paměti a objem čtení. Před analýzou spusťte SYSTEM FLUSH LOGS;, aby byly data aktualizovaná.

Základní dotaz pro top 20 těžkých dotazů za hodinu:

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' vybírá záznamy s metrikami. Případně vyloučte ExceptionWhileProcessing. ProfileEvents poskytují detailní čítače: SelectedMarks, SelectedParts, MergedRows. Vysoké hodnoty SelectedMarks naznačují neefektivní podmínku WHERE.

Pro řazení podle zdrojů:

• Zpomalení: ORDER BY duration_sec DESC
• CPU: ORDER BY total_cpu_sec DESC
• RAM: ORDER BY memory_usage DESC
• Disk: ORDER BY read_bytes DESC

Omezení analytického okna a použití LIMIT zabrání přetížení samotné tabulky MergeTree.

Identifikace zdrojů zatížení

Při mnoha klientech seskupte podle 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;

Pro orchestrátory (Airflow, Dagster) implementujte log_comment v klientovi:

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

Analýza podle značek:

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;

Alternativa – SQL komentáře: -- source:sales_dashboard s hledáním query ILIKE '%source:sales_dashboard%'.

Kontrola plánu provádění

EXPLAIN indexes = 1 vyhodnocuje efektivitu indexů bez spuštění dotazu. Příklad: Granules: 10/1043 – filtr omezí čtení na 1 % dat díky shodě v ORDER BY.

Vysoké full scan (Granules: 95000/98400) vyžaduje přepracování WHERE nebo klíče řazení. Ruční odhad: pro N řádků v tabulce 100M – čtení N × 8192 řádků.

Analýza reálného případu

Dotaz s 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;

Tabulka: ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). Problémy:

• Široký rozsah dat čte data za 2 roky.
• Použití uniqExact místo uniq pro úsporu prostředků.
• Použití PREWHERE pro rané filtrování.
• Přeorganizace ORDER BY na (event_date, user_id, event_id).

Porovnejte metriky před a po optimalizaci.

Přechod k monitorování

Denní analýza podle 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;

Nastavte upozornění na anomálie.

Seznam kontrol pro diagnostiku

• SYSTEM FLUSH LOGS
• system.processes – aktivní dotazy
• system.query_log – top podle zdrojů
• EXPLAIN indexes = 1 – plán čtení
• Kontrola WHERE v ORDER BY, PREWHERE
• uniqExact, JOIN, data – optimalizovat
• Oprava a porovnání metrik před/po

Co je důležité

• Používejte system.processes k okamžitému zastavení problematických dotazů.
• query_log s ProfileEvents poskytuje kompletní přehled o využití CPU, RAM i disku.
• log_comment je kritický pro multi-tenant prostředí.
• EXPLAIN indexes = 1 odhaluje neefektivní klíče řazení.
• Přejděte od jednorázové diagnostiky k denní analýze podle hash.

— Editorial Team