CPU 80 % Diagnose in ClickHouse

CPU-Last in ClickHouse diagnostizieren: Problematische Abfragen finden

Wenn die CPU-Auslastung bei 80 % liegt und Abfragen langsamer werden, beginnen Sie mit der Tabelle system.processes. Sie zeigt aktive Abfragen mit Metriken wie Laufzeit, Speichernutzung, gelesene Bytes und gelesene Zeilen.

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

Beenden Sie eine problematische Abfrage mit KILL QUERY WHERE query_id = 'xxx';. Für eine synchronisierte Beendigung verwenden Sie KILL QUERY WHERE query_id = 'xxx' SYNC;. So beseitigen Sie Spitzenlast schnell, ohne zu warten.

Analyse abgeschlossener Abfragen im query_log

Die Tabelle system.query_log erfasst Metriken für alle abgeschlossenen Abfragen: Dauer, CPU, Speicher und Datenmenge. Vor der Analyse führen Sie SYSTEM FLUSH LOGS; aus, um sicherzustellen, dass die Daten aktuell sind.

Einfache Abfrage zur Suche nach den 20 ressourcenintensivsten Abfragen der letzten Stunde:

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;

Filtern Sie type = 'QueryFinish', um nur abgeschlossene Abfragen mit vollständigen Metriken einzubeziehen. Falls nötig, können Sie ExceptionWhileProcessing ausschließen. ProfileEvents liefert detaillierte Zähler wie SelectedMarks, SelectedParts und MergedRows. Hohe Werte bei SelectedMarks deuten auf ineffiziente WHERE-Bedingungen hin.

Um nach Ressourcentyp zu sortieren:

• Langsame Abfragen: ORDER BY duration_sec DESC
• Hohe CPU-Nutzung: ORDER BY total_cpu_sec DESC
• Hoher RAM-Verbrauch: ORDER BY memory_usage DESC
• Hohe Festplatten-I/O: ORDER BY read_bytes DESC

Beschränken Sie Ihren Analysezeitraum und nutzen Sie LIMIT, um die MergeTree-Tabelle nicht zu überlasten.

Lastquellen identifizieren

Bei mehreren Clients gruppieren Sie nach user, client_hostname oder 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;

Für Orchestrierungstools wie Airflow oder Dagster fügen Sie log_comment in Ihren Client-Code ein:

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

Analysieren Sie nach Kommentar-Tag:

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;

Alternativ: Verwenden Sie SQL-Kommentare wie -- source:sales_dashboard und suchen mit query ILIKE '%source:sales_dashboard%'.

Ausführungspläne prüfen

Nutzen Sie EXPLAIN indexes = 1, um die Index-Effizienz ohne Ausführung der Abfrage zu bewerten. Beispiel: Granules: 10/1043 – der Filter reduziert die zu scannenden Daten auf nur 1 % dank Übereinstimmung mit der ORDER BY-Klausel.

Ein hoher Vollscans (z. B. Granules: 95000/98400) bedeutet, dass Sie die WHERE-Bedingung oder den Sortierschlüssel überarbeiten müssen. Manuelle Schätzung: Bei N Zeilen in einer Tabelle mit 100 Mio. Zeilen erwarten Sie etwa N × 8192 gelesene Zeilen.

Praxisbeispiel aus der Realität

Abfrage mit CPU-Auslastung >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;

Tabelle: ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). Probleme:

• Weitreichender Datumsbereich scannt zwei Jahre Daten.
• Verwendung von uniqExact statt uniq – ressourcenintensiver.
• Fehlende PREWHERE-Bedingung für frühe Filterung.
• Schlechter Sortierreihenfolge: Umordnung zu (event_date, user_id, event_id).

Vergleichen Sie die Leistungsmetriken vor und nach der Optimierung.

Hin zum Monitoring

Tägliche Analyse nach 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;

Setzen Sie Warnungen für Anomalien.

Diagnose-Checkliste

• SYSTEM FLUSH LOGS
• Überprüfen Sie system.processes – aktive Abfragen
• Prüfen Sie system.query_log – wichtigste Ressourcenverbraucher
• Führen Sie EXPLAIN indexes = 1 aus – Ausführungsplan
• Validieren Sie WHERE-Bedingungen und ORDER BY-Schlüssel
• Optimieren Sie uniqExact, JOIN und Datumsbereiche
• Implementieren Sie Korrekturen und messen Sie vorher/nachher

Wichtige Erkenntnisse

• Nutzen Sie system.processes, um problematische Abfragen sofort zu beenden.
• query_log mit ProfileEvents bietet einen umfassenden Überblick über CPU, RAM und Festplatten-Nutzung.
• log_comment ist essenziell in Multi-Tenant-Umgebungen.
• EXPLAIN indexes = 1 offenbart ineffiziente Sortierschlüssel.
• Wechseln Sie von einmaliger Debugging-Arbeit hin zu täglicher Analyse basierend auf Abfrage-Hash.

— Editorial Team