ClickHouse Cheatsheet 12 — Operations and Monitoring

SELECT * FROM system.tables LIMIT 5;
SELECT * FROM system.parts WHERE table = 'events';
SELECT * FROM system.metrics;
SELECT * FROM system.events;
SELECT * FROM system.processes;
SELECT * FROM system.query_log WHERE event_time > now() - INTERVAL 1 HOUR ORDER BY query_duration_ms DESC LIMIT 10;
SELECT * FROM system.merges;
SELECT * FROM system.mutations;

SELECT
    query, query_duration_ms, read_rows, memory_usage
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC LIMIT 10;

SELECT query_id, query, elapsed, memory_usage
FROM system.processes
ORDER BY elapsed DESC;

KILL QUERY WHERE query_id = '...';

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

SELECT table, queue_size, absolute_delay
FROM system.replicas;

<prometheus>
  <endpoint>/metrics</endpoint>
  <port>9363</port>
</prometheus>

OPTIMIZE TABLE events FINAL;

ALTER TABLE events DETACH PARTITION 202601;
ALTER TABLE events ATTACH PARTITION 202601;