Sampling query profiler

Replace `flamegraph.pl` in the following queries with the path where `flamegraph.pl` is located on your machine

```
SET query_profiler_cpu_time_period_ns = 10000000;
```

Run your query, then build the flame graph:

```
clickhouse client --allow_introspection_functions=1 \
    -q "SELECT arrayJoin(flameGraph(arrayReverse(trace)))
        FROM system.trace_log
        WHERE trace_type = 'CPU' AND query_id = '<query_id>'" \
    | flamegraph.pl > flame_cpu.svg
```

### Memory flame graph — all allocations[​](#memory-flame-graph-all "Direct link to Memory flame graph — all allocations")

```
SET memory_profiler_sample_probability = 1, max_untracked_memory = 1;
```

Run your query, then build the flame graph:

```
clickhouse client --allow_introspection_functions=1 \
    -q "SELECT arrayJoin(flameGraph(trace, size))
        FROM system.trace_log
        WHERE trace_type = 'MemorySample' AND query_id = '<query_id>'" \
    | flamegraph.pl --countname=bytes --color=mem > flame_mem.svg
```

### Memory flame graph — unfreed allocations[​](#memory-flame-graph-unfreed "Direct link to Memory flame graph — unfreed allocations")

This variant matches allocations against deallocations by pointer and shows only memory that was not freed during the query.

```
SET memory_profiler_sample_probability = 1, max_untracked_memory = 1,
    use_uncompressed_cache = 1,
    merge_tree_max_rows_to_use_cache = 100000000000,
    merge_tree_max_bytes_to_use_cache = 1000000000000;
```

Run the following query to build the flame graph:

```
clickhouse client --allow_introspection_functions=1 \
    -q "SELECT arrayJoin(flameGraph(trace, size, ptr))
        FROM system.trace_log
        WHERE trace_type = 'MemorySample' AND query_id = '<query_id>'" \
    | flamegraph.pl --countname=bytes --color=mem > flame_mem_unfreed.svg
```

### Memory flame graph — active allocations at a point in time[​](#memory-flame-graph-time-point "Direct link to Memory flame graph — active allocations at a point in time")

This approach lets you find peak memory usage and visualize what was allocated at that moment.

```
SET memory_profiler_sample_probability = 1, max_untracked_memory = 1;
```

#### Find memory usage over time[​](#find-memory-usage-over-time "Direct link to Find memory usage over time")

```
SELECT
    event_time,
    formatReadableSize(max(s)) AS m
FROM (
    SELECT
        event_time,
        sum(size) OVER (ORDER BY event_time) AS s
    FROM system.trace_log
    WHERE query_id = '<query_id>' AND trace_type = 'MemorySample'
)
GROUP BY event_time
ORDER BY event_time;
```

#### Find the time point with maximum memory usage[​](#find-time-point-maximum-memory-usage "Direct link to Find the time point with maximum memory usage")

```
SELECT
    argMax(event_time, s),
    max(s)
FROM (
    SELECT
        event_time,
        sum(size) OVER (ORDER BY event_time) AS s
    FROM system.trace_log
    WHERE query_id = '<query_id>' AND trace_type = 'MemorySample'
);
```

#### Build a flame graph of active allocations at that time point[​](#build-flame-graph "Direct link to Build a flame graph of active allocations at that time point")

```
clickhouse client --allow_introspection_functions=1 \
    -q "SELECT arrayJoin(flameGraph(trace, size, ptr))
        FROM (
            SELECT * FROM system.trace_log
            WHERE trace_type = 'MemorySample'
              AND query_id = '<query_id>'
              AND event_time <= '<time_point>'
            ORDER BY event_time
        )" \
    | flamegraph.pl --countname=bytes --color=mem > flame_mem_time_point_pos.svg
```

#### Build a flame graph of deallocations after that time point (to understand what was freed later)[​](#build-flame-graph-deallocations "Direct link to Build a flame graph of deallocations after that time point (to understand what was freed later)")

```
clickhouse client --allow_introspection_functions=1 \
    -q "SELECT arrayJoin(flameGraph(trace, -size, ptr))
        FROM (
            SELECT * FROM system.trace_log
            WHERE trace_type = 'MemorySample'
              AND query_id = '<query_id>'
              AND event_time > '<time_point>'
            ORDER BY event_time DESC
        )" \
    | flamegraph.pl --countname=bytes --color=mem > flame_mem_time_point_neg.svg
```

## Example[​](#example "Direct link to Example")

The code snippet below:

- Filters `trace_log` data by a query identifier and the current date.
- Aggregates by stack trace.
- Uses introspection functions to get a report of:
  - The names of symbols and corresponding source code functions.
  - The source code locations of these functions.

```
SELECT
    count(),
    arrayStringConcat(arrayMap(x -> concat(demangle(addressToSymbol(x)), '\n    ', addressToLine(x)), trace), '\n') AS sym
FROM system.trace_log
WHERE (query_id = '<query_id>') AND (event_date = today())
GROUP BY trace
ORDER BY count() DESC
LIMIT 10
```

- [Using the query profiler in self-managed deployments](#self-managed-query-profiler)
  - [Install ClickHouse with debug info](#debug-info)- [Check server config](#server-config)- [Configure profile timers](#configure-profile-timers)- [Analyze the `trace_log` system table](#analyze-trace-log-system-table)- [Building flame graphs with the `flameGraph` function](#flamegraph)
    - [CPU flame graph](#cpu-flame-graph)- [Memory flame graph — all allocations](#memory-flame-graph-all)- [Memory flame graph — unfreed allocations](#memory-flame-graph-unfreed)- [Memory flame graph — active allocations at a point in time](#memory-flame-graph-time-point)- [Example](#example)

Was this page helpful?