Sampling query profiler

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/optimizing-performance/sampling-query-profiler.md)

ClickHouse runs a sampling profiler that allows analyzing query execution.
Using the profiler, you can find the source code routines that are used the most frequently during query execution.
You can trace CPU time and wall-clock time spent including idle time.

The query profiler is automatically enabled in ClickHouse Cloud.
The following example query finds the most frequent stack traces for a profiled query, with resolved function names and source locations:

Tip

Replace the `query_id` value with the ID of the query you want to profile.

- ClickHouse Cloud- Self-managed

In ClickHouse Cloud, you can obtain the query ID by clicking **"..."** on the far right of the bar above the query result table (next to the table/chart toggle). This opens a context menu where you can click **"Copy query ID"**.

Use `clusterAllReplicas(default, system.trace_log)` to select from all nodes of the cluster:

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

```
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 trace_type = 'CPU' AND event_date = today()
GROUP BY trace
ORDER BY count() DESC
LIMIT 10
SETTINGS allow_introspection_functions = 1
```

## Using the query profiler in self-managed deployments[​](#self-managed-query-profiler "Direct link to Using the query profiler in self-managed deployments")

In self-managed deployments, to use the query profiler follow the steps below:

Install ClickHouse with debug info

### Install ClickHouse with debug info[​](#debug-info "Direct link to Install ClickHouse with debug info")

Install the `clickhouse-common-static-dbg` package:

1. Follow the instructions in step ["Set up the Debian repository"](/docs/install/debian_ubuntu#setup-the-debian-repository)
2. Run `sudo apt-get install clickhouse-server clickhouse-client clickhouse-common-static-dbg` to install ClickHouse compiled binary files with debug info
3. Run `sudo service clickhouse-server start` to start the server
4. Run `clickhouse-client`. The debug symbols from clickhouse-common-static-dbg will automatically be picked up by the server - you don't need to do anything special to enable them

Check server config

### Check server config[​](#server-config "Direct link to Check server config")

Ensure that the [`trace_log`](/docs/operations/server-configuration-parameters/settings#trace_log) section of your [server configuration file](/docs/operations/configuration-files) is set up. It is enabled by default:

```
<!-- Trace log. Stores stack traces collected by query profilers.
     See query_profiler_real_time_period_ns and query_profiler_cpu_time_period_ns settings. -->
<trace_log>
    <database>system</database>
    <table>trace_log</table>

    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <!-- Indication whether logs should be dumped to the disk in case of a crash -->
    <flush_on_crash>false</flush_on_crash>
    <symbolize>true</symbolize>
</trace_log>
```

This section configures the [trace\_log](/docs/operations/system-tables/trace_log) system table containing the results of the profiler functioning.
Remember that data in this table is valid only for a running server.
After the server restart, ClickHouse does not clean up the table and all the stored virtual memory address may become invalid.

Configure profile timers

### Configure profile timers[​](#configure-profile-timers "Direct link to Configure profile timers")

Set up the [`query_profiler_cpu_time_period_ns`](/docs/operations/settings/settings#query_profiler_cpu_time_period_ns) or [`query_profiler_real_time_period_ns`](/docs/operations/settings/settings#query_profiler_real_time_period_ns) settings.
Both settings can be used simultaneously.

These settings allow you to configure profiler timers.
As these are the session settings, you can get different sampling frequency for the whole server, individual users or user profiles, for your interactive session, and for each individual query.

The default sampling frequency is one sample per second, and both CPU and real timers are enabled.
This frequency allows you to collect sufficient information about your ClickHouse cluster whilst not affecting your server's performance.
If you need to profile each individual query, use a higher sampling frequency.

Analyze the system table

### Analyze the `trace_log` system table[​](#analyze-trace-log-system-table "Direct link to analyze-trace-log-system-table")

To analyze the `trace_log` system table allow introspection functions with the [`allow_introspection_functions`](/docs/operations/settings/settings#allow_introspection_functions) setting:

```
SET allow_introspection_functions=1
```

Note

For security reasons, introspection functions are disabled by default

Use the `addressToLine`, `addressToLineWithInlines`, `addressToSymbol` and `demangle` [introspection functions](/docs/sql-reference/functions/introspection) to get function names and their positions in ClickHouse code.
To get a profile for some query, you need to aggregate data from the `trace_log` table.
You can aggregate data by individual functions or by the whole stack traces.

Tip

If you need to visualize `trace_log` info, try [flamegraph](/docs/interfaces/third-party/gui#clickhouse-flamegraph) and [speedscope](https://www.speedscope.app).

## Building flame graphs with the `flameGraph` function[​](#flamegraph "Direct link to flamegraph")

ClickHouse provides the [`flameGraph`](/docs/sql-reference/aggregate-functions/reference/flame_graph) aggregate function which builds a flame graph directly from stack traces stored in `trace_log`.
The output is an array of strings in a format compatible with [flamegraph.pl](https://github.com/brendangregg/FlameGraph).

**Syntax:**

```
flameGraph(traces, [size = 1], [ptr = 0])
```

**Arguments:**

- `traces` — a stacktrace. [`Array(UInt64)`](/docs/sql-reference/data-types/array).
- `size` — an allocation size for memory profiling. [`Int64`](/docs/sql-reference/data-types/int-uint).
- `ptr` — an allocation address. [`UInt64`](/docs/sql-reference/data-types/int-uint).

When `ptr` is non-zero, `flameGraph` maps allocations (`size > 0`) and deallocations (`size < 0`) with the same size and pointer.
Only allocations that were not freed are shown.
Unmatched deallocations are ignored.

### CPU flame graph[​](#cpu-flame-graph "Direct link to CPU flame graph")

Note

The queries below require you to have [flamegraph.pl](https://github.com/brendangregg/FlameGraph) installed.

You can do so by running:

```
git clone https://github.com/brendangregg/FlameGraph
# Then use it as:
# ~/FlameGraph/flamegraph.pl
```

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: