Allocation profiling

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/allocation-profiling.md)

ClickHouse uses [jemalloc](https://github.com/jemalloc/jemalloc) as its global allocator. Jemalloc comes with tools for allocation sampling and profiling.

ClickHouse and Keeper allow you to control sampling using configs, query settings, `SYSTEM` commands and four letter word (4LW) commands in Keeper. There are several ways to inspect the results:

- Collect samples into `system.trace_log` under the `JemallocSample` type for per-query analysis.
- View live memory statistics and fetch heap profiles through the built-in [jemalloc web UI](#jemalloc-web-ui) (26.2+).
- Query the current heap profile directly from SQL using [`system.jemalloc_profile_text`](#fetching-heap-profiles-from-sql) (26.2+).
- Flush heap profiles to disk and analyze them with [`jeprof`](#analyzing-heap-profile-files-with-jeprof).

Note

This guide is applicable for versions 25.9+.
For older versions, please check [allocation profiling for versions before 25.9](/docs/operations/allocation-profiling-old).

## Sampling allocations[​](#sampling-allocations "Direct link to Sampling allocations")

To sample and profile allocations, start ClickHouse/Keeper with the `jemalloc_enable_global_profiler` config enabled:

```
<clickhouse>
    <jemalloc_enable_global_profiler>1</jemalloc_enable_global_profiler>
</clickhouse>
```

`jemalloc` will sample allocations and store the information internally.

You can also enable sampling per query using the `jemalloc_enable_profiler` setting.

Warning

Because ClickHouse is an allocation-heavy application, jemalloc sampling may incur performance overhead.

## Storing jemalloc samples in `system.trace_log`[​](#storing-jemalloc-samples-in-system-trace-log "Direct link to storing-jemalloc-samples-in-system-trace-log")

You can store jemalloc samples in `system.trace_log` under the `JemallocSample` type.
To enable it globally, use the `jemalloc_collect_global_profile_samples_in_trace_log` config:

```
<clickhouse>
    <jemalloc_collect_global_profile_samples_in_trace_log>1</jemalloc_collect_global_profile_samples_in_trace_log>
</clickhouse>
```

Warning

Because ClickHouse is an allocation-heavy application, collecting all samples in system.trace\_log may incur high load.

You can also enable it per query using the `jemalloc_collect_profile_samples_in_trace_log` setting.

### Example: analyzing memory usage of a query[​](#example-analyzing-memory-usage-trace-log "Direct link to Example: analyzing memory usage of a query")

First, run a query with the jemalloc profiler enabled and collect the samples into `system.trace_log`:

```
SELECT *
FROM numbers(1000000)
ORDER BY number DESC
SETTINGS max_bytes_ratio_before_external_sort = 0
FORMAT `Null`
SETTINGS jemalloc_enable_profiler = 1, jemalloc_collect_profile_samples_in_trace_log = 1

Query id: 8678d8fe-62c5-48b8-b0cd-26851c62dd75

Ok.

0 rows in set. Elapsed: 0.009 sec. Processed 1.00 million rows, 8.00 MB (108.58 million rows/s., 868.61 MB/s.)
Peak memory usage: 12.65 MiB.
```

Note

If ClickHouse was started with `jemalloc_enable_global_profiler`, you don't have to enable `jemalloc_enable_profiler`.
Same is true for `jemalloc_collect_global_profile_samples_in_trace_log` and `jemalloc_collect_profile_samples_in_trace_log`.

Flush the `system.trace_log`:

```
SYSTEM FLUSH LOGS trace_log
```

Then query it to get cumulative memory usage over time:

```
WITH per_bucket AS
(
    SELECT
        event_time_microseconds AS bucket_time,
        sum(size) AS bucket_sum
    FROM system.trace_log
    WHERE trace_type = 'JemallocSample'
      AND query_id = '8678d8fe-62c5-48b8-b0cd-26851c62dd75'
    GROUP BY bucket_time
)
SELECT
    bucket_time,
    sum(bucket_sum) OVER (
        ORDER BY bucket_time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_size,
    formatReadableSize(cumulative_size) AS cumulative_size_readable
FROM per_bucket
ORDER BY bucket_time
```

Find the time where memory usage was the highest:

```
SELECT
    argMax(bucket_time, cumulative_size),
    max(cumulative_size)
FROM
(
    WITH per_bucket AS
    (
        SELECT
            event_time_microseconds AS bucket_time,
            sum(size) AS bucket_sum
        FROM system.trace_log
        WHERE trace_type = 'JemallocSample'
          AND query_id = '8678d8fe-62c5-48b8-b0cd-26851c62dd75'
        GROUP BY bucket_time
    )
    SELECT
        bucket_time,
        sum(bucket_sum) OVER (
            ORDER BY bucket_time ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_size,
        formatReadableSize(cumulative_size) AS cumulative_size_readable
    FROM per_bucket
    ORDER BY bucket_time
)
```

Using that result, see which allocation stacks were most active at the peak:

```
SELECT
    concat(
        '\n',
        arrayStringConcat(
            arrayMap(
                (x, y) -> concat(x, ': ', y),
                arrayMap(x -> addressToLine(x), allocation_trace),
                arrayMap(x -> demangle(addressToSymbol(x)), allocation_trace)
            ),
            '\n'
        )
    ) AS symbolized_trace,
    sum(s) AS per_trace_sum
FROM
(
    SELECT
        ptr,
        sum(size) AS s,
        argMax(trace, event_time_microseconds) AS allocation_trace
    FROM system.trace_log
    WHERE trace_type = 'JemallocSample'
      AND query_id = '8678d8fe-62c5-48b8-b0cd-26851c62dd75'
      AND event_time_microseconds <= '2025-09-04 11:56:21.737139'
    GROUP BY ptr
    HAVING s > 0
)
GROUP BY ALL
ORDER BY per_trace_sum ASC
```

## Jemalloc web UI[​](#jemalloc-web-ui "Direct link to Jemalloc web UI")

Note

This section is applicable for versions 26.2+.

ClickHouse provides a built-in web UI for viewing jemalloc memory statistics at the `/jemalloc` HTTP endpoint.
It displays live memory metrics with charts, including allocated, active, resident, and mapped memory, as well as per-arena and per-bin statistics.
You can also fetch global and per-query heap profiles directly from the UI.

To access it, open in your browser:

```
http://localhost:8123/jemalloc
```

## Fetching heap profiles from SQL[​](#fetching-heap-profiles-from-sql "Direct link to Fetching heap profiles from SQL")

Note

This section is applicable for versions 26.2+.

The `system.jemalloc_profile_text` system table lets you fetch and view the current jemalloc heap profile directly from SQL, without needing external tools or flushing to disk first.

The table has a single column:

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Column Type Description|  |  |  | | --- | --- | --- | | `line` String Line from the symbolized jemalloc heap profile. | | | | | |

You can query the table directly — there is no need to flush a heap profile beforehand:

```
SELECT * FROM system.jemalloc_profile_text
```

### Output format[​](#output-format "Direct link to Output format")

The output format is controlled by the `jemalloc_profile_text_output_format` setting, which supports three values:

- `raw` — raw heap profile as produced by jemalloc.
- `symbolized` — jeprof-compatible format with embedded function symbols. Since symbols are already embedded, `jeprof` can analyze the output without requiring the ClickHouse binary.
- `collapsed` (default) — FlameGraph-compatible collapsed stacks, one stack per line with the byte count.

For example, to get the raw profile:

```
SELECT * FROM system.jemalloc_profile_text
SETTINGS jemalloc_profile_text_output_format = 'raw'
```

To get symbolized output:

```
SELECT * FROM system.jemalloc_profile_text
SETTINGS jemalloc_profile_text_output_format = 'symbolized'
```

### Additional settings[​](#fetching-heap-profiles-settings "Direct link to Additional settings")