Sql Debugging

Redpanda SQL: Debugging & Observability

Oxla is a closed-source C++ distributed columnar analytical database that speaks the PostgreSQL wire protocol. Its observability surface is three-layered: system catalog virtual tables (query via psql), a Prometheus metrics endpoint (HTTP on port 8080), and structured log output with a runtime-configurable level. There is no REST admin API — administration uses YAML config, environment variable overrides, a ConnectRPC admin service, and SQL system tables.

This skill covers the complete debugging workflow: from "what is running?" through "why is it slow?" to "is memory pressure causing OOM?".

Quickstart

# 1. Connect to Oxla (PostgreSQL wire protocol, default port 5432)
psql -h  -p 5432 -U oxla

# 2. See all cluster nodes and their election state
SELECT name, election_state, connected_nodes_count, degradation_error
FROM system.nodes;

# 3. See active (and recently completed) queries across the cluster
SELECT qid, requester, state, created, accepted, scheduled, executed, finished
FROM system.queries
ORDER BY created DESC;

# 4. Scrape Prometheus metrics (no auth required by default)
curl -s http://:8080/metrics | grep -E 'oxla_(query_errors|process_memory|num_nodes|admission)'

System Catalog Tables

Oxla exposes virtual tables in the system schema for diagnostics. They are populated at query runtime — there is no materialized cache. See [system-tables.md](references/system-tables.md) for full column reference and example queries.

Key tables at a glance

| Table | What it shows | |---|---| | system.nodes | Per-node election state, follower count, connected-node count, degradation error | | system.queries | Query lifecycle (created → scheduling → scheduled → executing → finished), state, workers | | system.transactions | Active catalog transactions (transactionid, snapshotid, owner node, owner name) | | system.storage_connections | Storage connections (name, type, schema, database, parameters JSON) | | system.execs | Per-node execution fragments (qid, datataskid, state, memory bytes, privileged flag) | | system.catalogs | External catalogs (Iceberg, Kafka/Redpanda) by name and type | | system.databases | All databases accessible to the current role | | system.tables | Tables by database, namespace, name | | system.columns | Columns with type and nullability | | information_schema.tables | Standard SQL information schema view of tables | | information_schema.columns | Standard SQL information schema view of columns |

Quick diagnostics

-- Is any node degraded?
SELECT name, election_state, degradation_error
FROM system.nodes
WHERE degradation_error IS NOT NULL;

-- What queries are stuck (not yet finished)?
SELECT qid, requester, state, created,
       EXTRACT(EPOCH FROM (NOW() - created)) AS age_seconds
FROM system.queries
WHERE finished IS NULL
ORDER BY created ASC;

-- Active catalog transactions (distributed catalog must be enabled)
SELECT * FROM system.transactions;

Prometheus Metrics

The metrics endpoint listens on port 8080 (configured in metrics.port in the cluster config file, e.g. config/Release/default_config.yml). No authentication is required by default. All metric names are prefixed oxla_.

# Full scrape
curl -s http://:8080/metrics

# Filter to health indicators
curl -s http://:8080/metrics \
  | grep -E 'oxla_(cluster_has_leader|node_is_ready|node_is_degraded|num_nodes_connected)'

# Query error rate
curl -s http://:8080/metrics | grep oxla_query_errors_total

# Memory usage
curl -s http://:8080/metrics | grep oxla_process_memory_total

See [metrics-and-logging.md](references/metrics-and-logging.md) for the full metric catalog with types, labels, and Prometheus query examples.

Log Levels

Oxla uses the plog library. Log levels in ascending verbosity order:

FATAL → ERROR → WARNING → INFO → DEBUG → VERBOSE

Default is INFO. Logs are written to stdout (with colors in TTY mode) and to files under $TMPDIR/oxla/ (defaults to /tmp/oxla/ when $TMPDIR is unset) with the path pattern server...log.

Set log level in config (requires restart)

# config/Release/default_config.yml (exact path is deployment-dependent)
logging:
  level: "DEBUG"

Or via environment variable (no restart needed at launch; restart required to pick up env change):

OXLA__LOGGING__LEVEL=DEBUG ./oxla_server

Set log level at runtime (no restart) via admin gRPC service

The admin gRPC service listens on port 9090 by default (configured in admin_api.port). It speaks ConnectRPC (HTTP/1.1 or HTTP/2 with protobuf or JSON encoding).

# Using grpcurl — set level to DEBUG
grpcurl -plaintext \
  -d '{"level": "LOG_LEVEL_DEBUG"}' \
  :9090 \
  oxla.admin.v1.LoggingService/SetLogLevel

# Get current level
grpcurl -plaintext \
  -d '{}' \
  :9090 \
  oxla.admin.v1.LoggingService/GetLogLevel

Valid level values: LOG_LEVEL_NONE, LOG_LEVEL_FATAL, LOG_LEVEL_ERROR, LOG_LEVEL_WARNING, LOG_LEVEL_INFO, LOG_LEVEL_DEBUG, LOG_LEVEL_VERBOSE.

See [metrics-and-logging.md](references/metrics-and-logging.md) for full details.

Memory / OOM Monitoring

Oxla runs an internal OOMMonitor background thread that checks the process RSS (Resident Set Size) from /proc/self/status every 100 ms. When RSS exceeds the operational limit — total - 1% (the 1% margin is k_oom_monitor_margin_factor = 0.01 from src/mem/limits.h), where total is the computed memory limit derived from memory.max or auto-detection — it triggers emergency actions: (1) cancel all running tasks/queries and (2) evict the storage cache, logging an allocation-state report.

The memory limit is set in config (config/Release/default_config.yml, exact path is deployment-dependent):

memory:
  max: 0          # 0 = auto-detect available RAM; otherwise set explicitly (e.g. 16G)
  max_non_query: 6442M  # RAM reserved for non-query overhead; minimum 6442M

Monitor memory via Prometheus:

# Current process RSS in bytes
curl -s http://:8080/metrics | grep oxla_process_memory_total

# Admission queue (queries waiting due to memory/concurrency limits)
curl -s http://:8080/metrics \
  | grep -E 'oxla_admission_(active_queries|enqueued_queries|timeout_queries_failed_total)'

See [troubleshooting.md](references/troubleshooting.md) for the full OOM investigation workflow.

Troubleshooting Workflows

| Symptom | First move | |---|---| | Queries stuck / not progressing | system.queries WHERE finished IS NULL; check state | | Node not participating | system.nodes — look for degradation_error IS NOT NULL | | Out-of-memory / process killed | oxla_process_memory_total metric; check memory.max config | | Query admission failures / timeouts | oxla_admission_timeout_queries_failed_total metric | | Kafka ingestion stalled | oxla_kafka_messages_consumed_total and oxla_kafka_messages_failed_total | | Iceberg table stale / rows missing | Check upstream Redpanda redpanda.iceberg.target.lag.ms, DLQ table, REST catalog auth — see [redpanda-iceberg-source.md](references/redpanda-iceberg-source.md) | | Storage reads slow | oxla_sf_read_bytes (single-file reader throughput) | | No cluster leader | oxla_cluster_has_leader_bool == 0 | | Background tasks stalled | oxla_tasks_ongoing_total{scheduler_role,file_task_type} (compaction/merge) |

See [troubleshooting.md](references/troubleshooting.md) for step-by-step playbooks.

External Data Sources: Redpanda / Kafka & Iceberg

Oxla ingests from external systems registered in system.catalogs (type is redpanda for a Kafka-protocol connection, or iceberg for an Apache Iceberg catalog). When ingestion stalls or an Iceberg table looks stale, the root cause is frequently on the Redpanda side, in Redpanda's Iceberg Topics integration — the producer of the Iceberg tables Oxla reads.

-- What external sources are registered, and of what type?
SELECT name, namespace_name, type FROM system.catalogs;

Redpanda Iceberg Topics (and its Tiered Storage prerequisite, and Server-Side Schema ID Validation) are Redpanda Enterprise Edition features — without a valid Redpanda license, topics cannot be created or modified with redpanda.iceberg.mode. Verify on the Redpanda cluster with rpk cluster license info.

Key upstream knobs to correlate with an Oxla symptom:

| Symptom in Oxla | Upstream Redpanda setting | |---|---| | Iceberg table stale / lags real time | redpanda.iceberg.target.lag.ms (commit cadence) | | Rows silently missing | redpanda.iceberg.invalid.record.action (drop vs dlq_table) + the ~dlq table | | Table cannot be loaded | iceberg_catalog_type=rest auth keys (iceberg_rest_catalog_*) | | Table vanished after topic delete | redpanda.iceberg.delete (default true) | | No Iceberg data ever produced | iceberg_enabled, Enterprise license, Tiered Storage prerequisite |

Full property tables (cluster-level iceberg_*, topic-level redpanda.iceberg.*, REST catalog auth keys, DLQ inspection, and the symptom-to-cause checklist) are in [redpanda-iceberg-source.md](references/redpanda-iceberg-source.md).

Reference Directory

• [system-tables.md](references/system-tables.md): Full column schema for every diagnostic system catalog table, with copy-pasteable example queries for node health, active queries, transactions, storage connections, and execution fragments.
• [metrics-and-logging.md](references/metrics-and-logging.md): Prometheus endpoint setup, the complete metric catalog (names, types, labels, descriptions), log level control via config/env/admin gRPC, and example PromQL queries.
• [troubleshooting.md](references/troubleshooting.md): Step-by-step workflows for diagnosing slow queries, memory/OOM pressure, node health problems, Kafka ingestion issues, and admission control failures.
• [redpanda-iceberg-source.md](references/redpanda-iceberg-source.md): Debugging Oxla's external data sources — the Redpanda/Kafka ingestion path and the Apache Iceberg catalog Oxla reads. Documents the upstream Redpanda Iceberg Topics integration (Enterprise-licensed): cluster properties (iceberg_enabled, iceberg_catalog_type, iceberg_default_catalog_namespace, iceberg_delete, iceberg_invalid_record_action, iceberg_default_partition_spec), REST catalog auth keys (iceberg_rest_catalog_*), topic properties (redpanda.iceberg.mode/delete/invalid.record.action/partition.spec/target.lag.ms), Schema ID Validation interplay, the ~dlq dead-letter queue, and an Oxla-symptom-to-Redpanda-cause checklist. Notes the Enterprise license requirement.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: redpanda-data
• Source: redpanda-data/skills
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.