Analytics PipelineClickHouse & Materialized Views
Guide Monitoring, Observability & Operations

Tracking Memory Pressure with system.metrics

ClickHouse OOM kills are almost never a surprise in hindsight — system.metrics and system.asynchronous_metrics expose the exact climb toward the ceiling, if you are sampling the right gauges. This how-to reads MemoryTracking against max_server_memory_usage, correlates it with concurrent Query count and background pool depth, and turns those into alert thresholds that fire before the kernel does.

The core distinction to internalize: system.metrics holds instantaneous gauges the server updates continuously (memory in use right now, queries running right now), while system.asynchronous_metrics holds values sampled on a background interval (OS memory available, jemalloc arena sizes). Memory pressure is a story told by both — server-tracked allocation from the first, and the OS-level reality and allocator overhead from the second.

Prerequisites

How the memory budget is layered

ClickHouse allocates within a server-wide ceiling, max_server_memory_usage, which is itself a fraction of physical RAM (default ratio 0.9). The live total is MemoryTracking in system.metrics. Each query draws against a per-query ceiling, max_memory_usage, inside that server budget. The space between MemoryTracking and the server ceiling is the headroom new queries and merges can consume; the space above the ceiling is what the OS keeps for the page cache and everything non-ClickHouse.

Server memory budget: MemoryTracking in use, query headroom, and OS reserve above the max_server_memory_usage cap A memory bar over 128 GiB of RAM. Teal to 78 GiB is in use per MemoryTracking; muted from 78 to 115 GiB is query headroom drawn by per-query max_memory_usage; pink from 115 to 128 GiB is OS reserve above the max_server_memory_usage cap. Markers show current MemoryTracking at 78 GiB and the cap at 115 GiB. Server memory budget · OS RAM = 128 GiB in use query headroom MemoryTracking = 78 GiB max_server_memory_usage = 115 GiB in use — system.metrics MemoryTracking headroom — per-query max_memory_usage draws here OS reserve — kept above the server cap for page cache and the kernel

Step 1 — Read the live memory gauge

MemoryTracking is the server’s own accounting of every allocation it controls. It is the single most important pressure signal.

sql
SELECT
    metric,
    formatReadableSize(value) AS readable,
    value
FROM system.metrics
WHERE metric IN (
    'MemoryTracking',                              -- total server-tracked bytes
    'Query',                                        -- queries running right now
    'BackgroundMergesAndMutationsPoolTask',         -- active merges + mutations
    'BackgroundFetchesPoolTask'                     -- active replica fetches
);

Expected output on a busy but healthy node:

text
┌─metric────────────────────────────────┬─readable──┬───────value─┐
│ MemoryTracking                        │ 78.00 GiB │ 83751862272 │
│ Query                                 │ 42.00 B   │          42 │
│ BackgroundMergesAndMutationsPoolTask  │ 11.00 B   │          11 │
│ BackgroundFetchesPoolTask             │ 3.00 B    │           3 │
└───────────────────────────────────────┴───────────┴─────────────┘

Query and the two background counters are the why behind MemoryTracking: a memory climb driven by a spike in Query is user load, while a climb with flat Query but rising BackgroundMergesAndMutationsPoolTask is merge pressure — often a symptom of the part-count problems tracked in part & merge health monitoring.

Step 2 — Read the ceiling and compute utilization

max_server_memory_usage is the ceiling MemoryTracking is racing toward. Read it live and compute the ratio in one query.

sql
SELECT
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking') AS tracked,
    (SELECT toUInt64(value) FROM system.asynchronous_metrics
       WHERE metric = 'CGroupMemoryTotal') AS cgroup_total,
    (SELECT toUInt64(value) FROM system.settings WHERE name = 'max_server_memory_usage') AS server_cap,
    round(100 * tracked / nullIf(server_cap, 0), 1) AS pct_of_cap;

When max_server_memory_usage is 0 it is derived from the ratio setting max_server_memory_usage_to_ram_ratio (default 0.9) against physical or cgroup RAM, so fall back to 0.9 * cgroup_total for the effective ceiling. pct_of_cap is the number your alerts key on: sustained above ~85% means new large queries will start failing with MEMORY_LIMIT_EXCEEDED.

Step 3 — Cross-check against OS and allocator reality

The server’s own accounting can diverge from what the OS sees because of allocator overhead. system.asynchronous_metrics exposes both the OS view and jemalloc internals.

sql
SELECT metric, formatReadableSize(value) AS readable
FROM system.asynchronous_metrics
WHERE metric IN (
    'OSMemoryAvailable',            -- RAM the OS reports as free for allocation
    'MemoryResident',               -- RSS of the clickhouse process
    'jemalloc.resident',            -- pages jemalloc holds resident
    'jemalloc.allocated',           -- bytes actually allocated by jemalloc
    'jemalloc.retained'             -- unmapped-but-reserved; harmless overhead
)
ORDER BY metric;

A healthy node shows MemoryResident modestly above MemoryTracking (allocator overhead), jemalloc.retained accounting for most of the gap, and OSMemoryAvailable comfortably positive. The danger sign is OSMemoryAvailable collapsing toward zero while MemoryTracking looks fine — that means untracked memory (dictionaries, the page cache under pressure, or a memory leak) is consuming RAM the server is not counting, and the kernel OOM killer, not ClickHouse’s soft limit, will act first.

Step 4 — Bound individual queries

Server-wide limits protect the node; per-query limits protect the node from one query. Set max_memory_usage so a single runaway aggregation cannot claim the whole budget.

sql
-- Cap one query at 20 GiB; it fails with MEMORY_LIMIT_EXCEEDED rather than starving peers.
SELECT event_type, uniqExact(user_id) AS uniques
FROM analytics.events_raw
GROUP BY event_type
SETTINGS max_memory_usage = 21474836480;   -- 20 GiB

Set this as a profile default for interactive roles rather than per statement. A sane arrangement on a 115 GiB server cap is max_memory_usage around 15–25% of the cap, leaving room for concurrency. Queries that legitimately need more should spill to disk with max_bytes_before_external_group_by and max_bytes_before_external_sort rather than raising their memory ceiling.

Step 5 — Alert on sustained pressure

Turn the utilization ratio into a trend alert so a brief spike from one heavy query does not page anyone.

sql
-- Fire when the node sits above 85% of its effective ceiling for a sustained window.
SELECT host,
       max(mem_pct) AS worst_pct,
       avg(mem_pct) AS avg_pct
FROM (
    SELECT host, sampled_at,
           100 * memory_tracking / effective_cap AS mem_pct
    FROM monitoring.memory_pressure_history
    WHERE sampled_at > now() - INTERVAL 3 MINUTE
)
GROUP BY host
HAVING avg_pct > 85
ORDER BY worst_pct DESC;

Practical thresholds: warn at 80% of the effective ceiling sustained for two minutes, page at 90%, and page immediately if OSMemoryAvailable drops below roughly 5% of total RAM regardless of MemoryTracking — the OS-level signal catches the untracked-memory case the server gauge misses.

Pair the utilization alert with a concurrency signal so the on-call knows the shape of the incident before opening a laptop. A pressure alert that arrives with Query at 3× its baseline is a load spike — shed traffic or scale out. The same alert with flat Query but elevated BackgroundMergesAndMutationsPoolTask is merge pressure — reduce insert-side part creation or throttle merges. Attaching that second dimension to the page turns a generic “memory high” into an actionable “memory high because of X,” which is the difference between a two-minute and a two-hour mitigation.

Verification

Confirm your understanding of the node’s live pressure with a single consolidated read, and check system.events for allocation failures that already happened:

sql
SELECT
    (SELECT formatReadableSize(value) FROM system.metrics WHERE metric = 'MemoryTracking') AS tracked,
    (SELECT formatReadableSize(value) FROM system.asynchronous_metrics WHERE metric = 'OSMemoryAvailable') AS os_free,
    (SELECT value FROM system.events WHERE event = 'QueryMemoryLimitExceeded') AS query_ooms;
-- query_ooms climbing between reads means queries are already hitting max_memory_usage.

A query_ooms counter that increments between samples confirms queries are being rejected at their per-query ceiling — the soft limit is doing its job, but it is time to either raise headroom or investigate the offending query shapes in the query log.

Gotchas and edge cases

  • MemoryTracking undercounts. It tracks what the server allocates through its own arenas, not the OS page cache, mmap’d dictionaries, or external libraries. Always pair it with OSMemoryAvailable — a node can OOM with MemoryTracking well under the cap.
  • max_server_memory_usage = 0 does not mean unlimited. Zero means “derive from the ratio,” so the effective ceiling is max_server_memory_usage_to_ram_ratio times RAM. Reading the setting alone will mislead you; compute the effective value.
  • cgroup limits override physical RAM in containers. In Kubernetes the ratio applies to the cgroup limit, not host RAM. Sample CGroupMemoryTotal from system.asynchronous_metrics, not /proc/meminfo, or your utilization math will be wrong.
  • jemalloc.retained is not a leak. Retained memory is unmapped address space jemalloc keeps for reuse; it inflates virtual size but not real pressure. Do not alert on it — alert on jemalloc.allocated and MemoryResident.
  • Merges and fetches during replica catch-up spike memory. A recovering replica running many concurrent fetches and merges can breach the ceiling even with no user queries; watch this alongside the replication runbook in monitoring replication lag with system.replicas.

Up: Replication & Availability Monitoring