CPU 80% Diagnostics in ClickHouse

Diagnosing CPU Load in ClickHouse: Finding Problematic Queries

When CPU usage hits 80% and queries start slowing down, begin with the system.processes table. It shows active queries with metrics like elapsed time, memory usage, read bytes, and 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;

Stop a problematic query using KILL QUERY WHERE query_id = 'xxx';. For synchronous termination, use KILL QUERY WHERE query_id = 'xxx' SYNC;. This quickly clears peak load without waiting.

Analyzing Completed Queries in query_log

The system.query_log table captures metrics for all finished queries: duration, CPU, memory, and data read volume. Before analyzing, run SYSTEM FLUSH LOGS; to ensure up-to-date data.

Basic query to find the top 20 resource-heavy queries over the past hour:

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;

Filter type = 'QueryFinish' to include only completed queries with full metrics. Exclude ExceptionWhileProcessing if needed. ProfileEvents provide detailed counters like SelectedMarks, SelectedParts, and MergedRows. High SelectedMarks values indicate inefficient WHERE clauses.

To sort by resource type:

• Slow queries: ORDER BY duration_sec DESC
• High CPU: ORDER BY total_cpu_sec DESC
• High RAM: ORDER BY memory_usage DESC
• High disk I/O: ORDER BY read_bytes DESC

Limit your analysis window and use LIMIT to avoid overloading the MergeTree table itself.

Identifying Load Sources

With multiple clients, group by user, client_hostname, or 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;

For orchestration tools like Airflow or Dagster, add log_comment in your client code:

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

Analyze by comment 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;

Alternative: Use SQL comments like -- source:sales_dashboard and search with query ILIKE '%source:sales_dashboard%'.

Checking Execution Plans

Use EXPLAIN indexes = 1 to evaluate index efficiency without running the query. Example: Granules: 10/1043 — the filter reduces data scanned to just 1% due to matching the ORDER BY clause.

A high full scan (e.g., Granules: 95000/98400) means you need to rework the WHERE condition or sorting key. Manual estimation: for N rows in a 100M-row table, expect reading N × 8192 rows.

Real-World Case Study

Query causing 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). Issues:

• Wide date range scans 2 years of data.
• Using uniqExact instead of uniq — more resource-intensive.
• Missing PREWHERE for early filtering.
• Poor sort order: reorder to (event_date, user_id, event_id).

Compare performance metrics before and after optimization.

Moving Toward Monitoring

Daily analytics by 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;

Set up alerts for anomalies.

Diagnostic Checklist

• SYSTEM FLUSH LOGS
• Check system.processes — active queries
• Review system.query_log — top resource consumers
• Run EXPLAIN indexes = 1 — execution plan
• Validate WHERE conditions and ORDER BY keys
• Optimize uniqExact, JOIN, and date ranges
• Apply fixes and measure before/after results

Key Takeaways

• Use system.processes to immediately kill problematic queries.
• query_log with ProfileEvents gives a complete view of CPU, RAM, and disk usage.
• log_comment is essential in multi-tenant environments.
• EXPLAIN indexes = 1 reveals inefficient sort keys.
• Shift from one-time debugging to daily analytics based on query hash.

— Editorial Team