Traces in SQLite

Which route is slow? What failed last night? What did this one request do? Stario records a span for every request, and with one environment variable those spans land in a SQLite file. The answers are then SQL queries against a file on disk, with no dashboard and no agent.

STARIO_TRACER=sqlite STARIO_TRACERS_SQLITE=traces.sqlite3 uv run python main.py

That is the whole setup. The file is a local record of one process, not a distributed trace store; for several machines or a hosted backend, write a custom tracer.

Settings#

Only the variables you set change anything. The rest keep the constructor defaults.

In a container, point the path at a mounted volume: STARIO_TRACERS_SQLITE=/data/traces.sqlite3. For anything these variables do not cover, a custom tracer module can return a SqliteTracer you constructed yourself.

What is in the file#

Three tables, defined in stario.telemetry.sqlite:

• spans: one row per finished span. span_id, trace_id, parent_id, name, start_ns, end_ns, duration_ns, status (ok or error), error text when it failed, and attrs_json.

• span_events: named events on a span, with name, time_ns, attrs_json, and body. An exception is an event named exception.

• span_links: references from one span to another, from Span.link().

The request facts live inside attrs_json, not in columns: request.method, request.path, response.status_code, and whatever your handlers added. The keys contain dots, so quote them: attrs_json ->> 'request.path'. Not every root span is a request; server.startup and server.shutdown are roots too, so filter on request.path IS NOT NULL when you mean traffic.

Open the file with sqlite3 traces.sqlite3 or any SQL client, and start with the question you have.

Which routes are slow?#

The average over all requests:

SELECT

  ROUND(AVG(duration_ns) / 1e6, 3) AS avg_ms,

  COUNT(*) AS n

FROM spans

WHERE parent_id IS NULL

  AND attrs_json ->> 'request.path' IS NOT NULL;

The twenty slowest, with their paths:

SELECT

  attrs_json ->> 'request.method' AS method,

  attrs_json ->> 'request.path' AS path,

  attrs_json ->> 'response.status_code' AS status,

  duration_ns / 1e6 AS dur_ms,

  datetime(end_ns / 1e9, 'unixepoch') AS ended_utc

FROM spans

WHERE attrs_json ->> 'request.path' IS NOT NULL

ORDER BY duration_ns DESC

LIMIT 20;

An approximate p95. SQLite has no percentile function, so this takes the row at the 95th position of the sorted durations, which is close enough once you have a few hundred samples:

SELECT duration_ns / 1e6 AS approx_p95_ms

FROM spans

WHERE parent_id IS NULL

  AND attrs_json ->> 'request.path' IS NOT NULL

ORDER BY duration_ns ASC

LIMIT 1 OFFSET (

  SELECT CASE

    WHEN COUNT(*) = 0 THEN 0

    ELSE MAX(0, CAST((COUNT(*) - 1) * 0.95 AS INTEGER))

  END

  FROM spans AS s2

  WHERE s2.parent_id IS NULL

    AND s2.attrs_json ->> 'request.path' IS NOT NULL

);

What failed?#

Responses with a 5xx status:

SELECT

  attrs_json ->> 'request.method' AS method,

  attrs_json ->> 'request.path' AS path,

  attrs_json ->> 'response.status_code' AS status,

  duration_ns / 1e6 AS dur_ms,

  datetime(end_ns / 1e9, 'unixepoch') AS ended_utc

FROM spans

WHERE CAST(attrs_json ->> 'response.status_code' AS INTEGER) >= 500

ORDER BY end_ns DESC

LIMIT 50;

Spans the code marked as failed, with the error text:

SELECT

  name,

  error,

  attrs_json ->> 'request.path' AS path,

  datetime(end_ns / 1e9, 'unixepoch') AS ended_utc

FROM spans

WHERE status = 'error'

ORDER BY end_ns DESC

LIMIT 50;

The exceptions themselves, from the events table:

SELECT

  e.name AS event_name,

  s.attrs_json ->> 'request.path' AS path,

  e.body AS detail,

  datetime(e.time_ns / 1e9, 'unixepoch') AS event_utc

FROM span_events AS e

JOIN spans AS s ON s.span_id = e.span_id

WHERE e.name = 'exception'

ORDER BY e.time_ns DESC

LIMIT 50;

What happened just now?#

The last twenty requests, newest first:

SELECT

  attrs_json ->> 'request.method' AS method,

  attrs_json ->> 'request.path' AS path,

  attrs_json ->> 'response.status_code' AS status,

  duration_ns / 1e6 AS dur_ms,

  datetime(end_ns / 1e9, 'unixepoch') AS ended_utc

FROM spans

WHERE attrs_json ->> 'request.path' IS NOT NULL

ORDER BY end_ns DESC

LIMIT 20;

What did one request do?#

Child spans share the request's trace_id. The ids are stored as bytes, so read them with hex(): add hex(trace_id) AS trace to any query above, then list that trace in order:

SELECT

  name,

  hex(parent_id) AS parent,

  duration_ns / 1e6 AS dur_ms,

  status

FROM spans

WHERE hex(trace_id) = '3F2A…'

ORDER BY start_ns;

Spans linked from elsewhere, such as a background job that records which request started it:

SELECT

  s.name AS from_span,

  l.name AS link_name,

  l.target_span_id,

  datetime(s.end_ns / 1e9, 'unixepoch') AS ended_utc

FROM span_links AS l

JOIN spans AS s ON s.span_id = l.span_id

ORDER BY s.end_ns DESC

LIMIT 50;

By the way#

• A trace file from an older schema has no migration. Delete it and start again.

• The tracer keeps its own health in memory, not in the file: tracer.stats() reports dropped_spans and writer_error_count on a SqliteTracer you constructed yourself.

• Telemetry design explains why a span looks the way it does, and Telemetry lists what you can put on one.

To try the queries on real data, serve examples/tiles with STARIO_TRACER=sqlite STARIO_TRACERS_SQLITE=traces.sqlite3 and click around for a minute.