Tutorials/Observability

Seeing what Storm does

Storm logs every statement where it executes. A log level shows the SQL, TRACE adds the values, a summary per request shows what the call cost, and the slow statement log names the execution that took too long.

Series · The Storm way4 min readKotlin · Spring Boot

01The task

A query misbehaves in one corner of the application. You want the exact SQL that corner runs, a way to reproduce it in a database client, and a way to confirm the fix, without logging every statement the system runs.

02Statements, scoped by type

Storm logs statements under the st.orm.sql logger. The log level is the only switch: nothing to annotate, and the execution path stays the one that runs in production. Each entity and projection also logs under a child logger, st.orm.sql.<Type>, so you can raise the level for the type you are investigating:

application.yml YAML
1
2
3
4
5
# application.yml: the log level is the only switch
logging:
  level:
    st.orm.sql.City: DEBUG   # one type, not every statement
↳ generated sql
-- SQL (SELECT City):
SELECT c.id, c.name
FROM city c
WHERE c.id = ?

Each statement logs once, headed by the operation and the type. A statement that resolves a reference reads SQL (SELECT City, fetch), which tells it apart from a primary key lookup your code asked for.

03SQL you can paste

At DEBUG the log shows the statement as sent, with placeholders. At TRACE the bound values are rendered into it, so you can paste it into a client and run it, or check its plan:

application.yml YAML
1
2
3
4
5
# TRACE renders the bound values into the statement
logging:
  level:
    st.orm.sql.City: TRACE
↳ generated sql
-- SQL (SELECT City):
SELECT c.id, c.name
FROM city c
WHERE c.id = 2

Values are database values: personal data, tokens, whatever your entities carry. Every Storm SQL logger renders them only at TRACE. Leave DEBUG available for diagnosis in production and keep TRACE out of it.

04What one call cost

A statement log tells you what ran. To decide what to change you need the total per unit of work. In Spring Boot, one property turns every way work enters the application into a summary: HTTP requests, @Scheduled methods, and Kafka, RabbitMQ, JMS and SQS listener methods. The summary has one row per distinct statement, sorted by total time, so a statement that ran eight times cheaply ranks above one slow statement when it cost more in total:

application.yml YAML
1
2
3
4
5
6
7
8
9
10
# One summary per unit of work: HTTP requests, @Scheduled methods,
# and Kafka, RabbitMQ, JMS and SQS listener methods
storm:
  sql-log:
    performance:
      enabled: true
      threshold:
        statements: 50    # report only the calls that exceed
        duration: 500ms  # a threshold, at WARN
console st.orm.sql.perf
1
2
3
4
SQL (GET /owners): 19 statements, 8 fetches, 142 ms in database, 678 ms total
    96 ms  6408 rows  7x  Visit         SELECT v.id, v.visit_date FROM visit v WHERE v.pet_id = ?
    28 ms     8 rows  8x  City   fetch  SELECT c.id, c.name FROM city c WHERE c.id = ?
    18 ms   112 rows  4x  Pet           SELECT p.id, p.name FROM pet p WHERE p.owner_id = ?

Each row shows the time, rows, executions and type, and fetch marks the statements that resolved references. Comparing 142 ms in database with 678 ms total tells you most of the call was spent outside the database. The summary carries no parameter values, so it is safe to log in production, and with thresholds it reports only the calls that exceed one. To measure a smaller block than a request, wrap it in sqlLog("importOwners") { }. In Ktor, sqlLogPerformance = true on the plugin does the same per request.

05The one slow execution

A summary judges a whole call. The slow statement log reports single executions: one line under st.orm.sql.slow at WARN for every execution whose database time exceeds the threshold, in a request or in background work, with the call site that ran it:

application.yml YAML
1
2
3
4
5
6
# One line per execution slower than the threshold, wherever it runs
storm:
  sql-log:
    slow:
      threshold: 200ms
console st.orm.sql.slow
1
2
3
4
5
SQL slow (SELECT Owner): 1840 ms in database, 3 rows, OwnerService.kt:42
    SELECT o.id, o.first_name, o.last_name, ...
    FROM owner o
    WHERE o.last_name = ?
    shape: 9b34305d1a7c4e02 (typically 6.0 ms, 306x)  parameters: 1

The shape line answers the first question a slow statement raises. typically 6.0 ms, 306x says this statement is normally fast and this execution was not, so look at the values or a changed plan. A shape that reads typically 310 ms with no multiplier is always slow, so look at the query and its indexes. The same log reports a transaction that held its connection idle, by default once the idle time passes one second. Where Storm can tell from the statement what to change, an advice: line follows; Statement advice walks through it.

06Beyond logging

In tests, SqlCapture turns statement counts and origins into assertions, so what you found while debugging becomes a regression test. In production, the storm-micrometer module reports every query and transaction as a Micrometer observation, and the storm.origin tag separates statements your code asked for from statements that resolved a reference, so you can chart and alert on the latter. JMX metrics cover the template cache, the entity cache and dirty checking; see Metrics.

07Keep going

The reference documentation covers the mechanics in depth: