ClickHouse High CPU Usage: Finding the Culprit

A ClickHouse server pegged at 100% CPU is almost always one of four things: a heavy live query, a runaway mutation, a backlog of merges, or a query pattern that hammers the server with cheap-but-frequent requests. This article walks through the SQL queries to figure out which one it is, starting from the most expensive operations and ending at retrospective ProfileEvents analysis.

Live queries

The fastest first check. system.processes shows everything currently running:

SELECT
    elapsed,
    query_id,
    user,
    read_rows,
    memory_usage,
    query
FROM system.processes
WHERE is_initial_query
  AND elapsed > 2
ORDER BY elapsed DESC;

A single query running for tens of seconds at high concurrency will saturate cores by itself. Note the query_id for any suspect rows; you can correlate it with system.query_log later, and you can kill it with:

KILL QUERY WHERE query_id = '<id>';

KILL QUERY is cooperative. If the query is in an uninterruptible state (a tight aggregation kernel) it may take a while to actually exit.

Active merges

Background merges are CPU-bound work. A merge backlog on a fast-ingesting table will keep the merge thread pool full.

SELECT
    database,
    table,
    elapsed,
    round((elapsed * (1 / progress)) - elapsed, 2) AS eta_seconds,
    progress,
    is_mutation,
    formatReadableSize(total_size_bytes_compressed) AS size,
    formatReadableSize(memory_usage) AS mem
FROM system.merges
ORDER BY elapsed DESC;

is_mutation = 1 means a mutation is masquerading as a merge (mutations rewrite parts using the merge pipeline). If you see a merge with high elapsed and slow progress, check whether the underlying disk is saturated; CPU may be waiting on IO rather than crunching numbers.

The merge thread pool defaults to background_pool_size = 16 since 22.x. If system.merges consistently has 16 rows you are at capacity. Raising it helps only if there is spare CPU and disk bandwidth.

Pending mutations

Mutations are ALTER TABLE ... UPDATE/DELETE operations that rewrite parts. A stuck mutation can keep CPU pegged for hours.

SELECT
    database,
    table,
    substr(command, 1, 30) AS command,
    sum(parts_to_do) AS parts_to_do,
    anyIf(latest_fail_reason, latest_fail_reason != '') AS last_failure
FROM system.mutations
WHERE NOT is_done
GROUP BY database, table, command
ORDER BY parts_to_do DESC;

parts_to_do is the number of parts still needing the mutation applied. If it isn't shrinking and last_failure is empty, the mutation pool may be starved. If last_failure has a message, the mutation is retrying forever; usually you want to KILL MUTATION and fix the root cause.

Historical analysis with ProfileEvents

When CPU was high in the past, system.processes and system.merges are useless because they only show now. system.query_log carries a ProfileEvents map per query that includes CPU counters in microseconds:

SELECT
    normalizedQueryHash(query) AS hash,
    current_database,
    sum(ProfileEvents['UserTimeMicroseconds']) / 1000 AS user_cpu_ms,
    sum(ProfileEvents['SystemTimeMicroseconds']) / 1000 AS sys_cpu_ms,
    count() AS executions,
    sum(query_duration_ms) AS wall_ms,
    user_cpu_ms / wall_ms AS cpu_per_sec,
    argMax(query, ProfileEvents['UserTimeMicroseconds']) AS heaviest_query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today()
GROUP BY hash, current_database
ORDER BY user_cpu_ms DESC
LIMIT 10;

UserTimeMicroseconds is the userspace CPU time attributed to the query, summed across all threads. cpu_per_sec (user CPU divided by wall time) tells you how many cores the query was using on average. A value of 8 means the query saturated 8 cores for the duration it ran.

To find one-off expensive executions rather than aggregated patterns:

SELECT
    event_time,
    query_id,
    user,
    ProfileEvents['UserTimeMicroseconds'] / 1000 AS user_cpu_ms,
    query_duration_ms,
    memory_usage,
    substring(query, 1, 120) AS query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
ORDER BY ProfileEvents['UserTimeMicroseconds'] DESC
LIMIT 20;

Cross-checking with OS metrics

If system.processes is empty, system.merges is empty, system.mutations has nothing pending, and ProfileEvents over the suspect window are unremarkable, the CPU is being used outside of normal query and merge paths. Common culprits:

• ZooKeeper traffic for a wide replicated cluster (visible in system.events as ZooKeeperUserExceptions, ZooKeeperTransactions).
• Background data movement between disks (system.events.DiskWritesPerformed, DiskReadsPerformed).
• HTTP handler overhead from very high QPS of cheap queries (look at system.query_log row count, not duration).

The OS-level top -H -p $(pidof clickhouse-server) shows thread names that correspond to ClickHouse worker pools (MergeMutate, QueryPipeline, BackgroundProc), which usually points you back to one of the system tables above.

Profiling a specific query

When you have isolated a slow query but cannot see why it is heavy, enable trace-based profiling for that session:

SET query_profiler_real_time_period_ns = 10000000;
SET query_profiler_cpu_time_period_ns = 10000000;
-- run the query
SELECT trace_type, count(), arrayStringConcat(arrayMap(x -> demangle(addressToSymbol(x)), trace), '\n')
FROM system.trace_log
WHERE query_id = '<query_id>'
GROUP BY trace_type, trace
ORDER BY count() DESC
LIMIT 20;

This gives you a stack-sample profile of the actual code path the query took, which is the level you need to identify expensive expressions, aggregate functions, or join algorithms.

Common Pitfalls

• Looking only at system.processes and concluding nothing is wrong because no query is running right now. The CPU has memory; check query_log for the last 5 minutes.
• Killing a query that is in a tight kernel and waiting for it to die in seconds. Sometimes it takes a minute, especially during a heavy aggregation.
• Raising background_pool_size without spare CPU. You just spread the same work over more threads and increase context switching.
• Assuming mutations are cheap. A mutation on a 10 TB table rewrites every affected part. It will saturate the merge pool until it is done.

Frequently Asked Questions

Q: How do I find the single query using the most CPU right now? A: SELECT * FROM system.processes ORDER BY elapsed DESC LIMIT 5; then cross-reference the query_id in system.query_log for full context once it finishes.

Q: My CPU is high but no queries are listed. What now? A: Check system.merges and system.mutations. Background work doesn't show up in system.processes.

Q: How do I see per-query CPU time after the query has finished? A: system.query_log.ProfileEvents['UserTimeMicroseconds'] and ['SystemTimeMicroseconds']. Both are in microseconds and summed across all threads.

Q: Can I cap CPU per user? A: Use max_threads on the user profile to limit how many cores a single query can use. There's no hard CPU quota, but max_concurrent_queries_for_user and max_threads together act as a soft cap.

Q: Are mutations counted in system.merges? A: Yes. They show up with is_mutation = 1. The mutation queue itself is system.mutations.