# Altinity Expert Clickhouse Kafka

> Diagnose ClickHouse Kafka engine health, consumer status, thread pool capacity, consumption issues, and Kafka rack-awareness problems. Use for Kafka lag, consumer errors, thread starvation, or unexpected cross-AZ Kafka traffic and cost.

- Skill: `altinity/altinity-expert-clickhouse-kafka` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add altinity/altinity-expert-clickhouse-kafka`
- Raw SKILL.md: https://api.skillmd.com/api/skills/altinity/altinity-expert-clickhouse-kafka/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: Apache-2.0
- Author: altinity (https://skillmd.com/u/altinity)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/altinity/altinity-expert-clickhouse-kafka

---


## Diagnostics

Run all queries from `checks.sql` in this skill's directory and analyze the results.

---

## Interpreting Results

### Consumer Health

Check if consumers are stuck by comparing exception time vs activity times:

- `last_exception_time >= last_poll_time` OR `last_exception_time >= last_commit_time` → consumer stuck on error, not progressing
- Otherwise → consumer healthy

The `exceptions` column is a tuple of arrays with matching indices — `exceptions.time[-1]` and `exceptions.text[-1]` give the most recent error.

### Thread Pool Capacity

- `kafka_consumers > mb_pool_size` → thread starvation — consumers waiting for available threads
- Fix: increase `background_message_broker_schedule_pool_size` (default: 16)
- Sizing: total Kafka + RabbitMQ/NATS consumers + 25% buffer

### Slow Materialized Views (Poll Interval Risk)

- MV avg duration > 30s → consumer may exceed `max.poll.interval.ms` and get kicked from the group
- MV executions with error status → likely consumer rebalances (consumer kicked, MV interrupted mid-batch)
- **Most common root cause for slow MVs:** multiple `JSONExtract` calls re-parsing the same JSON blob
- **Fix:** rewrite to one-pass `JSONExtract(json, 'Tuple(...)') AS parsed` + `tupleElement()` — see [troubleshooting.md](troubleshooting.md)

### Pool Utilization Trends (12h)

- Sustained high values near pool size → capacity pressure
- Spikes correlating with lag → temporary overload
- Flat zero → Kafka consumers may not be active

---

## Advanced Diagnostics

For deeper investigation, run queries from `advanced_checks.sql` in this skill's directory:

- **Consumer exception drill-down** — filter to a specific problematic Kafka table
- **Consumption speed measurement** — snapshot-based rate calculation
- **Topic lag via rdkafka_stat** — total lag per table and per-partition breakdown
- **Broker connection health** — connection state, errors, disconnects

**Important:** `rdkafka_stat` is **not enabled by default** in ClickHouse. It requires `<statistics_interval_ms>` in the Kafka engine settings. See `advanced_checks.sql` for setup instructions.

---

## Common Issues

For troubleshooting common errors and configuration guidance, see [troubleshooting.md](troubleshooting.md):

- Topic authorization / ACL errors
- Poll interval exceeded (slow MV / JSON parsing optimization)
- Thread pool starvation
- Parsing errors / dead letter queue
- Data loss with multiple materialized views
- Offset rewind / replay
- Parallel consumption tuning

---

## Rack Awareness and Cross-AZ Kafka Traffic

Load [references/rack-awareness.md](references/rack-awareness.md) when the
investigation involves AWS MSK, `client.rack`, `KAFKA_CLIENT_RACK`, Kafka
cross-AZ or cross-region cost, VPC endpoints, or migration to a new Kafka
cluster. It checks the broker-side replica selector, the real broker rack IDs,
the consumer's physical location, and the ClickHouse configuration before
recommending a change.

`client.rack` on its own changes nothing: the brokers must run
`RackAwareReplicaSelector`, which is not the default. Verify that first.

---

## Cross-Module Triggers

| Finding | Load Module | Reason |
|---------|-------------|--------|
| Slow MV inserts | `altinity-expert-clickhouse-ingestion` | Insert pipeline analysis |
| High merge memory | `altinity-expert-clickhouse-merges` | Merge patterns |
| Query-level issues | `altinity-expert-clickhouse-reporting` | Query optimization |
| Schema concerns | `altinity-expert-clickhouse-schema` | Table design |
| Cross-AZ cost remains after Kafka checks, or traffic uses ClickHouse interserver ports | `altinity-expert-clickhouse-replication` | Separate Kafka fetch traffic from inter-replica traffic |

---

## Settings Reference

| Setting | Scope | Notes |
|---------|-------|-------|
| `background_message_broker_schedule_pool_size` | Server | Thread pool for Kafka/RabbitMQ/NATS consumers (default: 16) |
| `kafka_num_consumers` | Table | Parallel consumers per table (limited by cores) |
| `kafka_thread_per_consumer` | Table | Required for parallel inserts (`= 1`) |
| `kafka_handle_error_mode` | Table | `stream` (21.6+) or `dead_letter` (25.8+) |
| `max_poll_interval_ms` | librdkafka | Max time between polls before consumer is kicked (default: 300s) |
| `statistics_interval_ms` | librdkafka | Enable rdkafka_stat collection (disabled by default) |