ClickHouse Thread Pool Configuration and Tuning

ClickHouse runs work across several specialized thread pools: a global query pool, a merges and mutations pool, a schedule pool for periodic tasks, separate pools for fetches and moves, and various connection pools. Each has its own default size and tuning trade-offs. Default values target large servers, so small hosts need lower values and very large servers may need higher ones. This article covers the pools, their defaults, and SQL queries for inspecting current usage.

Thread pool landscape

There is also a global thread pool sized by max_thread_pool_size (default 10000) that all per-query workers draw from.

Query parallelism: max_threads

max_threads controls how many threads a single query uses. It is a user-level setting, not a server setting.

<profiles>
    <default>
        <max_threads>0</max_threads>
    </default>
</profiles>

0 means auto: ClickHouse sets it to the number of physical CPU cores. Lower it for high-concurrency workloads where many queries should fit at once; raise it for low-concurrency analytical workloads where each query should use the whole machine.

SET max_threads = 8;
SELECT count() FROM events WHERE event_date = today();

Configuring background pools

Server-wide pool sizes live in the main config or a drop-in:

<clickhouse>
    <background_pool_size>16</background_pool_size>
    <background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
    <background_schedule_pool_size>128</background_schedule_pool_size>
    <background_fetches_pool_size>8</background_fetches_pool_size>
    <background_move_pool_size>8</background_move_pool_size>
    <background_buffer_flush_schedule_pool_size>16</background_buffer_flush_schedule_pool_size>
</clickhouse>

Rules of thumb:

• Stay at or below CPU core count for background_pool_size on hosts where queries also need CPU.
• Raise background_schedule_pool_size on clusters with many tables (it processes one entry per replicated table per cycle).
• background_merges_mutations_concurrency_ratio defines how many merge tasks each thread handles; the effective concurrency is background_pool_size * ratio.

Connection pools

Distributed queries reuse pooled connections to other shards.

<clickhouse>
    <distributed_connections_pool_size>1024</distributed_connections_pool_size>
    <connection_pool_max_wait_ms>5000</connection_pool_max_wait_ms>
</clickhouse>

On clusters with hundreds of shards and high fan-out, raise this pool. On small clusters the default is fine.

Inspecting active threads

Show all pool-related settings and their effective values:

SELECT name, value
FROM system.settings
WHERE name LIKE '%pool%';

Count threads per running query:

SELECT query, length(thread_ids) AS threads_count
FROM system.processes
ORDER BY threads_count DESC;

Background pool task counts:

SELECT metric, value
FROM system.metrics
WHERE metric LIKE 'Background%';

Look for BackgroundPoolTask, BackgroundFetchesPoolTask, BackgroundSchedulePoolTask, BackgroundMovePoolTask, and BackgroundBufferFlushSchedulePoolTask. A value at or near the configured pool size means the pool is saturated.

Thread-related asynchronous metrics:

SELECT *
FROM system.asynchronous_metrics
WHERE lower(metric) LIKE '%thread%'
ORDER BY metric ASC;

Global pool status:

SELECT *
FROM system.metrics
WHERE lower(metric) LIKE '%thread%';

Capturing stack traces

When pool saturation needs debugging, dump live stacks for threads holding pool slots:

SET allow_introspection_functions = 1;

WITH arrayMap(x -> demangle(addressToSymbol(x)), trace) AS frames
SELECT
    thread_id,
    query_id,
    arrayStringConcat(frames, '\n') AS stack
FROM system.stack_trace
WHERE stack ILIKE '%Pool%'
FORMAT Vertical;

This requires allow_introspection_functions = 1 and debug symbols installed for the most useful output.

Common Pitfalls

• Raising max_threads beyond core count expecting faster queries. Beyond the core count, threads contend rather than parallelize.
• Setting background_pool_size very high on hosts with concurrent queries. Background merges then starve interactive queries of CPU.
• Tuning background_schedule_pool_size too low on clusters with hundreds of replicated tables. The pool processes one task per table per tick; under-sizing causes replication queue lag.
• Treating BackgroundPoolTask always at zero as healthy. It just means no merges are running, which can also mean inserts are not happening.
• Forgetting that max_thread_pool_size is a hard global cap. If user-level max_threads times concurrent queries exceeds it, queries queue waiting for a thread.

Frequently Asked Questions

Q: What is the difference between max_threads and max_thread_pool_size? A: max_threads is per query; max_thread_pool_size is the global thread budget. The global cap defaults to 10000 and rarely needs changing.

Q: How do I know if merges are falling behind? A: Compare BackgroundPoolTask to background_pool_size over time, and watch parts_to_check and active parts per partition in system.parts. Sustained saturation plus growing part counts indicates the pool is undersized.

Q: Should max_threads match CPU cores? A: For low-concurrency analytical workloads, yes. For dashboards with many simultaneous users, lower it so concurrent queries fit on the cores you have.

Q: Does raising background_fetches_pool_size speed up replica recovery? A: Yes, up to a point. Beyond the number of network sources providing parts, additional fetch threads sit idle.

Q: How do I tune Kafka engine concurrency? A: background_message_broker_schedule_pool_size governs scheduling for the Kafka, RabbitMQ, and NATS engines. Raise it when you have many such tables and they fall behind.