> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Page detailing allocation profiling in ClickHouse
# Allocation profiling
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+).
* Analyze memory fragmentation from SQL using [`system.jemalloc_sampled_allocations`](#analyzing-fragmentation-from-sql) (26.9+).
* Flush heap profiles to disk and analyze them with [`jeprof`](#analyzing-heap-profile-files-with-jeprof).
This guide is applicable for versions 25.9+.
For older versions, please check [allocation profiling for versions before 25.9](/concepts/features/performance/allocation-profiling-old).
Sampling allocations
To sample and profile allocations, start ClickHouse/Keeper with the `jemalloc_enable_global_profiler` config enabled:
```xml theme={null}
1
```
`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`
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:
```xml theme={null}
1
```
**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
First, run a query with the jemalloc profiler enabled and collect the samples into `system.trace_log`:
```sql theme={null}
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.
```
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`:
```sql theme={null}
SYSTEM FLUSH LOGS trace_log
```
Then query it to get cumulative memory usage over time:
```sql theme={null}
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:
```sql theme={null}
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:
```sql theme={null}
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
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.
```text theme={null}
http://localhost:8123/jemalloc
```
The server UI includes all tabs: Summary, Allocations, Arenas, Operations, Global Profiler, Query Profiler, Fragmentation, and Raw Output.
```text theme={null}
http://localhost:9182/jemalloc
```
The Keeper UI is available on the HTTP control port. This port is **disabled by default** and must be explicitly enabled by setting `keeper_server.http_control.port` in the Keeper configuration:
```xml theme={null}
9182
```
Once enabled, the UI provides the same visualizations as the server — Summary, Allocations, Arenas, Operations, Global Profiler, and Raw Output — except for the Query Profiler and Fragmentation tabs, which require SQL access to the system tables.
**Security**
The Keeper HTTP control port does not have application-level authentication. Unlike the ClickHouse Server jemalloc UI — where all data queries go through the SQL HTTP handler and require user/password credentials — the Keeper REST API endpoints are unauthenticated. This is consistent with other Keeper HTTP control endpoints (commands, storage, dashboard).
Restrict access to this port using network-level controls: bind Keeper to localhost, use firewall rules, or place it behind a reverse proxy with authentication. When no `listen_host` is configured, Keeper defaults to listening on localhost only.
Keeper also exposes REST API endpoints for programmatic access:
* `GET /jemalloc/stats` — raw `malloc_stats_print` output
* `GET /jemalloc/status` — profiling state as JSON (`prof_enabled`, `prof_active`, `thread_active_init`, `lg_sample`)
* `GET /jemalloc/profile?format={collapsed|raw}` — flushes a heap profile with server-side symbolization, returns collapsed stacks suitable for flame graph rendering (default) or the raw jemalloc dump
Fetching heap profiles from SQL
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:
```sql theme={null}
SELECT * FROM system.jemalloc_profile_text
```
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:
```sql theme={null}
SELECT * FROM system.jemalloc_profile_text
SETTINGS jemalloc_profile_text_output_format = 'raw'
```
To get symbolized output:
```sql theme={null}
SELECT * FROM system.jemalloc_profile_text
SETTINGS jemalloc_profile_text_output_format = 'symbolized'
```
Additional settings
* `jemalloc_profile_text_symbolize_with_inline` (Bool, default: `true`) — Whether to include inline frames when symbolizing. Disabling this speeds up symbolization significantly but loses precision as inlined function calls will not appear in the stacks. Only affects `symbolized` and `collapsed` formats.
* `jemalloc_profile_text_collapsed_use_count` (Bool, default: `false`) — When using the `collapsed` format, aggregate by allocation count instead of bytes.
Example: generating a flame graph from SQL
Since the default output format is `collapsed`, you can pipe the output directly to FlameGraph:
```sh theme={null}
clickhouse-client -q "SELECT * FROM system.jemalloc_profile_text" | flamegraph.pl --color=mem --title="Allocation Flame Graph" --width 2400 > result.svg
```
To generate a flame graph by allocation count instead of bytes:
```sh theme={null}
clickhouse-client -q "SELECT * FROM system.jemalloc_profile_text SETTINGS jemalloc_profile_text_collapsed_use_count = 1" | flamegraph.pl --color=mem --title="Allocation Count Flame Graph" --width 2400 > result.svg
```
Analyzing fragmentation from SQL
This section is applicable for versions 26.9+.
jemalloc serves small allocations from slabs: fixed-size blocks of memory split into regions of one size class. A slab is freed only when every region in it is free. If most allocations of a size class are freed quickly, but a few of them live for a long time, the slabs stay pinned with mostly free regions. The memory stays resident, but it is not allocated. This is what fragmentation means here.
Two system tables help find where it comes from:
* [`system.jemalloc_arena_bins`](/reference/system-tables/jemalloc_bins) shows how much memory each size class wastes in each arena. `waste` is the bytes held in slabs but not used by live allocations. `purpose` marks dedicated arenas (`mergetree`, `jit`, `cache`); they hold long-lived data by design, so filter them out with `purpose = ''` when looking for unexpected fragmentation. `system.jemalloc_bins` has the same data summed over arenas.
* `system.jemalloc_sampled_allocations` lists the sampled allocations that are currently alive, one row per sample: the backtrace (`trace`), how long it has been alive (`age_ns`), the size and size class, and the arena. Reading the table flushes a new heap profile.
Joining them on arena and size class shows which code allocated the old objects in the wasteful size classes.
Keep in mind when reading the results:
* Only threads with the profiler enabled produce samples (`jemalloc_enable_global_profiler` in the config or the `jemalloc_enable_profiler` setting).
* Each row stands for about `weight` real allocations, and small allocations are sampled rarely. Adjust `jemalloc_profiler_sampling_rate` if the size classes you care about have no samples.
* Sampled allocations are not stored in slabs, so they do not add to `waste` themselves. They only tell you what code allocates in that size class.
* Every live sample costs two extra pages (128 KiB on builds with 64 KiB pages) until the allocation is freed. Disabling the profiler stops new samples but does not free the existing ones.
The following query lists the backtraces of allocations older than a minute in the size classes that waste more than 1 MiB:
```sql theme={null}
WITH 60e9 AS min_age_ns
SELECT
b.arena,
b.size AS reg_size,
b.waste AS class_waste,
s.est_old_objects,
s.stack
FROM system.jemalloc_arena_bins AS b
INNER JOIN
(
SELECT
arena,
size_class,
sum(weight) AS est_old_objects,
arrayStringConcat(arrayMap(a -> demangle(addressToSymbol(a)), arrayReverse(trace)), ';') AS stack
FROM system.jemalloc_sampled_allocations
WHERE age_ns > min_age_ns
GROUP BY arena, size_class, trace
) AS s ON s.size_class = b.index AND s.arena = b.arena
WHERE b.large = 0 AND b.purpose = '' AND b.waste > 1048576
ORDER BY b.waste DESC, s.est_old_objects DESC
SETTINGS allow_introspection_functions=1
```
Example: generating a fragmentation flame graph
The same query can split the waste of each size class between its backtraces, in proportion to their number of old allocations, and print the result in the collapsed format for `flamegraph.pl`. Size classes with waste but without old enough samples go under an `[unattributed]` frame, so the total of the flame graph is the total waste. Read `system.jemalloc_sampled_allocations` once per query: every read flushes a new profile, so two subqueries would see different data.
```sql theme={null}
WITH 60e9 AS min_age_ns
SELECT format('{} {}',
if(s.est_old_objects > 0, s.stack, format('[unattributed];arena_{}_class_{}', toString(b.arena), toString(b.size))),
toString(toUInt64(if(s.est_old_objects > 0, b.waste * s.est_old_objects / sum(s.est_old_objects) OVER (PARTITION BY b.arena, b.index), b.waste))))
FROM system.jemalloc_arena_bins AS b
LEFT JOIN
(
SELECT
arena,
size_class,
sum(weight) AS est_old_objects,
arrayStringConcat(arrayMap(a -> demangle(addressToSymbol(a)), arrayReverse(trace)), ';') AS stack
FROM system.jemalloc_sampled_allocations
WHERE age_ns > min_age_ns
GROUP BY arena, size_class, trace
) AS s ON s.size_class = b.index AND s.arena = b.arena
WHERE b.large = 0 AND b.purpose = '' AND b.waste > 0
SETTINGS allow_introspection_functions=1
```
```sh theme={null}
clickhouse-client -q "..." | flamegraph.pl --color=mem --title="Fragmentation Flame Graph" --width 2400 > result.svg
```
Flushing heap profiles to disk
If you need to save heap profiles as files for offline analysis with `jeprof`, you can flush them to disk.
By default, the heap profile file will be generated in `/tmp/jemalloc_clickhouse._pid_._seqnum_.heap` where `_pid_` is the PID of ClickHouse and `_seqnum_` is the global sequence number for the current heap profile.
For Keeper, the default file is `/tmp/jemalloc_keeper._pid_._seqnum_.heap`, and follows the same rules.
To flush the current profile:
```sql theme={null}
SYSTEM JEMALLOC FLUSH PROFILE
```
It will return the location of the flushed profile.
```sh theme={null}
echo jmfp | nc localhost 9181
```
A different location can be defined by appending the `MALLOC_CONF` environment variable with the `prof_prefix` option.
For example, if you want to generate profiles in the `/data` folder where the filename prefix will be `my_current_profile`, you can run ClickHouse/Keeper with the following environment variable:
```sh theme={null}
MALLOC_CONF=prof_prefix:/data/my_current_profile
```
The generated file will be appended to the prefix PID and sequence number.
Analyzing heap profile files with `jeprof`
After flushing heap profiles to disk, they can be analyzed using `jemalloc`'s tool called [jeprof](https://github.com/jemalloc/jemalloc/blob/dev/bin/jeprof.in). It can be installed in multiple ways:
* Using the system's package manager
* Cloning the [jemalloc repo](https://github.com/jemalloc/jemalloc) and running `autogen.sh` from the root folder. This will provide you with the `jeprof` script inside the `bin` folder
There are many different output formats available. Run `jeprof --help` for the full list of options.
Symbolized heap profiles
Starting from version 26.1+, ClickHouse automatically generates symbolized heap profiles when you flush using `SYSTEM JEMALLOC FLUSH PROFILE`.
The symbolized profile (with `.symbolized` extension) contains embedded function symbols and can be analyzed by `jeprof` without requiring the ClickHouse binary.
For example, when you run:
```sql theme={null}
SYSTEM JEMALLOC FLUSH PROFILE
```
ClickHouse will return the path to the symbolized profile (e.g., `/tmp/jemalloc_clickhouse.12345.0.heap.symbolized`).
You can then analyze it directly with `jeprof`:
```sh theme={null}
jeprof /tmp/jemalloc_clickhouse.12345.0.heap.symbolized --output_format [ > output_file]
```
**No binary required**: When using symbolized profiles (`.symbolized` files), you don't need to provide the ClickHouse binary path to `jeprof`. This makes it much easier to analyze profiles on different machines or after the binary has been updated.
If you have an older non-symbolized heap profile and still have access to the ClickHouse binary, you can use the traditional approach:
```sh theme={null}
jeprof path/to/clickhouse path/to/heap/profile --output_format [ > output_file]
```
For non-symbolized profiles, `jeprof` uses `addr2line` to generate stacktraces which can be really slow.
If that's the case, it is recommended to install an [alternative implementation](https://github.com/gimli-rs/addr2line) of the tool.
```bash theme={null}
git clone https://github.com/gimli-rs/addr2line.git --depth=1 --branch=0.23.0
cd addr2line
cargo build --features bin --release
cp ./target/release/addr2line path/to/current/addr2line
```
Alternatively, `llvm-addr2line` works equally well (But note, that `llvm-objdump` is not compatible with `jeprof`)
And later use it like this `jeprof --tools addr2line:/usr/bin/llvm-addr2line,nm:/usr/bin/llvm-nm,objdump:/usr/bin/objdump,c++filt:/usr/bin/llvm-cxxfilt`
When comparing two profiles, you can use the `--base` argument:
```sh theme={null}
jeprof --base /path/to/first.heap.symbolized /path/to/second.heap.symbolized --output_format [ > output_file]
```
Examples
Using symbolized profiles (recommended):
* Generate a text file with each procedure written per line:
```sh theme={null}
jeprof /tmp/jemalloc_clickhouse.12345.0.heap.symbolized --text > result.txt
```
* Generate a PDF file with a call-graph:
```sh theme={null}
jeprof /tmp/jemalloc_clickhouse.12345.0.heap.symbolized --pdf > result.pdf
```
Using non-symbolized profiles (requires binary):
* Generate a text file with each procedure written per line:
```sh theme={null}
jeprof /path/to/clickhouse /tmp/jemalloc_clickhouse.12345.0.heap --text > result.txt
```
* Generate a PDF file with a call-graph:
```sh theme={null}
jeprof /path/to/clickhouse /tmp/jemalloc_clickhouse.12345.0.heap --pdf > result.pdf
```
Generating a flame graph
`jeprof` allows you to generate collapsed stacks for building flame graphs.
You need to use the `--collapsed` argument:
```sh theme={null}
jeprof /tmp/jemalloc_clickhouse.12345.0.heap.symbolized --collapsed > result.collapsed
```
Or with a non-symbolized profile:
```sh theme={null}
jeprof /path/to/clickhouse /tmp/jemalloc_clickhouse.12345.0.heap --collapsed > result.collapsed
```
After that, you can use many different tools to visualize collapsed stacks.
The most popular is [FlameGraph](https://github.com/brendangregg/FlameGraph) which contains a script called `flamegraph.pl`:
```sh theme={null}
cat result.collapsed | /path/to/FlameGraph/flamegraph.pl --color=mem --title="Allocation Flame Graph" --width 2400 > result.svg
```
Another interesting tool is [speedscope](https://www.speedscope.app/) that allows you to analyze collected stacks in a more interactive way.
Additional options for the profiler
`jemalloc` has many different options available, which are related to the profiler. They can be controlled by modifying the `MALLOC_CONF` environment variable.
For example, the interval between allocation samples can be controlled with `lg_prof_sample`.
If you want to dump the heap profile every N bytes you can enable it using `lg_prof_interval`.
It is recommended to check `jemalloc`s [reference page](https://jemalloc.net/jemalloc.3.html) for a complete list of options.
Other resources
ClickHouse/Keeper expose `jemalloc` related metrics in many different ways.
**Warning**
It's important to be aware that none of these metrics are synchronized with each other and values may drift.
System table `asynchronous_metrics`
```sql theme={null}
SELECT *
FROM system.asynchronous_metrics
WHERE metric LIKE '%jemalloc%'
FORMAT Vertical
```
[Reference](/reference/system-tables/asynchronous_metrics)
System table `jemalloc_bins`
Contains information about memory allocations done via the jemalloc allocator in different size classes (bins) aggregated from all arenas.
[Reference](/reference/system-tables/jemalloc_bins)
System table `jemalloc_stats` (26.2+)
Returns the full output of `malloc_stats_print()` as a single string. Equivalent to the `SYSTEM JEMALLOC STATS` command.
```sql theme={null}
SELECT * FROM system.jemalloc_stats
```
Prometheus
All `jemalloc` related metrics from `asynchronous_metrics` are also exposed using the Prometheus endpoint in both ClickHouse and Keeper.
[Reference](/reference/settings/server-settings/settings/other#prometheus)
`jmst` 4LW command in Keeper
Keeper supports the `jmst` 4LW command which returns [basic allocator statistics](https://github.com/jemalloc/jemalloc/wiki/Use-Case%3A-Basic-Allocator-Statistics):
```sh theme={null}
echo jmst | nc localhost 9181
```