Diagnostics

Run all queries from the file checks.sql and analyze the results.

Problem-Specific Investigation

Insert with MV Overhead - Correlate by Query ID

When inserts feed materialized views, slow MVs cause insert delays. To correlate a slow insert with its MV breakdown:

-- Correlate slow insert with MV breakdown (requires query_id) select view_name, view_duration_ms, read_rows, written_rows, status from system.query_views_log where query_id = '{query_id}' order by view_duration_ms desc

Kafka Consumer Exception Drill-Down (Targeted)

Use this only for problematic Kafka tables to avoid noisy output.

-- Filter to a specific Kafka table when lag is observed select hostName() as host, database, table, consumer_id, is_currently_used, dateDiff('second', last_poll_time, now()) as last_poll_age_s, dateDiff('second', last_commit_time, now()) as last_commit_age_s, num_messages_read, num_commits, length(assignments.topic) as assigned_partitions, length(exceptions.text) as exception_count, exceptions.text[-1] as last_exception from clusterAllReplicas('{cluster}', system.kafka_consumers) where database = '{db}' and table = '{kafka_table}' order by is_currently_used desc, last_poll_age_s desc limit 50

Ad-Hoc Query Guidelines

Required Safeguards

-- Always limit results limit 100

-- Always time-bound where event_date = today() -- or where event_time > now() - interval 1 hour

-- For query_log, filter by type where type = 'QueryFinish' -- completed -- or where type like 'Exception%' -- failed

Useful Filters

-- Filter by table where has(tables, 'database.table_name')

-- Filter by user where user = 'producer_app'

-- Filter by insert size where written_rows > 1000000 -- large inserts where written_rows < 100 -- micro-batches