altinity-expert-clickhouse-kafka

Original：🇺🇸 English

Translated

Diagnose ClickHouse Kafka engine health, consumer status, thread pool capacity, and consumption issues. Use for Kafka lag, consumer errors, and thread starvation.

3installs

Sourcealtinity/skills

Added on2026-02-11

NPX Install

npx skill4agent add altinity/skills altinity-expert-clickhouse-kafka

SKILL.md Content

View Translation Comparison →

Diagnostics

Run all queries from the file checks.sql 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

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:

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:

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

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

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)