Query log and stats

DNSY records every answered query. Events pass through a bounded channel (10,000 events) to a batched SQLite writer (up to 500 events or 1 second per batch). When the channel is full, events are dropped and counted instead of slowing DNS. A batch that fails to write (disk full, locked or read-only database) counts its events as dropped as well and increments a flush-error counter. GET /api/v1/stats/summary reports both since the server started, as querylog_dropped and querylog_flush_errors; watch them for gaps in the log.

Sampling, size cap and a full disk

• [querylog] sample = N keeps one raw query_log row in N queries (default 1: all). Only raw rows are sampled: minute rollups and hourly top-N counts include every query, so the stats endpoints stay exact while the log grows N times slower. Searching the log shows the sample; at high QPS use 100 or more. 0 is treated as 1.
• [querylog] max_size_mb = M (default 0: unlimited) caps the live data in the database (used pages, not the file size; freed pages are reused). After each retention pass, while the data exceeds the cap, the oldest UTC day of raw rows is deleted. Rollups are kept. Today’s rows are never purged; if only today remains, a warning is logged. The cap is read at startup.
• When a write fails with SQLITE_FULL, the events of that batch are dropped and counted, the log warns once, and /readyz reports the warning querylog_disk_full (the status stays 200). The warning clears after the next successful write; free disk space or lower retention_days or max_size_mb.

What is stored

Per query: time, client address, transport, principal, query name (lowercased), type, response code, answer source (authoritative, cache, cache_stale, upstream, blocked, refused, error; with the upstream address or blocklist name as detail), DNSSEC status and latency in microseconds.

With client redaction enabled, only the /24 (IPv4) or /48 (IPv6) network address is stored, in the log and in the rollups.

Rollups and stats

Per minute: counts by response code, answer source and DNSSEC status, plus latency sum and maximum. Per hour: top query names, top blocked names and top clients. The summary reports the cache hit ratio (cache plus cache_stale answers over those plus upstream answers), stale answers, and secure and bogus counts. Rollup buckets are whole minutes or hours, so a range includes any bucket it touches.

Once a UTC day is more than 48 hours old, the hourly retention task folds it into daily rollups: stats_day (the minute counters summed per day, with latency sum and maximum) and stats_top_day (the 5000 most frequent names, blocked names and clients of that day). Each day is folded in one transaction and only once, so a pass interrupted after the fold is finished by the next one without double counting. The minute and hour rows of folded days are then deleted in batches, so only the last two to three days exist at minute and hour granularity.

• summary, timeseries with a bucket of one day or more, and the top lists read daily rows for folded days and minute or hour rows for the recent days. A folded day that the range touches counts whole, like any other rollup bucket. A 30-day top-domains query reads about 150 000 rows instead of every hourly row.
• timeseries with a bucket under one day only has data for the last two to three days. A day-or-longer bucket holds the days whose first second falls in it.
• A top list over folded days is exact for names that rank in the top 5000 of every day it covers; a name that stayed below the 5000th place on a day loses that day’s count. Totals, summaries and timeseries are exact.
• This is a one-way change: the minute and hour detail of folded days is not kept. Restore a backup to get it back.

Searching

Filter by time range, client, query-name substring, type, response code, answer source and DNSSEC status. Results are newest first with keyset pagination: pass the returned cursor to get the next page; pages stay stable while new rows arrive, and every page costs the same however deep it is (the cursor bounds the index range).

Three indexes serve the searches:

On a 10 million row log a middle cursor page went from 125 ms to 0.03 ms, client = from 1.6 s to 0.01 ms and a deep source = refused page from 274 ms to 24 ms (measurements in the database analysis). Other filters (query name substring, type, DNSSEC status, common sources) still scan the time index, which is fast for common values and slow for values that almost never match.

Migration 0015 recreates all three indexes over the compact columns (the partial index tests the source codes 4 to 6).

The first start after an upgrade builds the two indexes while holding the database: about 7 s per 10 million rows for the client index. The server logs applying migration 0010, this can take a while on a large query log before it starts and does not accept connections until it finishes. The indexes add write work per logged query (two indexes instead of one) and file size (about 30 percent for the client index).

Row encoding

Rows are stored compactly (migration 0015): transport, source and dnssec are small integers and client is the 4 (IPv4) or 16 (IPv6) address bytes. The API, the stream and the web UI show the same text values as before; the mapping lives in dnsy-observe and the codes are fixed.

The migration rebuilds the table in one transaction, so the first start after the upgrade holds the database while it copies the log (the server logs applying migration 0015 first) and an interrupted or failing run leaves the old table untouched. Expect minutes at tens of millions of rows. Rows keep their ids. The table is about 24 percent smaller (200 000 rows, three indexes: 26.3 MB to 20.2 MB after VACUUM). The freed pages stay inside the file for reuse until the database is vacuumed. Back up first, as for any upgrade.

Reading it over the API

GET /api/v1/querylog searches with the filters above and a cursor, and GET /api/v1/querylog/stream tails new rows as server-sent events (operator or admin, at most 4 streams per user and 16 overall). GET /api/v1/stats/* serves the summary, timeseries and top lists, and GET /api/v1/cache the live cache counters. See REST API. The API applies client redaction again on output and replaces anything shaped like a token with [redacted].

In the web UI

The query log page (/query-log, see Web UI) is a front end for the search and the live tail: filters, a cursor-paged table, a detail drawer and a tail toggle.

Retention

A background task, once an hour, first folds completed days older than 48 hours into the daily rollups (see above), then deletes raw rows older than the retention period, hourly and daily top-N rollups that end before it, and expired sessions. Daily counter rollups are kept 400 days, or the retention period when that is longer (the stats range is capped at 366 days, so nothing reads older ones).

Deletes run in transactions of at most 50 000 rows, so lowering the retention on a large log (PATCH /settings) never blocks the query-log writer for more than a few milliseconds at a time and the write-ahead log stays small. The task also runs PRAGMA optimize once a day (and the server once more on shutdown) so SQLite refreshes planner statistics only where needed.