Query Metrics

Query Metrics shows a second-by-second timeline of how a query consumed resources during its execution: memory, CPU, disk IO, cache hits and misses, network, and hundreds of other counters. Think of it as a heart rate monitor for a single query.

Navigate to Tools > Query Metrics to get started.

What Are Query Metrics?

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

These snapshots are stored in the system.query_metric_log table. Query Metrics turns this raw data into visual timelines, grouped by category and unit, so you can see exactly how resources were consumed 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 if it's spending 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/miss ratio for a specific workload

Getting Started

1. Set the From and To datetime fields (default: 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 will show many more groups.

Understanding the Charts

Layout

Charts are displayed two per row in a grid layout. Each chart shows one category of metrics at a specific 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 is meaningful. You'll see labels like "Memory, Bytes" and "Memory, Count" side by side.

Reading the Charts

Each colored line represents 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 multiple 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, current memory allocated by the query. Goes up and down as the query allocates and releases memory.
• peak_memory_usage, the highest memory_usage seen so far. Only goes up.

Disk IO Chart

• OSReadBytes, bytes read from disk. A steadily climbing line means the query is scanning data.
• OSWriteBytes, bytes written to disk. A spike usually means the query is spilling intermediate results to disk.
• DiskReadElapsedMicroseconds, time spent waiting for disk reads. High values mean IO is the bottleneck.

Cache Chart

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

How Units Work

CHOps automatically detects the unit of each metric from its column name and ensures metrics with different units are never mixed in the same chart.

This means 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 to know what to look for.

Simple SELECT (e.g., 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 hardcode column names. Instead:

1. Fetch all rows for the selected query with SELECT * (typically 1-60 rows for a 1-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, the 100 most active are kept
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 (no second query needed)

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 enabled by default. If the table doesn't exist or you want to adjust the collection interval, use the query_metric_log_interval session setting:

-- Check if it's enabled (default 1000ms)
SELECT getSetting('query_metric_log_interval');
 
-- Set 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 granularity but increase overhead. Setting to 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 is reading 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 are rising at the same moment in other charts
3. If ArenaAllocBytes is rising, the query is building a large aggregation or sort buffer
4. If the External Operations category appears right after the spike, the query started spilling to disk as a fallback

Check Cache Effectiveness

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

Detect Disk Spilling

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

• ExternalSortWritePart / ExternalSortMerge means ORDER BY spilled. Increase max_bytes_before_external_sort.
• ExternalAggregationWritePart / ExternalAggregationMerge means GROUP BY spilled. Increase max_bytes_before_external_group_by.
• ExternalJoinWritePart / ExternalJoinMerge means JOIN spilled. Consider a different JOIN algorithm or increase memory limits.

Spilling is not an error, it's a safety mechanism. But it is significantly 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), SystemTimeMicroseconds (CPU in kernel mode)
• If RealTime >> UserTime + SystemTime, the query is waiting (IO, locks, network)
• If UserTime is dominant, the query is CPU-bound (consider materialized views or projections to reduce computation)
• The Count chart shows SoftPageFaults, HardPageFaults, ContextSwitches, high hard page faults mean the working set doesn't fit in RAM