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.

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

Rack Awareness and Cross-AZ Kafka Traffic

Load 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

Settings Reference