How to Check Memory Usage in ClickHouse

How do I check and control memory usage in ClickHouse?

The memory usage command monitors RAM consumed by ClickHouse queries or the whole server, helping prevent OOM errors and optimize performance.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the fastest way to see current memory usage?

Query the system.metrics and system.asynchronous_metrics tables. They expose real-time counters for total and per-query memory.

SELECT metric, value FROM system.metrics WHERE metric ILIKE '%Memory%';

How do I check memory used by a running query?

The system.processes table holds one row per live query, including the memory_usage column.

SELECT query_id, user, formatReadableSize(memory_usage) AS mem FROM system.processes ORDER BY memory_usage DESC;

Can I audit historical peaks?

Enable the internal system.query_log. It records read_rows, read_bytes, and memory_usage after each query finishes.

SELECT query, formatReadableSize(memory_usage) AS mem FROM system.query_log WHERE event_time > now() - INTERVAL 1 HOUR ORDER BY memory_usage DESC;

How to limit memory per session or query?

Use the max_memory_usage setting when connecting or in each statement. ClickHouse aborts the query if the limit is exceeded.

SET max_memory_usage = 2e9; -- 2 GB SELECT ...;

Session example in the Galaxy SQL editor

In Galaxy, open a new tab, paste the SET statement, run it, then execute your Orders analysis. The editor shows a warning if the limit triggers.

Which system metrics matter most?

MemoryTracking (allocated), RealMemoryUsage (RSS), and UncompressedCacheBytes (cache) give a complete picture. Monitor them with Grafana or CLI scripts.

Best practices for low-memory environments

Use SAMPLE for large tables, avoid ORDER BY RAND(), prefer LIMIT early, and partition Orders data by order_date to prune scans.

Why How to Check Memory Usage in ClickHouse is important

How to Check Memory Usage in ClickHouse Example Usage


-- Find top 5 memory-hungry queries touching Orders in the last day
SELECT query,
       formatReadableSize(memory_usage) AS mem
FROM system.query_log
WHERE event_time >= yesterday()
  AND query ILIKE '%Orders%'
ORDER BY memory_usage DESC
LIMIT 5;

How to Check Memory Usage in ClickHouse Syntax


-- View server-wide memory counters
SELECT metric, value
FROM system.metrics
WHERE metric ILIKE '%Memory%';

-- Monitor memory of live ecommerce queries
SELECT query_id,
       user,
       formatReadableSize(memory_usage) AS mem,
       query
FROM system.processes;

-- Enforce a 1 GB cap before running a big Orders join
SET max_memory_usage = 1e9;  -- bytes
SELECT c.name, SUM(oi.quantity * p.price) AS lifetime_value
FROM Customers c
JOIN Orders o  ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
GROUP BY c.name;

Common Mistakes

Relying only on OS tools like top or htop. They miss per-query stats. Fix: query system.metrics and system.processes for ClickHouse-native data.
Setting max_memory_usage globally without testing. It can kill legitimate large reports. Fix: tune per-session or per-user, then raise slowly.