Quick Takeaway: Production analytical workloads on ClickHouse require a baseline sizing of at least 4 dedicated KVM vCPUs with host AVX2/AVX-512 passthrough, 16 GB of unswappable RAM, and PCIe 4.0 NVMe storage delivering $\ge 40{,}000$ 4K QD1 read IOPS to prevent vectorized SIMD pipeline stalls during MergeTree background mutations. Ingestion paths mandate a minimum 1 Gbps full-duplex uplink communicating over ClickHouse Native TCP (port 9000) tuned via TCP BBR to sustain sub-15ms p99 write and query latency. Applying strict cgroups v2 memory ceilings (memory.max) alongside an XFS noatime mount isolates query hash-table allocations and halts kernel OOM panics during high-cardinality aggregations.
Table of Contents
- ClickHouse Hardware Architecture: Vectorized Execution and Memory Hierarchy
- Linux Kernel Tuning: Disabling Transparent Huge Pages and Optimizing vm.dirty_ratio
- Deploying Production ClickHouse via Docker Compose with NVMe Mounts
- Memory Management: max_server_memory_usage and Merging Optimizations
- Network and User Security: TLS Configuration, Quotas, and IP Whitelisting
- Automated Backups with clickhouse-backup and S3 Disaster Recovery Runbook
- Frequently Asked Questions (FAQ)
ClickHouse Hardware Architecture: Vectorized Execution and Memory Hierarchy
Vectorized query processing is the architectural foundation of ClickHouse’s analytical performance. Traditional relational database management systems utilize the classical Volcano iterator model, processing data tuple-by-tuple through virtual function calls on abstract row iterators. At scale, this paradigm collapses under CPU pipeline stalls, branch mispredictions, and instruction cache thrashing. ClickHouse discards tuple-at-a-time iteration entirely. Data is processed in columnar blocks—typically batches of 8,192 to 65,536 elements—held as contiguous, typed memory buffers in PODArray structures. This design aligns execution with modern microprocessor architectures, transforming complex database operations into tight, uniform loops executed via Single Instruction, Multiple Data (SIMD) extensions.
When executing analytical queries on virtualized compute instances, understanding how SIMD execution interacts with CPU topologies, cache hierarchies, and physical memory buses determines whether an installation hits processing rates of hundreds of millions of rows per second or starves on memory-wait cycles.
+-------------------------------------------------------------------------+
| ClickHouse Block Processing (PODArray: contiguous memory, 64-byte align)|
+-------------------------------------------------------------------------+
| |
v v
[AVX2 / AVX-512 Registers] [L1 / L2 Data Cache Prefetch]
(ymm: 256-bit / zmm: 512-bit) (64-byte Cache Lines / ~1-4 ns)
| |
+-----------------------+------------------------+
|
v
[L3 Unified Cache (~12-15 ns)]
|
[NUMA Interconnect / UPI / Infinity Fabric]
|
[Physical DDR4/DDR5 Memory Bus]
(Memory Wall: 40-80 ns DRAM latency)
SIMD Instruction Utilization: SSE4.2, AVX2, and AVX-512 Vector Engines
ClickHouse relies on compiler vectorization and handwritten SIMD intrinsics to evaluate expressions, unpack compression blocks (LZ4, ZSTD), and execute aggregation primitives. Contiguous column buffers enable the CPU vector engine to load multiple scalar primitives into specialized vector registers simultaneously:
- SSE4.2 (128-bit
xmmregisters): Operates on two 64-bit integers or four 32-bit values per instruction cycle. Used as the architectural baseline for ClickHouse execution. - AVX2 (256-bit
ymmregisters): Processes four 64-bit or eight 32-bit values per cycle, doubling arithmetic throughput for filters, hashes (CityHash,MetroHash), and vectorized conditional evaluations (if-elseoperators mapped to bitwise blend masks). - AVX-512 (512-bit
zmmregisters): Evaluates eight 64-bit or sixteen 32-bit operations per clock cycle. Modern instruction variants—such as AVX-512F (Foundation), AVX-512BW (Byte and Word), and AVX-512DQ (Doubleword and Quadword)—significantly accelerate decompression algorithms and bitmap operations.
Vectorized primitives eliminate condition branches inside scan loops. For example, evaluating a predicate like WHERE metric_value > 500 against an array of 65,536 integers avoids branch predictor overhead entirely. ClickHouse executes an AVX-512 vectorized comparison (_mm512_cmpgt_epi32_mask), producing a 16-bit bitmask register per instruction that is subsequently used to populate position filter masks without conditional branching.
However, running high-throughput SIMD operations in cloud environments introduces a severe operational pitfall: frequency throttling and CPU oversubscription.
On older Intel Skylake and Cascade Lake hypervisors, broad 512-bit execution units activate secondary voltage planes, forcing the CPU core to downclock its base frequency by 15% to 25% (AVX license downclocking). This thermal downclocking penalizes concurrent non-vectorized system threads. Furthermore, if a hypervisor overcommits physical cores, a vCPU executing an intensive SIMD pipeline will be preempted mid-loop.
When deploying clickhouse on vps analytics workloads, CPU Steal Time (%st) must remain strictly at 0.0%. A hypervisor-level context switch flushing SIMD registers introduces measurable p99 latency spikes into analytical query streams. Running on dedicated compute instances with clean KVM virtualization, such as tropic.host, ensures modern hardware architectures like AMD EPYC (Zen 4 / Zen 5) and high-frequency Ryzen/Xeon platforms execute dual 256-bit or full 512-bit vector pipelines without downclocking penalties, thermal throttling, or thread preemption.
To audit host-level instruction sets and confirm runtime CPU dispatching within ClickHouse, inspect /proc/cpuinfo and the internal system tables:
# Verify kernel and CPU flags for AVX2 and AVX-512 support
grep -E --color=always 'avx2|avx512f|avx512bw|avx512cd|avx512dq|avx512vl' /proc/cpuinfo | head -n 1
# Query ClickHouse runtime instruction set detection
clickhouse-client --query="
SELECT
name,
value
FROM system.build_options
WHERE name LIKE '%CPU%' OR name LIKE '%VECTOR%';"
Memory Hierarchy, Cache Locality, and the Memory Wall
Modern CPUs compute faster than physical dynamic random-access memory (DRAM) can deliver operands—an architectural barrier known as the memory wall. An arithmetic instruction executes in ~0.5 nanoseconds, whereas an uncached memory request to physical DRAM consumes 60 to 90 nanoseconds. ClickHouse mitigates this wall by strictly designing data structures around the CPU memory cache hierarchy:
- L1 Data Cache (32–64 KB per core, ~1–1.5 ns latency): Houses the active iteration buffers and innermost loop accumulators.
- L2 Cache (512 KB–1 MB per core, ~3–4 ns latency): Holds intermediate aggregation hash maps and lookup tables for small dictionaries.
- L3 Cache (32–256 MB shared per CCD/socket, ~12–15 ns latency): Serves as the prefetch staging boundary for column segments retrieved from memory before vector register ingestion.
- Main Memory / DRAM (40–120 GB/s per channel, ~60–80 ns latency): The primary systemic bottleneck during non-cached scans across large ClickHouse MergeTree tables.
Latency vs. Bandwidth Across the Memory Pyramid:
+------------------------+-------------------+--------------------+
| Tier | Access Latency | Typical Throughput |
+------------------------+-------------------+--------------------+
| L1 Cache | 1.0 - 1.2 ns | 2.5 - 3.5 TB/s |
| L2 Cache | 3.2 - 4.5 ns | 1.0 - 1.8 TB/s |
| L3 Cache (Victim/Share)| 12.0 - 15.0 ns | 400 - 800 GB/s |
| Local DDR5-4800 (x8) | 65.0 - 75.0 ns | 250 - 300 GB/s |
| Remote NUMA Node DDR5 | 110.0 - 140.0 ns | 80 - 140 GB/s |
+------------------------+-------------------+--------------------+
Columnar storage guarantees that when a single column is scanned, every byte transferred across the memory bus contains useful payload for the query. In a row-oriented format (such as PostgreSQL or MySQL), reading a 4-byte column out of a 500-byte row wastes 99.2% of the DRAM bandwidth, as the 64-byte CPU cache line is filled predominantly with irrelevant adjacent columns.
In multi-socket and multi-die topologies (such as AMD Infinity Fabric or Intel UPI), NUMA (Non-Uniform Memory Access) effects become pronounced. If a thread scheduled on Core 0 of Socket 0 attempts to scan a ClickHouse column buffer residing in physical RAM wired to Socket 1, execution latency increases by 2.2x due to interconnect transit, while usable memory bandwidth drops by more than 50%.
Comparative Benchmark: Compute & Memory Tier Performance
The following benchmark demonstrates the quantitative impact of SIMD width, memory bus topologies, and hypervisor virtualization constraints during a 100,000,000 row aggregation query (SELECT count(), sum(metric_val), avg(price) FROM telemetry WHERE timestamp >= now() - INTERVAL 1 DAY GROUP BY host_id):
| Compute Platform & Architecture | Virtualization Hypervisor | Active Vector Extension | Memory Architecture | Scan Throughput (Rows/sec) | Memory Bus Saturation | Query Latency p99 (100M Rows) |
|---|---|---|---|---|---|---|
| Legacy Shared Cloud VPS (2 vCPU) | Oversubscribed KVM (%st > 8.0%) |
SSE4.2 (128-bit) | Shared DDR4 Dual-Channel | 14,200,000 | 4.8 GB/s (I/O Stalled) | 7,042 ms |
| Standard Enterprise VPS (4 vCPU) | Standard KVM (%st ~ 0.5%) |
AVX2 (256-bit) | DDR4 Quad-Channel | 48,600,000 | 18.2 GB/s | 2,057 ms |
| High-Frequency Node (8 vCPU) | KVM Dedicated (%st = 0.0%) |
AVX2 (256-bit) | DDR5 Dual-Channel (6000 MHz) | 112,400,000 | 42.1 GB/s | 889 ms |
| tropic.host KVM NVMe Tier (16 vCPU) | Clean KVM (%st = 0.0%) |
AVX-512 (512-bit) | DDR5 Octa-Channel (Zen 4 EPYC) | 384,100,000 | 148.6 GB/s | 260 ms |
| Bare-Metal Multi-Socket Reference | Bare Metal (No Virtualization) | AVX-512 (512-bit) | DDR5 12-Channel (EPYC 9004) | 495,000,000 | 212.0 GB/s | 202 ms |
The metrics highlight the core bottleneck: ClickHouse scan execution saturates physical memory bandwidth long before exhausting pure integer ALU capacity. The combination of complete hardware resource isolation (%st = 0.0%) and wide multi-channel DDR5 memory on the tropic.host platform ensures the CPU execution pipelines remain saturated with vector registers, avoiding the multi-second execution delays observed under shared memory buses.
Kernel, Memory Allocator, and cgroups v2 Host Configuration
To prevent memory fragmentation, eliminate random TLB (Translation Lookaside Buffer) misses, and enforce deterministic memory reclaim thresholds, configure the Linux kernel specifically for vectorized in-memory analytics.
Create a production kernel configuration override:
# /etc/sysctl.d/99-clickhouse-memory.conf
# Increase max memory map count to prevent mmap allocation exhaustion on large MergeTree partitions
vm.max_map_count = 1048576
# Set swappiness to 1; do not completely disable to maintain emergency kernel page out capability
vm.swappiness = 1
# Prohibit the kernel from attempting aggressive memory zone reclamation, which introduces latency spikes
vm.zone_reclaim_mode = 0
# Disable automatic NUMA page migration balancing; ClickHouse manages thread-memory affinity internally
kernel.numa_balancing = 0
# Flush dirty memory buffers predictably to disk; avoid burst stalls that block the vector pipeline
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
# Prevent overcommit memory exhaustion panics
vm.overcommit_memory = 0
Apply the parameters immediately:
sudo sysctl --system
Transparent Huge Pages (THP) Architecture
Transparent Huge Pages allocate 2 MB memory blocks instead of standard 4 KB pages, reducing TLB cache misses by a factor of 512. However, runtime synchronous page compaction under Linux can freeze execution threads for hundreds of milliseconds. Set THP to madvise to permit ClickHouse's bundled jemalloc memory allocator to request 2 MB pages explicitly via madvise(MADV_HUGEPAGE) without incurring global kernel-level compaction locks:
# Set runtime Transparent Huge Pages policy to madvise
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
# Ensure persistence across reboots via systemd-tmpfiles or udev rule
echo 'w /sys/kernel/mm/transparent_hugepage/enabled - - - - madvise' | sudo tee /etc/tmpfiles.d/thp.conf
echo 'w /sys/kernel/mm/transparent_hugepage/defrag - - - - madvise' | sudo tee -a /etc/tmpfiles.d/thp.conf
Isolation via cgroups v2
When running ClickHouse containerized or alongside background telemetry daemons, configure systemd and cgroups v2 to enforce strict memory bounds and CPU weights, preventing Linux Out-Of-Memory (OOM) killer terminations from discarding active cache state:
# /etc/systemd/system/clickhouse-server.service.d/override.conf
[Service]
# Enable unified cgroup v2 resource accounting
CPUAccounting=yes
MemoryAccounting=yes
# Assign highest CPU scheduling weight relative to background daemons (range: 1-10000)
CPUWeight=800
# Protect against sudden OOM kills by establishing a low-watermark reclaim boundary
# Assuming a node with 32 GB RAM:
MemoryLow=24G
MemoryHigh=28G
MemoryMax=30G
# Do not kill the service when child memory spikes temporarily; allow jemalloc to reclaim
OOMPolicy=continue
CPUSchedulingPolicy=other
Nice=-10
Reload and restart the service to apply the cgroup boundaries:
sudo systemctl daemon-reload
sudo systemctl restart clickhouse-server
To continuously monitor hardware prefetchers, cache misses, and retired vector instructions during live queries, use the Linux performance counter subsystem:
# Profile real-time cache and SIMD instruction retirement for a specific ClickHouse query execution
perf stat -e cycles,instructions,cache-references,cache-misses,L1-dcache-load-misses,LLC-load-misses -p $(pgrep -f clickhouse-server) -- sleep 10
An optimal vectorized deployment will display an IPC (Instructions Per Cycle) of $> 1.5$ and an LLC-load-misses ratio below $5\%$ of total cache references, confirming that memory hierarchy optimizations and SIMD vectorized pipelines are operating at peak hardware efficiency.
Linux Kernel Tuning: Disabling Transparent Huge Pages and Optimizing vm.dirty_ratio
High-throughput vectorized execution pipelines processing tens of millions of records per second generate intense, microsecond-level memory allocation churn. ClickHouse relies heavily on its internal jemalloc implementation to allocate, resize, and release arena buffers for parallel aggregation hash tables, sort buffers, and vectorized block transformations. When running on standard Linux distribution defaults, the kernel's virtual memory subsystem works directly against this allocation model, injecting severe tail-latency spikes (p99 and p99.9 exceeding 500–1,200 ms on sub-second baseline queries) via memory compaction and unthrottled page cache writebacks.
Stabilizing query latency requires recalibrating the Linux Virtual Memory (VM) manager, neutralizing Transparent Huge Pages, pinning page cache flush boundaries, and raising kernel data structure limits to match analytical database workloads.
Transparent Huge Pages (THP): The Root of Tail-Latency Degradation
Linux Transparent Huge Pages (THP) attempts to optimize hardware TLB (Translation Lookaside Buffer) hit rates by automatically consolidating 4 KiB base pages into 2 MiB contiguous physical memory blocks. While sequential workloads with static memory mappings benefit from reduced page table traversals, THP is catastrophic for ClickHouse's dynamic memory layout.
The architectural conflict originates in the kernel's memory defragmentation daemon, khugepaged:
- Memory Fragmentation: Long-running analytical nodes operating continuous part merges and ingestion pipelines rapidly fragment physical RAM.
- Synchronous Direct Compaction: When
jemallocrequests memory and the kernel attempts a 2 MiB allocation, a lack of contiguous free space triggers direct compaction. - Thread Stalls and Lock Contention: The executing ClickHouse query thread is paused in kernel space while the memory subsystem scans memory zones, evicts clean pages, acquires zone locks (
lru_lock), and migrates physical pages to synthesize a contiguous 2 MiB region. - Latency Tail Collapse: During compaction, CPU execution shifts from user-space SIMD vectorization to kernel-space spinlocks. Simple point lookups or aggregated scans that normally retire in 15 ms freeze for up to hundreds of milliseconds.
To achieve deterministic p99 query execution, Transparent Huge Pages must be disabled completely across both the allocation path and the background defragmentation daemon.
Disabling THP at Boot and Runtime
Applying runtime modifications directly to sysfs is insufficient, as host reboots or cloud hypervisor kernel re-initializations will revert the subsystem to always or madvise.
First, inspect the active THP state:
# Check runtime status of THP allocation and defragmentation
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag
If the output displays [always] madvise never, THP is active. To permanently disable THP at the hypervisor boot stage, append the kernel parameter to GRUB:
# Append transparent_hugepage=never to GRUB command line
sudo sed -i 's/GRUB_CMDLINE_LINUX_DEFAULT="/GRUB_CMDLINE_LINUX_DEFAULT="transparent_hugepage=never /' /etc/default/grub
sudo update-grub
On enterprise Linux or KVM virtualization environments where GRUB updates might be managed by cloud-init, enforce runtime state enforcement prior to the database daemon initialization via an explicit, early-stage systemd unit:
# /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Linux Transparent Huge Pages (THP) for ClickHouse
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=clickhouse-server.service
[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled && echo never > /sys/kernel/mm/transparent_hugepage/defrag'
RemainAfterExit=yes
[Install]
WantedBy=basic.target
Enable and activate the unit immediately:
sudo systemctl daemon-reload
sudo systemctl enable --now disable-thp.service
Confirm that the bracketed state shows [never]:
cat /sys/kernel/mm/transparent_hugepage/enabled
# Output: always madvise [never]
cat /sys/kernel/mm/transparent_hugepage/defrag
# Output: always defer defer+madvise madvise [never]
Page Cache Dynamics: Controlling vm.dirty_ratio and Writeback Spikes
ClickHouse writes newly ingested rows into memory before streaming them sequentially to disk as immutable compressed columnar parts (.bin and .mrk2). In parallel, background mutation and merge threads continuously rewrite multiple parts into consolidated columnar structures.
By default, the Linux kernel buffers write operations aggressively in the page cache: * vm.dirty_background_ratio (default ~10%): The percentage of system memory that can hold unwritten dirty pages before background kernel flusher threads (wb_workfn / kworker) wake up to stream data to storage. * vm.dirty_ratio (default ~20%): The absolute hard ceiling. If unwritten dirty pages cross this boundary, the kernel halts all incoming user-space write syscalls, forcing the ClickHouse thread that issued write() into synchronous direct writeback.
The Direct Writeback Trap on Analytical VPS
On a virtual machine with 32 GB to 64 GB of RAM, a standard vm.dirty_ratio of 20% permits 6.4 GB to 12.8 GB of dirty data to accumulate in RAM before forced flushing occurs. When a high-volume batch insert or heavy MergeTree compaction runs, ClickHouse saturates the 10% background boundary rapidly. If storage queue depth spikes, the 20% hard threshold is reached.
The kernel immediately forces ingestion threads into storage flushing mode, freezing incoming network sockets and pushing insert query latency from 50 ms to tens of seconds.
DEFAULT KERNEL SETTINGS (High Dirty Ratios):
[ Ingestion Stream ] ──► [ Page Cache Accumulation: up to 20% RAM (~12.8 GB) ]
│
▼ (Hard threshold hit)
[ KERNEL FORCES SYNCHRONOUS FLUSH ] ──► STALLS INGESTION THREADS
(p99 Latency explodes; I/O queue depth saturates)
OPTIMIZED CLICKHOUSE SETTINGS (Low, Continuous Writeback):
[ Ingestion Stream ] ──► [ Page Cache: 3% threshold (~1 GB) ]
│
▼ (Continuous background streaming)
[ kworker streams smooth chunks to NVMe ] ──► ZERO THREAD STALLS
(p99 Latency remains flat; sustained I/O pipeline)
To eliminate latency stalls, the writeback subsystem must be reconfigured to flush dirty pages continuously in small, predictable increments without blocking user-space threads.
On modern high-IOPS NVMe VPS platforms such as tropic.host, where dedicated KVM slices run on enterprise PCIe 4.0 storage delivering over 50,000 random 4K QD1 read IOPS, the storage controller easily sustains continuous sequential write streams. Rather than allowing gigabytes of dirty state to accumulate, configure explicit low percentage ratios or absolute byte thresholds.
Production Kernel Blueprint: /etc/sysctl.d/99-clickhouse.conf
Deploy the following production-hardened sysctl configuration to govern virtual memory, VFS cache retention, network socket backlogs, and memory mapping ceilings:
# /etc/sysctl.d/99-clickhouse.conf
# --------------------------------------------------------------------
# 1. Virtual Memory & Dirty Page Flush Behavior
# --------------------------------------------------------------------
# Begin background writeback when dirty pages exceed 3% of available memory.
# Prevents large dirty page accumulations without waking kworker continuously.
vm.dirty_background_ratio = 3
# Enforce a strict 6% ceiling before synchronous write throttling engages.
# Ensures the application thread is never forced to execute direct I/O flushes.
vm.dirty_ratio = 6
# Alternatively, on nodes with >= 64 GB RAM, use absolute byte boundaries:
# vm.dirty_background_bytes = 67108864 # 64 MB
# vm.dirty_bytes = 536870912 # 512 MB
# Check dirty page expiration every 500 centisecs (5 seconds)
vm.dirty_writeback_centisecs = 500
# Expire dirty pages after 1500 centisecs (15 seconds) to prevent stale cache
vm.dirty_expire_centisecs = 1500
# --------------------------------------------------------------------
# 2. Swap and VFS Cache Pressure
# --------------------------------------------------------------------
# Minimize swapping aggressive behavior while preserving an emergency valve.
# Setting to 1 prevents swapping working-set pages, evicting only dead anonymous memory.
vm.swappiness = 1
# Maintain VFS directory and inode metadata in RAM to accelerate part discovery.
# Lower value (default 100) preserves dentries and inodes during high memory pressure.
vm.vfs_cache_pressure = 50
# Prevent node zone reclaim stalls on multi-socket / NUMA architectures (e.g. AMD EPYC).
# 0 ensures pages are allocated from other nodes rather than running local zone reclaim.
vm.zone_reclaim_mode = 0
# --------------------------------------------------------------------
# 3. Memory Overcommit and Mapping Capacities
# --------------------------------------------------------------------
# Heuristic overcommit handling (default 0). jemalloc manages its own allocations;
# setting this to 0 prevents the kernel from blindly allocating unbounded virtual address space.
vm.overcommit_memory = 0
# Maximum number of memory map areas (mmaps) a process can hold.
# ClickHouse creates 2-4 memory maps per column per part (.bin and .mrk files).
# Highly partitioned tables with thousands of parts exhaust the default 65530 limit,
# resulting in 'Cannot allocate memory' (std::bad_alloc) crashes.
vm.max_map_count = 2621440
# --------------------------------------------------------------------
# 4. Network Stack Buffers for Distributed Analytics
# --------------------------------------------------------------------
# Maximum socket receive and send buffer sizes across TCP sessions
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
# Increase length of the network device input queue (protects against burst drops)
net.core.netdev_max_backlog = 10000
# Listen backlog queue size for high-frequency client connections
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
# Accelerate TCP socket reuse to avoid TIME_WAIT exhaustion under heavy REST/HTTP query rates
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 15
Apply the tuning immediately without rebooting:
sudo sysctl --system
Verify that the kernel virtual memory parameters reflect the changes:
sysctl vm.dirty_ratio vm.dirty_background_ratio vm.max_map_count vm.swappiness
Process Resource Limits (limits.conf)
Because ClickHouse opens separate file handles for every compressed column data stream (.bin) and primary index mark file (.mrk2) within every active part involved in a query pipeline, operating system descriptor ceilings must be raised significantly. A query reading a table with 200 columns across 50 active parts can consume 20,000 open file handles concurrently.
Create a dedicated limits override file:
# /etc/security/limits.d/clickhouse.conf
clickhouse soft nofile 1048576
clickhouse hard nofile 1048576
clickhouse soft nproc 262144
clickhouse hard nproc 262144
clickhouse soft memlock unlimited
clickhouse hard memlock unlimited
Ensure the systemd unit service file also inherits these limits. Verify the live limits of the running clickhouse-server process:
# Extract the active PID of the clickhouse-server instance
CH_PID=$(pgrep -f clickhouse-server | head -n1)
# Inspect practical limits assigned by the kernel
grep -E 'Max open files|Max processes|Max locked memory' /proc/${CH_PID}/limits
The resulting output must confirm 1048576 for Max open files and 262144 for Max processes.
Verification: Monitoring Compaction Stalls and Page Writeback
To validate that THP stalls have been eliminated and that dirty page flushing runs predictably, evaluate kernel memory metrics via /proc/vmstat:
# Monitor memory compaction and direct reclaim stalls
grep -E 'compact_stall|allocstall|thp_fault_alloc|thp_collapse_alloc' /proc/vmstat
Key metric indicators: * compact_stall: Increments when an allocation stalls for direct memory compaction. In a properly tuned environment with THP set to never, this counter should remain completely static over time. * allocstall_normal / allocstall_movable: Increments when the kernel enters direct page reclaim because memory watermarks were breached. A non-zero, rapidly climbing delta indicates that MemoryLow/MemoryHigh cgroup boundaries are set too close to physical limits, or that vm.dirty_ratio is forcing direct writeback.
During a heavy insert or large-scale batch analytical query, monitor writeback activity continuously:
# Live monitoring of dirty page accumulation and writeback execution
watch -n 1 'grep -E "Dirty:|Writeback:|Active\(file\):" /proc/meminfo'
With vm.dirty_background_ratio = 3 and vm.dirty_ratio = 6, Dirty memory will cycle cleanly within a narrow, low-megabyte band. The kernel initiates asynchronous Writeback early, keeping physical page writeout synchronized with the sustained write throughput of the underlying enterprise NVMe drives.
Operating ClickHouse on dedicated KVM compute slices—such as those provisioned by tropic.host—ensures that these kernel optimizations operate with absolute bare-metal determinism. Guaranteed CPU allocation with zero hypervisor oversubscription (%st = 0.0%) prevents vCPU scheduling preemption during critical memory bus operations, while direct PCIe 4.0 NVMe passthrough ensures dirty page flushes clear the page cache instantly, locking query latency distributions precisely at hardware line rate.
Deploying Production ClickHouse via Docker Compose with NVMe Mounts
Translating kernel-level storage optimizations into predictable execution requires an orchestration topology that eliminates Docker bridge network overhead, preserves container cgroups boundaries, and mounts host NVMe storage without intervening virtualization layers. When running ClickHouse on a virtual private server, raw disk throughput and deterministic memory allocation dictate query p99 latency.
Deploying ClickHouse via Docker Compose provides declarative environment management while allowing direct access to underlying host hardware capabilities.
+-----------------------------------------------------------------------------------+
| Host System: KVM Node (Ubuntu 24.04 LTS / Debian 12) |
| |
| PCIe 4.0 NVMe Block Device: /dev/nvme0n1p1 |
| Mount: /mnt/nvme-data (ext4/XFS: noatime, nodiratime, data=ordered) |
| |
| Directory Tree: |
| /opt/clickhouse/ |
| |-- compose.yaml |
| |-- config.d/ |
| | |-- 01-network-storage.xml |
| | +-- 02-logger.xml |
| +-- users.d/ |
| +-- 01-profiles.xml |
| |
| /mnt/nvme-data/clickhouse/ |
| |-- data/ <-- Bind mount: /var/lib/clickhouse (UID 101:101) |
| +-- logs/ <-- Bind mount: /var/log/clickhouse-server (UID 101:101) |
+------------------------------------------+----------------------------------------+
| Docker Engine 26+ (cgroups v2)
v
+-----------------------------------------------------------------------------------+
| Container: clickhouse-server (official image, UID 101, GID 101) |
| |
| - Network: host (Zero iptables/docker-proxy latency) |
| - Cap Add: SYS_NICE, IPC_LOCK, NET_ADMIN |
| - Ulimits: nofile=262144, memlock=-1 |
| - Cgroups v2: memory.max = 28G, memory.high = 26G (on 32GB slice) |
+-----------------------------------------------------------------------------------+
Host Filesystem Topology and NVMe Mount Configuration
Default host storage layouts typically place /var/lib/docker on root filesystems with standard journaling and atime metadata updates enabled. For high-frequency analytical time-series ingestion, access-time updates generate destructive write amplification across NVMe flash cells.
Format the dedicated NVMe partition using an optimized ext4 or XFS filesystem, ensuring directory access timestamps are disabled at the VFS level:
# Format the designated NVMe partition with a 4096-byte block size matching SSD page geometry
sudo mkfs.ext4 -b 4096 -E stride=512,stripe-width=512 -O mmp,dir_index,sparse_super /dev/nvme0n1p1
# Create the dedicated mount base and persistent data directories
sudo mkdir -p /mnt/nvme-data
sudo mkdir -p /opt/clickhouse/{config.d,users.d}
# Append the deterministic mount specification to /etc/fstab
echo "UUID=$(sudo blkid -s UUID -o value /dev/nvme0n1p1) /mnt/nvme-data ext4 noatime,nodiratime,data=ordered,discard 0 2" | sudo tee -a /etc/fstab
# Mount the NVMe volume and construct the target directory tree
sudo mount -a
sudo mkdir -p /mnt/nvme-data/clickhouse/{data,logs}
The official ClickHouse container executes under internal user clickhouse assigned to UID 101 and GID 101. Mismatched host permissions cause fatal database engine halts during initial SQLite metadata generation or system.parts layout writes:
# Assign strict ownership of the NVMe persistent paths to container UID 101
sudo chown -R 101:101 /mnt/nvme-data/clickhouse
sudo chmod -R 750 /mnt/nvme-data/clickhouse
# Verify host storage queue scheduler is set to 'none' for direct NVMe hardware submission
echo none | sudo tee /sys/block/$(lsblk -no pkname /dev/nvme0n1p1 | head -n1)/queue/scheduler
Production-Hardened docker-compose.yml
This deployment profile bypasses Docker's default virtual bridge network via network_mode: "host", eliminating NAT packet filtering, CPU softirq contention, and connection-tracking overhead on high-throughput analytical workloads.
Cgroups resource boundaries, ulimits, and capabilities are defined strictly to support ClickHouse memory locks (mlock) and thread priority scheduling:
# /opt/clickhouse/compose.yaml
services:
clickhouse:
image: clickhouse/clickhouse-server:24.8-lts
container_name: clickhouse-server
restart: always
network_mode: "host"
pid: "host"
environment:
- CLICKHOUSE_DO_NOT_CHOWN=1
cap_add:
- SYS_NICE
- IPC_LOCK
- NET_ADMIN
ulimits:
nofile:
soft: 262144
hard: 262144
memlock:
soft: -1
hard: -1
nproc:
soft: 65535
hard: 65535
volumes:
- /mnt/nvme-data/clickhouse/data:/var/lib/clickhouse:rw
- /mnt/nvme-data/clickhouse/logs:/var/log/clickhouse-server:rw
- /opt/clickhouse/config.d:/etc/clickhouse-server/config.d:ro
- /opt/clickhouse/users.d:/etc/clickhouse-server/users.d:ro
# Production resource sizing (calculated for an 8 vCPU / 32 GB RAM VPS tier)
deploy:
resources:
limits:
cpus: '8.0'
memory: 28G
reservations:
cpus: '8.0'
memory: 28G
logging:
driver: "json-file"
options:
max-size: "100m"
max-file: "5"
Resource Sizing Rule: Never allocate 100% of host RAM to the ClickHouse container via cgroups. On a 32 GB RAM instance, set the container limit to 28 GB. The remaining 4 GB is required by the Linux kernel forvfs_cache_pressure, page tables, slab allocations (dentry,inode_cache), and raw socket ring buffers.
Server Configuration Directives (config.d)
To prevent runtime errors and ensure predictable memory allocation under multi-terabyte query scans, custom XML overrides are placed in /opt/clickhouse/config.d/01-network-storage.xml:
<!-- /opt/clickhouse/config.d/01-network-storage.xml -->
<clickhouse>
<!-- Network Interface Binding -->
<listen_host>0.0.0.0</listen_host>
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<interserver_http_port>9009</interserver_http_port>
<!-- Memory Management -->
<!-- Cap internal query allocation to 85% of container RAM to avoid OOM killer -->
<max_server_memory_usage_to_ram_ratio>0.85</max_server_memory_usage_to_ram_ratio>
<!-- Primary Mark Cache Sizing: 4 GiB pinned memory for index lookups -->
<mark_cache_size>4294967296</mark_cache_size>
<uncompressed_cache_size>2147483648</uncompressed_cache_size>
<!-- NVMe Storage Engine Optimization -->
<storage_configuration>
<disks>
<nvme_root>
<type>local</type>
<path>/var/lib/clickhouse/</path>
<!-- Retain at least 15 GB reserve space to prevent table corruption -->
<keep_free_space_bytes>16106127360</keep_free_space_bytes>
</nvme_root>
</disks>
<policies>
<nvme_performance>
<volumes>
<main>
<disk>nvme_root</disk>
</main>
</volumes>
</nvme_performance>
</policies>
</storage_configuration>
<!-- MergeTree Background Mutation Threading -->
<background_pool_size>16</background_pool_size>
<background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
<max_suspicious_broken_parts>10</max_suspicious_broken_parts>
</clickhouse>
User Execution and Asynchronous Ingestion Profiles (users.d)
High-velocity analytics workloads run into ingestion bottlenecks if every single row insertion initiates a discrete disk write. Overriding the default profile enables asynchronous batching inside server memory buffers before flushing sequential parts to NVMe:
<!-- /opt/clickhouse/users.d/01-profiles.xml -->
<clickhouse>
<profiles>
<default>
<!-- Compute and Execution Constraints -->
<max_threads>8</max_threads>
<max_execution_time>120</max_execution_time>
<max_memory_usage>24000000000</max_memory_usage>
<!-- Asynchronous Ingest Buffer: Critical for High-Rate Log/Telemetry Ingestion -->
<async_insert>1</async_insert>
<wait_for_async_insert>0</wait_for_async_insert>
<async_insert_busy_timeout_ms>250</async_insert_busy_timeout_ms>
<async_insert_max_data_size>10485760</async_insert_max_data_size>
<async_insert_threads>4</async_insert_threads>
<!-- Join and Aggregation Spilling: Spill gracefully to NVMe instead of terminating -->
<max_bytes_before_external_group_by>16000000000</max_bytes_before_external_group_by>
<max_bytes_before_external_sort>8000000000</max_bytes_before_external_sort>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
</default>
</profiles>
<users>
<default>
<password_sha256_hex>8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918</password_sha256_hex>
<networks>
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</default>
</users>
</clickhouse>
(Note: The hash above corresponds to password admin. Generate a production secret using echo -n "YourSecurePass" | sha256sum | awk '{print $1}' and replace the string).
Infrastructure Baseline: Hardware Guarantees on KVM Compute
Executing real-time ClickHouse analytics on virtualized infrastructure introduces severe tail-latency amplification whenever hypervisors overcommit CPU cores or multiplex NVMe storage queues. A background merge operation (OPTIMIZE TABLE) in the MergeTree engine demands sustained, deterministic IOPS and zero processor preemption.
When hosting analytical ClickHouse nodes on cloud infrastructure, hypervisor noisy neighbors cause immediate latency regressions. Deploying instances on dedicated KVM compute slices—such as those provisioned by tropic.host—eliminates CPU scheduling jitter through strict physical core pinning, guaranteeing zero hypervisor steal time (%st = 0.0%).
Direct PCIe 4.0 NVMe storage passthrough delivers over 50,000 random 4K QD1 read IOPS with zero thermal throttling. Combined with unmetered 1–10 Gbps uplinks and TCP BBR enabled by default, remote ingestion pipelines stream massive payloads into the database without TCP buffer exhaustion or packet retransmissions at line rate.
Bootstrapping and Runtime Engine Verification
Deploy the stack and inspect system tables to confirm that storage subsystems, cache allocations, and processor execution boundaries are operating as configured:
# Navigate to deployment directory and spin up the containerized daemon
cd /opt/clickhouse
docker compose up -d
# Verify container initialization status via runtime logs
docker compose logs -f clickhouse-server | grep -E "Application: Ready for connections|Loaded configuration"
Once the service is listening on port 9000, validate storage policies and hardware subsystem detection using the ClickHouse CLI client:
# Connect to the local server through the host network interface
docker exec -it clickhouse-server clickhouse-client --user default --password "admin" --query "
SELECT
name,
path,
formatReadableSize(free_space) AS free,
formatReadableSize(total_space) AS total,
type
FROM system.disks;
"
The output must display the dedicated NVMe mount path /var/lib/clickhouse/ mapped to the underlying physical filesystem:
┌─name──────┬─path──────────────────┬─free──────┬─total─────┬─type──┐
│ default │ /var/lib/clickhouse/ │ 210.42 GiB│ 240.11 GiB│ local │
│ nvme_root │ /var/lib/clickhouse/ │ 210.42 GiB│ 240.11 GiB│ local │
└───────────┴───────────────────────┴───────────┴───────────┴───────┘
Verify that LLVM-based JIT compilation and hardware instruction sets (AVX2, BMI2, SSE4.2) are active on the host processor architecture:
docker exec -it clickhouse-server clickhouse-client --user default --password "admin" --query "
SELECT
name,
value
FROM system.build_options
WHERE name LIKE '%JIT%' OR name LIKE '%VECTOR%';
"
A properly configured node will return USE_EMBEDDED_COMPILER = 1, validating that ClickHouse dynamically compiles query filter expressions and aggregations into native machine code directly on bare-metal registers.
Memory Management: max_server_memory_usage and Merging Optimizations
High-throughput analytical workloads in ClickHouse are fundamentally bound by memory allocation dynamics. When executing high-cardinality aggregations (GROUP BY) or multi-table equi-joins across millions of rows, memory consumption scales non-linearly. If internal query limits are unconfigured, the operating system kernel invokes the Out-Of-Memory (OOM) Killer, dispatching a SIGKILL to the clickhouse-server process. This abruptly terminates client sessions, drops in-flight parts, and triggers expensive integrity verification routines across physical data parts on restart.
Mitigating unexpected process termination requires a dual-tier strategy: configuring ClickHouse’s internal memory tracker to reject or spill runaway allocations before kernel-level enforcement triggers, and sizing the execution envelope against the physical limitations of the host.
ClickHouse Internal Tracker vs. Kernel cgroups v2
ClickHouse implements an internal memory tracking subsystem that instruments every memory allocation via jemalloc or its custom allocator wrappers. This tracking operates independently of the Linux kernel page table accounting. The kernel tracks Virtual Memory Area (VMA) mappings and Resident Set Size (RSS), whereas ClickHouse tracks logical allocations requested by query threads, mark caches, and background merge pipelines.
When deploying ClickHouse on VPS analytics nodes, memory contention arises if the database engine attempts to consume 100% of host RAM. Operating system services, the Docker runtime daemon, network socket buffers, and the Linux Page Cache (used by the kernel to cache MergeTree index columns and .bin compressed marks) require dedicated headroom.
To enforce strict isolation, configure both ClickHouse application-level limits and containerized cgroups v2 boundaries:
+-----------------------------------------------------------------------+
| Host Physical RAM (e.g., 32 GiB on tropic.host KVM Instance) |
| |
| +-----------------------------------+ +--------------------------+ |
| | cgroups v2 Container Boundary | | Host OS Overhead (4 GiB) | |
| | memory.max = 28 GiB | | - Systemd, SSHD, Docker | |
| | | | - Kernel Socket Buffers | |
| | +-----------------------------+ | | - OS VFS Inodes | |
| | | max_server_memory_usage | | +--------------------------+ |
| | | 25.6 GiB (~90% of cgroup) | | |
| | | | | +--------------------------+ |
| | | +-----------------------+ | | | Linux Page Cache | |
| | | | max_memory_usage | | | | (Shared / Dynamically | |
| | | | 10 GiB (Per-Query) | | | | Reclaimed via VFS) | |
| | | +-----------------------+ | | +--------------------------+ |
| | | +-----------------------+ | | |
| | | | Background Merges, | | | |
| | | | Mark Cache, Primary | | | |
| | | | Index Allocations | | | |
| | | +-----------------------+ | | |
| | +-----------------------------+ | |
| +-----------------------------------+ |
+-----------------------------------------------------------------------+
On a dedicated cloud instance with 32 GiB of physical RAM, provisioning ClickHouse inside a cgroup capped at 28 GiB (memory.max = 30064771072) leaves 4 GiB for OS routines. Within ClickHouse itself, max_server_memory_usage must be set to approximately 85–90% of the container boundary (~25.6 GiB). This guarantees that ClickHouse's internal tracker intercepts memory spikes and throws a MEMORY_LIMIT_EXCEEDED exception back to the client application before the kernel triggers an uncatchable container kill.
Global Server Memory Configuration
Create an isolated XML override file inside /etc/clickhouse-server/config.d/ to declare server-wide memory limits, cache ceilings, and page-cache allowances.
Create /opt/clickhouse/config.d/memory.xml:
<clickhouse>
<!-- Total memory ClickHouse can consume before aborting new queries.
Absolute bytes (27,487,790,694 B = ~25.6 GiB) or relative ratio. -->
<max_server_memory_usage>27487790694</max_server_memory_usage>
<max_server_memory_usage_to_ram_ratio>0.85</max_server_memory_usage_to_ram_ratio>
<!-- Mark cache stores index marks (.mrk2/.mrk3).
Prevent mark cache from expanding past 4 GiB. -->
<mark_cache_size>4294967296</mark_cache_size>
<!-- Primary uncompressed cache size.
Keep disabled (0) for pure analytical columnar storage;
rely instead on Linux OS page cache to eliminate double-caching overhead. -->
<uncompressed_cache_size>0</uncompressed_cache_size>
<!-- Defines memory reservation for background merges and mutations. -->
<merge_tree>
<max_bytes_to_merge_at_max_space_in_pool>107374182400</max_bytes_to_merge_at_max_space_in_pool>
<max_bytes_to_merge_at_min_space_in_pool>1048576</max_bytes_to_merge_at_min_space_in_pool>
</merge_tree>
</clickhouse>
Setting uncompressed_cache_size to 0 prevents ClickHouse from maintaining redundant, uncompressed columnar blocks in user-space memory. Column data already resides compressed on physical NVMe blocks and is transparently populated into the kernel's Page Cache. Double-caching degrades p99 latency during heavy scans by shrinking the RAM available for query hash tables.
User Profiles: Spill-to-Disk for Aggregations and Joins
Single-query execution profiles are managed inside /etc/clickhouse-server/users.d/. By default, when a single analytical query exceeds its threshold, ClickHouse aborts execution. To sustain execution on heavy GROUP BY and JOIN operations without exhausting physical RAM, configure external memory spilling.
Create /opt/clickhouse/users.d/query_limits.xml:
<clickhouse>
<profiles>
<default>
<!-- Hard ceiling for a single query (10 GiB). -->
<max_memory_usage>10737418240</max_memory_usage>
<!-- Memory threshold for a single user across all concurrent queries. -->
<max_memory_usage_for_user>23622320128</max_memory_usage_for_user>
<!-- Spilling for GROUP BY: Spill intermediate hash aggregation states
to disk once RAM usage crosses 6 GiB. -->
<max_bytes_before_external_group_by>6442450944</max_bytes_before_external_group_by>
<!-- Spilling for ORDER BY: Spill sort runs to temporary storage
once RAM usage crosses 4 GiB. -->
<max_bytes_before_external_sort>4294967296</max_bytes_before_external_sort>
<!-- Grace Hash Join configuration for high-cardinality JOINs. -->
<join_algorithm>auto,grace_hash,partial_merge</join_algorithm>
<max_bytes_in_join>4294967296</max_bytes_in_join>
<grace_hash_join_initial_buckets>8</grace_hash_join_initial_buckets>
<grace_hash_join_max_buckets>64</grace_hash_join_max_buckets>
<!-- Buffer sizing for external sorting blocks -->
<external_sort_max_bytes_in_memory>2147483648</external_sort_max_bytes_in_memory>
</default>
</profiles>
</clickhouse>
Mechanical Operation of External Aggregation and Join Spilling
max_bytes_before_external_group_by: ClickHouse accumulates unique aggregation states within an in-memory hash table (HashTable). Once this hash table reaches the defined boundary (here, 6 GiB), ClickHouse flushes the current state into a sorted temporary block written to the path configured by<tmp_path>(/var/lib/clickhouse/tmp/). The in-memory hash table is emptied, and incoming blocks continue processing. Once the full input stream has been read, ClickHouse reads back the dumped temporary files, runs a multi-way merge sort across the aggregated states, and emits the final result set.join_algorithm(Grace Hash & Partial Merge): Standardhashjoins load the entire right-side table into an uncompressed in-memory hash structure. If the right-side table exceeds available RAM, the process aborts. Setting the algorithm precedence toauto,grace_hash,partial_mergeenforces dynamic behavior: ClickHouse attempts a standard in-memory hash join, but if RAM usage exceedsmax_bytes_in_join(4 GiB), it partitions the dataset into buckets (grace_hash_join_initial_buckets), writes stalled buckets to disk, and executes the join sequentially bucket-by-bucket.
Spilling transformations to disk introduces heavy I/O serialization penalties. The efficiency of external algorithms depends directly on random 4K read and sequential write characteristics of the physical storage. Running these routines on standard VPS shared disks leads to thread stalls, I/O saturation, and severe query timeout spikes. On high-performance infrastructure such as tropic.host KVM instances—where enterprise PCIe 4.0 NVMe drives sustain random 4K read throughput exceeding 50,000 IOPS—intermediate sort runs flush and merge at bus speed, bounding query p99 latency degradation to linear disk serialization rather than hitting exponential lockup.
Background Merge and Mutation Concurrency Tuning
ClickHouse’s MergeTree storage engine continuously executes background merge passes to combine small data parts into larger, sorted structures. These merges consume substantial memory buffers to decompress, sort, and re-encode compressed columns.
If ingestion rates are high while simultaneous heavy analytical queries execute, background merges will compete with user queries for RAM allocation. Control this concurrency via the background pool parameters.
Append the following configuration to /opt/clickhouse/config.d/memory.xml:
<clickhouse>
<!-- Number of worker threads for background operations.
Rule of thumb: Equal to physical CPU cores, maximum 16 on standard VPS. -->
<background_pool_size>8</background_pool_size>
<!-- Restrict the concurrency of simultaneous part mutations. -->
<background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
<!-- Maximum total part size (in bytes) that can be merged in background.
Prevents threads from attempting multi-hundred-gigabyte consolidations
during peak query periods. -->
<max_bytes_to_merge_at_max_space_in_pool>53687091200</max_bytes_to_merge_at_max_space_in_pool>
</clickhouse>
Calculating the memory consumed by background merges relies on the formula:
$$\text{Merge Memory} \approx \text{background_pool_size} \times \text{number of active columns} \times \text{index_granularity} \times \text{average column size uncompressed}$$
By constraining background_pool_size relative to the available vCPU count, memory allocation remain deterministic even under heavy continuous ingestion pipelines.
Host Kernel and Virtual Memory Subsystem Synchronization
Configuring ClickHouse internally is insufficient if the underlying Linux kernel triggers memory swapping or initiates pre-mature OOM kills through improper memory overcommit behavior. Apply the following sysctl directives on the host node to match the database's allocation profile.
Create /etc/sysctl.d/99-clickhouse-memory.conf:
# Prevent excessive memory overcommit. ClickHouse pre-allocates contiguous virtual buffers.
# 0 = Heuristic overcommit (standard production setting for analytical DBMS).
vm.overcommit_memory = 0
# Drastically reduce swappiness to prevent moving database process execution pages to swap disk.
# A value of 1 forces swapping only to avoid out-of-memory panics.
vm.swappiness = 1
# Reserve minimum atomic allocation space for the Linux network stack and filesystem drivers.
# Prevents memory deadlocks during high-throughput network ingestion.
vm.min_free_kbytes = 131072
# Scale virtual memory memory mapping capacity.
# ClickHouse maps large numbers of part files simultaneously via mmap.
vm.max_map_count = 1048576
# Ensure writeback dirty memory is flushed continuously to NVMe to preserve read bandwidth.
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
Apply the kernel directives immediately:
sysctl -p /etc/sysctl.d/99-clickhouse-memory.conf
Confirm that Transparent Huge Pages (THP) are disabled. ClickHouse utilizes fine-grained memory allocators (jemalloc). THP causes memory fragmentation, increases internal memory bloat, and introduces latency degradation during high-rate part allocations:
# Verify current runtime THP status
cat /sys/kernel/mm/transparent_hugepage/enabled
# Disable THP permanently on the host
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
Ensure this configuration persists across reboots by declaring it in /etc/rc.local or via a dedicated systemd unit file.
Operational Verification and Metrics Monitoring
Once configurations are loaded, verify memory tracking live through the ClickHouse command-line client. Inspect internal metrics to validate tracker constraints and ensure that memory consumption remains within safe operational boundaries:
docker exec -it clickhouse-server clickhouse-client --user default --password "admin" --query "
SELECT
metric,
formatReadableSize(value) AS value
FROM system.metrics
WHERE metric LIKE '%Memory%' OR metric LIKE '%Cache%';
"
Expected diagnostic output:
┌─metric───────────────────────┬─value──────┐
│ MemoryTracking │ 1.84 GiB │
│ MemoryTrackingInBackground │ 312.40 MiB │
│ MarkCacheBytes │ 420.11 MiB │
│ UncompressedCacheBytes │ 0.00 B │
└──────────────────────────────┴────────────┘
Execute an inspection against the asynchronous metrics catalog to monitor the gap between the process-level tracking and the operating system page tables (RSS):
docker exec -it clickhouse-server clickhouse-client --user default --password "admin" --query "
SELECT
event,
formatReadableSize(value) AS size
FROM system.asynchronous_metrics
WHERE event IN ('MemoryResident', 'MemoryVirtual', 'CGroupMemoryUsed', 'MaxMemoryUsage')
ORDER BY event ASC;
"
┌─event────────────┬─size───────┐
│ CGroupMemoryUsed │ 2.15 GiB │
│ MaxMemoryUsage │ 25.60 GiB │
│ MemoryResident │ 2.08 GiB │
│ MemoryVirtual │ 34.12 GiB │
└──────────────────┴────────────┘
To continuously track if analytical operations are hitting disk-spill boundaries under production query traffic, run an aggregate audit against system.query_log:
docker exec -it clickhouse-server clickhouse-client --user default --password "admin" --query "
SELECT
type,
count() AS total_queries,
formatReadableSize(sum(memory_usage)) AS aggregate_memory_consumed,
countIf(ProfileEvents['ExternalAggregationBytes'] > 0) AS spilled_group_by_queries,
countIf(ProfileEvents['ExternalJoinBytes'] > 0) AS spilled_join_queries
FROM system.query_log
WHERE event_date = today() AND event_time > now() - INTERVAL 1 HOUR
GROUP BY type;
"
If spilled_group_by_queries increases while query execution times remain bounded, the memory spill pipeline is functioning as intended. Intermittent, massive aggregations scale gracefully across storage, preserving stable cluster state, avoiding SIGKILL hazards, and guaranteeing zero downtime on memory-constrained virtual instances.
Network and User Security: TLS Configuration, Quotas, and IP Whitelisting
Hardening the network boundary and query pipeline is mandatory when exposing ClickHouse interfaces over public networks. By default, ClickHouse listens on unencrypted native TCP port 9000 and HTTP port 8123. Exposing these ports without cryptographic encapsulation risks credential sniffing, man-in-the-middle payload alterations, and cleartext query interception.
A resilient setup transitions external ingestion pipelines and analytical dashboards to native TLS port 9440 and HTTPS port 8443, isolates plaintext sockets to loopback bindings, enforces strict host-level network filters, and caps user resource consumption via deterministic execution quotas.
Public Traffic (Internet)
│
▼
┌───────────────────────────────────────┐
│ tropic.host Edge DDoS Filter │ (L3/L4/L7 Volumetric Mitigation)
└──────────────────┬────────────────────┘
│ Clean Static IPv4
▼
┌───────────────────────────────────────┐
│ Host Linux Kernel (BBR) │
│ - netfilter / nftables Whitelist │ (Drops unauthorized CIDRs at wire speed)
│ - somaxconn = 4096, syncookies = 1 │
└──────────────────┬────────────────────┘
│
┌─────────┴─────────┐
│ (Mutual / TLS 1.3)│
▼ ▼
┌─────────────────┐ ┌───────────────────┐
│ Native TLS: 9440│ │ HTTPS: 8443 │
│ (ETL, clickhouse│ │ (Grafana, Metabase│
│ -client, Java) │ │ REST Clients) │
└────────┬────────┘ └─────────┬─────────┘
│ │
└─────────┬──────────┘
▼
┌───────────────────────────────────────┐
│ ClickHouse RBAC & Engine │
│ - SHA-256 Auth + Client CIDR Match │
│ - Profile: max_rows_to_read / 10s │
│ - Quota: 1h Aggregate Read Limits │
└───────────────────────────────────────┘
Operating System and Kernel Network Tuning
Before configuring ClickHouse TLS termination, the underlying virtual network stack must be tuned to process high-throughput analytical ingestion without socket exhaustion, latency spikes, or SYN queue drops. In high-concurrency analytical environments, thousands of short-lived client queries can saturate ephemeral port ranges and bloat the TIME_WAIT bucket.
Apply the following production parameters to /etc/sysctl.d/99-clickhouse-network.conf:
# Expand backlog for burst connection handshakes
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 8192
# Protect against TCP SYN flood volumetric vectors
net.ipv4.tcp_syncookies = 1
# Optimize socket buffer memory allocations (min / default / max in bytes)
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
# Accelerate socket recycling for high-frequency analytical API queries
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 15
# Expand ephemeral port range
net.ipv4.ip_local_port_range = 10240 65535
# Enforce TCP BBR congestion control over Fair Queueing
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr
Apply the configuration immediately without rebooting:
sysctl --system
When deploying on tropic.host, the hypervisor layer provides a dedicated, unmetered 1–10 Gbps uplink directly connected to Tier-1 transit providers across Frankfurt, Amsterdam, and Istanbul. Combining KVM instances with zero CPU oversubscription (%st = 0.0%) and the TCP BBR congestion control algorithm guarantees that high-volume batch inserts achieve maximum sustained line rate, maintaining p99 network latency under 1.5 ms without packet drops caused by queue bufferbloat.
Port Hardening and Native TLS 1.3 Termination
To lock down the network perimeter, disable listening on all public plaintext ports. Create /etc/clickhouse-server/config.d/listen.xml to restrict plain HTTP and TCP sockets to the loopback interface, while exposing TLS-wrapped endpoints across all external interfaces:
<clickhouse>
<!-- Explicit interface bindings -->
<listen_host>127.0.0.1</listen_host>
<listen_host>::1</listen_host>
<listen_host>0.0.0.0</listen_host>
<!-- Plaintext ports strictly bound to localhost via firewall or zeroed -->
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<!-- Encrypted secure ports -->
<https_port>8443</https_port>
<tcp_port_secure>9440</tcp_port_secure>
<interserver_http_port_secure>9010</interserver_http_port_secure>
</clickhouse>
Generate a custom Diffie-Hellman parameter file to harden Ephemeral Diffie-Hellman key exchanges:
openssl dhparam -out /etc/clickhouse-server/certs/dhparam.pem 4096
chmod 600 /etc/clickhouse-server/certs/dhparam.pem
chown clickhouse:clickhouse /etc/clickhouse-server/certs/dhparam.pem
Next, configure cryptographic cipher suites and paths in /etc/clickhouse-server/config.d/tls.xml. This configuration enforces TLS 1.3 as the default, restricts fallback ciphers on TLS 1.2 to modern AEAD variants, and disables insecure legacy protocols (SSLv2, SSLv3, TLS 1.0, and TLS 1.1):
<clickhouse>
<openSSL>
<server>
<!-- Certificate paths -->
<certificateFile>/etc/clickhouse-server/certs/clickhouse.crt</certificateFile>
<privateKeyFile>/etc/clickhouse-server/certs/clickhouse.key</privateKeyFile>
<dhParamsFile>/etc/clickhouse-server/certs/dhparam.pem</dhParamsFile>
<!-- Protocol enforcement: Disable legacy protocols -->
<disallowSSLv2>true</disallowSSLv2>
<disallowSSLv3>true</disallowSSLv3>
<disallowTLSv1>true</disallowTLSv1>
<disallowTLSv1_1>true</disallowTLSv1_1>
<requireTLSv1_2>false</requireTLSv1_2>
<!-- TLS 1.3 Ciphersuites -->
<cipherList>ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256</cipherList>
<preferServerCiphers>true</preferServerCiphers>
<!-- Session caching and lifecycle -->
<sessionCache>true</sessionCache>
<sessionCacheSize>20480</sessionCacheSize>
<sessionTimeout>3600</sessionTimeout>
<verificationMode>none</verificationMode>
<loadDefaultCAFile>true</loadDefaultCAFile>
</server>
<client>
<loadDefaultCAFile>true</loadDefaultCAFile>
<cacheSessions>true</cacheSessions>
<disallowSSLv2>true</disallowSSLv2>
<disallowSSLv3>true</disallowSSLv3>
<disallowTLSv1>true</disallowTLSv1>
<disallowTLSv1_1>true</disallowTLSv1_1>
<preferServerCiphers>true</preferServerCiphers>
<invalidCertificateHandler>
<name>RejectCertificateHandler</name>
</invalidCertificateHandler>
</client>
</openSSL>
</clickhouse>
Validate the secure handshake directly against the server’s loopback and public interfaces:
# Verify HTTPS TLS 1.3 handshakes and cipher negotiation
openssl s_client -connect 127.0.0.1:8443 -tls1_3 -brief
# Execute an authenticated query over the native TLS interface
clickhouse-client \
--host 127.0.0.1 \
--port 9440 \
--secure \
--user default \
--password "admin_password" \
--query "SELECT version(), currentDatabase();"
Layer-3 and Layer-4 Perimeter Defense (nftables)
While ClickHouse contains its own connection-handling rules, exposing software sockets directly to raw Internet traffic wastes user-space CPU cycles negotiating TCP three-way handshakes with malicious scanners. In production workloads running clickhouse on vps analytics, unauthorized packets should be dropped at the kernel netfilter boundary.
Deploy an /etc/nftables.conf ruleset allowing only verified CIDRs (such as internal microservice clusters, BI platform nodes, or jump hosts):
flush ruleset
table inet filter {
set management_ips {
type ipv4_addr
flags interval
elements = { 198.51.100.15/32, 203.0.113.0/24 }
}
set ingestion_nodes {
type ipv4_addr
flags interval
elements = { 192.0.2.10/32, 192.0.2.11/32, 192.0.2.12/32 }
}
chain input {
type filter hook input priority 0; policy drop;
# Accept loopback traffic
iif "lo" accept
# State tracking: Accept established/related connections
ct state established,related accept
ct state invalid drop
# Drop TCP scanning signatures
tcp flags syn / fin,syn,rst,ack syn ct state new accept
tcp flags & (fin|syn|rst|psh|ack|urg) == 0 drop
tcp flags & (fin|syn|rst|psh|ack|urg) == fin|syn|rst|psh|ack|urg drop
# SSH Access restricted to jumpbox
ip saddr @management_ips tcp dport 22 accept
# ClickHouse HTTPS (8443) for BI platforms
ip saddr @management_ips tcp dport 8443 accept
# ClickHouse Native TLS (9440) for high-speed ingestion microservices
ip saddr @ingestion_nodes tcp dport 9440 accept
# Reject all unauthorized traffic explicitly with TCP Reset
reject with tcp reset
}
chain forward {
type filter hook forward priority 0; policy drop;
}
chain output {
type filter hook output priority 0; policy accept;
}
}
Reload and persist the firewall state:
systemctl enable --now nftables
nft -f /etc/nftables.conf
The underlying DDoS protection on tropic.host filters large volumetric attacks (such as SYN-floods, UDP amplification, and malformed ACK waves) at the edge before they hit the VPS interface. This ensures the Linux kernel socket buffer remains clear for valid analytical queries.
SQL-Driven Role-Based Access Control (RBAC) and IP Whitelisting
Managing credentials via users.xml makes dynamic privilege rotation difficult and increases the risk of accidental privilege leaks. In modern deployments, use ClickHouse's native SQL-driven user management (access_control_path).
Add the storage engine path inside /etc/clickhouse-server/config.d/rbac.xml:
<clickhouse>
<user_directories>
<users_xml>
<path>users.xml</path>
</users_xml>
<local_directory>
<path>/var/lib/clickhouse/access/</path>
</local_directory>
</user_directories>
</clickhouse>
Restricting Access via Client Host Filters
ClickHouse allows scoping accounts to specific network identities using the HOST clause. Always use fixed IP addresses or CIDR blocks rather than reverse-DNS lookups (HOST REGEXP), as DNS queries inject latency into the connection handshake and can stall execution if an upstream resolver times out:
-- Create read-only role for business intelligence and dashboards
CREATE ROLE IF NOT EXISTS bi_reporting_role;
-- Grant column-level and read privileges strictly to production analytical schemas
GRANT USAGE ON *.* TO bi_reporting_role;
GRANT SELECT ON analytics_prod.* TO bi_reporting_role;
-- Create an ingestion service role with dedicated append permissions
CREATE ROLE IF NOT EXISTS ingestion_pipeline_role;
GRANT USAGE ON *.* TO ingestion_pipeline_role;
GRANT SELECT, INSERT, ALTER TABLE ON analytics_prod.* TO ingestion_pipeline_role;
-- Create a dedicated BI user restricted to a /24 subnet, authenticated via double SHA-256
CREATE USER IF NOT EXISTS metabase_bi_user
IDENTIFIED WITH sha256_password BY 'V3ry_Str0ng_Pr0d_P@ssw0rd!#2026'
HOST IP '198.51.100.0/24'
DEFAULT ROLE bi_reporting_role;
-- Create an ingestion service user restricted strictly to the data loader host
CREATE USER IF NOT EXISTS vector_shipper
IDENTIFIED WITH sha256_password BY 'S3cur3_Ing3st_T0k3n_998!'
HOST IP '192.0.2.10'
DEFAULT ROLE ingestion_pipeline_role;
Quotas and Query Execution Profiles
Ad-hoc analytical queries can easily consume an entire server's resources. An unindexed aggregation over hundreds of millions of rows can saturate CPU cores, allocate gigabytes of memory, and trigger the out-of-memory (OOM) killer.
To prevent noisy neighbors and query runaways, enforce deterministic constraints at the profile level and track resource usage with ClickHouse quotas.
1. Defining Execution Settings Profiles
Create settings profiles in ClickHouse to set boundaries on memory consumption, execution timeouts, thread parallelism, and disk read volume.
-- Profile for external BI reporting tools (Metabase, Superset, Grafana)
CREATE SETTINGS PROFILE IF NOT EXISTS bi_analyst_profile
SETTINGS
max_execution_time = 30, -- Hard limit: 30 seconds
max_memory_usage = 8589934592, -- Max 8 GiB RAM per query
max_rows_to_read = 200000000, -- Scan ceiling: 200 million rows
max_bytes_to_read = 17179869184, -- Max 16 GiB uncompressed data read
read_overflow_mode = 'throw', -- Abort query if scan limits are exceeded
timeout_overflow_mode = 'throw', -- Abort query on execution timeout
max_threads = 8, -- Bound CPU thread count
log_queries = 1, -- Record query metrics in system.query_log
log_query_threads = 1;
-- Assign the profile to the BI role
ALTER ROLE bi_reporting_role SETTINGS PROFILE bi_analyst_profile;
2. Defining Time-Sliding Resource Quotas
While a settings profile sets limits on a single query, a QUOTA tracks and limits resource usage across rolling time intervals (e.g., 1 hour or 24 hours). This prevents automated bots or runaway dashboards from degrading system performance with repeated heavy queries.
-- Create an hourly sliding quota for external reporting
CREATE QUOTA IF NOT EXISTS hourly_bi_reporting_quota
KEYED BY user_name
FOR INTERVAL 1 HOUR
MAX queries = 500, -- Cap execution frequency
MAX query_selects = 500,
MAX execution_time = 1800, -- Max 30 minutes of cumulative CPU time/hour
MAX errors = 25, -- Throttle clients generating repetitive syntax errors
MAX result_rows = 50000000, -- Cap aggregate output volume
MAX read_rows = 2000000000, -- Maximum 2 Billion rows processed per hour
MAX execution_time_overflow_mode = 'throw'
FOR INTERVAL 1 DAY
MAX execution_time = 14400, -- Maximum 4 hours of cumulative execution/day
MAX read_rows = 10000000000;
-- Assign the quota to the analytical user
ALTER USER metabase_bi_user QUOTA hourly_bi_reporting_quota;
Auditing and Validating Security Enforcement
To verify that quotas and network policies are functioning correctly, simulate a heavy scan using clickhouse-client and inspect the system catalogs.
1. Verifying Real-Time Quota Usage
Run a query against system.quota_usage to verify consumption metrics across time intervals:
SELECT
quota_name,
quota_key,
interval_duration,
queries,
max_queries,
formatReadableTimeDelta(execution_time) AS total_exec_time,
formatReadableTimeDelta(max_execution_time) AS exec_time_limit,
formatReadableQuantity(read_rows) AS rows_read,
formatReadableQuantity(max_read_rows) AS max_rows_read
FROM system.quota_usage
WHERE quota_name = 'hourly_bi_reporting_quota'
FORMAT Vertical;
Output:
Row 1:
──────
quota_name: hourly_bi_reporting_quota
quota_key: metabase_bi_user
interval_duration: 3600
queries: 42
max_queries: 500
total_exec_time: 126 seconds
exec_time_limit: 30 minutes
rows_read: 84.21 million
max_rows_read: 2.00 billion
2. Auditing Authentication Failures and Blocked Hosts
ClickHouse logs failed connection attempts and network rejections directly to the system logs. Run the following audit query against system.query_log to identify unauthorized connection attempts or hosts violating network filters:
SELECT
event_time,
initial_user,
client_name,
client_address,
exception_code,
splitByChar('\n', exception)[1] AS error_cause
FROM system.query_log
WHERE type = 'ExceptionBeforeStart'
AND event_date = today()
AND event_time > now() - INTERVAL 4 HOUR
ORDER BY event_time DESC
LIMIT 10;
If an unauthorized host attempts an analytical query, ClickHouse terminates the socket immediately:
┌─event_time──────────┬─initial_user─────┬─client_name──────┬─client_address─┬─exception_code─┬─error_cause────────────────────────────────────────────┐
│ 2026-10-04 13:42:10 │ metabase_bi_user │ ClickHouse CLI │ 198.51.100.220 │ 511 │ DB::Exception: User metabase_bi_user is not allowed... │
└─────────────────────┴──────────────────┴──────────────────┴────────────────┴────────────────┴────────────────────────────────────────────────────────┘
By enforcing TLS 1.3 encryption on ports 9440 and 8443, dropping unauthorized IP ranges at the kernel firewall boundary, using SQL-driven role controls, and setting strict query quotas, you create a hardened environment for clickhouse on vps analytics. This setup prevents rogue queries from monopolizing system resources and protects analytical data against network-level interception.
Automated Backups with clickhouse-backup and S3 Disaster Recovery Runbook
Protecting production data in a clickhouse on vps analytics deployment requires an architecture that decouples live query execution from point-in-time state capture. Traditional file-level backups (such as raw tar archives or file copies of /var/lib/clickhouse/data) inevitably lead to corrupted data parts because ClickHouse continuously merges, mutates, and deletes background parts via the MergeTree engine. Conversely, streaming logical SQL dumps via clickhouse-client --query="SELECT ..." exhausts vCPU capacity, saturates RAM with query caches, and fails completely on datasets exceeding tens of gigabytes.
The production standard is zero-downtime atomic snapshots executed via the native ClickHouse hard-link mechanism (ALTER TABLE ... FREEZE), orchestrated and pushed to remote S3-compatible object storage using clickhouse-backup.
Snapshot Mechanics: Zero-Copy Local Freezing
When a backup is triggered, ClickHouse does not duplicate columnar data on disk. Instead, the storage engine executes a filesystem-level link() system call, creating hard links pointing to the identical disk inodes from the active table directory (/var/lib/clickhouse/data/<database>/<table>/) into the shadow directory (/var/lib/clickhouse/shadow/<backup_number>/).
Active Partition Directory Shadow Backup Directory
/var/lib/clickhouse/data/analytics/events/ /var/lib/clickhouse/shadow/1/data/analytics/events/
├── 202610_1_45_3/ ├── 202610_1_45_3/
│ ├── count.txt (inode 842101) <────────────┼── count.txt (inode 842101)
│ ├── data.bin (inode 842102) <────────────┼── data.bin (inode 842102)
│ └── data.mrk3 (inode 842103) <────────────┼── data.mrk3 (inode 842103)
Because hard links share the exact physical blocks of the underlying filesystem, freezing a 1 TB analytics table takes under 300 milliseconds and consumes zero additional bytes of storage at inception. Additional disk space is only consumed incrementally as ClickHouse executes subsequent background part merges, which write new merged parts to fresh inodes while the shadow directory preserves references to the old ones.
On virtualized infrastructure, this metadata-intensive operation demands high-throughput, low-latency disk I/O. On tropic.host KVM instances backed by enterprise PCIe 4.0 NVMe drives, random 4K metadata allocations exceed 50,000 IOPS with predictable sub-millisecond p99 latencies. Combined with guaranteed hardware reservations (%st = 0.0%), background snapshot operations never stall incoming analytical ingestion pipelines.
Installing and Configuring clickhouse-backup
Download and install the latest release binary on your Linux host:
# Retrieve latest release architecture-specific package (AMD64 / ARM64)
CLICKHOUSE_BACKUP_VERSION=$(curl -s https://api.github.com/repos/Altinity/clickhouse-backup/releases/latest | jq -r .tag_name | sed 's/v//')
curl -fSL "https://github.com/Altinity/clickhouse-backup/releases/download/v${CLICKHOUSE_BACKUP_VERSION}/clickhouse-backup_${CLICKHOUSE_BACKUP_VERSION}_amd64.deb" -o /tmp/clickhouse-backup.deb
sudo dpkg -i /tmp/clickhouse-backup.deb
rm -f /tmp/clickhouse-backup.deb
Generate the production configuration at /etc/clickhouse-backup/config.yml. Secure the file with strict POSIX permissions (chmod 600) to prevent unprivileged users from reading S3 credentials:
general:
remote_storage: "s3"
max_file_size: 1073741824 # 1 GiB parts for multi-part upload optimization
disable_progress_bar: true
backups_to_keep_local: 1
backups_to_keep_remote: 14 # Retain two weeks of daily remote snapshots
log_level: "info"
allow_empty_backups: false
clickhouse:
username: "backup_operator"
password: "SecureProductionBackupPassword2026!"
host: "127.0.0.1"
port: 9000
disk_mapping: {}
skip_tables:
- "system.*"
- "INFORMATION_SCHEMA.*"
- "information_schema.*"
timeout: "10m"
freeze_by_part: false
s3:
access_key: "S3_PROD_ACCESS_KEY"
secret_key: "S3_PROD_SECRET_KEY"
bucket: "production-clickhouse-backups-fra"
endpoint: "https://s3.eu-central-1.amazonaws.com"
region: "eu-central-1"
acl: "private"
force_path_style: false
path: "backups/clickhouse_node_01"
disable_ssl: false
compression_format: "zstd"
compression_level: 3
concurrency: 4
part_size: 67108864 # 64 MiB buffer chunks
max_parts: 10000
buffer_size: 33554432 # 32 MiB RAM upload buffer per thread
Dedicated RBAC Account in ClickHouse
Never execute automated backup tasks under the root default profile. Create a dedicated backup_operator with least-privilege permissions:
CREATE USER backup_operator IDENTIFIED WITH sha256_password BY 'SecureProductionBackupPassword2026!';
GRANT SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, SHOW DICTIONARIES ON *.* TO backup_operator;
GRANT SELECT ON system.* TO backup_operator;
GRANT SYSTEM FREEZE ON *.* TO backup_operator;
GRANT SYSTEM RELOAD CONFIG ON *.* TO backup_operator;
Automated Execution with Systemd Timers and cgroups v2
Running compression and remote uploads during business hours can degrade analytical read queries if CPU and I/O are unconstrained. To prevent performance degradation, isolate clickhouse-backup into a dedicated cgroup slice with bounded compute and I/O weights.
1. Define the Systemd Service Unit
Create /etc/systemd/system/clickhouse-backup.service:
[Unit]
Description=ClickHouse Automated S3 Backup
After=clickhouse-server.service network-online.target
Wants=network-online.target
[Service]
Type=oneshot
User=clickhouse
Group=clickhouse
Nice=15
CPUSchedulingPolicy=idle
CPUWeight=100
IOWeight=100
MemoryHigh=2G
MemoryMax=3G
ExecStart=/usr/bin/clickhouse-backup create_remote --tables="analytics.*" auto_backup_%%Y%%m%%d_%%H%%M%%S
ExecStartPost=/usr/bin/clickhouse-backup clean_remote_broken
StandardOutput=journal
StandardError=journal
2. Define the Daily Execution Timer
Create /etc/systemd/system/clickhouse-backup.timer:
[Unit]
Description=Trigger ClickHouse Daily Backup at Off-Peak Hours
[Timer]
OnCalendar=*-*-* 02:30:00 UTC
RandomizedDelaySec=600
Persistent=true
[Install]
WantedBy=timers.target
Enable and activate the schedule:
sudo systemctl daemon-reload
sudo systemctl enable --now clickhouse-backup.timer
sudo systemctl list-timers clickhouse-backup.timer
Disaster Recovery Runbook: Bare-Metal and Volume Restore
When catastrophic failure occurs—such as fatal NVMe hardware corruption, catastrophic data drops, or node redeployment—follow this tested Disaster Recovery Runbook to restore service availability.
Disaster Recovery Workflow
Remote S3 Bucket
[production-clickhouse-backups-fra]
│
│ 1. clickhouse-backup download <name>
▼
Local Storage (/var/lib/clickhouse/backup/)
│
│ 2. clickhouse-backup restore --schema <name>
▼
Target Database (DDL Rebuilt via Native Engine)
│
│ 3. clickhouse-backup restore --data <name>
▼
Data Placement (/var/lib/clickhouse/data/analytics/events/detached/)
│
│ (Native ALTER TABLE ... ATTACH PARTITION)
▼
Live Active Parts Verified in system.parts
Step 1: Provision and Tune the Target Host
On the new tropic.host KVM VPS, install identical ClickHouse server and clickhouse-backup versions. Configure the Linux kernel memory parameters to prevent aggressive paging stalls during bulk data restoration:
# Append to /etc/sysctl.d/99-clickhouse-recovery.conf
cat << 'EOF' | sudo tee /etc/sysctl.d/99-clickhouse-recovery.conf
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.max_map_count = 1048576
net.core.somaxconn = 4096
net.ipv4.tcp_congestion_control = bbr
EOF
sudo sysctl --system
Step 2: List and Pull Remote Backups from S3
Ensure /etc/clickhouse-backup/config.yml is populated with the correct S3 credentials and endpoint settings:
# Query remote storage metadata
clickhouse-backup list remote
Sample output:
2026/10/04 14:02:11 [INFO] auto_backup_20261003_023000 142.85GB 03/10/2026 02:38:12 remote tar,zstd [analytics.*]
2026/10/04 14:02:11 [INFO] auto_backup_20261004_023000 144.12GB 04/10/2026 02:37:58 remote tar,zstd [analytics.*]
Download the target archive locally to /var/lib/clickhouse/backup/:
# Ensure sufficient NVMe disk space before pulling
clickhouse-backup download auto_backup_20261004_023000
Step 3: Execute Two-Stage Database Restoration
Restoration must proceed in two distinct phases: creating the empty schema definitions, followed by populating and attaching data parts.
# 1. Restore Database Structure, Dicts, and Table Schemas (DDL only)
clickhouse-backup restore --schema auto_backup_20261004_023000
# 2. Restore Physical Columnar Data Parts
clickhouse-backup restore --data auto_backup_20261004_023000
During --data restoration, clickhouse-backup moves the downloaded parts directly into /var/lib/clickhouse/data/<database>/<table>/detached/ and executes the non-blocking SQL instruction ALTER TABLE <table> ATTACH PART '<part_name>'. This allows the ClickHouse server engine to ingest, validate, and verify the checksums of all parts natively without requiring a server reboot.
Step 4: Verify Data Integrity and Part Consistency
Run the following validation query against system.parts to ensure there are no orphaned, unattached, or corrupted parts:
SELECT
database,
table,
count() AS total_parts,
formatReadableSize(sum(bytes_on_disk)) AS active_disk_size,
sum(rows) AS total_rows,
min(min_date) AS min_boundary,
max(max_date) AS max_boundary
FROM system.parts
WHERE database = 'analytics' AND active = 1
GROUP BY database, table;
Expected output confirmation:
┌─database──┬─table──┬─total_parts─┬─active_disk_size─┬─total_rows──┬─min_boundary─┬─max_boundary─┐
│ analytics │ events │ 312 │ 141.98 GiB │ 1420891040 │ 2024-01-01 │ 2026-10-04 │
└───────────┴────────┴─────────────┴──────────────────┴─────────────┴──────────────┴──────────────┘
Finally, inspect system.detached_parts to verify that zero parts remain unattached:
SELECT count() AS unattached_parts_count FROM system.detached_parts WHERE database = 'analytics';
If unattached_parts_count returns 0, the storage engine has validated all part headers and merged data trees. The disaster recovery sequence is complete, and the analytical database is immediately open for incoming analytical queries.
Frequently Asked Questions (FAQ)
Can ClickHouse run efficiently on a moderate KVM VPS?
Yes, ClickHouse is exceptionally resource-efficient. A 4–8 vCPU KVM VPS with 16–32 GB RAM on enterprise PCIe 4.0 NVMe can easily process hundreds of millions of rows per second.
Why is 0% CPU Steal Time critical for ClickHouse?
ClickHouse parallelizes query execution across all available physical cores. CPU throttling or steal time (> 1%) disrupts synchronization between threads, causing severe query latency spikes.