How to Monitor Slow Queries in ClickHouse

Galaxy Glossary

How do I find and optimize slow queries in ClickHouse?

Use system tables and settings to detect, analyze, and optimize queries that exceed a desired execution time threshold.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why should I track slow queries in ClickHouse?

Slow queries indicate inefficient SQL, insufficient indexing, or resource bottlenecks. Tracking them lets you tune schema, rewrite queries, and resize hardware before performance hurts users.

Which ClickHouse tables log slow queries?

The system.query_log and system.processes tables record every statement. Filter by query_duration_ms, type='QueryFinish', and time windows to isolate slowness.

How do I enable detailed query logging?

Set log_queries = 1 and adjust log_query_threads = 1, query_log_min_query_duration_ms, and query_log_min_type in config.xml or per session.Lower thresholds capture more data.

How can I list queries slower than 1 s today?

Run a SELECT against system.query_log filtering by event_time > today() and query_duration_ms > 1000. Sort by duration to surface worst offenders quickly.

How do I pinpoint slow joins on ecommerce tables?

Filter the log by query LIKE '%Orders% JOIN %Customers%'.Combine with arraySum(memory_usage) to find memory-hungry statements joining Orders and Customers.

What are best practices after finding slow queries?

Rewrite SQL to minimize full scans, pre-aggregate where possible, use appropriate table engines, and add MATERIALIZED VIEWs for frequently requested slices. Test with EXPLAIN and benchmark.

How can I alert on slow queries automatically?

Create a scheduled query exporting slow-query rows to a dashboard or send alerts via system.tasks and external scripts. Monitor trends and investigate spikes promptly.

Why How to Monitor Slow Queries in ClickHouse is important

How to Monitor Slow Queries in ClickHouse Example Usage


-- Detect "Orders" queries running over 200 ms this week
SELECT
    query_start_time,
    query_duration_ms,
    user,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 200
  AND event_time >= toStartOfWeek(now())
  AND query ILIKE '%SELECT%FROM Orders%'
ORDER BY query_duration_ms DESC;

How to Monitor Slow Queries in ClickHouse Syntax


SELECT
    query_start_time,
    query_duration_ms,
    user,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > <duration_ms>
  AND event_date >= today()
ORDER BY query_duration_ms DESC;

-- Example for ecommerce: find "Orders" queries >200 ms
SELECT query_start_time, query_duration_ms, query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 200
  AND query ILIKE '%Orders%'
ORDER BY query_duration_ms DESC;

Common Mistakes

Ignoring the <code>type</code> column. Query log stores <code>QueryStart</code>, <code>QueryFinish</code>, and <code>ExceptionBeforeStart</code>. Filtering only on duration without <code>type='QueryFinish'</code> mixes incomplete data. Always restrict to <code>QueryFinish</code> to measure actual runtimes.
Logging every query in production. Setting <code>query_log_min_query_duration_ms</code> to <code>0</code> produces huge logs and disk I/O. Use a realistic threshold (e.g., 50–100 ms) or restrict to specific users to avoid performance impact.