Query Metrics

Query Metrics shows a second-by-second timeline of how a query used resources while it ran: memory, CPU, disk IO, cache hits and misses, network, and hundreds of other counters. It shows the resource use of one query over its whole lifetime.

Go to Tools > Query Metrics.

What Are Query Metrics?

When a query runs for more than about 1 second, ClickHouse® takes a snapshot of its resource use every second. Each snapshot records hundreds of counters: how much memory the query uses right now, how many bytes it has read from disk so far, how many cache hits and misses occurred, and much more.

ClickHouse® stores these snapshots in the system.query_metric_log table. Query Metrics turns the raw data into visual timelines, grouped by category and unit, so you can see exactly how resources were used over the query's lifetime.

When is this useful?

• A query uses too much memory and you want to see exactly when the memory spike happens.
• A query is slow and you want to know whether it spends time on disk reads, cache misses, or network waits.
• You want to compare two queries to understand why one is faster than the other.
• You need to tune cache sizes and want to see the hit and miss ratio for a specific workload.

Getting Started

1. Set the From and To datetime fields. The default is the last 1 hour.
2. Click Load Queries. This shows queries that have per-second metric data.
3. Click a query in the list to see its details (query ID, SQL text, duration), then click Use This Query.
4. Click Show Query Metrics.
5. Charts appear, grouped by category and unit.

Only categories with non-zero data appear. A simple SELECT 1 might show only Memory and CPU. A complex query with JOINs, disk spilling, and remote reads shows many more groups.

Understanding the Charts

Layout

Charts appear two per row in a grid. Each chart shows one category of metrics at one unit of measurement. The X axis is time, one data point per second of query execution. The Y axis is the metric value, labeled with the unit.

If a category has metrics with different units, for example Memory has both byte values and count values, they appear as separate charts, so the Y axis scale stays meaningful. You see labels like "Memory, Bytes" and "Memory, Count" side by side.

Reading the Charts

Each colored line is one metric. The legend at the bottom shows the metric names, with the ProfileEvent_ prefix stripped for readability. Click a legend item to show or hide that metric.

Tip: If a chart has too many lines to read clearly, click legend items to hide the less important ones and focus on what matters.

Chart Splitting

When a category has more than 4 metrics of the same unit, CHOps splits them into sub-charts of 4 metrics each. The charts are labeled with part numbers: "In-Memory Caches, Count (1/3)", "(2/3)", "(3/3)". The most active metrics, by total value across the query's lifetime, appear in the first sub-chart.

Reading Patterns

Memory Chart

A typical Memory, Bytes chart shows:

• memory_usage, the current memory the query has allocated. It goes up and down as the query allocates and releases memory.
• peak_memory_usage, the highest memory_usage so far. It only goes up.

Disk IO Chart

• OSReadBytes, bytes read from disk. A steady climb means the query scans data.
• OSWriteBytes, bytes written to disk. A spike usually means the query spills intermediate results to disk.
• DiskReadElapsedMicroseconds, time spent waiting for disk reads. High values mean IO is the bottleneck.

Cache Chart

• MarkCacheHits against MarkCacheMisses. The ratio tells you whether the mark cache is effective. High misses mean the cache is too small.
• PageCacheHits against PageCacheMisses. The same, for the uncompressed page cache.

How Units Work

CHOps detects the unit of each metric from its column name, and never mixes metrics with different units in the same chart.

So a category like "CPU & Time" may produce two charts: one for time metrics (μs) and one for count metrics (page faults, context switches). Both are labeled clearly.

Metric Categories

CHOps classifies the hundreds of possible metrics into categories. Only categories with non-zero data appear. Categories are ordered by how commonly they appear.

Common Categories (appear for most queries)

Write-Path Categories (appear for INSERT queries)

Infrastructure Categories (appear based on your setup)

Rare Categories

What Appears Per Query Type

Different query types activate different categories. Use this as a quick reference for what to look for.

Simple SELECT (for example, SELECT * FROM table WHERE id = 123)

SELECT with JOIN

Everything from Simple SELECT, plus:

SELECT with GROUP BY / ORDER BY

Everything from Simple SELECT, plus:

INSERT

Distributed SELECT (across shards)

Everything from Simple SELECT, plus:

Cloud Storage Query (S3 / Azure / GCS)

Everything from Simple SELECT, plus:

Replicated Table Write

Everything from INSERT, plus:

A simple SELECT 1 might show only Memory (bytes) and CPU (μs).

How Discovery Works

system.query_metric_log has over 700 columns, and the list changes between ClickHouse® versions. CHOps does not hard-code column names. Instead:

1. Fetch all rows for the selected query with SELECT * (typically 1 to 60 rows for a 1 to 60 second query).
2. Scan every row to find columns where any row has a non-zero value. This catches metrics that activate mid-query (for example, ExternalSortWritePart might be 0 for the first 5 seconds, then spike when the sort spills to disk).
3. Sort by activity. The most active metrics (highest total absolute value) come first.
4. Cap at 100 columns. If more than 100 are active, CHOps keeps the 100 most active.
5. Classify each column by category and unit.
6. Split categories with more than 4 metrics of the same unit into sub-charts.
7. Build charts directly from the fetched data. There is no second query.

This approach is version-agnostic. It works across all ClickHouse® versions, because SELECT * discovers the schema at query time.

Prerequisites

Enabling query_metric_log

On ClickHouse® 26.3 LTS (CHOps's minimum supported version), query_metric_log is on by default. If the table does not exist, or you want to adjust the collection interval, use the query_metric_log_interval session setting:

-- Check whether it is on (default 1000ms)
SELECT getSetting('query_metric_log_interval');
 
-- Set it for a specific user profile (in users.xml or users.d/)

<!-- /etc/clickhouse-server/users.d/query_metric_log.xml -->
<clickhouse>
  <profiles>
    <default>
      <!-- Collect interval in milliseconds (default 1000). Set to 0 to disable. -->
      <query_metric_log_interval>1000</query_metric_log_interval>
    </default>
  </profiles>
</clickhouse>

Lower values give more detail but add overhead. A value of 0 disables collection entirely.

Troubleshooting

Tips

Compare Two Queries

Analyze the slow query and note which categories have high values. Then analyze the fast query. The difference tells you exactly why one is slower.

For example, if the slow query shows high MarkCacheMisses and the fast query shows high MarkCacheHits, the slow query reads from disk while the fast one reads from cache. The fix is either to warm the cache or increase mark_cache_size.

Diagnose Memory Spikes

If a query is killed with "Memory limit exceeded":

1. Find the timestamp where peak_memory_usage jumps in the Memory, Bytes chart.
2. Look at what other metrics rise at the same moment in other charts.
3. If ArenaAllocBytes rises, the query builds a large aggregation or sort buffer.
4. If the External Operations category appears right after the spike, the query started to spill to disk as a fallback.

Check Cache Effectiveness

In the In-Memory Caches, Count charts, compare hits against misses:

Detect Disk Spilling

If the External Operations category appears, the query went above in-memory limits:

• ExternalSortWritePart and ExternalSortMerge mean ORDER BY spilled. Increase max_bytes_before_external_sort.
• ExternalAggregationWritePart and ExternalAggregationMerge mean GROUP BY spilled. Increase max_bytes_before_external_group_by.
• ExternalJoinWritePart and ExternalJoinMerge mean JOIN spilled. Consider a different JOIN algorithm or higher memory limits.

Spilling is not an error. It is a safety mechanism. But it is much slower than in-memory processing.

Understand Time Metrics

When you see both "CPU & Time, Time (μs)" and "CPU & Time, Count" charts:

• The Time chart shows RealTimeMicroseconds (wall clock), UserTimeMicroseconds (CPU in user mode), and SystemTimeMicroseconds (CPU in kernel mode).
• If RealTime is much higher than UserTime + SystemTime, the query waits (IO, locks, network).
• If UserTime dominates, the query is CPU-bound. Consider materialized views or projections to reduce computation.
• The Count chart shows SoftPageFaults, HardPageFaults, and ContextSwitches. High hard page faults mean the working set does not fit in RAM.