Diagnostic CPU 80 % dans ClickHouse

Diagnostic de la charge CPU dans ClickHouse : repérer les requêtes problématiques

Quand la charge CPU atteint 80 % et que les requêtes ralentissent, commencez par la table system.processes. Elle affiche les requêtes actives avec des métriques comme le temps écoulé, l'utilisation mémoire, les octets lus et les lignes lues.

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

Interrompez une requête problématique avec KILL QUERY WHERE query_id = 'xxx';. Pour une interruption synchrone, utilisez KILL QUERY WHERE query_id = 'xxx' SYNC;. Cela supprime rapidement le pic de charge sans attendre.

Analyse des requêtes terminées dans query_log

La table system.query_log capture les métriques de toutes les requêtes terminées : durée, utilisation CPU, mémoire et volume de données lues. Avant toute analyse, exécutez SYSTEM FLUSH LOGS; pour garantir des données à jour.

Requête basique pour trouver les 20 requêtes les plus gourmandes au cours de la dernière heure :

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;

Filtrez type = 'QueryFinish' pour ne garder que les requêtes terminées avec des métriques complètes. Excluez ExceptionWhileProcessing si nécessaire. Les champs ProfileEvents fournissent des compteurs détaillés comme SelectedMarks, SelectedParts et MergedRows. Des valeurs élevées de SelectedMarks indiquent des clauses WHERE inefficaces.

Pour trier par type de ressource :

• Requêtes lentes : ORDER BY duration_sec DESC
• Haute charge CPU : ORDER BY total_cpu_sec DESC
• Haut usage RAM : ORDER BY memory_usage DESC
• Haut I/O disque : ORDER BY read_bytes DESC

Restreignez votre fenêtre d’analyse et utilisez LIMIT pour éviter de surcharger la table MergeTree elle-même.

Identifier les sources de charge

Avec plusieurs clients, regroupez par user, client_hostname ou 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;

Pour les outils d’orchestration comme Airflow ou Dagster, ajoutez log_comment dans votre code client :

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

Analysez par balise de commentaire :

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;

Alternative : utilisez des commentaires SQL comme -- source:sales_dashboard et recherchez avec query ILIKE '%source:sales_dashboard%'.

Vérifier les plans d’exécution

Utilisez EXPLAIN indexes = 1 pour évaluer l’efficacité des index sans exécuter la requête. Exemple : Granules : 10/1043 — le filtre réduit la quantité de données lues à seulement 1 % grâce à un alignement avec la clause ORDER BY.

Un grand scan complet (ex. Granules : 95000/98400) signifie qu’il faut revoir la condition WHERE ou la clé de tri. Estimation manuelle : pour N lignes dans une table de 100 millions de lignes, prévoyez la lecture de N × 8192 lignes.

Étude de cas réel

Requête causant une charge 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;

Table : ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). Problèmes :

• Large plage de dates : scan de 2 ans de données.
• Utilisation de uniqExact au lieu de uniq — plus coûteuse en ressources.
• Absence de PREWHERE pour un filtrage précoce.
• Ordre de tri déficient : réorganisez vers (event_date, user_id, event_id).

Comparez les métriques de performance avant et après optimisation.

Vers une surveillance proactive

Analyse quotidienne par 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;

Configurez des alertes pour détecter les anomalies.

Check-list diagnostic

• SYSTEM FLUSH LOGS
• Vérifiez system.processes — requêtes actives
• Revoyez system.query_log — consommateurs de ressources
• Exécutez EXPLAIN indexes = 1 — plan d’exécution
• Validez les conditions WHERE et les clés ORDER BY
• Optimisez uniqExact, JOIN et plages de dates
• Appliquez les corrections et mesurez les résultats avant/après

Points clés

• Utilisez system.processes pour tuer immédiatement les requêtes problématiques.
• query_log avec ProfileEvents offre une vue complète de l’utilisation CPU, RAM et disque.
• log_comment est essentiel dans les environnements multi-locataires.
• EXPLAIN indexes = 1 révèle les clés de tri inefficaces.
• Passez du débogage ponctuel à une analyse quotidienne basée sur le hachage de requête.

— Editorial Team