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

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.