Impala Memory Limits

Core concept — SET MEM_LIMIT and failure modes

An Impala query has a memory limit, specified either per-query with SET MEM_LIMIT=8G or inherited from the impalad daemon's cluster-wide default. When the query's operators — hash joins, aggregations, sorts — accumulate state (building hash tables, buffering rows), they reserve memory from a per-query pool. If an operator hits its reservation limit before the operation completes, one of two things happens: either the operator spills a partition to disk, freeing memory to continue, or it fails with an OOM error, killing the entire query.

The difference between these outcomes is not random. Spilling is graceful degradation: the query runs slower (disk I/O added) but finishes. OOM is catastrophic: the query crashes, all work is lost, and the user either reruns with a smaller dataset or begs for more memory. The limit exists to protect the cluster — a single query should not hog all RAM and starve other queries — but when it is set too low, protective becomes punitive, and queries that could complete at their natural memory consumption instead fail for no reason.

How memory estimation works — EXPLAIN gives a guess

Before a query runs, Impala's planner runs through each operator and tries to estimate how much memory it will need. A hash join estimates the size of the build side based on EXPLAIN row counts; an aggregation estimates the number of unique groups; a sort estimates the volume of rows to buffer. These estimates are almost always wrong — they rely on table statistics (which may be stale), rely on join selectivity guesses, and do not account for skew — but they give a starting point. The planner sums these per-operator estimates and reports a total memory requirement in EXPLAIN output under memory=.

Run EXPLAIN SELECT ... and the last line of the plan shows the estimated memory requirement. This is what the admission control system uses to decide whether the query can start — if the cluster's memory is too tight, the query waits in a queue. But the estimate is a floor, not a ceiling. If the actual working set is larger than the estimate (skewed data, stale statistics, or an overlooked operator), the query will hit its limit and either spill or crash. The gap between estimate and reality is where most memory surprises live.

Per-query MEM_LIMIT — the knob you control

The SET MEM_LIMIT command is how you override the cluster default for a single query. It accepts human-readable strings like 8G, 1024M, or 500GB and applies to that query and its successors in the same session. A session inherits the daemon's default if no SET is issued; a new session gets a fresh default.

The practical pattern is to size MEM_LIMIT to your data and cluster: if your cluster has 128GB of RAM per node and you want to run 4 concurrent large queries, each query gets a ceiling of 20G (accounting for daemon overhead and other background tasks) so none starves the others. For an interactive BI query against a moderate dataset, 4G often suffices; for a nightly batch job that processes a huge fact table, 50G may be necessary. The trap is setting it too low and wondering why an hour-long query suddenly fails when it crosses some invisible boundary — usually the point where a hash join's build side spill victim is re-read and repartitioned recursively.

Per-node daemon limits — the cluster ceiling

The impalad daemon itself enforces a cluster-wide default via the --mem_limit flag at startup. This is usually set to 80% of the node's physical RAM — for a 256GB node, that is --mem_limit=204G — to reserve space for the OS and other processes. All queries on that node share this pool; if too many queries run concurrently, they contend for the total, and memory pressure builds quickly.

Individual SET MEM_LIMIT commands cannot exceed the daemon's limit (a lower bound will be imposed silently). The daemon limit is the ultimate ceiling; queries cannot spend more than that no matter what the user requests. If you set the daemon limit too high, a single rogue query can starve the node and crash other jobs. Too low, and queries that could complete are rejected at admission time. The right value depends on your workload: a data warehouse with many concurrent mid-size queries benefits from a lower daemon limit (forcing admission control to serialize them), while a dedicated reporting cluster for large batch jobs can run hotter.

Memory reservation — the buffer pool guarantee

Memory limits work because of buffer pool reservations: each operator that might spill (hash joins, aggregations, sorts) reserves a minimum amount of memory guaranteed not to be reclaimed under pressure — usually the size of a single spill buffer, enough to make forward progress. The reservation is a contract: the operator promises never to request more than its limit, and the buffer pool promises to always grant at least the reservation.

Without reservations, spilling operators could deadlock: imagine a hash join that spills down to zero memory, then tries to read a spilled partition back to rebuild its hash table — but there is no memory left to grant, so it stalls forever. Reservations prevent this by guaranteeing that even under maximum pressure, an operator retains the buffers it needs to process one more partition. This is the reason Impala can safely let queries spill: the reservation system ensures that spilling terminates in completion, not starvation.

Spilling to disk — graceful degradation when memory is tight

When an operator (usually a hash join or aggregation) exhausts its reservation mid-build, it chooses a victim partition — typically the largest — and writes its buffers to a scratch directory on local disk. This frees memory, and the operator continues building the remaining partitions in memory, spilling another victim each time pressure returns. The build phase completes with some partitions resident in memory, others parked on disk.

During the probe phase (for joins) or merge phase (for sorts), the operator processes spilled partitions one at a time by reading them back from disk, rebuilding or merging them in memory, and then rereading their probe-side partners. The result: slower execution, but completion. If a spilled partition is itself too large to fit in memory (due to skew), the operator recursively repartitions it with a different hash and processes the smaller sub-partitions. Recursion adds disk I/O passes but guarantees that even wildly skewed data finishes, not crashes.

OOM failure — when memory pressure wins

Spilling is not a free lunch. Scratch directories must have enough disk space and I/O bandwidth to absorb the spilled volume; if scratch fills, the spill itself fails and the query crashes with an OOM error. The scratch path defaults to /tmp or a configured scratch directory and must be monitored for capacity.

More commonly, OOM happens when an operator's estimate was so wrong that it runs out of memory before it can even start spilling, or when an operator that cannot spill (like a late-stage sort after all data is already in memory) runs out of buffers. Estimates that assume uniform data but hit a join key that is 1000x more frequent are a classic culprit. The query crashes, the user sees a cryptic error, and the node's log fills with memory-pressure warnings. The remedy is to lower MEM_LIMIT so admission control rejects the query instead of accepting it to fail mid-run.

Admission control — gating concurrency by memory

Admission control is the cluster-wide brake on memory pressure. When a query arrives, its EXPLAIN memory estimate is compared against the current memory used by already-running queries. If accepting the new query would exceed a configured threshold (often 90% of the daemon limit), the query is enqueued instead of admitted. It sits in a queue, waiting for other queries to finish and free memory, then eventually starts.

Without admission control, a dozen large queries can start simultaneously, each underestimating its memory by 50%, and suddenly the node is swamped with memory pressure. All queries spill at once, scratch I/O becomes the bottleneck, and throughput collapses as each query fights for disk access. Admission control prevents this by serializing large queries: only one or two run at a time, each gets its memory budget, and smaller queries fill the gaps. The tradeoff is latency: a query waits longer to start, but when it does, it has a good chance of finishing instead of spilling or crashing.

Monitoring memory usage — where to look

Impala exposes memory metrics at both the query and daemon level. Per-query, the Impala web UI at http://localhost:25000 shows each running query's current memory consumption, peak, and limit. Query details view breaks down memory by operator: you can see which hash joins or aggregations are eating the most. Queries that spill show spill metrics: partition count spilled, recursion depth, and how many extra disk passes were needed.

At the daemon level, impalad --help | grep mem lists the key flags. --mem_limit_process_percentage (default 95%) sets the max fraction of system RAM that all queries can use. --buffer_pool_limit (tied to --mem_limit) caps the buffer pool size. Look for repeated OOM errors, queries stuck in the admission queue, or a pattern where queries spill only at certain times (indicating resource contention). High scratch I/O (check /proc/diskstats) hints that many queries are spilling.

Debugging under-estimation — why queries fail

When a query fails with OOM despite a reasonable MEM_LIMIT, the problem is usually under-estimation. The EXPLAIN guess was too low, so the query ran out of memory before spilling could save it. To debug:

1. Check EXPLAIN memory estimate. Run EXPLAIN SELECT ... and note the memory= field. If it says 500M but your dataset is huge, alarm bells should ring.

2. Look for stale table statistics. Run COMPUTE STATS table_name on tables involved in the query. Planner memory estimates are only as good as the numRows and totalSize statistics.

3. Trace the skew. If a join or aggregation has a highly frequent value (nulls, empty string, a default ID), the planner's uniform-distribution assumption breaks down. Filter or rewrite the query to avoid the skew.

4. Raise MEM_LIMIT as a temporary measure to confirm the diagnosis. If the query then completes, memory under-estimation was the culprit.

Tuning MEM_LIMIT for your workload

Setting MEM_LIMIT is part science, part empiricism. Start with a reasonable default: for a cluster with N nodes and M cores per node, and W concurrent workload slots (usually M / 2 to account for I/O waits), divide the daemon limit by W. A 256GB node with 32 cores might run 8 concurrent queries at 20G each, or 16 at 10G each.

Run your production workload (or a realistic sample) with this setting and monitor peak memory usage per query. If queries routinely spill, raise MEM_LIMIT. If you see repeated OOM crashes, lower it and increase the admission queue depth so more queries wait instead of failing. If queries never approach their limit, you are wasting allocation — lower it to admit more concurrent queries without over-committing.

For specific workloads (like one huge ETL job), set a session-level SET MEM_LIMIT just for that job. For interactive BI, a moderate cluster-wide default with admission control is often better than trying to hand-tune each query.

Cluster-wide resource planning

Memory limits do not exist in isolation; they are part of a cluster resource model. A 10-node cluster with 256GB per node and a 204GB daemon limit has about 2TB of total query memory across all nodes. If your concurrent workload can run 64 queries at 20G each, you have exactly 1.28TB allocated — tight, but tenable. If your admission control is tuned too generously and 100 queries start simultaneously, each thinking it has 20G but only 12G is actually available, you hit memory pressure cluster-wide.

Admission control works per-node, not cluster-wide in vanilla Impala (Cloudera's CDW adds a stateful coordinator), so over-admission is possible if nodes have different daemon limits or workload distributions are uneven. The remedy is conservative admission thresholds (use 70-80% instead of 90%) and regular monitoring of actual vs estimated memory. If spilling is endemic, admission control is too loose; if queries routinely wait in the queue, it is too tight.

Common pitfalls and fixes

Over-estimation by the planner: rarely, but occasionally, EXPLAIN memory overestimates reality (complex queries with many joins can pessimistically assume Cartesian products). If admission control rejects queries that would actually fit, collect a histogram of actual memory usage and compare to estimates, then consider tuning planner cost model if the disparity is systematic. This is a last resort; usually estimation is too low.

Scratch directory fills: if scratch fills under spill load, queries fail with cryptic I/O errors. Monitor scratch capacity and ensure it has 1.5-2x the peak working set size of your largest queries. Use a separate fast local SSD if available.

Admission queue backlog: if many queries pile up in the queue, consider a higher daemon limit (if nodes have spare RAM) or breaking up huge queries into smaller stages so they complete faster and free memory.

Production checklist

Before deploying a memory-sensitive Impala workload:

▪ Compute table statistics for all tables: COMPUTE STATS table updates row counts and size estimates so the planner's memory estimate is less of a guess.

▪ Set daemon limit conservatively: use 75-80% of physical RAM, leaving headroom for OS and unexpected spikes.

▪ Configure admission control: enable it, set the memory threshold to 80% of daemon limit, and queue length to 2-3x expected concurrent queries.

▪ Test with peak load: run your expected concurrent workload and watch for OOM errors, excessive spilling, and admission queue depth. Adjust MEM_LIMIT and admission thresholds based on observed behavior.

▪ Monitor scratch disk: track capacity and I/O utilization; if scratch becomes a bottleneck, it is a sign admission is too loose or estimates are too low.

▪ Set session defaults for known large queries: use SET MEM_LIMIT for ETL jobs and other known large operations rather than relying on admission control to guess.