Storage
Spider Sense keeps what it collects in an H2 file database under the user’s home directory. The UI therefore still answers after the monitored application has stopped, and every Spider Sense process on the machine, embedded or standalone, sees the same data.
Where
The default JDBC URL is jdbc:h2:~/db/spider-sense/sense;AUTO_SERVER=TRUE, which is the file ~/db/spider-sense/sense.mv.db.
sense.trace.db appears beside it when H2 logs a problem.
spidersense.db overrides the URL with a path (~/db/other/sense) or a full jdbc:h2: URL.
A path may end in the file’s own .mv.db suffix (or the older .h2.db), which is dropped because H2 adds it, so the file status prints names the same database.
AUTO_SERVER=TRUE lets embedded and standalone Spider Sense processes share the file without any setup:
-
The first process to open the file becomes its server. A second process that opens the same URL connects to the first over a local TCP port, transparently. Two applications each embedding Spider Sense, plus a standalone
java -jar spider-sense.jaropened to look at both, all share one database at once. -
When the process holding the file exits, the next opener takes over. Stop the application, run
java -jar spider-sense.jar, and the traces of the session that just ended are on screen.
The H2 driver runs inside the SenseClassLoader, which the OpenTelemetry agent is told to leave alone.
The application’s traces therefore never contain Spider Sense’s own SQL.
An application that uses H2 itself loads its own copy of the driver in its own class loader, and the two copies share no class and no state.
What is stored
There is one row per span, log record, metric point, tingle and mark (a named moment, see Marks), plus a per-trace summary row that the writer maintains. The lists and aggregations the API serves are therefore SQL over indexed columns rather than a scan of blobs. Attributes and events are kept as JSON text in the same row. A stored attributes or resource text that is not a JSON object, or events text that is not an array of events, which only a row of another version could hold, reads as none, and the server logs that once. The columns beside them hold the values the queries need, extracted once at ingest.
CREATE TABLE IF NOT EXISTS service (
name VARCHAR(255) PRIMARY KEY,
language VARCHAR(64),
pid BIGINT,
first_seen BIGINT NOT NULL, -- epoch ms
last_seen BIGINT NOT NULL,
resource VARCHAR(65535) NOT NULL -- JSON object of the resource attributes
);
CREATE TABLE IF NOT EXISTS span (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
trace_id CHAR(32) NOT NULL,
span_id CHAR(16) NOT NULL,
parent_span_id CHAR(16),
service VARCHAR(255) NOT NULL,
name VARCHAR(1024) NOT NULL,
kind VARCHAR(8) NOT NULL, -- SERVER CLIENT INTERNAL PRODUCER CONSUMER
start_ms BIGINT NOT NULL,
start_ns BIGINT NOT NULL,
duration_ns BIGINT NOT NULL,
status VARCHAR(5) NOT NULL, -- UNSET OK ERROR
status_message VARCHAR(4096),
entry BOOLEAN NOT NULL, -- SERVER/CONSUMER, or a non-db root CLIENT/PRODUCER, unless the endpoint is ignored (Endpoint identity)
error BOOLEAN NOT NULL,
slow BOOLEAN NOT NULL, -- entry over slow.request.ms, or db over slow.query.ms
category VARCHAR(10) NOT NULL, -- http db messaging rpc internal
endpoint VARCHAR(1024), -- entry spans: the endpoint name (see Endpoint identity)
endpoint_id CHAR(12),
http_method VARCHAR(16),
http_route VARCHAR(1024),
http_status INT,
db_system VARCHAR(64),
db_statement VARCHAR(4096), -- cut at 2000 characters
db_namespace VARCHAR(255),
db_operation VARCHAR(64),
db_table VARCHAR(255),
query_id CHAR(12),
error_type VARCHAR(512),
error_message VARCHAR(4096), -- as received
error_id CHAR(12), -- hash over the root cause and its frame, or the normalised message
scope VARCHAR(255),
attributes VARCHAR(65535) NOT NULL, -- JSON object; the longest string values cut to fit, ending in …
events VARCHAR(65535) NOT NULL -- JSON array; cut the same way
);
CREATE INDEX IF NOT EXISTS span_start ON span (start_ms);
CREATE UNIQUE INDEX IF NOT EXISTS span_trace ON span (trace_id, span_id);
CREATE INDEX IF NOT EXISTS span_service ON span (service, start_ms);
CREATE INDEX IF NOT EXISTS span_endpoint ON span (endpoint_id, start_ms);
CREATE INDEX IF NOT EXISTS span_query ON span (query_id, start_ms);
CREATE INDEX IF NOT EXISTS span_error ON span (error_id, start_ms);
CREATE TABLE IF NOT EXISTS trace (
trace_id CHAR(32) PRIMARY KEY,
start_ms BIGINT NOT NULL,
end_ms BIGINT NOT NULL,
duration_ns BIGINT NOT NULL, -- last span end minus first span start
root_span_id CHAR(16),
root_name VARCHAR(1024) NOT NULL,
root_service VARCHAR(255) NOT NULL,
root_kind VARCHAR(8) NOT NULL,
services VARCHAR(4096) NOT NULL, -- JSON array
span_count INT NOT NULL,
error_count INT NOT NULL,
db_count INT NOT NULL,
http_status INT,
slow BOOLEAN NOT NULL,
error BOOLEAN NOT NULL
);
CREATE INDEX IF NOT EXISTS trace_start ON trace (start_ms);
CREATE INDEX IF NOT EXISTS trace_service ON trace (root_service, start_ms);
CREATE TABLE IF NOT EXISTS log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
at_ms BIGINT NOT NULL,
service VARCHAR(255) NOT NULL,
severity_number INT NOT NULL,
severity VARCHAR(8) NOT NULL,
body VARCHAR(65535) NOT NULL,
logger VARCHAR(512),
trace_id CHAR(32),
span_id CHAR(16),
attributes VARCHAR(65535) NOT NULL
);
CREATE INDEX IF NOT EXISTS log_at ON log (at_ms);
CREATE INDEX IF NOT EXISTS log_trace ON log (trace_id);
CREATE INDEX IF NOT EXISTS log_service ON log (service, at_ms);
CREATE TABLE IF NOT EXISTS metric (
service VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
type VARCHAR(12) NOT NULL, -- gauge sum histogram
unit VARCHAR(64),
description VARCHAR(1024),
monotonic BOOLEAN NOT NULL,
temporality VARCHAR(12), -- cumulative delta
PRIMARY KEY (service, name)
);
CREATE TABLE IF NOT EXISTS metric_series (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
service VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
attr_hash CHAR(12) NOT NULL,
attributes VARCHAR(4096) NOT NULL, -- JSON object, keys sorted; cut as span attributes are
CONSTRAINT metric_series_key UNIQUE (service, name, attr_hash)
);
CREATE TABLE IF NOT EXISTS metric_point (
series_id BIGINT NOT NULL,
at_ms BIGINT NOT NULL,
value DOUBLE, -- gauge/sum value, histogram mean
count BIGINT, -- histogram
sum DOUBLE,
min DOUBLE, -- NULL when the sender did not report it (optional in OTLP)
max DOUBLE,
buckets VARCHAR(8192), -- JSON {"bounds":[...],"counts":[...]}
PRIMARY KEY (series_id, at_ms)
);
CREATE TABLE IF NOT EXISTS tingle (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
at_ms BIGINT NOT NULL,
kind VARCHAR(16) NOT NULL, -- slow-request slow-query error
service VARCHAR(255) NOT NULL,
title VARCHAR(1024) NOT NULL,
detail VARCHAR(4096) NOT NULL,
trace_id CHAR(32),
span_id CHAR(16),
duration_ms DOUBLE
);
CREATE INDEX IF NOT EXISTS tingle_at ON tingle (at_ms);
CREATE UNIQUE INDEX IF NOT EXISTS tingle_span ON tingle (trace_id, span_id, kind);
CREATE TABLE IF NOT EXISTS mark (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
at_ms BIGINT NOT NULL,
name VARCHAR(64) NOT NULL, -- [A-Za-z0-9._-]; "start" is written by the writer
service VARCHAR(255),
note VARCHAR(1024)
);
CREATE INDEX IF NOT EXISTS mark_at ON mark (at_ms);
CREATE TABLE IF NOT EXISTS ack (
finding_id VARCHAR(64) PRIMARY KEY, -- the finding id, kind + ':' + 12 hex (Findings)
at_ms BIGINT NOT NULL,
note VARCHAR(1024),
resolved BOOLEAN NOT NULL DEFAULT FALSE -- a resolution rather than an acknowledgement
);
CREATE TABLE IF NOT EXISTS db_table (
service VARCHAR(255) NOT NULL,
schema_name VARCHAR(255) NOT NULL, -- '' when the database reports none
table_name VARCHAR(255) NOT NULL, -- as the database reports it: ITEMS on H2, items on PostgreSQL
product VARCHAR(64), -- DatabaseMetaData.getDatabaseProductName()
indexes VARCHAR(65535) NOT NULL, -- JSON array [{"name":…,"unique":…,"columns":[…]}], columns in key order, "" for a key part with no column
seen_ms BIGINT NOT NULL,
PRIMARY KEY (service, schema_name, table_name)
);
CREATE TABLE IF NOT EXISTS meta (
key VARCHAR(64) PRIMARY KEY,
value VARCHAR(4096) NOT NULL -- schema_version, created_at
);
The schema is created with IF NOT EXISTS at startup.
The stored meta.schema_version is read before any table is created.
A database of another version therefore has its tables dropped before this version’s are created, since an index of this version may be on a column an older table lacks.
The CLI, which refuses another version, leaves such a database exactly as it found it.
It also refuses a database without meta: one that holds tables is one Spider Sense never wrote, such as the application’s own named by a mistaken --db, and an empty one, such as the one behind a jdbc:h2:mem: URL, gets no tables from the CLI.
meta.schema_version is 9, the version that made a span unique by its trace and span id, and a tingle by its span and kind.
A database of an earlier version may hold a span or a tingle twice, and a unique index cannot be built over it, so the upgrade drops its tables like any other.
Version 8 keyed metric by service as well as name, version 7 moved the error group key to the root cause and its frame, version 6 added the resolved column of ack, version 5 added db_table, version 4 added ack, and version 3 added mark.
A version that changes a table or the meaning of a stored id drops and recreates every table, because the store holds at most the last 24 hours of a development session by default, and running the application again writes new data.
Version 7 changed every error_id, and so the id of every error finding.
Acknowledgements and resolutions recorded before the upgrade are therefore lost with the rest of the tables, rather than kept as rows whose finding_id no finding has any more.
An ack row is an acknowledged finding (Acknowledgements), or, with resolved true, a resolved one (Resolutions).
The two share a table because a finding has one decision recorded at a time, and the newer one replaces the older through the same MERGE INTO ack … KEY (finding_id).
The retention sweeper does not delete an ack row by age, so a finding acknowledged or resolved longer ago than the retention is still listed as acknowledged, or as a regression, when it appears again.
DELETE /api/data removes it with everything else.
A db_table row is the index catalog of one table of one service, as the extension read it through JDBC metadata (The extension).
It arrives as a log record whose attributes are spidersense.schema.table, spidersense.schema.schema, spidersense.schema.product and spidersense.schema.indexes.
The decoder turns that record into a catalog row instead of a log line.
The row is merged on its key (MERGE INTO db_table … KEY (service, schema_name, table_name)), so a table looked up again after a restart replaces its row.
The indexes text is stored as received, and the findings read it back (The schema block in Findings).
entry is decided once, when the row is written, so rows written by an older Spider Sense keep the flag they were written with.
A root INTERNAL or database span from before the entry rule narrowed still counts as a request until the retention sweeper removes it.
The endpoint name and the ignored endpoints that the span comments name are defined in Endpoint identity.
The finding id in the ack table is the one Findings defines.
The read-only user
Beside the tables, the schema creates one H2 user, spider_sense_reader, with CREATE USER IF NOT EXISTS and a constant password, and grants it SELECT on schema PUBLIC.
POST /api/sql and the CLI’s sql run their statements as this user.
Because the user has SELECT and nothing else, H2 itself refuses every write, every piece of DDL and the administrator-only functions (FILE_WRITE, CSVWRITE, FILE_READ, LINK_SCHEMA, RUNSCRIPT) to any statement that gets that far.
The grant is on the schema rather than on the tables, so it covers a table a later version adds without a schema_version bump.
Creating the user is idempotent on every open.
Only a server creates the user when it opens the database, and the CLI never does.
Through AUTO_SERVER=TRUE the CLI may have joined the database of an older Spider Sense, and adding a user to it is as much a change to somebody else’s database as recreating its tables would be.
The reader connects with the same JDBC URL minus the settings a non-administrator may not apply, keeping only AUTO_SERVER and NON_KEYWORDS.
H2 turns most of those settings into a SET at connect time.
DB_CLOSE_DELAY, which the in-memory fallback sets, is one an ordinary user is refused.
How it is written
Ingest never touches the database on the request thread.
The OTLP handler decodes the request into records and hands them to a Writer.
The Writer is a bounded queue of 10,000 batches or 50,000 records, whichever comes first, drained by one daemon thread.
A record is a span, a log record, a metric point or a catalog row.
When the queue is full, the oldest batches are dropped, and /api/status.storage.droppedBatches counts each.
The thread flushes every 200 ms, or as soon as 500 records are waiting, in one transaction per flush with JDBC batch inserts.
A flush takes at most 5,000 records off the queue, the batch that crosses that count included, and the next flush follows at once while 500 or more are waiting.
A burst the thread cannot keep up with therefore waits in the queue, where the oldest batches are dropped and counted, rather than in one transaction that builds every row of it in the heap, which in agent mode is the monitored application’s.
Every text value is cut to its column before it is bound: a service or metric name at 255 characters as it is decoded, so every table holds the same name, and a trace’s services by leaving out the names past its width.
A cut that would fall between the two halves of a surrogate pair, such as an emoji, leaves the pair out whole, since half of one has no UTF-8 form and every answer would end the value in ?.
A histogram point whose buckets do not fit their column keeps its count, sum, min and max and is stored without buckets.
A double attribute that JSON has no number for, NaN or an infinity, is stored as the string "NaN", "Infinity" or "-Infinity".
A key/value list, as an attribute or a log body, is stored as a JSON object however deep it nests, inside another list or an array included, and an attribute that is one reads back as that object’s JSON text.
An object too long for its column is cut as that text, like any other long string value.
An array is stored as a JSON array, and an array longer than every string value of a row too long for its column becomes its JSON text, which is then cut in the same way and reads back as that text.
A metric series' attributes are cut in the same way before they are hashed, so the stored text reads back, and its attr_hash is of that text, which is what an import looks the series up by.
The import hashes a document’s attributes with their keys sorted and their values as the document writes them, not read into the store’s values and written again, so a whole-valued double such as 2.0 or a nested object finds the series it came from.
When a flush still fails, each of its batches is written again in a transaction of its own, so a row the database refuses loses only the export that carried it.
A failed flush is rolled back whole before that, whatever failed, a stack overflow on an attribute nested thousands of levels deep included, so no batch is stored twice.
A batch that fails on its own as well is lost, and droppedBatches counts it.
So does every batch of a flush that an Error other than a stack overflow ends, such as an OutOfMemoryError, since nothing of that flush is stored.
A span is merged on its (trace_id, span_id), the unique key of span (MERGE INTO span … KEY (trace_id, span_id)), so an export the exporter sends again, because the answer to the first was lost to a timeout or a reset connection, stores each of its spans once.
A tingle is keyed by the span it was raised from and its kind, the unique key (trace_id, span_id, kind) of tingle, and is inserted only when no row of that key is stored (INSERT INTO tingle … SELECT … WHERE NOT EXISTS (…)).
The copy of an export therefore raises no second tingle, whether it lands in a later flush or in the same one, since the inserts of a flush run in order.
A tingle without a span has no key, and a comparison with its null ids matches no row, so it is inserted as it is.
The catalog rows of a flush are merged in the same transaction.
After each flush the writer recomputes the trace rows of the trace ids the flush touched (MERGE INTO trace … SELECT … FROM span WHERE trace_id IN (…) GROUP BY trace_id), because a trace’s spans arrive in several exports and from several services.
It locks those rows first, by merging a placeholder in trace id order, and only then reads their spans.
The spans are read 500 trace ids at a time, because H2’s cost for one IN list grows with the square of its length, and a flush after a burst or an import touches tens of thousands of traces.
Another process sharing the file may be flushing the other half of the same trace; the lock makes the second writer wait for the first one’s commit and read every span, where it would otherwise overwrite the row with its own half.
It then publishes the tingles the flush inserted, and its counts, to the SSE stream, so a tingle the copy of an export raised again is not published again either.
It also publishes a service event for each service whose row the flush inserted, after the commit, so /api/services lists the service by the time the event arrives.
The service row is merged on every flush that carries the service.
It is updated, and inserted only when the update found none, because a MERGE would overwrite first_seen.
Two processes sharing the file can both find no row for a service new to it.
The second insert then waits for the first one’s commit and fails on the primary key, and the writer updates the row that commit left, in the same transaction, so first_seen stays and no batch is lost.
When the sighting comes from a process that no start mark of that service names yet, the writer also inserts a mark named start for that service with the note pid <pid> instance <service.instance.id>, or pid <pid> when the resource has no service.instance.id (Automatic start marks).
It compares with the marks rather than with the one pid the service row stores, because two processes may export one service name at once, an application and its tests or two instances of one application, and each export of the other would otherwise insert a mark.
It reads the marks after it has written the row, in the same transaction, so that the lock on the row orders them with the sweep that forgets a service (Retention).
since=start resolves to that mark.
A JVM shutdown hook flushes what is queued, and then closes the database.
H2 closes a file database from an exit hook of its own, and the JVM runs its hooks concurrently in no set order, so H2 may close the pool’s connections under that flush.
DB_CLOSE_ON_EXIT=FALSE, which turns H2’s hook off, is refused beside AUTO_SERVER=TRUE, so the flush is written to complete whichever of the two hooks runs first.
It writes through a connection of its own outside the pool, in transactions of at most 5,000 records as a flush takes them.
Opened while H2 is closing the file, that connection waits for the close to finish and opens the file again, and a write that H2 closed under it is tried again the same way.
Once the rows are in, the flush shuts down the engine this process holds, so the file is written and closed before the JVM halts, whichever hook ran first; a file that has grown past its data is compacted on the way (The size of the file).
After the flush at exit has begun, the writer’s thread writes nothing more, since writing through the pool after H2 closed the file would open it again behind that flush.
An export that arrives after it has begun is still stored.
The OpenTelemetry SDK sends its last spans, logs and metrics from an exit hook of its own, which usually runs after the writer’s has found the queue empty, and in agent mode the embedded server’s daemon threads keep answering while the hooks run.
Such an export is written on the request thread before it is answered, the same way: through a connection of its own, after which the engine is shut down again.
The SDK’s hook waits for the answer, and the JVM waits for the hook, so a command-line job whose main returns before the exporter’s first scheduled export still has every span it made stored.
Metric points are written on the same path.
A metric row describes one service’s instrument, so two services that export one name as different instruments, one a CUMULATIVE sum and the other a DELTA sum, keep a row each, and each service’s points are read by its own row.
A point carries its resource’s process.pid and service.instance.id as attributes of its own, and a point that names either of its own keeps that one.
Each process therefore has series of its own, so two processes that export one service name at once, an application and its tests or two instances of one application, never interleave their cumulative points in one series, where one’s total differenced against the other’s would read as hundreds of collections.
Two containers of one service both run their JVM as pid 1, and the service.instance.id the OpenTelemetry SDK gives every process tells them apart; a reader takes a series' process from its service.instance.id, else from its process.pid.
A restarted process starts new series, and the old ones are swept once their points have aged out (Retention).
A sender that reports neither keeps one series per attribute set, as before.
A series row is looked up by (service, name, attr_hash) through a small in-memory cache of ids.
The sweeper and DELETE /api/data, in this process or in any other sharing the file, delete series rows behind that cache.
Each flush that carries metric points therefore checks, in one SELECT, that the cached ids of its series still have a row, and looks up or creates again a series whose row is gone.
That check locks the series rows (FOR UPDATE), and the sweep of series with no point left locks its candidates and checks again before it deletes them, so a sweep running while a flush adds the first points of a series waits for that flush and keeps the series.
An import looks its series up with the same lock, so a sweep waits for the import’s points in the same way.
The ingest cap
spidersense.ingest.max-spans-per-second caps the spans the receiver accepts in one second, so a load test does not fill the file with its spans.
It is unset by default.
The receiver counts the spans it has accepted in the current wall-clock second.
Once the count is over the cap, the first span of a trace it has not decided yet is dropped, and a span of a trace already accepted is kept.
A trace is decided once: every later span of a dropped trace is dropped too, even in a later second when the count is back under the cap, and does not count toward that second.
The decisions are kept for the last 10,000 accepted and the last 10,000 dropped trace ids, two bounded, insertion-ordered sets, so a burst of dropped traces never pushes out an accepted one.
A span of a trace that has fallen out of both sets is decided afresh.
Every stored trace therefore stays complete, and the dropped ones are whole traces, as long as a trace’s spans arrive while its decision is still kept.
Logs and metric points are never dropped.
The dropped spans are counted in /api/status.storage.droppedSpans and carried on the SSE stats event as droppedSpans.
The answer to the export they came in reports them as rejected, in a partial success (Ingest).
The UI shows the number in the sidebar foot while it grows (Layout and Controls).
How it is read
Every API answer is one or a few SQL statements over the window:
-
Lists (
trace,log,tingle, scatter points):WHERE start_ms BETWEEN ? AND ?with the filters as further predicates, thenORDER BY start_ms DESC LIMIT ?. The trace list breaks a tie inside one millisecond bytrace_id, and the log list byid, so thebeforeandbeforeIdcursor of Ordering and sorting pages without a gap. A span’ssummaryis made from its attributes andtraceholds none, so the root spans of a list that shows its traces are read after the list is cut:SELECT s.* FROM trace t JOIN span s ON s.trace_id = t.trace_id AND s.span_id = t.root_span_id WHERE t.trace_id IN (…), a seek for each row of the page rather than one for each trace of the window. -
Aggregations (services, endpoints, queries, errors):
GROUP BYoverspanwithCOUNT(*),SUM(…),MAX(…)andPERCENTILE_DISC(0.5|0.95|0.99) WITHIN GROUP (ORDER BY duration_ns), which H2 2.x supports. H2’sPERCENTILE_DISCis one of the durations, the one at positionf · (n − 1)from zero rounded half down, rather than the SQL standard’s nearest rank: the p95 of eleven calls is the tenth. The percentiles read row by row in Java (the dependencies, the map’s dependency nodes and edges,slow-external, the N+1 median) use the same definition, so two answers over the same calls give the same p95. The response-time histogram is four moreSUM(CASE WHEN NOT error AND duration_ns ⇐ :bound …)columns in the same statements, with the bounds derived fromslow.request.ms. Apdex is arithmetic over them in Java. -
Time series:
GROUP BY start_ms / :bucketfor counts, errors, histogram buckets and percentiles per bucket. -
The service map:
service → serviceedges are one self-join,span c JOIN span p ON p.trace_id = c.trace_id AND p.span_id = c.parent_span_id WHERE c.entry AND p.service <> c.service, grouped by the two services. The external targets reuse the dependency scan, minus the outbound spans whose child is aSERVERorCONSUMERspan: a call to another service, which that self-join counts, to the service’s own endpoint, which is no edge, or to an ignored endpoint, which is no request. They also leave out anhttporrpcspan that has not settled, whose end,start_ms + duration_ns / 1000000, is within 2.2 s of the server’s clock (Map), and theuser → serviceedges leave out a span with a parent that is not stored and has not settled. The dependency scan, which a service’sdependenciesread too, is every outbound span of the window, with no cap, read row by row because the target of an HTTP call, a queue or an RPC service lives in the attributes. An outbound span is one of a dependency category whose kind is neitherSERVERnorCONSUMER, chosen by kind rather than byentry, because an ignored endpoint’s span hasentryfalse and is still a request the service answered. A database’s target is itsdb_systemanddb_namespacecolumns, so a database span’s attributes are not read. A group keeps the duration of each call and the number that failed, which is what its percentiles need, so a busy window holds one duration per call in memory rather than one span row. -
A trace:
SELECT … FROM span WHERE trace_id = ? ORDER BY start_ns, span_id, plus its logs. The span id breaks a tie, so two readings of one trace list its spans alike. -
Findings (Findings):
n-plus-oneisGROUP BY trace_id, query_id HAVING COUNT(*) >= 5over the database spans of the window, attributed to the entry span by the same parent-chain walk the query callers use. Every such pair of the window is read, with no limit, so a finding’saffectedcounts the same requests itsrequestsdoes. The spans of those pairs are read by one more pass over the window’s database spans, in the order of the window’s index, and the pairs are picked out of it in Java; the occurrences are then sorted by start and span id. Read by their trace ids instead, inINlists of 500, every span of every candidate trace came back through the primary key, one page read per span, which cost ten times the pass on a worker whose traces run a hundred statements each; and left to choose, H2 read the groups' spans by the query index and tested each against the list, which cost ten times more again.dbCallsPerRequestanddbMsPerRequestof an endpoint count the database spans whose nearest entry span up the parent chain, within the trace, is one of the endpoint’s requests and of their own service, by the same walk the query callers use. The spans are read with the walk’s columns, the query id and the duration, and the walk and the sums are Java over what came back. A database span’s nearest entry span of its own service lies on its chain before the chain leaves the service, so with a service given only that service’s spans are read. Every entry span is a request of its own, so two requests of one service in one trace, a request it makes to itself or a message it consumes itself, each count only their own statements.slow-jobis the same aggregation overspan WHERE parent_span_id IS NULL AND kind = 'INTERNAL'grouped by(service, name), and a database span is a run’s when its chain reaches the run without passing an entry span. Only the endpoints and jobs that crossed the threshold are counted, since only they become findings: the ids of the traces that hold one of their requests or runs are read first, from the(endpoint_id, start_ms)index or, for a job’s runs, the service’s(service, start_ms)index, and one pass over the service’s window, in the order of its index, keeps the spans of those traces.checkandcomparecount every endpoint of the window, from one pass over its spans. The job’s aggregate carriesMAX(start_ms), and its newest run, whose attributes give the code frames, is read at that instant through the same index, with the row id breaking a tie; ordered by start with a second sort key, the read sorted every span of the service in the window. The sample of traces aslow-endpointorslow-jobsplits its time over is read by the(trace_id, span_id)index (USE INDEX (span_trace)), the ids in the statement: left to itself, H2 read every span of the service in the window for a sample of twenty traces.slow-queryandslow-jobput their threshold in the statement,HAVING PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY duration_ns) > :thresholdNs, and read every group that passes it rather than the top of a list.slow-queryaggregates, in that one pass, only the statements no ignored endpoint ran: a span that is not an ignored span and whose trace root, read through the trace summary, is not one either (Ignored endpoints). The other kinds are the endpoint, query and error aggregations above, filtered by the thresholds. A scan of the window that several rules need is made once per answer and handed to each of them. The parent-chain walk is one: the query callers, the error endpoints,n-plus-one,n-plus-one-http,slow-externalandlog-errorall read the same one. The outbound HTTP spans are the other:n-plus-one-httpandslow-externalread the same rows. Every outbound HTTP span of the window is read, with no limit, because the call’s target and name live in its attributes and a finding counts every call and every request of the window. A row is held as the names it is grouped by and its measures, without its attributes, which are read again by span id for the one call a finding takes its code from. For one service the walk reads only the traces that have a span of that service in the window, which is where every entry span its spans can reach lies: their ids come from the service’s(service, start_ms)index first, and the pass over the window keeps their rows. The query and error lists read it the same way, so a finding’s callers and endpoints are the list’s, except that aslow-queryfinding’s callers leave out the calls an ignored endpoint’s trace made, which the queries page still lists. The error list and each window ofcomparealso read the failed outbound HTTP calls of their groups in the window, by theerror_idindex, because a group’stargetis made from their attributes. -
No statement tests its rows against
IN (SELECT … FROM span …). H2 reuses such a subquery’s result only while its table has not changed, and under a running writerspanchanges at every flush, so the subquery ran again for every row of the outer statement: the findings of a 15-minute window of the shared demo, 2 seconds with the writer idle, took minutes under it, and three of four asked at once got no answer within 300 seconds. The ids the read is about come from a statement of their own, and either a pass over the window keeps their rows in Java (the walk, the database work) or the rows are read by the key inINlists of 500 (the traces of a group, the trace list by request). -
The traces that contain a group (a finding’s evidence and sample, the slowest and newest traces of the endpoint, query and error pages):
SELECT DISTINCT s.trace_id FROM span s WHERE s.<group> = ? AND s.start_ms BETWEEN ? AND ?, thenSELECT * FROM trace t WHERE t.trace_id IN (…) AND t.start_ms ⇐ ?over those ids, newest first in Java, the trace id breaking a tie. The matching span starts in the window, so the first statement is a range of the group’s(…, start_ms)index. It reads the group’s spans that start in the window, never every span the store keeps of the group. The trace list by request reads the same way, with the entry span’s filters on the first statement and the page’s cursor and limit on each list of ids. The slowest are ordered by the group’s own spans rather than by the trace:SELECT t.* FROM (SELECT s.trace_id, MAX(s.duration_ns) AS match_ns FROM span s WHERE s.<group> = ? AND s.start_ms BETWEEN ? AND ? GROUP BY s.trace_id) m JOIN trace t ON t.trace_id = m.trace_id WHERE t.start_ms BETWEEN ? AND ? ORDER BY m.match_ns DESC, t.trace_id, the same range of the same index, then each trace row by its key. Aslow-externalgroup has no column to match, so its traces are those of its slowest calls, in the calls' order, read by the trace’s key. -
The schema block of a query group:
SELECT schema_name, table_name, indexes FROM db_table WHERE service = ?, read once per answer and matched against each statement’s tables in Java, case-insensitively. -
A time selector that names a mark:
SELECT at_ms FROM mark WHERE name = ? [AND service = ?] ORDER BY at_ms DESC LIMIT 1. -
The acknowledgements and resolutions of a page of findings:
SELECT * FROM ack WHERE finding_id IN (…), one statement for the page. A finding’s state reads the two neweststartmarks of its service withSELECT at_ms FROM mark WHERE name = 'start' AND service = ? AND at_ms ⇐ ? ORDER BY at_ms DESC LIMIT 2. It runs the rules of the page’s kinds again over the run between those two marks, for ids alone, and keeps what they found in memory for as long as the server runs. -
Free-text search (
q):LOWER(name) LIKE ? ESCAPE '\' OR LOWER(attributes) LIKE ? ESCAPE '\'over the spans of the window, andLOWER(body) LIKE ? ESCAPE '\' OR LOWER(logger) LIKE ? ESCAPE '\' OR LOWER(attributes) LIKE ? ESCAPE '\'over its logs. The pattern is the lower-cased text with its%,_and\escaped, between two%. A text that holds a quote, a backslash or a control character is matched against theattributesa second time, as the JSON writer escapes it, with anOR. Aqof 8 to 32 hex digits is compared witht.trace_id LIKE ?too, on thetracerows the window reads anyway and before their spans are looked at, so it adds no scan. No index serves these patterns, so the search reads every span and log record of the window, of a store that keeps at most 1,000,000 spans by default (Retention). -
JVM and metrics:
metric_pointjoined withmetric_series, resampled in Java where the API asks for it.
Connections come from H2’s JdbcConnectionPool, at most 8.
The writer borrows one of them for each flush and returns it after, so a flush can wait for a connection behind API reads; only the flush at exit opens a connection outside the pool.
Every read runs under a query timeout of 30 seconds, set on the statement before it runs and taken off before the connection goes back to the pool, since H2 keeps the timeout on the session and the writer’s and the sweeper’s statements have no bound.
A read H2 stops at it is answered 503 with The read took longer than 30 seconds and was stopped. Try again, or ask over a shorter range., which the UI shows with its Try again (Live refresh), and the server logs the statement.
A request whose browser has gone, because the page was reloaded or left, still runs on the server, since nothing tells a handler that its client dropped the connection, and the 30-second timeout ends it.
The process that opens the file sizes H2’s page cache to its own heap: a sixteenth of the maximum heap, never below H2’s 16 MiB and never above 256 MiB.
Every scan of a window reads the window’s pages, and a cache smaller than them reads each page from the file again on every scan; findings makes several.
The cache lives in the heap of the process that owns the engine, which is the monitored application in embedded mode, so it is a share of that heap rather than a fixed size.
A process that joined another one’s engine through AUTO_SERVER leaves the size alone, and so does a JDBC URL that names CACHE_SIZE itself.
H2 keeps the size in the file, so it is set only once the schema version is accepted, and a CLI that refuses a file of another version leaves the size that version set.
Reads are plain JDBC through one small helper, Sql.query(sql, params, rowMapper).
There is no ORM and no spring-jdbc, so the nested server jar carries only H2 beyond what it already has.
The CLI reads the same way when no server answers.
It opens the same URL, so it joins a running auto-server, or opens the file itself for the length of the command when none is running.
It runs the same Queries without a writer or a sweeper.
Its open refuses a missing file, and refuses another schema_version instead of dropping the tables, since the database it joined may belong to an older server that is still writing to it.
The commands that write are mark, ack and unack, resolve and unresolve, and import, and they write through the same connection with plain statements.
import alone inserts spans, and it does so through the Writer’s own insert and `trace merge, so an imported trace is stored exactly as a received one (Export and import).
An import is one transaction, and a written row stays locked until it commits.
It therefore writes the rows a running writer also writes, the metric, metric_series and service rows, last, just before the commit, and updates a service row only when it widens the row’s first or last sighting.
A running application’s flushes then never wait out H2’s lock timeout behind an import of its own earlier session.
Retention
spidersense.retention.hours defaults to 24.
A daemon sweeper runs a minute after start and every five minutes after that, deleting with DELETE FROM span|trace|log|metric_point|tingle|mark|db_table WHERE <time> < now - retention, then metric_series rows with no points.
A trace goes whole, by its start: the spans of a trace that started before the cutoff go with it, the ones that started after the cutoff included (DELETE FROM span WHERE trace_id IN (SELECT trace_id FROM trace WHERE start_ms < ? AND end_ms >= ?) before the span delete), so no span is left without its trace row.
The sweep runs those deletes in one transaction, and while the writer of its process flushes nothing, so a flush that stores a late span of such a trace, a long job’s child or a message consumed a day after it was produced, commits before the deletes or after them, never between the span deletes and the trace delete.
A catalog row goes by its seen_ms, because a catalog older than the retention describes a run no window can show any more.
A service with a span inside the retention keeps its catalog rows however old they are (DELETE FROM db_table d WHERE d.seen_ms < ? AND NOT EXISTS (SELECT 1 FROM span s WHERE s.service = d.service AND s.start_ms >= ?)).
The extension reads a table once per process and DDL, so an application that has run for longer than the retention would otherwise lose the schema block of the statements it still runs.
Each service’s newest start mark is spared: the writer inserts one only for a process id new to the service, so a service that has run for longer than the retention would otherwise have none, and since=start would fail for it.
The older of two processes sharing a service name loses its own start mark that way, and the writer, which remembers each process whose mark it has found stored, does not mark it again at its next export, which would move since=start to that moment.
That memory ends with the writer’s process.
After a restart of it, such as of the standalone Spider Sense both processes send to, the process of the service’s last export is still not new, since the service row keeps its process id and the flush that wrote the row marked it; another process sharing the name whose mark is gone is marked again at its next export.
The sweep forgets a service last, so a test, a renamed application or a mistaken otel.service.name does not stay in every list for good.
A service goes once it has no span inside the retention and its last_seen is older than twice the retention, and each of its start marks goes with the row.
Every export moves last_seen, of spans, of logs and of metrics alike, so a service that still runs is never forgotten: an application under the Java agent exports its JVM metrics however quiet it is.
A flush of another process may be committing the service’s first sighting in that long, so the sweep locks the rows it found and reads them again before it deletes, as it does for metric_series.
A flush that comes after that lock waits at the row and finds it gone, and the marks it then reads hold none of the service’s, so the row it writes never comes back without a start mark.
A service back after that is a new one: its first export inserts its row and a start mark, whatever the writer remembered of its process, and the row names a first sighting of that moment.
DELETE /api/data runs the same deletes without the time bound, and empties ack, acknowledgements and resolutions alike.
It runs them in one transaction, after the writer of its process has flushed what was queued and while that writer flushes nothing else.
A flush between two of the deletes would otherwise keep its spans and lose its trace rows.
A writer of another process sharing the file may still commit while the transaction runs, so the trace delete keeps a trace whose spans are still there (DELETE FROM trace WHERE trace_id NOT IN (SELECT trace_id FROM span)).
At 24 hours of a few requests per second the file stays in the low hundreds of megabytes.
The size of the file
H2 appends a chunk to the file for every commit, which is every flush: the new rows, and a copy of every index page that leads to them.
A chunk’s space is reused once no page in it is current any more and no statement is still reading the version it belongs to, 45 seconds after that (H2’s RETENTION_TIME).
The file therefore grows while a statement runs for seconds: every chunk written meanwhile is kept for it.
Several agents asking for findings in a loop, each answer taking twenty seconds, grew a store of 100 MiB of data to 3.2 GB in forty minutes, and the same loop over a fresh store grew it 2.5 MB a second once a statement held a version for minutes.
Every statement of Spider Sense’s own is bounded by its window and by the 30-second read timeout (How it is read), and the findings of a 15-minute window of the shared demo answer in a few seconds; sql is bounded by nothing but what it asks and its own 10 seconds.
The file never shrinks while it is open, and an H2 close compacts for MAX_COMPACT_TIME, 200 ms, which moved nothing in that file.
The flush at exit (How it is written) therefore shuts the engine down itself when the file is 256 MiB or more, whether or not anything was queued, and reads H2’s fill rates first: when less than half of the file is live data, the chunks' share of the file times the live share of the chunks, it closes with SHUTDOWN COMPACT, which rewrites the file to its live data.
The 3.2 GB file became 106 MiB in under two seconds.
A smaller file, or one that is mostly live, closes as before, since the rewrite costs time in proportion to the live data and would save little.
A store that has grown therefore shrinks at the next exit of the application or standalone server that writes to it; deleting the file is the other way, and it deletes the marks, acknowledgements and resolutions with the telemetry.
Retention is by time, with one row cap as a guard: spidersense.retention.spans, default 1,000,000, and 0 for none.
After the time sweep, while SELECT COUNT(*) FROM span is above the cap, the sweeper takes the oldest hour of data (MIN(start_ms) of span, plus one hour) and deletes every row of span, trace, log, metric_point and tingle before that instant.
When fewer spans than that hour holds are over the cap, the instant is instead one millisecond after the start of the span whose deletion brings the count to the cap, so a load test that crosses the cap inside one hour loses its oldest spans and not the hour.
A trace that started before that instant goes whole, as it does on the time sweep, and each hour’s deletes are one transaction run while the writer flushes nothing, as the time sweep’s are.
Marks and catalog rows are included only when they are older than the retention, as before.
The sweeper proceeds one hour at a time and stops once the count is at the cap, so a count a little over the cap deletes only the oldest traces that hold the excess.
The cap is by rows rather than by file size because an H2 file does not shrink when rows go.
A size read after a delete would say the same number and ask for the next hour, until nothing was left.
/api/status.retention reports both limits, as { "hours": 24, "spans": 1000000 }.
Failure
If the database cannot be opened, because the file is corrupt or a permission is missing, the server logs one line and falls back to jdbc:h2:mem:spidersense-<pid> so the UI still works for the session.
It marks /api/status.storage.fallback as true with the reason, which is H2’s own message.
Only a race with another process opening the same file at the same moment is retried, for up to 15 seconds; any other failure falls back at once, since the open runs on the monitored application’s premain thread.
The race includes the two processes creating the schema at once, whose loser H2 tells that an object already exists, as a general error 50000, or now and then that a catalog row is not in its primary index, 90143.
Nothing in this path throws out of premain.