Overview
A security analytics provider ran ReplicatedMergeTree tables across a multi-shard cluster. Replication queues would stall behind stuck entries, ad-hoc analyst queries hit memory limits and failed, and delete mutations accumulated without completing. AceMQ took over support with named senior engineers and no tier-1 triage.
Challenge
ClickHouse failure modes are unfamiliar to teams coming from row-store databases. A stalled replication queue entry blocks everything behind it and needs deliberate intervention. Memory limit failures on analyst queries were being answered by raising limits, which pushed the cluster toward host-level memory pressure instead. Mutations were being issued as routine deletes without regard for the rewrite cost they impose.
Environment
ClickHouse on Kubernetes with ClickHouse Keeper coordination, serving security event search and analytics.
Approach
AceMQ established diagnosis procedures for each recurring failure class so the customer's team could handle common cases directly, reserving escalation for genuinely novel incidents. Query-level controls replaced blanket memory limit increases, and mutation practice was brought under policy.
Solution
- Built diagnosis and recovery procedures for stalled replication queue entries and Keeper session issues
- Replaced blanket memory limit increases with per-query and per-user limits plus controlled spilling to disk
- Rewrote the highest-cost analyst queries to use projections and aggregate tables instead of full scans
- Established mutation policy covering batching, scheduling, and when TTL-based expiry is the better tool
- Instrumented replication delay, mutation backlog, and memory usage per query as tracked signals
- Provided 24/7 escalation directly to senior ClickHouse engineers under a 15-minute emergency SLA
Outcome
Replication stalls are now detected and cleared before they affect query results, and memory-related query failures dropped to a small fraction of their previous rate. The customer's team resolves most recurring cases from the runbooks without escalating.
Technologies
ClickHouseClickHouse Keeper
Kubernetes
Prometheus
Kafka
S3
Grafana
