A query is slow while CPU is free or one query takes all CPU

A query runs slowly, but the server has free CPU capacity. Another query uses all available CPU cores and slows down other queries.

Both problems can have the same cause: query parallelism.

ClickHouse executes query work in parallel. The max_threads setting controls the maximum number of query-processing threads for many execution stages. Its default value is 0, which enables automatic selection.

This default works well for many workloads. However, it does not guarantee the best performance for every query or workload.

Consider two production cases.

Case 1: A large query uses too few threads.

A query reads and aggregates a large table. The server has spare CPU capacity, but the query finishes slowly. Its execution pipeline may use fewer threads than expected.

Increasing max_threads may improve throughput if the query has enough parallel work.

Case 2: One query takes all available CPU.

A large query starts with several other queries. It uses many threads and competes for CPU time. The other queries become slower.

Reducing max_threads can limit the query's parallelism. This can improve response times for the rest of the workload.

Do not change this setting before you inspect the query. A slow query may also read too much data, wait for storage, or perform expensive operations. Check the query plan and resource usage first.

Why it happens

ClickHouse divides query execution into processing stages. These stages can process data in parallel when the execution plan allows it.

The max_threads setting limits the number of threads used by many query-processing stages. A higher value can allow more parallel work. It can also increase CPU and memory pressure.

The default value is 0. ClickHouse selects the thread limit automatically based on the CPU resources available to the server. The exact behavior depends on the execution stage and server configuration.

A thread limit does not mean that every query uses that many threads. The actual number depends on several factors:

• The query plan and available parallel work.
• The number of data parts and streams to process.
• The storage system and its read performance.
• The amount of data that each stage must process.
• Other execution limits and resource constraints.

For example, a query may read data from several parts in parallel. However, a later aggregation stage may have less parallel work available. Raising max_threads cannot remove that limit.

The max_streams_to_max_threads_ratio setting affects how ClickHouse relates data streams to the thread limit. Its default is 1. Increasing it does not directly increase the CPU thread limit.

Other settings control parallelism for specific operations. max_insert_threads controls parallel threads for supported insert operations. max_final_threads controls parallelism for supported FINAL processing.

These settings have different purposes. Change the setting that matches the slow operation.

Remember that more threads do not always mean a faster query. Threads compete for CPU, memory bandwidth, storage bandwidth, and other shared resources.

How to confirm

Start with a slow query. Collect its query ID, duration, thread information, and profile events. Then inspect its execution pipeline.

Step 1: Find the slow query

Run this query to find recent completed queries. Adjust the time range and duration threshold for your workload.

SELECT
    event_time,
    query_id,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    thread_ids,
    ProfileEvents
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

The query returns completed queries from the last hour. It includes the recorded thread IDs and profile events.

Check these columns:

• query_duration_ms shows the query duration in milliseconds.
• read_rows shows the number of rows read.
• read_bytes shows the number of bytes read.
• memory_usage shows the recorded memory usage.
• thread_ids lists thread IDs associated with the query log entry.
• ProfileEvents contains counters for events recorded during query execution.

The thread_ids column does not tell you how many threads ran simultaneously. A query can use different threads at different times.

ProfileEvents is a map of event names and values. Inspect its contents for CPU and I/O information. The exact events depend on the ClickHouse version and execution path.

For example, inspect the event names recorded for one query.

SELECT
    query_id,
    ProfileEvents
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_id = 'your-query-id';

Replace your-query-id with the actual query ID. Check that the query ID identifies the execution you want to investigate.

Look for events that help explain CPU time, rows read, and I/O activity. Do not assume that a high thread count proves a CPU bottleneck.

Step 2: Inspect the execution pipeline

Use EXPLAIN PIPELINE to see the processors and parallel streams in a query plan.

Run the same query that you want to investigate, or use a representative query.

EXPLAIN PIPELINE
SELECT
    customer_id,
    count()
FROM events
GROUP BY customer_id;

Replace events and customer_id with objects from your workload.

Inspect the pipeline output. Look for processor stages with multiple parallel streams. Compare the expected parallelism with the actual pipeline structure.

The pipeline shows the planned processor structure. It does not prove that all streams remain busy during execution.

Step 3: Check the effective settings

Read the effective values before you change anything.

SELECT
    name,
    value,
    changed,
    description
FROM system.settings
WHERE name IN
(
    'max_threads',
    'max_final_threads',
    'max_insert_threads',
    'max_streams_to_max_threads_ratio'
)
ORDER BY name;

This query shows the current session's settings and their effective values. A value of 0 can mean automatic selection, not zero threads.

Check the server settings as well when you need to understand the server-level defaults. The available columns can differ by version, so inspect system.server_settings on your target server.

SELECT *
FROM system.server_settings
WHERE name IN
(
    'max_threads',
    'max_final_threads',
    'max_insert_threads',
    'max_streams_to_max_threads_ratio'
);

Use system.settings to check the current query context. Use system.server_settings to inspect server-level settings where they are exposed. Compare these values with the defaults for your installed release.

The settings

The following defaults come from ClickHouse master at commit 9f79cc1, dated 30 October 2026. Verify them against the final target release before publication.

These settings can be applied through a settings profile, a session-level SET statement, or a query-level SETTINGS clause. They do not require a server restart when changed through these methods.

A settings profile can provide a consistent policy for users or workloads. Session settings apply to the current session. Query settings apply to one query.

Choose the narrowest scope that solves the problem. This reduces the risk of changing unrelated workloads.

How to fix it

Use a small change first. Measure the result before you increase or decrease the thread limit again.

Fix 1: Lower max_threads for high concurrency

Use this change when large queries compete for CPU with other queries.

For example, a server has 16 CPU cores. Several users run analytical queries at the same time. Each query can use many threads, so the workload can create heavy CPU contention.

Start by limiting each query to four threads. This is a test value, not a universal recommendation.

Option A: Apply the setting to one query.

SELECT
    customer_id,
    count()
FROM events
GROUP BY customer_id
SETTINGS max_threads = 4;

This change limits the query without changing other queries in the session.

Option B: Apply the setting to the current session.

SET max_threads = 4;

Run your subsequent queries in the same session. The setting does not automatically apply to other sessions.

Option C: Apply the setting through a settings profile.

Use a profile when you want to apply a consistent thread limit to a group of users.

Add the following setting to the appropriate profile in users.xml. Merge it with your existing configuration instead of replacing the entire file.

<clickhouse>
    <profiles>
        <analytical_users>
            <max_threads>4</max_threads>
        </analytical_users>
    </profiles>
</clickhouse>

Assign the profile to the intended users through your existing user configuration. Confirm that the users receive the profile and that their effective settings show the expected value.

Use a profile-specific limit when the workload has predictable concurrency requirements. Do not apply a low limit to every user without testing their queries.

Fix 2: Raise max_threads for a large query

Use this change when a large query has spare CPU capacity and enough parallel work.

For example, a reporting query scans and aggregates a large table. The execution pipeline can process more data in parallel, but the current thread limit restricts that work.

Test a value of 16 on a server with sufficient CPU capacity. Compare it with the automatic default and your current effective setting.

SELECT
    customer_id,
    sum(amount)
FROM events
GROUP BY customer_id
SETTINGS max_threads = 16;

This change allows the query to use more threads in supported stages. It does not guarantee that the query will use all 16 threads or finish faster.

Test this setting with representative data. Monitor CPU utilization, memory usage, query duration, and concurrent workload performance.

If the query becomes faster without affecting other workloads, consider using the value for that query or workload.

Do not increase the global limit simply because one query is slow. First confirm that the query has enough parallel work and that CPU capacity is available.

Fix 3: Tune max_insert_threads for INSERT SELECT

An INSERT SELECT operation can read data, transform it, and write the result. Its performance depends on the execution pipeline and the available resources.

When the insert operation is the bottleneck, test max_insert_threads separately from max_threads.

For example, start with four insert threads.

INSERT INTO events_archive
SELECT *
FROM events
WHERE event_date < '2026-01-01'
SETTINGS max_insert_threads = 4;

Use this value as a starting point for testing. Increase it only when the insert operation can use more parallel work and the server has sufficient capacity.

Do not assume that this setting controls every stage of an INSERT SELECT query. Other stages can have separate parallelism limits.

Fix 4: Investigate FINAL processing

If the slow query uses FINAL, inspect its pipeline before changing max_final_threads.

For example, test the query with an explicit value of four.

SELECT *
FROM events
FINAL
SETTINGS max_final_threads = 4;

[VERIFY] Confirm the supported behavior and applicability of max_final_threads for your table engine and query on the target release.

Do not change this setting for queries that do not use the relevant FINAL processing.

Fix 5: Check the stream-to-thread ratio

The default max_streams_to_max_threads_ratio is 1 in the specified master revision.

Keep this value initially. Change it only when you have evidence that stream selection limits the execution pipeline.

How to verify the fix

Run the same query before and after the change. Use the same data, query parameters, and test conditions.

Compare these measurements:

• Query duration.
• Rows and bytes read.
• CPU utilization during execution.
• Memory usage.
• Number of concurrent queries.
• Performance of other queries during the test.

Use system.query_log to compare completed executions. Filter by query ID, query text, or a suitable time range.

SELECT
    event_time,
    query_id,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    thread_ids
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

Use the actual query IDs to identify the test executions. Avoid comparing runs with very different input sizes or background workloads.

Check EXPLAIN PIPELINE again if you need to confirm the planned parallel structure. A setting change may not alter every stage of the pipeline.

For a large query, success means shorter execution time without excessive resource use.

For a high-concurrency workload, success means better overall throughput or response times for competing queries. A single query may take longer after you lower its thread limit. That can be an acceptable trade-off.

Keep the change only when measurements show a clear improvement.

Risks and trade-offs

More threads can increase CPU contention. A high max_threads value can allow one query to consume more CPU time. Under concurrency, this can increase response times for other queries.

More threads do not guarantee better performance. Storage limits, data distribution, aggregation stages, and other bottlenecks can prevent a query from using additional parallelism effectively.

A lower limit can increase query duration. Queries that need substantial parallel processing may take longer when you reduce their thread limit.

Settings can interact. max_threads, max_insert_threads, max_final_threads, and max_streams_to_max_threads_ratio do not control identical operations. Change one relevant setting at a time.

Per-query limits do not guarantee a server-wide CPU budget. Many queries can each use their permitted threads at the same time. Monitor total workload behavior rather than relying on one query's limit.

Automatic defaults can change. Check system.settings and system.server_settings on your installed server. Do not assume that another ClickHouse release uses the same defaults.

Summary

• Use max_threads to control query parallelism.
• Lower max_threads when queries compete for CPU under high concurrency.
• Raise max_threads for a large query only when measurements support the change.
• Use max_insert_threads for applicable INSERT SELECT workloads.
• Compare query duration, CPU use, memory use, and concurrent query performance before keeping a change.

References

clickhouse documentation