Understanding ClickHouse Architecture

Columnar Storage and Execution Model

ClickHouse stores data by columns rather than rows, allowing efficient compression and vectorized processing. This architectural choice is optimized for analytics but introduces certain risks—especially around memory usage and query execution on large datasets when improperly tuned.

Distributed Replication

Data is distributed across multiple shards and replicated across replicas using ZooKeeper. Each part ingestion and merge task runs independently across replicas, meaning performance or memory issues on one node can silently cause inconsistency or lag.

Common Symptoms and Root Causes

Uncontrolled Memory Growth

Frequent complaints include out-of-memory errors or Kubernetes pod evictions. Often, this stems from improperly configured max_bytes_before_external_group_by or max_memory_usage settings.

Replica Lag and Part Mutations

Lagging replicas can fall behind due to large part merges or background mutations that overwhelm I/O and memory budgets. Slow merges delay part movement and eventually disrupt SELECT consistency across replicas.

Step-by-Step Diagnostic Process

Step 1: Review Memory Configuration

Check memory usage constraints per user and query.

SELECT * FROM system.settings WHERE name LIKE '%memory%';

Validate values for:

• max_memory_usage
• max_memory_usage_for_all_queries
• max_bytes_before_external_group_by

Step 2: Monitor Replica Queue

Identify lagged replicas and pending operations.

SELECT * FROM system.replication_queue WHERE is_currently_executing = 1;

This shows parts not yet fetched, merged, or mutated. Pay attention to create_time and num_tries.

Step 3: Analyze Merge Performance

Use system.merges to identify bottlenecks:

SELECT * FROM system.merges WHERE is_currently_executing = 1;

Large merges that hang or retry often indicate disk I/O issues or overly aggressive mutation settings.

Architectural Implications

Memory Isolation Per Role

On shared environments like Kubernetes, ClickHouse's greedy memory use can violate pod limits. Configure role-based memory limits using profiles and segregate ingestion vs. query workloads with different user.xml profiles.

ZooKeeper Bottlenecks

Each part operation creates ZooKeeper events. For high-ingestion clusters, improper insert_quorum and mutation frequency lead to ZK overload. Monitor system.zookeeper and enforce rate-limiting through background_pool_size tuning.

Step-by-Step Remediation

1. Tune Memory Settings

<users>
  <default>
    <profiles>
      <default>
        <max_memory_usage>4294967296</max_memory_usage>
        <max_bytes_before_external_group_by>1073741824</max_bytes_before_external_group_by>
      </default>
    </profiles>
  </default>
</users>

2. Balance Merge Load

SET background_pool_size = 8;
SET background_merge_pool_size = 4;

3. Use TTL and Part Compacting

TTL-based policies reduce long-lived large parts:

ALTER TABLE events MODIFY TTL event_time + INTERVAL 7 DAY;

4. Monitor ZK Connection Load

SELECT * FROM system.zookeeper WHERE path LIKE '%replicas%';

Best Practices for Stability

• Segment workloads by isolating ingestion and query nodes
• Use S3-backed storage for large clusters to offload disk pressure
• Pre-aggregate data on ingestion if query latency matters
• Monitor system.part_log for part size explosions
• Apply mutations sparingly; schedule in low-load windows

Conclusion

ClickHouse offers unmatched performance for OLAP workloads, but stability under scale requires architectural foresight. By proactively monitoring memory, merges, and replication internals, engineers can prevent silent failures that only surface under load. Tuning memory boundaries, balancing background tasks, and isolating roles go a long way in achieving resilient, high-throughput ClickHouse clusters.

FAQs

1. Why do merges cause replica lag in ClickHouse?

Long-running merges monopolize disk I/O and block mutation fetches. Without tuning merge concurrency, replicas accumulate lag.

2. Can ClickHouse run reliably on Kubernetes?

Yes, but only with strict resource limits, anti-affinity, persistent volumes, and separate profiles for ingestion/query paths.

3. How do I detect silent data inconsistencies?

Query system.replication_queue and compare part hashes across replicas. Differences suggest missed fetch or merge conflicts.

4. How do I reduce ClickHouse memory usage during heavy queries?

Use external GROUP BY with max_bytes_before_external_group_by and consider optimizing schema for denormalized access.

5. Is ZooKeeper a scaling bottleneck in ClickHouse?

Yes. Excessive mutations and inserts per second stress ZK. Monitor session count and event lag; consider sharding large tables.