Home  /  Serve results  /  Troubleshooting

Troubleshooting: Unresponsive queries

View as Markdown

This guide helps you find out why a query hangs or doesn’t return results, and how to fix it.

A query that reads from an object that can’t serve results yet waits until the object is ready. This is how Materialize makes sure that every result is consistent.

Common causes

  • Snapshotting source: A new source must read a snapshot of the existing upstream data before queries on it return.
  • Stalled source: A source has stopped ingesting data, so its dependencies can’t advance.
  • Hydrating objects: After an index or materialized view is created, or its cluster restarts or is resized, Materialize rebuilds the object’s state. Queries that read from it wait until hydration finishes.
  • Unhealthy cluster: The cluster is out of memory and restarting, or its CPU is saturated.

If none of these causes applies, the query may be running, just slowly. See Slow queries.

Diagnosing the issue

Find running queries

List the queries that are still running, from the statement log:

SELECT
  a.execution_id,
  s.connection_id,
  a.cluster_name,
  now() - a.began_at AS running_for,
  left(a.sql, 60) AS sql
FROM mz_internal.mz_recent_activity_log AS a
JOIN mz_internal.mz_sessions AS s ON s.id = a.session_id
WHERE a.finished_at IS NULL
ORDER BY a.began_at;

             execution_id             | connection_id | cluster_name | running_for  |                             sql
--------------------------------------+---------------+--------------+--------------+--------------------------------------------------------------
 01a0ee6a-3442-7115-a3bb-f502ad3e5a30 |    1518224533 | quickstart   | 00:00:06.021 | SELECT count(*) FROM orders a, orders b WHERE a.amount + b.a

Note the connection_id. You need it to cancel the query.

The statement log is sampled and written in batches, so a running query may not appear here, especially if it started only a few seconds ago.

Check for snapshotting sources

SELECT s.name, s.type, st.snapshot_committed
FROM mz_internal.mz_source_statistics AS st
JOIN mz_catalog.mz_sources AS s ON s.id = st.id
WHERE s.id LIKE 'u%'
  AND NOT st.snapshot_committed;

Any source in the result is still snapshotting, and queries that depend on it wait until the snapshot completes.

Check for stalled sources

SELECT name, type, status, error
FROM mz_internal.mz_source_statuses
WHERE id LIKE 'u%'
  AND status IN ('stalled', 'paused');

A source in the result isn’t ingesting data. The error column shows why.

Check for hydrating objects

SELECT o.name, o.type, r.name AS replica
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_cluster_replicas AS r ON r.id = h.replica_id
WHERE h.hydrated IS NOT TRUE;

Any object in the result is still hydrating on that replica. You can also see hydration status on the object’s workflow graph in the console: click Clusters, select the cluster, select the object under Materialized Views or Indexes, then open the Workflow tab.

Check cluster health

Check whether the cluster’s replicas restarted recently:

SELECT c.name AS cluster, r.name AS replica, h.status, h.reason, h.occurred_at
FROM mz_internal.mz_cluster_replica_status_history AS h
JOIN mz_catalog.mz_cluster_replicas AS r ON r.id = h.replica_id
JOIN mz_catalog.mz_clusters AS c ON c.id = r.cluster_id
WHERE h.occurred_at > now() - INTERVAL '1 day'
ORDER BY h.occurred_at DESC
LIMIT 10;

A status of offline with reason oom-killed means the replica ran out of memory and restarted. Queries running on it restart from the beginning. If the query itself caused the out-of-memory error, the replica restarts in a loop until you cancel the query.

To check CPU and memory utilization, see Check cluster utilization.

Resolution

Cancel the query

Cancel a running query with pg_cancel_backend, using the connection_id from Find running queries:

SELECT pg_cancel_backend(1518224533);

The statement log then records the query with finished_status = 'canceled'. The cluster can take a while to tear down the query’s dataflow, so its CPU and memory usage may stay high for some time after you cancel it.

Wait for the snapshot or hydration

Snapshotting and hydration take time proportional to data volume and query complexity. They finish on their own.

Fix the source

To fix a stalled source, see Troubleshoot ingestion.

Fix the cluster

  • If your query caused the cluster to run out of memory or saturate its CPU, cancel it, then reduce its cost. See Expensive queries.
  • If other work on the cluster caused it, wait for that work to finish, run the query on a different cluster, or size up the cluster.
  • To get notified before a cluster reaches its capacity, set up alerting.
Back to top ↑