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.

Common causes

First, confirm the spike window and scope

Establish when the anomaly started and which replicas it affected, replacing <cluster_name> with the name of your cluster:

SELECT
    u.occurred_at,
    r.name AS replica_name,
    u.cpu_percent
FROM mz_internal.mz_cluster_replica_utilization_history 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 = '<cluster_name>'
  AND u.occurred_at > now() - INTERVAL '6 hours'
ORDER BY u.occurred_at DESC;

        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.

• If the anomaly repeats on a fixed cadence, or CPU stays high while the cluster’s inputs are idle, suspect a temporal filter. A window whose boundary moves with mz_now() retracts rows as they age out, so it generates work with no upstream input at all.

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.

• CPU is low but freshness is degraded: the work is not being spread across workers. Start with 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:

SET CLUSTER TO <cluster_name>;
EXPLAIN ANALYZE CLUSTER CPU WITH SKEW;

                  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.

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 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:

EXPLAIN ANALYZE CPU WITH SKEW FOR MATERIALIZED VIEW orders_enriched;

          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 <object_name> 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:

SELECT customer_id, count(*) AS num_rows
FROM orders
GROUP BY customer_id
ORDER BY num_rows DESC
LIMIT 20;

 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? for other causes of skew, such as cross joins and ORDER BY/LIMIT/OFFSET queries.

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

SET CLUSTER TO <cluster_name>;
EXPLAIN ANALYZE CLUSTER CPU;

                  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 <object_name> to see its operators.

Resolution

Optimize the expensive object. 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 = '<new size>').

Prevention

• Review the plan of a new object with 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.

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:

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 = '<cluster_name>'
  AND NOT h.hydrated;

  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, 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.

• Cluster resizes, which hydrate the new replicas before dropping the old ones.

Prevention

• Introduce new objects through a blue/green deployment 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.

Ad-hoc query load

A cluster serves SELECTs 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

SET CLUSTER TO <cluster_name>;

SELECT object_id, type, count(*) AS active_peeks
FROM mz_introspection.mz_active_peeks
GROUP BY object_id, type
ORDER BY active_peeks DESC;

 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 instead.

Resolution

Serve queries from a cluster separate from the one maintaining the objects, as described in the operational guidelines.

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 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:

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 = '<cluster_name>';

 name | messages_received | updates_committed
------+-------------------+-------------------
 lg   |               171 |               170
(1 row)

These counters are best-effort and only meaningful as rates; see 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.

Prevention

• Size clusters against the peak change rate rather than the average, and revisit the sizing when upstream workloads change.

• 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 for the same window:

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 = '<cluster_name>';

 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.

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.

SET CLUSTER TO <cluster_name>;
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;

 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 × <number of workers> 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 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 = '<new size>') or move objects to another cluster.

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 for how to lay out clusters. See Check cluster health for the corresponding freshness symptoms.