Diagnósticos de CPU al 80% en ClickHouse

Diagnóstico de carga de CPU en ClickHouse: cómo encontrar consultas problemáticas

Cuando el uso de CPU alcanza el 80% y las consultas empiezan a ralentizarse, comienza revisando la tabla system.processes. Muestra las consultas activas con métricas como tiempo transcurrido, uso de memoria, bytes leídos y filas leídas.

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

Detén una consulta problemática con KILL QUERY WHERE query_id = 'xxx';. Para una terminación síncrona, usa KILL QUERY WHERE query_id = 'xxx' SYNC;. Esto elimina rápidamente la carga pico sin esperar.

Análisis de consultas completadas en query_log

La tabla system.query_log captura métricas de todas las consultas finalizadas: duración, CPU, memoria y volumen de datos leídos. Antes de analizar, ejecuta SYSTEM FLUSH LOGS; para asegurarte de tener datos actualizados.

Consulta básica para encontrar las 20 consultas más intensivas en recursos durante la última hora:

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;

Filtra type = 'QueryFinish' para incluir solo consultas completadas con métricas completas. Excluye ExceptionWhileProcessing si es necesario. ProfileEvents proporciona contadores detallados como SelectedMarks, SelectedParts y MergedRows. Valores altos en SelectedMarks indican cláusulas WHERE ineficientes.

Para ordenar por tipo de recurso:

• Consultas lentas: ORDER BY duration_sec DESC
• Alto uso de CPU: ORDER BY total_cpu_sec DESC
• Alto uso de RAM: ORDER BY memory_usage DESC
• Alta I/O en disco: ORDER BY read_bytes DESC

Limita tu ventana de análisis y usa LIMIT para evitar sobrecargar la propia tabla MergeTree.

Identificación de fuentes de carga

Con múltiples clientes, agrupa por user, client_hostname o 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;

Para herramientas de orquestación como Airflow o Dagster, agrega log_comment en tu código cliente:

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

Analiza por etiqueta de comentario:

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: Usa comentarios SQL como -- source:sales_dashboard y búscalos con query ILIKE '%source:sales_dashboard%'.

Verificación de planes de ejecución

Usa EXPLAIN indexes = 1 para evaluar la eficiencia de los índices sin ejecutar la consulta. Ejemplo: Granules: 10/1043 — el filtro reduce los datos escaneados a solo un 1% gracias a que coincide con la cláusula ORDER BY.

Un alto escaneo completo (por ejemplo, Granules: 95000/98400) indica que debes reestructurar la condición WHERE o la clave de ordenación. Estimación manual: para N filas en una tabla de 100 millones, espera leer N × 8192 filas.

Estudio de caso real

Consulta causando 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;

Tabla: ReplacingMergeTree, PARTITION BY toYYYYMM(event_date), ORDER BY (event_id, user_id, event_date). Problemas:

• Escaneo de rango de fechas amplio: 2 años de datos.
• Uso de uniqExact en lugar de uniq — más costoso en recursos.
• Falta de PREWHERE para filtrado temprano.
• Orden de clasificación deficiente: reordénalo como (event_date, user_id, event_id).

Compara las métricas de rendimiento antes y después de la optimización.

Transición hacia monitoreo continuo

Análisis diario por 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;

Configura alertas para anomalías.

Lista de verificación diagnóstica

• SYSTEM FLUSH LOGS
• Revisa system.processes — consultas activas
• Analiza system.query_log — consumidores de recursos
• Ejecuta EXPLAIN indexes = 1 — plan de ejecución
• Valida condiciones WHERE y claves ORDER BY
• Optimiza uniqExact, JOIN y rangos de fecha
• Aplica correcciones y mide resultados antes/después

Conclusiones clave

• Usa system.processes para matar de inmediato consultas problemáticas.
• query_log con ProfileEvents ofrece una visión completa del uso de CPU, RAM y disco.
• log_comment es esencial en entornos multiinquilino.
• EXPLAIN indexes = 1 revela claves de ordenación ineficientes.
• Cambia de depuración puntual a análisis diario basado en hash de consulta.

— Editorial Team