Troubleshooting
View as MarkdownThis section contains troubleshooting guides for queries that don’t perform as expected. Each guide starts from a symptom, helps you find the cause with SQL against the system catalog, and describes how to fix it.
Troubleshooting guides
| Guide | Description |
|---|---|
| Slow queries | Find which stage of a query’s lifecycle takes the most time, and fix it. Includes a section for self-managed deployments. |
| Unresponsive queries | Find why a query hangs or never returns, and cancel it. |
| Expensive queries | Find queries that use a lot of CPU or memory on a cluster, and reduce their cost. |
Query history
All three guides use the statement log, which records a sample of the SQL
statements issued against Materialize in the last 24 hours. You can browse
it in the Query history tab of the Materialize
console, or query it through
mz_internal.mz_recent_activity_log.
To query the statement log, connect as a superuser or as a user granted the
mz_monitor role.
Statements are sampled and throttled, so not every statement appears in the log. In Materialize Cloud, Materialize controls the sample rate and may change it at any time. In self-managed deployments, the operator sets it.
Control the sample rate on Materialize Self-Managed
The fraction of statements logged is the smaller of two values:
| Parameter | Scope | Description |
|---|---|---|
statement_logging_max_sample_rate |
System | Upper bound for every session. 0 disables statement logging. |
statement_logging_sample_rate |
Session | Rate the session requests. New sessions start at statement_logging_default_sample_rate. |
A higher sample rate makes the log more complete, which helps when you need to
find a specific slow query. The tradeoff is CPU overhead on environmentd, which
grows with statement throughput. Separately,
statement_logging_target_data_rate caps the bytes written per second, so on a
busy instance some sampled statements are still dropped even at a rate of 1.0.
To change the system-wide rates, connect as the mz_system user and run:
ALTER SYSTEM SET statement_logging_max_sample_rate = 0.5;
ALTER SYSTEM SET statement_logging_default_sample_rate = 0.5;
To log every statement in your own session while you debug, run:
SET statement_logging_sample_rate = 1.0;
To check the rates in effect:
SHOW statement_logging_max_sample_rate;
SHOW statement_logging_sample_rate;
ALTER SYSTEM SET takes effect immediately and overrides the Helm chart value.
For the Helm chart settings, storage costs, and how to revert an override, see
Query History.
For a complete history of DDL statements, use
mz_audit_events.