Analytics PipelineClickHouse & Materialized Views
Guide Real-Time Data Ingestion Pipeline Implementation

Sizing num_layers for Buffer Parallelism

The num_layers argument in a Buffer engine signature decides how many independent flush lanes the table maintains, and that single integer governs how much concurrent insert traffic the buffer can absorb without serializing writers behind a lock. Set it too low and high-concurrency ingestion contends on a handful of layers; set it too high and each idle lane multiplies memory reservations and background flush-pool scheduling for no throughput gain. This guide sizes num_layers against CPU cores and real insert concurrency, then measures whether the lanes are actually flushing in parallel.

Each layer is a self-contained in-memory partition of the buffer with its own row, byte, and time counters and its own lock. An incoming INSERT is routed to one layer, appends under that layer’s lock, and returns; the layer flushes on its own thresholds independently of the others. Parallelism is therefore bounded by how many layers exist and how evenly inserts spread across them — which is exactly what num_layers and your writer count jointly control.

Prerequisites

How inserts fan out across layers

When a writer inserts into a Buffer table, ClickHouse picks a layer and appends the block under that layer’s mutex. Concurrent inserts that land on different layers proceed in true parallel; two inserts that hash to the same layer serialize behind its lock. With N layers and W evenly balanced concurrent writers, effective write parallelism is min(N, W) — adding layers beyond the writer count buys nothing, and adding writers beyond the layer count reintroduces contention.

Concurrent inserts fanning across independent Buffer layers into a shared flush pool Concurrent writers route inserts to num_layers independent lanes, each with its own lock and counters. Inserts on different layers run in parallel; same-layer inserts serialize on the lane's lock. A shared background flush pool drains ready layers into one destination MergeTree. Effective write parallelism is the smaller of layer count and writer count; flush parallelism is bounded by layers and pool size. WRITERS num_layers lanes · own lock + counters DESTINATION writer A writer B writer C writer D layer 1 lock ▸ counters layer 2 lock ▸ counters layer 3 lock ▸ counters layer 4 lock ▸ counters same layer → lock wait flush pool background schedule threads events_raw effective write parallelism = min(num_layers, writer count)

Step 1 — Read the core count and current concurrency

Parallelism is a fraction of hardware. Pull the physical cores and the live insert concurrency the buffer already sees.

sql
SELECT metric, value
FROM system.asynchronous_metrics
WHERE metric IN ('CPUCores', 'OSThreadsTotal');

-- Peak concurrent inserts into the buffer over the last hour.
SELECT max(concurrent) AS peak_concurrent_inserts
FROM (
    SELECT event_time, count() AS concurrent
    FROM system.query_log
    WHERE query_kind = 'Insert' AND type = 'QueryStart'
      AND has(tables, 'analytics.events_buffer')
      AND event_time > now() - INTERVAL 1 HOUR
    GROUP BY event_time
);

Expected: CPUCores reports the node’s physical cores, and peak_concurrent_inserts is a small multiple of your writer count. These two numbers bracket the sensible range for num_layers.

Step 2 — Choose num_layers

Pick the value that covers your real insert concurrency without exceeding what the CPU can flush. Two rules bound it:

  • Lower bound — writer concurrency. num_layers should be at least your peak concurrent insert count so writers rarely collide on a lane. Fewer layers than writers guarantees lock contention at peak.
  • Upper bound — CPU cores. Flushes are CPU work (serialization, compression) handled by the background flush pool. Setting num_layers far above the core count creates lanes that can never flush concurrently and inflates baseline memory. Keep it at or below the core count, typically 8–32 on a 16–32 core node.
sql
-- Recreate with a layer count matched to concurrency and cores.
DROP TABLE IF EXISTS analytics.events_buffer;

CREATE TABLE analytics.events_buffer AS analytics.events_raw
ENGINE = Buffer(
    'analytics', 'events_raw',
    16,            -- num_layers: >= peak writers, <= CPU cores
    10, 45,        -- min/max_time (s)
    100000, 1000000,       -- min/max_rows
    10485760, 268435456    -- min/max_bytes
);

When peak concurrency and core count disagree, favor the core count as the ceiling — a lane that cannot get a flush thread is pure overhead. The 16 here suits a 16-core node fronting 8–16 concurrent writers.

Step 3 — Match the background flush pool

Layers can only flush as fast as the background pool has threads. If num_layers exceeds background_buffer_flush_schedule_pool_size, ready layers queue for a flush thread and drain latency climbs even though the lanes themselves are idle.

sql
SELECT name, value
FROM system.server_settings
WHERE name = 'background_buffer_flush_schedule_pool_size';

Keep the pool size at least equal to num_layers (default is 16). If you raise num_layers to 32, raise the pool to match in the server config and restart, or the extra lanes serialize at flush time regardless of how well inserts spread.

Step 4 — Confirm inserts spread evenly

Uneven fan-out defeats the whole point: if every writer routes to the same lane, extra layers sit empty. Because ClickHouse assigns a layer per insert (round-robin under the hood), many small writers spread better than a few large ones. Confirm the destination is receiving parts from multiple lanes by watching part arrival concurrency after a load test.

sql
-- Distinct flush timestamps in a short window ≈ how many lanes drained in parallel.
SELECT
    toStartOfSecond(modification_time) AS sec,
    count()                            AS parts_that_second
FROM system.parts
WHERE database = 'analytics' AND table = 'events_raw'
  AND modification_time > now() - INTERVAL 2 MINUTE
GROUP BY sec
HAVING parts_that_second > 1
ORDER BY sec DESC;

Seeing several parts land in the same second is direct evidence that multiple lanes flushed concurrently. If parts only ever arrive one at a time, either the writers are serializing on a single lane or the flush pool is the bottleneck.

Verification

Measure flush parallelism directly through the background pool’s live occupancy and queue depth.

sql
SELECT metric, value
FROM system.metrics
WHERE metric IN (
    'BackgroundBufferFlushSchedulePoolTask',   -- flushes running now
    'BackgroundBufferFlushSchedulePoolSize'    -- pool capacity
);

Under sustained load, ...PoolTask should climb toward your num_layers value but stay under ...PoolSize. If it pins at the pool size, the flush pool — not the layer count — is the constraint, and raising num_layers will not help until the pool grows. Cross-check that overall memory stayed within budget with the counters in tracking memory pressure with system metrics.

Gotchas & edge cases

  • Layers multiply the per-layer thresholds. Every threshold in the signature is per layer, so raising num_layers raises worst-case resident memory to num_layers × max_bytes and worst-case resident rows to num_layers × max_rows. Re-derive the byte ceiling whenever you change the layer count — the math is worked through in tuning buffer table flush thresholds.
  • A single writer cannot use many lanes at once. num_layers parallelizes distinct concurrent inserts, not one big insert. A single-threaded writer sees no benefit past one layer; concurrency has to come from the client, which is what the semaphore-bounded pattern in using Python asyncio for concurrent ClickHouse inserts supplies.
  • num_layers = 1 serializes every writer. A single lane forces all inserts through one lock, turning concurrent writers into a queue. It also defeats parallel flushing entirely — the whole table drains on one thread. Never run production ingestion on a one-layer buffer.
  • Over-provisioned layers hide behind healthy metrics. Sixty-four layers on a 16-core node will not error; they just waste memory and leave most lanes perpetually below their flush floor, which surfaces later as unexpected merge lag on the destination. Diagnose that downstream symptom with diagnosing background merge lag and walk it back to an oversized layer count.

Up: Async Processing & Buffer Tables