How to Fix 'max memory exceeded' in ClickHouse

How do I fix the "max memory exceeded" error in ClickHouse?

The error appears when a query needs more RAM than the limits defined by ClickHouse settings such as max_memory_usage.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What triggers the "max memory exceeded" error?

ClickHouse aborts a query when its RAM demand surpasses the limit set by max_memory_usage (session) or max_memory_usage_for_user (user). The default may be lower than your dataset or JOIN/ORDER BY operation requires.

How do I raise the memory limit for one query?

Add a SETTINGS clause or run a SET statement. This scopes the change to the current session or single statement, keeping cluster-wide defaults safe.

Example

SELECT o.id, c.name, SUM(oi.quantity) AS items FROM Orders o INNER JOIN OrderItems oi ON oi.order_id = o.id INNER JOIN Customers c ON c.id = o.customer_id GROUP BY o.id, c.name SETTINGS max_memory_usage = 8589934592;

How do I change the limit permanently?

Edit config.xml (server-wide) or users.xml (per-user) and restart ClickHouse. Increase <max_memory_usage> or <max_memory_usage_for_user> to a realistic value based on your hardware.

Which auxiliary settings help large JOINs and ORDER BYs?

Enable spill-to-disk before the hard limit is hit. Tune max_bytes_before_external_sort and max_bytes_before_external_group_by so ClickHouse offloads intermediate data to disk instead of failing.

What query optimizations reduce memory?

Project only needed columns, filter early, use JOIN … ANY when possible, and pre-aggregate with materialized views. Smaller datasets mean smaller working sets.

Best practices recap

Start by optimizing SQL. Then bump max_memory_usage for heavy analytics, keep spill settings below the hard ceiling, and monitor system.query_log to catch memory-hungry statements early.

Why How to Fix 'max memory exceeded' in ClickHouse is important

How to Fix 'max memory exceeded' in ClickHouse Example Usage


--Raise memory only for this analytical join
SELECT c.id, c.name, SUM(oi.quantity) AS total_items, SUM(oi.quantity * p.price) AS revenue
FROM Orders o
INNER JOIN OrderItems oi ON oi.order_id = o.id
INNER JOIN Products p ON p.id = oi.product_id
INNER JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.id, c.name
ORDER BY revenue DESC
SETTINGS max_memory_usage = 12e9,            --12 GB
         max_bytes_before_external_sort = 2e9; --spill after 2 GB

How to Fix 'max memory exceeded' in ClickHouse Syntax


--Session-level adjustment
SET max_memory_usage = <bytes>;

--Per-query adjustment in SQL
SELECT column_list
FROM table
SETTINGS max_memory_usage = <bytes>,
         max_bytes_before_external_sort = <bytes>,
         max_bytes_before_external_group_by = <bytes>;

--Server-wide (config.xml)
<max_memory_usage>17179869184</max_memory_usage>
<max_memory_usage_for_user>17179869184</max_memory_usage_for_user>

Common Mistakes

Blindly increasing max_memory_usage without query optimization. This masks inefficient SQL and can crash the server when multiple sessions run concurrently. First filter data and limit selected columns, then increase memory only if needed.
Setting max_bytes_before_external_sort higher than max_memory_usage. The server will still abort because the spill threshold is unreachable. Always keep spill limits lower than the hard memory ceiling.

Frequently Asked Questions (FAQs)

Can I set max_memory_usage in the JDBC URL?

Yes. Append ?max_memory_usage=8589934592 (8 GB) to the connection string to apply the limit to every query executed via that session.

How do I find queries that hit the limit?

Query system.query_log where exception_code = 241. The ProfileEvents column shows peak memory usage so you can prioritize tuning.

Does increasing max_memory_usage slow down ClickHouse?

Not directly. Higher limits let queries run instead of failing, but if physical RAM is exhausted the OS may start swapping, which degrades performance. Monitor system memory.