# Troubleshoot clusters Guides for diagnosing common cluster problems. If you're seeing unexpected cluster behavior, these guides can help you find the cause: - [Memory spikes](/clusters/troubleshoot-clusters/memory-spike/) - [CPU spikes](/clusters/troubleshoot-clusters/cpu-troubleshooting/) - [Hydration failures](/clusters/troubleshoot-clusters/hydration-failures/) --- ## Troubleshoot anomalies in CPU usage A cluster replica's CPU goes to the dataflows that maintain the cluster's indexes, materialized views, and sinks, and to the ad-hoc queries served from it. An anomaly in CPU usage means one of those started doing more work, or that the same work stopped being spread evenly across the replica's workers. Each section below covers one cause, with the queries that diagnose it, how to resolve it, and how to prevent it from recurring. > **Important:** The introspection relations and `EXPLAIN ANALYZE` statements in this guide > report on **the cluster and replica your session is connected to**. Run > `SET CLUSTER TO ` first, and re-run per replica on a cluster with > a replication factor above 1. ## Common causes | Cause | Description | | ----- | ----------- | | [Worker skew](#worker-skew) | One worker does far more work than its peers, so the replica saturates a core while the rest idle. | | [An expensive object](#an-expensive-object) | A single dataflow dominates the cluster's CPU. | | [Hydration](#hydration) | A replica restart, cluster resize, or DDL forced objects to rebuild from their inputs. | | [Ad-hoc query load](#ad-hoc-query-load) | `SELECT`s served by the cluster compete with its maintenance work. | | [Upstream data volume](#upstream-data-volume) | More data is arriving from sources, so there is more incremental work to do. | | [Memory pressure causing increased spill to disk](#memory-pressure-causing-increased-spill-to-disk) | A replica near its memory limit burns CPU on I/O rather than on your dataflows. | | [An undersized cluster](#an-undersized-cluster) | The cluster has no headroom left for the workload it carries. | ## First, confirm the spike window and scope Establish when the anomaly started and which replicas it affected, replacing `` with the name of your cluster: ```mzsql SELECT u.occurred_at, rh.replica_name, u.cpu_percent FROM mz_internal.mz_cluster_replica_utilization_history u JOIN mz_internal.mz_cluster_replica_history rh ON u.replica_id = rh.replica_id JOIN mz_catalog.mz_clusters c ON rh.cluster_id = c.id WHERE c.name = '' AND u.occurred_at > now() - INTERVAL '6 hours' ORDER BY u.occurred_at DESC; ``` ```nofmt occurred_at | replica_name | cpu_percent ----------------------------+--------------+-------------- 2026-09-15 16:26:44.899+00 | r1 | 39.634147625 2026-09-15 16:25:44.9+00 | r1 | 34.412956225 2026-09-15 16:24:44.899+00 | r1 | 34.81781385 2026-09-15 16:23:44.904+00 | r1 | 0 2026-09-15 16:22:46.4+00 | r1 | (5 rows) ``` Samples land about once a minute, and each one is a difference against the previous sample, so the oldest row of a replica's history has a `NULL` `cpu_percent`. Every replica of a cluster maintains the same objects, so: - If **all replicas** spike together, the workload itself changed. Continue with the checks below. - If **one replica** spikes, that replica is doing something its peers are not, most often rehydrating after a restart. See [Hydration](#hydration). - If the anomaly **repeats on a fixed cadence**, or CPU stays high while the cluster's inputs are idle, suspect a [temporal filter](/transform-data/optimization/#improve-performance-when-using-temporal-filters). A window whose boundary moves with `mz_now()` retracts rows as they age out, so it generates work with no upstream input at all. > **Important:** `cpu_percent` is a percentage of the replica's *total* allocation across all of > its cores, so it averages over workers. A replica whose work is concentrated on > one worker can peg a core while reporting a modest `cpu_percent`. > **Low CPU is therefore not a clean bill of health.** Degraded freshness with > unremarkable CPU is the signature of [worker skew](#worker-skew): the busy > worker is saturated, and averaging it against idle peers hides that. Which way the number points tells you which check to run first: - **CPU is high**: the cluster is doing more work than it can absorb. Start with [an expensive object](#an-expensive-object). - **CPU is low but freshness is degraded**: the work is not being spread across workers. Start with [worker skew](#worker-skew). ## Worker skew Materialize distributes work across a replica's workers by hashing keys, such as join keys and `GROUP BY` keys. If one key value accounts for a disproportionate share of rows, the worker responsible for that value does disproportionate work while its peers idle. A cross join is the extreme case. It has no join key to hash on, so every cross join running on a replica moves all of its data to a single worker, no matter how the input values are distributed. ### Diagnosing the issue **Step 1. Find the skewed object.** Connect to the cluster and run [`EXPLAIN ANALYZE CLUSTER CPU WITH SKEW`](/sql/explain-analyze/#explain-analyze--with-skew): ```mzsql SET CLUSTER TO ; EXPLAIN ANALYZE CLUSTER CPU WITH SKEW; ``` ```nofmt object | global_id | worker_id | max_operator_cpu_ratio | worker_elapsed | avg_elapsed | total_elapsed ------------------------------------------+-----------+-----------+------------------------+-----------------+-----------------+----------------- materialize.public.orders_enriched | t356 | 2 | 3.74 | 00:00:00.675664 | 00:00:00.207876 | 00:00:00.831505 materialize.public.orders_enriched | t356 | 1 | 1.85 | 00:00:00.033013 | 00:00:00.207876 | 00:00:00.831505 materialize.public.orders_by_customer | t365 | 1 | 1.57 | 00:00:00.188396 | 00:00:00.120242 | 00:00:00.48097 materialize.public.orders_by_customer | t365 | 2 | 1.17 | 00:00:00.140562 | 00:00:00.120242 | 00:00:00.48097 materialize.public.orders_by_customer | t365 | 3 | 0.81 | 00:00:00.097985 | 00:00:00.120242 | 00:00:00.48097 materialize.public.orders_enriched | t356 | 0 | 0.77 | 00:00:00.079949 | 00:00:00.207876 | 00:00:00.831505 materialize.public.orders_enriched | t356 | 3 | 0.73 | 00:00:00.042878 | 00:00:00.207876 | 00:00:00.831505 materialize.public.orders_by_customer | t365 | 0 | 0.45 | 00:00:00.054026 | 00:00:00.120242 | 00:00:00.48097 (8 rows) ``` There is one row per worker per dataflow, sorted by ratio, so rows for different objects interleave. A ratio near `1` means a worker is doing a roughly average share of the work. Here `orders_enriched` has a worker at `3.74` on a four-worker replica, close to the theoretical maximum of `4`: one worker is doing nearly all of that dataflow's work. > **Important:** `max_operator_cpu_ratio` is the maximum across all of a dataflow's operators, so > a high ratio can come from an operator that contributes almost nothing to the > dataflow's CPU time. Only act on a row whose `total_elapsed` is also > significant. Two further caveats: - The numbers accumulate from the moment each dataflow was created, so a short burst of skew in a long-running dataflow is diluted by its history. To measure a window, sample twice and compare. - On a cluster with many objects the output can run to thousands of rows. Use [`EXPLAIN ANALYZE ... AS SQL`](/sql/explain-analyze/#explain-analyze--as-sql) to get the underlying query, then filter and sort it yourself. **Step 2. Localize the skew to an operator.** Drill into the object you identified: ```mzsql EXPLAIN ANALYZE CPU WITH SKEW FOR MATERIALIZED VIEW orders_enriched; ``` ```nofmt operator | worker_id | cpu_ratio | worker_elapsed | avg_elapsed | total_elapsed -----------------------------+-----------+-----------+-----------------+-----------------+----------------- Differential Join %0 » %1 | 0 | 0.08 | 00:00:00.006928 | 00:00:00.087401 | 00:00:00.349606 Differential Join %0 » %1 | 1 | 0.11 | 00:00:00.009606 | 00:00:00.087401 | 00:00:00.349606 Differential Join %0 » %1 | 2 | 3.73 | 00:00:00.326225 | 00:00:00.087401 | 00:00:00.349606 Differential Join %0 » %1 | 3 | 0.08 | 00:00:00.006846 | 00:00:00.087401 | 00:00:00.349606 Arrange (#0{customer_id}) | 0 | 0.77 | 00:00:00.002539 | 00:00:00.00329 | 00:00:00.01316 Arrange (#0{customer_id}) | 1 | 1.84 | 00:00:00.006057 | 00:00:00.00329 | 00:00:00.01316 Arrange (#0{customer_id}) | 2 | 0.65 | 00:00:00.002136 | 00:00:00.00329 | 00:00:00.01316 Arrange (#0{customer_id}) | 3 | 0.74 | 00:00:00.002426 | 00:00:00.00329 | 00:00:00.01316 Read u11 | | | | | Arrange (#1{customer_id}) | 0 | 0.6 | 00:00:00.070684 | 00:00:00.117398 | 00:00:00.469594 Arrange (#1{customer_id}) | 1 | 0.15 | 00:00:00.017521 | 00:00:00.117398 | 00:00:00.469594 Arrange (#1{customer_id}) | 2 | 2.96 | 00:00:00.347571 | 00:00:00.117398 | 00:00:00.469594 Arrange (#1{customer_id}) | 3 | 0.29 | 00:00:00.033816 | 00:00:00.117398 | 00:00:00.469594 Read u10 | | | | | (14 rows) ``` Use `FOR INDEX ` for an index. The operator with the highest ratio, most often a `Join` or `Reduce`, is where the skew originates. Here it is the join, and the arrangement it reads from names the offending key: `customer_id`. The per-object form reports `cpu_ratio` rather than the `max_operator_cpu_ratio` of the cluster-wide form, since it is already per operator. Leaf `Read` rows have no per-worker records of their own and report empty metrics. **Step 3. Find the hot key.** A skewed `Join` or `Reduce` is almost always caused by a **hot key**: one value in the join or `GROUP BY` column(s) accounts for far more rows than the rest. Confirm it by counting rows per value: ```mzsql SELECT customer_id, count(*) AS num_rows FROM orders GROUP BY customer_id ORDER BY num_rows DESC LIMIT 20; ``` ```nofmt customer_id | num_rows -------------+---------- 1 | 390000 1024 | 1 1280 | 1 1536 | 1 1792 | 1 2048 | 1 ... (20 rows) ``` A single value with an outsized count confirms the hot key. A common trigger is an upstream change that collapses a once-diverse column to a single value or to `NULL`. See [Is work distributed equally across workers?](/transform-data/dataflow-troubleshooting/#is-work-distributed-equally-across-workers) for other causes of skew, such as cross joins and `ORDER BY`/`LIMIT`/`OFFSET` queries. > **Note:** Not every plan shape skews on a hot key. `count` and `sum` plan as an > accumulable reduce, whose cost barely depends on rows per key, and `min`/`max` > over a non-monotonic input plans as a bucketed hierarchical reduce, which > deliberately spreads one key across workers. Joins skew the hardest. ### Resolution Restructure the query so the hot key's rows aren't concentrated on a single worker, for example by pre-aggregating or filtering rows before the join, or by adding a column to the key so that values spread across more workers. If the skew can't be eliminated, size the cluster for the busiest worker's load rather than the average, since the other workers won't absorb it. ### Prevention - Key joins and aggregations on columns with high cardinality. A column that is mostly one value, or mostly `NULL`, concentrates on one worker by construction. - Watch for upstream schema changes that collapse a once-diverse column to a single value or to `NULL`. This is the most common way a healthy dataflow becomes skewed without any change on the Materialize side. - Test with data whose key distribution matches production. Skew is a property of the data, not of the query, so uniformly distributed test data will not reproduce it. - Monitor per-worker CPU rather than the replica average, since `cpu_percent` hides skew by construction. ## An expensive object One dataflow can dominate a cluster's CPU without any skew: the work is spread evenly across workers, there is just a lot of it. ### Diagnosing the issue ```mzsql SET CLUSTER TO ; EXPLAIN ANALYZE CLUSTER CPU; ``` ```nofmt object | global_id | total_elapsed ------------------------------------------+-----------+----------------- materialize.public.orders_enriched | t356 | 00:00:00.832362 materialize.public.orders_by_customer | t365 | 00:00:00.482028 (2 rows) ``` The output is sorted by `total_elapsed`, so the objects at the top are the cluster's most expensive dataflows. Drill into one with `EXPLAIN ANALYZE CPU FOR MATERIALIZED VIEW ` to see its operators. > **Note:** `total_elapsed` accumulates from the moment the dataflow was created, so a > long-lived object can outrank one that is expensive *right now*. To see which > operators are busy at this moment, use > [`mz_compute_operator_durations_histogram`](/transform-data/dataflow-troubleshooting/#debugging-expensive-dataflows-and-operators). ### Resolution [Optimize the expensive object](/transform-data/optimization/). Cross joins and joins without a suitable index are the usual culprits. Alternatively, move the object to its own cluster, or size the cluster up with [`ALTER CLUSTER ... SET (SIZE = '')`](/sql/alter-cluster/). ### Prevention - Review the plan of a new object with [`EXPLAIN PLAN`](/sql/explain-plan/) before it reaches a production cluster, and check that joins use an index on the join key. - Keep unrelated expensive objects on separate clusters, so that one object's cost cannot starve the others. See the [operational guidelines](/clusters/operational-guidelines/). ## Hydration Hydration is CPU-intensive: the replica reprocesses each object's inputs from scratch. Any event that drops a replica's in-memory state re-runs that work, and hydrating a new object on a busy cluster can saturate it outright. ### Diagnosing the issue Check what is still hydrating, including sources and sinks: ```mzsql SELECT o.name, h.replica_id FROM mz_internal.mz_hydration_statuses h JOIN mz_catalog.mz_objects o ON h.object_id = o.id JOIN mz_catalog.mz_clusters c ON o.cluster_id = c.id WHERE c.name = '' AND NOT h.hydrated; ``` ```nofmt name | replica_id --------+------------ big_mv | (1 row) ``` Zero rows is the healthy steady state: everything on the cluster is hydrated. A `NULL` `replica_id` means compute introspection has not reported on the object yet, which is normal for an object that has only just been created. ### Resolution Hydration resolves itself, so no action is needed unless it keeps recurring. If it does, find what is triggering it: - **Replica restarts**, including [OOM crash loops](/transform-data/freshness-troubleshooting/#check-for-oom-crash-loops), which rehydrate the whole cluster on every restart. - **DDL or deploy activity**. Creating, altering, or dropping an object hydrates it on whichever cluster it lives on. See [Check for DDL or deploy activity](/transform-data/freshness-troubleshooting/#check-for-ddl-or-deploy-activity). - **Cluster resizes**, which hydrate the new replicas before dropping the old ones. ### Prevention - Introduce new objects through a [blue/green deployment](/developer-tools/dbt/blue-green-deployments/) rather than on a live production cluster, so hydration happens on a cluster that is not serving traffic. - Size clusters so that they do not OOM, since an OOM crash loop rehydrates the whole cluster on every restart. - To shorten hydration without paying for a larger cluster continuously, use [autoscaling](/clusters/autoscaling/). ## Ad-hoc query load A cluster serves `SELECT`s from the same replicas that maintain its indexes and materialized views, so query traffic and maintenance work compete for the same CPU. A burst of queries, or a query that can't be answered from an index, shows up as a cluster-wide spike. Client-side restart loops are a frequent cause: each reconnect resubmits the same queries, and each one spins up a temporary dataflow. ### Diagnosing the issue ```mzsql SET CLUSTER TO ; SELECT object_id, type, count(*) AS active_peeks FROM mz_introspection.mz_active_peeks GROUP BY object_id, type ORDER BY active_peeks DESC; ``` ```nofmt object_id | type | active_peeks -----------+-------+-------------- u9 | index | 2 t331 | index | 1 (2 rows) ``` A `u` prefix identifies a user object, here an index answering two reads. A `t` prefix is a transient dataflow, built to answer a query that no existing arrangement could serve. A steady supply of `t` rows means queries are paying to build dataflows rather than reading from indexes. This is a point-in-time snapshot of in-flight reads, so sample it repeatedly during a spike. For the window after the fact, query [`mz_internal.mz_recent_activity_log`](/sql/system-catalog/mz_internal/#mz_recent_activity_log) instead. ### Resolution Serve queries from a cluster separate from the one maintaining the objects, as described in the [operational guidelines](/clusters/operational-guidelines/#three-tier-architecture). ### Prevention - Separate serving clusters from compute clusters from the start, so that a burst of query traffic cannot delay maintenance work. Use [role-based access control](/security/cloud/access-control/#role-based-access-control-rbac) to keep ad-hoc queries off production compute clusters. - Index the columns that ad-hoc queries filter on, so that a query is answered from an existing arrangement instead of building a temporary dataflow. A view that is queried repeatedly is cheaper as a materialized view than as an ad-hoc `SELECT` over an unmaterialized one. - Configure clients with backoff on reconnect, so that a client-side restart loop does not multiply into repeated query bursts. ## Upstream data volume A dataflow's steady-state CPU is proportional to the rate of change flowing through it, not to the size of its inputs. A cluster that was comfortable can saturate when an upstream system starts writing faster, with no change on the Materialize side. ### Diagnosing the issue If the cluster hosts sources, sample their counters twice a minute apart and compare: ```mzsql SELECT s.name, ss.messages_received, ss.updates_committed FROM mz_internal.mz_source_statistics ss JOIN mz_catalog.mz_sources s ON ss.id = s.id JOIN mz_catalog.mz_clusters c ON s.cluster_id = c.id WHERE c.name = ''; ``` ```nofmt name | messages_received | updates_committed ------+-------------------+------------------- lg | 171 | 170 (1 row) ``` These counters are best-effort and only meaningful as rates; see [counter metrics](/sql/system-catalog/mz_internal/#counter-metrics). Joining `mz_catalog.mz_sources` restricts the result to top-level sources. To include the tables created from a source, join `mz_catalog.mz_objects` instead. ### Resolution If the rate is far above what the workload was sized for, either size the cluster up or reduce the volume upstream. If the sources live on a different cluster from the objects that spiked, check that cluster too: downstream compute inherits its inputs' change rate. See [Check source ingestion](/transform-data/freshness-troubleshooting/#check-source-ingestion). ### Prevention - Size clusters against the peak change rate rather than the average, and revisit the sizing when upstream workloads change. - [Monitor freshness](/transform-data/monitor-freshness/) so that a rising change rate surfaces as lag before it surfaces as saturation. ## Memory pressure causing increased spill to disk A replica that is close to its memory limit spills data to disk, and the resulting paging registers as CPU time that isn't doing any of your work. ### Diagnosing the issue Check the other columns of [`mz_cluster_replica_utilization`](/sql/system-catalog/mz_internal/#mz_cluster_replica_utilization) for the same window: ```mzsql SELECT r.name AS replica_name, u.cpu_percent, u.memory_percent, u.disk_percent, u.swap_percent FROM mz_internal.mz_cluster_replica_utilization u JOIN mz_catalog.mz_cluster_replicas r ON u.replica_id = r.id JOIN mz_catalog.mz_clusters c ON r.cluster_id = c.id WHERE c.name = ''; ``` ```nofmt replica_name | cpu_percent | memory_percent | disk_percent | swap_percent --------------+-------------+--------------------+--------------+-------------- r1 | 0.863405875 | 0.8431712962962964 | | (1 row) ``` `disk_percent` and `swap_percent` are `NULL` when the replica reports no corresponding limit, as above. A replica under memory pressure reports rising values in both. ### Resolution High `memory_percent` alongside rising `disk_percent` or `swap_percent` means you are looking at a memory problem wearing a CPU costume. Size the cluster up, or reduce the memory footprint of its objects. ### Prevention - Alert on `memory_percent` as well as `cpu_percent`, so that memory pressure is caught before it presents as a CPU anomaly. - Reduce the memory footprint of expensive objects, for example by filtering earlier in the dataflow or by reducing the number of arrangements. See [optimization](/transform-data/optimization/). ## An undersized cluster If CPU is spread evenly across workers with no recent hydration, DDL, or upstream change, the cluster is undersized for its workload. ### Diagnosing the issue To measure how much headroom is left, subscribe to the time workers spend idle. `SUBSCRIBE` streams results, so wrap it in `COPY ... TO STDOUT` or a cursor; a bare `SUBSCRIBE` prints nothing in `psql`. ```mzsql SET CLUSTER TO ; BEGIN; DECLARE c CURSOR FOR SUBSCRIBE ( SELECT sum(slept_for_ns * count) FROM mz_introspection.mz_scheduling_parks_histogram ); FETCH 5 c WITH (timeout = '6s'); COMMIT; ``` ```nofmt mz_timestamp | mz_diff | sum ---------------+---------+--------------- 1789489597053 | 1 | 1232890339840 1789489598000 | -1 | 1232890339840 1789489598000 | 1 | 1239430677504 1789489599000 | 1 | 1244927487744 1789489599000 | -1 | 1239430677504 (5 rows) ``` Each tick emits a retraction (`mz_diff = -1`) of the previous value and an insertion (`mz_diff = 1`) of the new one. The `sum` is **cumulative** parked nanoseconds across all of the replica's workers, not a rate, so take the difference between two insertions to get the idle time for that interval. Over a window of `T` seconds, a fully idle cluster accrues `T × ` seconds of idle time. As a rule of thumb, a cluster with healthy headroom stays above 10% of that. [`mz_catalog.mz_cluster_replica_sizes`](/sql/system-catalog/mz_catalog/#mz_cluster_replica_sizes) gives the worker count for a size. Because this aggregates across workers, it will not reveal skew. Use it alongside `EXPLAIN ANALYZE CLUSTER CPU`, not instead of it. ### Resolution Size the cluster up with [`ALTER CLUSTER ... SET (SIZE = '')`](/sql/alter-cluster/) or move objects to another cluster. > **Important:** Sizing up does not always reduce CPU. A cluster runs each object's operators on > every worker, so the operator count grows with both the number of objects and > the number of workers, and coordination overhead grows with it. A cluster that > hosts many objects can therefore use *more* CPU after a resize. > If freshness degrades and CPU rises after sizing up, the cluster is too large > for its workload. Size back down, or split the objects across several smaller > clusters. ### Prevention - Track idle time continuously rather than sampling it during an incident, so that shrinking headroom is visible before the cluster saturates. - Size from the hydration peak. If CPU during hydration stayed well below the replica's capacity, the cluster has room to shrink. - Prefer several smaller clusters over one large one, particularly when hosting many objects. Cross-cluster joins and strict serializability still hold. - Follow the [operational guidelines](/clusters/operational-guidelines/) for how to lay out clusters. See [Check cluster health](/transform-data/freshness-troubleshooting/#check-cluster-health) for the corresponding freshness symptoms. --- ## Troubleshoot anomalies in memory usage A memory spike is a sudden increase in a cluster replica's memory usage. A spike that outgrows the replica's [heap limit](/observability/replica-resource-usage/) makes Materialize spill to disk, which slows the cluster down, or triggers an out-of-memory (OOM) kill and replica restart. This guide helps you find what caused a spike and how to prevent it recurring. For sustained CPU/memory pressure and OOM crash loops on an undersized cluster, see [Check cluster health](/transform-data/freshness-troubleshooting/#check-cluster-health). This guide focuses on **spikes**: sudden increases against an otherwise healthy baseline. ## Step 1: Find when the spike happened ### Check historical spike [`mz_internal.mz_cluster_replica_metrics_history`](/sql/system-catalog/mz_internal/#mz_cluster_replica_metrics_history) retains per-replica memory samples across restarts (at least 30 days by default), so you can find a past spike even if the replica has since recovered, restarted, or been replaced by a resize: ```mzsql SELECT c.name AS cluster_name, rh.replica_name, h.process_id, max(h.heap_bytes) AS peak_heap_bytes, max(h.heap_limit) AS heap_limit_bytes, min(h.occurred_at) FILTER (WHERE h.heap_bytes > 0.9 * h.heap_limit) AS first_above_90pct FROM mz_internal.mz_cluster_replica_metrics_history h JOIN mz_internal.mz_cluster_replica_history rh ON rh.replica_id = h.replica_id JOIN mz_catalog.mz_clusters c ON c.id = rh.cluster_id WHERE h.occurred_at > now() - INTERVAL '7 days' GROUP BY c.name, rh.replica_name, h.process_id HAVING max(h.heap_bytes) > 0.9 * max(h.heap_limit) ORDER BY peak_heap_bytes DESC; ``` ``` cluster_name | replica_name | process_id | peak_heap_bytes | heap_limit_bytes | first_above_90pct --------------+--------------+------------+-----------------+-------------------+------------------------ prod_compute | r1 | 0 | 15461882265 | 17179869184 | 2026-09-08 15:49:03+00 (1 row) ``` ### Check a live spike To check whether a replica is under memory pressure right now, query [`mz_internal.mz_cluster_replica_metrics`](/sql/system-catalog/mz_internal/#mz_cluster_replica_metrics): ```mzsql SELECT c.name AS cluster_name, r.name AS replica_name, m.process_id, m.heap_bytes, m.heap_limit, round(100 * m.heap_bytes::numeric / m.heap_limit, 1) AS heap_pct FROM mz_internal.mz_cluster_replica_metrics m JOIN mz_catalog.mz_cluster_replicas r ON r.id = m.replica_id JOIN mz_catalog.mz_clusters c ON c.id = r.cluster_id ORDER BY heap_pct DESC; ``` ``` cluster_name | replica_name | process_id | heap_bytes | heap_limit | heap_pct --------------+--------------+------------+-------------+-------------+---------- prod_compute | r1 | 0 | 15461882265 | 17179869184 | 90.0 prod_sources | r1 | 0 | 3221225472 | 17179869184 | 18.8 (2 rows) ``` For sub-minute detail and high-water marks that survive a spike shorter than the sampling interval, see [Replica resource usage](/observability/replica-resource-usage/). ## Step 2: Match the spike to a cause Cross-reference the spike window against these common causes. ### A new object hydrated on the cluster Creating an index, materialized view, or source loads its full result set into memory: [hydration](/fundamentals/concepts/hydration/). If the new object landed on a cluster that already runs production workloads, its hydration competes with that cluster's steady-state memory. Check [`mz_catalog.mz_audit_events`](/sql/system-catalog/mz_catalog/#mz_audit_events) for `create` events on the cluster around the spike window: ```mzsql SELECT occurred_at, event_type, object_type, details FROM mz_catalog.mz_audit_events WHERE occurred_at BETWEEN '' AND '' AND object_type IN ('index', 'materialized-view', 'source') ORDER BY occurred_at; ``` See also [Check for DDL or deploy activity](/transform-data/freshness-troubleshooting/#check-for-ddl-or-deploy-activity). **Resolution**: Put new objects on their own cluster and cut over with a [blue/green deployment](/developer-tools/dbt/blue-green-deployments/) instead of adding them to an existing production cluster. See [Operational guidelines](/clusters/operational-guidelines/) and [Optimize hydration requirements](/clusters/optimize-hydration-requirements/). ### An ad-hoc query stood up a dataflow If a query cannot be served by an existing index or materialized view, a new dataflow is created to serve it. This is similar to hydrating a new index; it causes memory usage to increase. See [Dataflows: mental model and basic terminology](/transform-data/dataflow-troubleshooting/#dataflows-mental-model-and-basic-terminology) for background. Use `EXPLAIN PLAN FOR ` to check whether a query hits an existing index or builds new operators, then use [Dataflow troubleshooting](/transform-data/dataflow-troubleshooting/) to identify which dataflow or operator is consuming memory. **Resolution**: Rewrite the query to reuse an existing index, or add one, rather than relying on ad-hoc plans for hot-path queries. Some query patterns force a new dataflow even though they look selective, for example a `WHERE` clause using `<> ALL (...)` instead of `NOT IN (...)`. ### Many concurrent SUBSCRIBEs restarted at once Like an ad-hoc query, an active `SUBSCRIBE` runs as its own dataflow. A cluster serving many concurrent subscribes (for example, one per connected UI session) can multiply its restart cost: every subscribe re-establishes its own snapshot when its replica comes back online, on top of the cluster rebuilding its indexes. That combined restart cost can exceed what the same subscribes cost at steady state, and, if it exceeds the replica's memory limit, produce a restart loop that looks unrecoverable because every restart recreates the same spike. Check how many subscribes are active on a cluster, using [`mz_internal.mz_subscriptions`](/sql/system-catalog/mz_internal/#mz_subscriptions): ```mzsql SELECT count(*) AS active_subscribes FROM mz_internal.mz_subscriptions s JOIN mz_catalog.mz_clusters c ON c.id = s.cluster_id WHERE c.name = ''; ``` **Resolution**: - Add jittered backoff to client reconnect logic so subscribes re-establish gradually instead of all at once. - As a stopgap, moving the workload to a new cluster lets clients reconnect gradually against already-hydrated indexes, avoiding the simultaneous spike while you fix the reconnect pattern above. ### The working set outgrew memory If a cluster's data no longer fits in memory, Materialize spills arrangements to its scratch disk, which shows up as elevated `disk_bytes` on `mz_cluster_replica_metrics` alongside memory near the limit, rather than an outright OOM. Two common contributors: - **Compaction state.** The same source data can require more memory to rehydrate on one replica than another if their upstream collections are compacted to different degrees; a freshly seeded environment can rehydrate cheaper than a long-running one that has retained more history. - **Unnecessarily wide objects.** An index or materialized view that carries columns no downstream query uses still arranges them, growing the arrangement for no benefit. **Resolution**: Project objects down to only the columns downstream queries need, and use [Dataflow troubleshooting: Why is Materialize using so much memory?](/transform-data/dataflow-troubleshooting/#why-is-materialize-using-so-much-memory) to find which arrangement is largest. ## Related pages - [Hydration](/fundamentals/concepts/hydration/) - [Dataflow troubleshooting](/transform-data/dataflow-troubleshooting/) - [Freshness troubleshooting](/transform-data/freshness-troubleshooting/) - [Replica resource usage](/observability/replica-resource-usage/) - [Operational guidelines](/clusters/operational-guidelines/) --- ## Troubleshoot hydration failures [Hydration](/fundamentals/concepts/hydration/) rebuilds the in-memory state of the objects on a cluster by reading from Materialize's storage layer. A cluster is hydrated once every object on it is. A query served by an index that is still hydrating blocks until that index is ready, so a cluster that never finishes hydrating looks like a query that never returns, and a blue/green deployment that waits on it never cuts over. Reading a materialized view without an index goes to its persist shard instead, and waits only when the shard has not yet reached the query's timestamp. This guide helps you diagnose a cluster that never reaches a hydrated state. For a hydration that completes but spikes memory, see [Memory spikes](/clusters/troubleshoot-clusters/memory-spike/). To size a cluster from a hydration that did complete, see [Optimize cluster size](/clusters/sizing/). ## Step 1: Find what has not hydrated [`mz_internal.mz_hydration_statuses`](/sql/system-catalog/mz_internal/#mz_hydration_statuses) reports one row per object per replica. Filtering on `id LIKE 'u%'` restricts the result to user objects, leaving out Materialize's own introspection indexes: ```mzsql SELECT c.name AS cluster_name, o.name AS object_name, o.type, h.replica_id, h.hydrated FROM mz_internal.mz_hydration_statuses AS h JOIN mz_catalog.mz_objects AS o ON o.id = h.object_id JOIN mz_catalog.mz_clusters AS c ON c.id = o.cluster_id WHERE h.hydrated IS NOT TRUE AND o.id LIKE 'u%' ORDER BY c.name, o.name; ``` ```nofmt cluster_name | object_name | type | replica_id | hydrated --------------+-------------+-------------------+------------+---------- batch_jobs | orders_idx | index | | f batch_jobs | orders_mv | materialized-view | | f (2 rows) ``` An empty result means every object is hydrated. Otherwise, `replica_id` tells you which case you are in: - **`NULL`**, as in the output above: no replica has reported on the object. Usually the cluster has no replicas, so nothing is hydrating it. Give it one with [`ALTER CLUSTER ... SET (REPLICATION FACTOR = )`](/sql/alter-cluster/). A replica that has only just started also reports `NULL` until it reports in, so re-run the query before acting on a cluster that does have replicas. A cluster left at `0` additionally pins the history of its inputs, so read [Step 3](#step-3-check-whether-an-inputs-history-is-pinned) before leaving it that way. - **Set**: that replica is hydrating the object, or failing to. Continue to [Step 2](#step-2-check-for-a-rehydration-loop). Rows are per object per replica, so on a cluster with more than one replica an object appears here while another replica may already be serving it. ## Step 2: Check for a rehydration loop Hydration is memory-intensive, and a replica that runs out of memory while hydrating is killed and restarted, which begins hydration again from the start. If the workload no longer fits the replica's size, every restart repeats the same out-of-memory kill and the cluster never reaches a hydrated state. Hydration peaks well above steady state. Steady-state memory is therefore a poor guide to what the next restart will need, and a cluster sized against it can stop fitting as data grows, with no change to the objects on it. ```mzsql SELECT rh.cluster_name, rh.replica_name, rh.size, count(DISTINCT h.occurred_at) FILTER (WHERE h.status = 'offline') AS offline_events, max(h.occurred_at) FILTER (WHERE h.reason = 'oom-killed') AS last_oom FROM mz_internal.mz_cluster_replica_status_history AS h JOIN mz_internal.mz_cluster_replica_history AS rh ON rh.replica_id = h.replica_id WHERE h.occurred_at > now() - INTERVAL '1 day' GROUP BY rh.cluster_name, rh.replica_name, rh.size HAVING count(DISTINCT h.occurred_at) FILTER (WHERE h.status = 'offline') > 0 ORDER BY offline_events DESC; ``` ```nofmt cluster_name | replica_name | size | offline_events | last_oom --------------+--------------+-------+----------------+------------------------ analytics | r1 | 25cc | 12 | 2026-09-23 15:53:41+00 (1 row) ``` Repeated `offline_events` over a short window is a rehydration loop. A `last_oom` confirms memory as the cause. It stays `NULL` when the orchestrator could not attribute the restart, so treat the restart count as the primary signal. > **Note:** Query the history rather than > [`mz_internal.mz_cluster_replica_statuses`](/sql/system-catalog/mz_internal/#mz_cluster_replica_statuses), > which reports only the current status and misses a restart that happens > between polls. Join to > [`mz_internal.mz_cluster_replica_history`](/sql/system-catalog/mz_internal/#mz_cluster_replica_history) > rather than `mz_catalog.mz_cluster_replicas`, so that replicas dropped by a > resize still resolve to a name. A multi-process replica records one row per > process per restart, which is why the count is over distinct `occurred_at` > rather than over rows. **Resolution**: size the cluster up with [`ALTER CLUSTER ... SET (SIZE = '')`](/sql/alter-cluster/). To choose a size from what the last successful hydration actually required, see [Optimize cluster size](/clusters/sizing/). To lower the peak instead of raising the size, see [Optimize hydration requirements](/clusters/optimize-hydration-requirements/). ## Step 3: Check whether an input's history is pinned A new object starts reading at an `as_of` equal to the latest of its inputs' read frontiers, which is the earliest time at which all of them are readable at once. Materialize compacts historical data whenever possible, to reduce resource consumption. Under normal circumstances, this means that about 1 second of history is available, and so a new object will have to replay only about 1 second of upstream history on top of the current snapshot. However, if compaction has been held back, the new object will need to replay more upstream history. This can take longer, and require more memory. One pinned input is not enough on its own to cause that. Because the `as_of` takes the *latest* of the read frontiers, a single current input keeps it from being dragged back. An object starts far in the past only when **every** one of its inputs is pinned. A pinned input still retains more unconsolidated updates, which can make reading it more expensive, but it no longer sets where the object starts. To determine if the input history is pinned, compare each object's "read frontier" against its "write frontier", using [`mz_internal.mz_frontiers`](/sql/system-catalog/mz_internal/#mz_frontiers): ```mzsql SELECT o.name AS input, o.type, f.read_frontier::timestamp AS readable_from, f.write_frontier::timestamp - f.read_frontier::timestamp AS retained_history FROM mz_internal.mz_frontiers AS f JOIN mz_catalog.mz_objects AS o ON o.id = f.object_id WHERE o.id LIKE 'u%' AND f.read_frontier IS NOT NULL AND f.write_frontier IS NOT NULL ORDER BY retained_history DESC LIMIT 5; ``` ```nofmt input | type | readable_from | retained_history --------------+-------------------+-------------------------+------------------ orders | table | 2026-09-15 15:31:02.511 | 192:59:38.363 shipments | table | 2026-09-23 18:30:40 | 00:00:01.874 shipments_mv | materialized-view | 2026-09-23 18:30:40 | 00:00:01.874 (3 rows) ``` A healthy object retains about a second of history. Hours or days mean its compaction is being held back. A single row here is not a diagnosis. By the `as_of` rule above, pinned inputs only slow an object down when *all* of its inputs are pinned, so check the whole input set of the object that will not hydrate rather than acting on the worst row. [`mz_internal.mz_compute_dependencies`](/sql/system-catalog/mz_internal/#mz_compute_dependencies) lists an object's inputs. An explicit `RETAIN HISTORY` window is an expected reason for hours or days of retained history, not a fault. Check [`mz_internal.mz_history_retention_strategies`](/sql/system-catalog/mz_internal/#mz_history_retention_strategies) before treating a large `retained_history` as a problem. ### Find what is holding compaction back An object holds a read hold on its inputs at a time no greater than its own write frontier, so anything whose write frontier has stopped advancing pins the history of everything it reads. Indexes on a cluster with no replicas are the exception, covered below. #### Possible cause: an object cannot finish hydrating The rehydration loop in [Step 2](#step-2-check-for-a-rehydration-loop) freezes that object's write frontier, so it pins its own inputs for as long as the loop runs, and each restart has more history to replay than the last. Resolve it as described in [Step 2](#step-2-check-for-a-rehydration-loop). #### Possible cause: a cluster has a replication factor of `0` A cluster with `REPLICATION FACTOR` set to `0` never advances the write frontiers of the objects on it. Materialized views and sinks there go on pinning the history of their inputs for as long as they exist. An index on such a cluster does not pin its inputs by itself: with no replica to serve reads from it, the controller advances its read hold anyway, and the holds it places on its own inputs follow. An index only pins when something else holds its since back, such as a materialized view or sink on the same cluster that reads from it. List what such a cluster still carries: ```mzsql SELECT dep.name AS input, o.name AS object_name, o.type, c.name AS cluster_name FROM mz_internal.mz_compute_dependencies AS d JOIN mz_catalog.mz_objects AS dep ON dep.id = d.dependency_id JOIN mz_catalog.mz_objects AS o ON o.id = d.object_id JOIN mz_catalog.mz_clusters AS c ON c.id = o.cluster_id WHERE c.replication_factor = 0 ORDER BY c.name, o.name; ``` ```nofmt input | object_name | type | cluster_name --------+-------------+-------------------+-------------- orders | orders_mv | materialized-view | batch_jobs (1 row) ``` [`mz_internal.mz_compute_dependencies`](/sql/system-catalog/mz_internal/#mz_compute_dependencies) covers indexes and materialized views but not sinks, so a sink on the cluster pins its inputs without appearing here. Index rows are worth reading against the rule above: an index listed here is pinning only if a materialized view or sink on the same cluster reads from it. **Resolution**: drop the materialized views and sinks the cluster carries, along with any index one of them reads from. The read holds belong to the objects rather than to the cluster, so dropping the cluster works only because it takes its objects with it. Setting a cluster's replication factor to `0` is usually what caused the problem, and never fixes it. An index the cluster carries on its own is not holding anything back and does not need dropping. Dropping is destructive. Dropping a materialized view discards its persisted output, so recreating it hydrates it again from its inputs. One with dependents needs [`DROP MATERIALIZED VIEW ... CASCADE`](/sql/drop-materialized-view/), which drops those dependents too and leaves you to recreate them. Capture the definitions with [`SHOW CREATE MATERIALIZED VIEW`](/sql/show-create-materialized-view/) or [`SHOW CREATE INDEX`](/sql/show-create-index/) first. Once the objects are gone, the read frontiers of their inputs advance again and a new deployment can follow within seconds. Do not restore a replica to a cluster that has sat at `0` for days while it still carries a materialized view or sink. The replica rehydrates that object, and any index it reads from, through the whole retained backlog, which is the out-of-memory case in [Step 2](#step-2-check-for-a-rehydration-loop). Drop those objects and recreate them instead. A cluster carrying only indexes does not have this problem, because their read holds were forwarded while it was paused and a new replica starts them near the present. ## Related pages - [Hydration](/fundamentals/concepts/hydration/) - [Optimize cluster size](/clusters/sizing/) - [Optimize hydration requirements](/clusters/optimize-hydration-requirements/) - [Memory spikes](/clusters/troubleshoot-clusters/memory-spike/) - [Freshness troubleshooting](/transform-data/freshness-troubleshooting/)