Monitor a running server

The tables that matter#

Track table size and part count#

Part count is the single most useful ingestion health signal. Rising counts mean inserts are outpacing merges:

SELECT
    database,
    table,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

Alert when any table approaches parts_to_delay_insert (default 1000). Reaching parts_to_throw_insert (default 3000) fails inserts outright — see inserting data.

Find expensive queries#

SELECT
    query_duration_ms,
    query,
    read_rows,
    formatReadableSize(read_bytes) AS read_data,
    formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

The type column also carries QueryStart, ExceptionBeforeStart, and ExceptionWhileProcessing. Query failures by type:

SELECT
    type,
    count() AS queries
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 DAY
GROUP BY type;

Measure compression per column#

Compression ratio tells you where storage is going and whether a codec or type change would pay off:

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'uk_price_paid'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

A column with a poor ratio is a candidate for a narrower type, LowCardinality, or a specialized codec — see data types.

Watch replication lag#

On a replicated cluster, lag is the number that matters:

SELECT
    database,
    table,
    is_leader,
    is_readonly,
    absolute_delay,
    queue_size
FROM system.replicas
WHERE absolute_delay > 0;

is_readonly set on a replica usually means it has lost its connection to ClickHouse Keeper. A growing queue_size with a stable absolute_delay means the replica is fetching but not keeping up.

Check stuck mutations#

Mutations run asynchronously and can fail quietly:

SELECT
    database,
    table,
    mutation_id,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE NOT is_done;

A mutation that is not done and reports a failure reason will not retry itself.

See what is running now#

SELECT
    query_id,
    user,
    elapsed,
    read_rows,
    formatReadableSize(memory_usage) AS memory,
    query
FROM system.processes
ORDER BY elapsed DESC;

Kill a runaway query by id:

KILL QUERY WHERE query_id = 'the-query-id';

Passing your own query_id when issuing a query makes this straightforward — the HTTP interface accepts it as a parameter.

What to alert on#

• Part count per table approaching parts_to_delay_insert.
• Replication lag above your tolerance for stale reads.
• Query failure rate from system.query_log by type.
• Disk usage from system.parts, trended rather than sampled.
• Stuck mutations with a non-empty latest_fail_reason.

For logs, metrics, and traces from your applications rather than from the database itself, ClickStack pairs ClickHouse with HyperDX and an OpenTelemetry collector.

Related#

• Insert data efficiently — the cause behind most part-count alerts
• Optimize query performance — what to do with the slow queries you find
• MergeTree and data parts — what parts and merge levels mean