Why ClickHouse memory limit exceeded errors happen and who fixes them
A query or insert fails with a memory limit error on a production ClickHouse cluster, and the first question is which consumer hit the ceiling. The cause is usually one query, cache or background merge, and the fix depends on which one. Raising a limit without knowing the consumer often moves the failure to the next busy hour rather than removing it. Start by reading the error text and the memory metrics on the affected node, then check system.processes for queries running at the time, ordered by peak memory usage, as the memory debugging guide describes.
Memory pressure on ClickHouse usually comes from three places: user queries that build large hash tables or sort large result sets, caches such as the mark and uncompressed caches sized too generously for the host, and merges that combine large parts in the background. Each has a different remedy, such as per-query limits and spilling to disk, cache sizing, or schema and partition changes that produce smaller merges. Confirm current setting names and defaults against the version you run, because behavior changes across releases and your own pre-production tests are the reliable reference.
In-house teams can usually handle a single recurring query or a documented limit change, especially if they already run ClickHouse and have monitoring in place. Bring in outside help when memory errors spread across shards, appear after a version upgrade, involve replicated tables and merges, or require a schema redesign that must be tested against representative data before production. DeployOpen plans and implements this work inside your own infrastructure, including your cloud accounts, data centers or air-gapped networks, and documents the limits, changes and evidence so your team can operate the result.
How to find which queries and resources use memory
This sequence moves from the queries running right now to the longer-lived consumers of memory, so you can tell whether a memory limit error comes from a query, a cache or a background merge. The output is a short list of named consumers with their peak usage, which becomes the basis for choosing a fix.
Capture the exact error and its context
Record the full error text, the timestamp, the user, the query text and the host or replica that raised it. Your operations team usually pulls this from the server log and the client application. The step is done when you can match one failing query to a time window and a node, because every later query in this sequence is filtered by that window.
Rank running queries by peak memory
Query system.processes on the affected node and sort by peak memory usage, which the ClickHouse memory debugging guide recommends as the first check. Note the user, elapsed time, read rows and the query text for the top entries. If the failing query has already finished, this view will not show it, so move to the history step. The step is done when you have a ranked list of current heavy queries on that node.
Check server-wide memory metrics
Read the memory metrics in system.metrics and system.asynchronous_metrics, including the total tracked memory and the operating system memory figures, and compare them with the configured server memory limit. A total close to the limit at the time of the error points to many concurrent consumers, while a low total points to one query with a per-query limit. The step is done when you can state whether the server was near its overall limit.
Separate query memory from caches
Inspect cache sizes in system.asynchronous_metrics and the cache and dictionary views for your version, such as mark cache, uncompressed cache, query cache and loaded dictionaries. Caches are sized in configuration and do not disappear when queries end, so a large cache can leave less headroom for queries. The step is done when you know the share of memory held by caches and whether that share is stable or growing.
Check merges and mutations for memory use
Query system.merges to list active merges with their memory usage and part sizes, and check system.mutations for long-running or failing mutations. Background merges of large parts can use significant memory and do not appear in system.processes. The step is done when you can say whether merges or mutations were active at the time of the error and how large the parts involved were.
Find the failing query in query history
Search system.query_log for the failing query text or user within the time window from step one, filtering on the exception code and the memory usage column for your version. Check the retention and sampling settings first, because a short retention or a log_queries_probability below 1 can leave gaps in history. Confirm the exact column names in your server version before writing the query. The step is done when you have the failing query's peak memory and its exception, or a clear note that history does not cover it.
Record the consumers and rank the likely causes
Write a short table listing each consumer you found, its peak memory, its type (query, cache or merge) and the time it ran. Group repeated patterns, such as the same aggregation running on a schedule. The output is the evidence that decides the fix: a per-query limit, a spill setting, a schema or query change, or a cache resize. The step is done when every error window has at least one named consumer or a documented gap.
Choosing between limits, spilling and schema changes
Per-query limits suit a runaway query: one statement, run by one user or application, whose peak memory is far above what its peers use. Settings profiles let you cap memory per query and per user, so a single report cannot take the whole server down. The trade-off is that a limit is a blunt instrument. A legitimate month-end report may fail after a limit is tightened, and the error will look like a server problem to the people who depend on it.
- Cap per query, not only per server: Set a maximum memory usage for queries in a settings profile assigned to the users who run them, so interactive dashboards and batch jobs get different ceilings.
- Separate profiles by workload: Give reporting, ad hoc analysis and ingestion their own profiles and quotas so one group cannot consume the others' headroom.
- Watch for breakage after changes: Before lowering a limit, run the reports that depend on it in a pre-production environment with representative data and confirm which ones fail.
- Expect clear failures: A capped query fails with a memory limit error instead of degrading the node, which protects other users but means someone must own the fix.
- Document exceptions: Keep a short, named list of users or jobs with raised limits so the exception is visible during review.
Before changing any limit, confirm which user and query caused the error and whether the workload is expected to need that memory.
Spilling lets large GROUP BY and ORDER BY operations write intermediate state to disk instead of holding it all in RAM. It keeps the query alive when the data set is larger than memory allows, which is often the right answer for legitimate heavy reports. The cost is time: disk I/O is slower than memory, so the query takes longer, and the temporary files consume disk space on the same node that stores your data. Spilling is a relief valve for occasional large operations, not a reason to run every aggregation through disk.
- Know which operators spill: Large GROUP BY and ORDER BY are the usual candidates. Check the ClickHouse memory and external aggregation settings in the version you run, since names and defaults change between releases.
- Plan disk headroom: Temporary spill files need space on the node. Confirm free space and alerting on the data volume before enabling spilling for heavy queries.
- Accept slower queries: Expect higher latency for spilled queries. Set expectations with report owners rather than letting them discover it.
- Pair spilling with limits: Spilling without a ceiling can exhaust disk. Combine it with per-query limits so a query cannot spill without bound.
- Verify with the same query: Re-run the failing query with spilling enabled on a representative data set and record time and peak memory.
Check whether the query still fails under the same limit with spilling enabled, and whether disk usage on the node stays within your alert thresholds.
Some memory is held by caches rather than by queries. Mark caches, uncompressed caches and similar structures keep data in memory to speed up reads, and their size is set by configuration rather than by the workload running at the moment. A cache sized too generously for the host can leave too little memory for queries, even when no single query looks wrong. Reducing cache size trades read speed for headroom, so the decision depends on what the cache is actually buying you.
- Confirm the cache is the holder: Use memory metrics to see how much memory caches occupy compared with query memory before changing anything.
- Size against host memory: Set cache limits as a share of the memory left after the operating system, merges and expected query peaks are accounted for.
- Measure the speed loss: Compare query latency and cache hit rates before and after a reduction, since some dashboards depend on cached reads.
- Restart considerations: Check whether a configuration change to cache size requires a server restart in your version, and schedule it within a maintenance window.
- Review on hardware change: Revisit cache sizes whenever memory, data volume or concurrency changes.
Confirm the exact cache settings and their defaults in the documentation for your release before editing configuration.
A structural cause means every query reads or groups more data than the design should require. Typical signs are wide tables read for a few columns, ordering keys that do not match the common filters, and dashboards that recompute the same aggregation over raw events each time. Changing the schema or query shape reduces memory at the source and usually lasts longer than a limit. It also takes the most planning, since it can touch ingestion, existing data and application code.
- Match the ordering key to filters: Filters on columns outside the ordering key scan more data and build larger intermediate results.
- Read fewer columns: Select only the columns a report needs, and avoid SELECT * in scheduled jobs.
- Pre-aggregate repeated work: Materialized views or summary tables can replace repeated large GROUP BY operations over raw events.
- Plan migrations carefully: Changing an ordering key requires a new table and data copy, so estimate disk and time with your team before starting.
- Retest the business output: Compare results from old and new shapes on the same data to confirm the numbers still match.
Check the query log for the top memory consumers and confirm which of these changes would remove the cause, not only lower the peak.
Memory settings and when each fits
This table compares the main memory controls in a self-managed ClickHouse server: per-query limits, server-wide memory and cache sizing, spilling to disk, and per-user profiles and quotas. Read each row as a question to answer for your workload, then confirm the exact setting keys and defaults against the version you run. The main takeaway is that a single query limit rarely fixes a memory error on its own, so most fixes combine a query limit, spilling and a schema or query change.
| Control | What it governs | When it fits and the trade-off |
|---|---|---|
| Per-query memory limit (max_memory_usage) | Caps the memory one query may allocate before it fails with a memory limit error | Fits runaway ad hoc or dashboard queries. A low cap stops the query early but fails queries that legitimately need more, so pair it with spilling for heavy GROUP BY or ORDER BY work. Confirm the key name and scope on your version. |
| Total server memory limit (max_server_memory_usage and related ratios) | Caps memory across all queries, merges and background work on the node | Fits protecting the host from OOM kills when many queries run together. Set it below physical RAM with headroom for the OS and caches. Too low a value causes errors under normal concurrency, so confirm how it interacts with cache sizes on your build. |
| Mark, uncompressed and query caches | Memory held for index marks, uncompressed blocks and query result reuse | Fits read-heavy workloads that repeat the same data. Large caches reduce disk reads but compete with query memory, so shrink them first when the server is under pressure. Check the cache size keys in your configuration reference. |
| Merge and mutation memory | Memory used by background merges and mutations on large parts | Fits clusters where errors appear during merges rather than queries. Limiting concurrent merges or mutations lowers peak use but slows part consolidation. Check merge-related settings and system.merges before changing them. |
| External aggregation and sort spilling | Lets GROUP BY and ORDER BY write intermediate data to disk once they reach a memory threshold | Fits large aggregations and sorts that exceed RAM. Queries run slower because of disk I/O, and the temporary disk must have space and speed. Confirm the threshold keys and that the temporary data location is configured. |
| Settings profiles | Applies memory and other query settings to groups of users | Fits separating interactive dashboards from batch jobs, giving each a cap that suits its pattern. Profiles need maintenance as users and workloads change, and a user with a broader profile can still run heavy queries. Confirm inheritance rules in access control docs. |
| Quotas | Limits resource use such as query counts or execution time over an interval per user or role | Fits stopping one team from consuming shared capacity over time. Quotas do not cap memory per query, so use them with per-query limits. Confirm which quota fields your version supports. |
| Schema and query changes | Reduces the memory a query needs, for example by narrowing selected columns or aligning ordering keys | Fits repeated errors on the same query shape. It takes engineering time and testing, but it removes the cause instead of raising the ceiling. Validate results and timings in a pre-production environment with representative data. |
Setting limits safely and proving the fix
Change memory limits in a pre-production environment first, using a copy of the data and the exact queries that failed in production. Limits set in production without a rehearsal often move the failure to a different query, a different hour or a different node. Before changing anything, record the current values of settings such as max_memory_usage, max_bytes_before_external_group_by and max_bytes_before_external_sort, along with the query that produced the error and its peak memory from system.query_log. Those numbers become the baseline you compare against after the change. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, and it also asks you to read the changelog before upgrading, because minor backward-incompatible changes can occur. Confirm the behavior of each setting you plan to touch against the documentation for your exact version, not from memory of an older release.
Roll the change through replicas one node at a time, and keep the rest of the cluster serving traffic while you do it. Apply the setting to a single replica, watch the memory metrics and system.processes under normal load for a full business cycle, then extend it to the others. Settings applied per user or through a settings profile are easier to roll back than changes to server configuration files, so decide early which mechanism you will use and document the rollback command. Remember that a change that fixes one replica can still leave a distributed query failing on another shard, so check each node. Ask your team to confirm how their replicas are configured and whether any ingestion pipeline depends on the current limit.
Prove the fix by reproducing the failing query under the new settings, not by waiting for the next incident. Run the same query text against the same data volume in pre-production, capture its peak memory from system.query_log, and confirm it completes without MEMORY_LIMIT_EXCEEDED. Then check that it still produces the same result as before, since spilling to disk can change timing and resource use without changing output. Finally, keep the evidence: the query, the settings, the peak memory figures and the production monitoring you will watch for the next few weeks. A limit that passes one test run may still fail under peak concurrency, so plan a load test that matches your real concurrency before calling the incident closed.
Operational risks when tuning memory
Memory fixes change more than the failing query. Limits, spill settings, caches and access rules all affect other users, other replicas and the evidence you need for the next incident. Review each change against the risks below before it reaches production.
Limits that starve reporting
A per-query memory limit that stops one runaway query can also stop legitimate month-end reports that were already within budget. Before lowering limits, list the scheduled and ad hoc workloads that run on the same server, with their typical peak memory from system.query_log. Confirm with the report owners which jobs must finish, and move them to a settings profile with a separate limit rather than tightening the shared default.
Spill-to-disk filling local storage
Enabling external aggregation or sorting moves intermediate data to local disk, which can fill the volume that holds parts and merges. A query that spills heavily can then block inserts and merges on the same node. Check free space on the data and temporary paths, set a spill threshold you have tested, and alert on temporary data growth before you raise limits.
Cache changes affecting latency
Shrinking the mark, uncompressed or query caches frees memory but pushes more reads to disk, which raises query latency for every user of those tables. Enlarging them can starve query and merge memory on a busy node. Measure cache hit behavior before and after the change, and confirm the effect on dashboards with strict response targets.
Settings that differ across replicas
In a ReplicatedMergeTree setup, a memory setting changed on one replica but not the others makes the same query succeed on one node and fail on another. Distributed queries then show intermittent errors that look random. Apply profile and server configuration changes to every replica in the same window, and verify each node with a single test query.
Access changes that alter heavy queries
Restricting the default user, which starts with all permissions, or moving analysts into new roles changes who can run large queries and which profile applies to them. A role that loses its settings profile may run with unbounded memory, while a tightened profile may block a team mid-project. Review grants and profiles together, and use the SQL-driven access control workflow so changes are recorded.
Losing diagnostic history
Memory investigations depend on what happened in the past. If system.query_log has short retention or sampling is set too low, the peak memory of the failing query may be gone by the time someone looks. Before changing settings, record the current log retention and sampling, and confirm they will keep enough history to compare before and after.
Questions teams ask before fixing memory errors
Can our team fix ClickHouse memory limit errors without outside help?
Often yes, if you have someone comfortable with SQL, Linux and the ClickHouse server logs. The first step is identifying which queries, merges or caches consume memory, and ClickHouse exposes that through system.processes, memory metrics and the query log. The harder part is changing query patterns, schema and limits without breaking dashboards or ingestion. Teams without a dedicated ClickHouse owner usually find the diagnosis manageable and the schema and limit changes the riskier part, so they often bring in a partner for review and testing.
What drives the effort and cost of fixing memory errors?
Four factors matter most: how deep the diagnosis must go, how many clusters are involved, whether schema changes are needed, and how much testing a production change requires. A single cluster with one clear offending query is a small job. Several clusters with shared ingestion, materialized views and mixed workloads take longer because each fix can shift memory pressure elsewhere. Costs are one-time for diagnosis and remediation, and recurring only if you add ongoing monitoring or retainer support. Ask any quote to break these components out separately.
How long does diagnosis and remediation usually take?
Duration depends on scope, not on a fixed rule. A focused investigation of one cluster with an identified query is typically shorter than a review that covers several clusters, schema redesign and a staged rollout to production. Time also depends on how quickly you can reproduce the problem in a pre-production environment with representative data and queries, which the ClickHouse production guidance recommends. Ask for a timeline tied to the written scope and to the milestones where your team must approve changes.
What does the work need from our staff?
Plan for named people in four roles: a platform owner who approves limit and configuration changes, an engineer who can run and read queries against system tables, a data owner who can explain which dashboards and jobs matter, and an on-call contact for the maintenance window. You will also need access to a staging environment with data close enough to production to reproduce the errors. Without staging access, most fixes must be validated in production, which raises risk and usually lengthens the work.
Will upgrading ClickHouse fix memory limit errors?
Not reliably on its own. Newer releases can change memory behavior, but an upgrade does not remove an unbounded query, a heavy merge or an oversized cache. The ClickHouse security policy lists which versions receive security updates, and stable and LTS release notes describe changes, including minor backward-incompatible ones. Read the changelog for your target version, test in pre-production, and treat the upgrade as a separate change from memory tuning so you can tell which change had which effect.
What should a quote for this work include?
A quote should follow from a written scope and name each deliverable. Ask for the clusters and nodes covered, the diagnosis method and the system tables and metrics reviewed, the list of proposed limit, spill and schema changes, the test plan and staging requirements, the rollout and rollback steps, and the handover documentation. Also ask which items are one-time and which recurring, what your staff must provide, what is excluded, and how changes found out of scope are priced. DeployOpen agrees scope, deliverables and timelines in writing before work starts.
Is changing memory limits risky for production workloads?
It can be, because limits that are too low fail legitimate queries and limits that are too high let one query exhaust the server. Changes should be made in a pre-production environment first, with representative data, and rolled out with monitoring on the same metrics used for diagnosis. Make sure you can revert each setting quickly. Be careful with settings that trade memory for disk spilling, since they can slow queries and increase I/O, which can affect other users of the cluster.
How DeployOpen scopes a memory troubleshooting engagement
The sequence moves from symptoms and query evidence to a tested, written change set and a handover that your team can operate without us. Each step closes with a named output so you can check progress against the agreed scope.
Collect symptoms and query evidence
DeployOpen gathers the exact error text, the failing statements, the times they occur and the affected nodes, then pulls query history and memory metrics from your cluster. We ask for read-only access to system tables and your existing Prometheus or Grafana data where it exists. The step closes with a written symptom summary that lists the suspect queries, caches and merges, with the evidence behind each one.
Rank the likely memory consumers
Using the approach in ClickHouse's memory debugging guide, we query system.processes ordered by peak memory usage and compare those results with memory metrics captured during the incident window. We separate ad hoc analytical queries, background merges, mark and uncompressed caches, and inserts that buffer large blocks. The output is a ranked list that states, for each item, whether the evidence is conclusive or still needs confirmation.
Reproduce on a pre-production copy
The ClickHouse production FAQ recommends a pre-production environment with representative data and queries, so we reproduce the failure there before touching production. Your team provides or approves the data subset, schema and query set, and we confirm the copy's version matches production. The step closes when the suspect query fails or succeeds under the same memory conditions observed in production, or when the gap between the two is documented.
Agree the change set in writing
We propose the candidate fixes, which may include memory limits and settings profiles, spill-to-disk configuration, query rewrites, schema or ordering key changes, and cache sizing. Each item states its expected effect, its risk, the restart or downtime it needs and how it will be rolled back. Your owners approve the list in writing, and anything outside the signed scope goes through a separate quote.
Implement and verify each change
DeployOpen applies the approved changes in your environment, one at a time where possible, and reruns the reproduction workload after each. We check system.query_log and memory metrics to confirm the error no longer appears and that peak usage sits within the new limits. The step closes with a before and after comparison for every change, and a record of anything that did not behave as expected.
Confirm the fix under real load
Where the agreed scope includes it, we watch the cluster through a defined period of normal production traffic and review the same memory signals and error logs. This confirms that the fix holds across the workload mix, not only in the reproduction. Your team confirms the observation window and the success criteria in the written scope before this step starts.
Hand over runbooks and alert guidance
We deliver a runbook that explains how to find the top memory consumers, what each setting does, which values were chosen and why, and how to roll them back. We also recommend which memory metrics to alert on and thresholds to confirm against your own baseline. The step closes when your operators have walked through the runbook and the documented settings match what is running in production.
How DeployOpen works on ClickHouse memory issues
DeployOpen approaches ClickHouse memory limit errors by first measuring where memory goes, then changing limits, query patterns and schema only where the evidence points. The work runs inside your own infrastructure, with each change tested against representative queries before it reaches production.
How we scope
- We confirm which clusters, query users and error messages are in scope before any work starts.
- The written scope names the memory metrics, system tables and query logs we will review.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work begins.
How we build
- We trace the heaviest queries, merges and caches using system.processes and memory metrics.
- We propose memory limit, spilling and schema changes and test them on a pre-production copy.
- We confirm each change against representative data and queries before rollout to production.
How we hand over
- You receive documented limit settings, query guidance and the reasoning behind each change.
- Runbooks explain which memory metrics to watch and how to respond when limits are reached.
- Your team walks through the monitoring queries and tuning steps before the engagement closes.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- A retainer can cover re-checking memory behavior after version upgrades and workload growth.
- Ask us to confirm the support scope and response arrangements in your written quote.
