jfr-sql
/jfr-sql
Podpowiada schemat bazy JFR z profilu Jeffrey, nazwy pól zdarzeń i pułapki, które zwracają błędny wynik bez komunikatu.
- Autor
- petrbouda ↗
- Repozytorium
- jeffrey ↗
- Licencja
- Apache-2.0
- Ostatnia zmiana repozytorium
- 12.11.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 1
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 12.11.2026, bez odwołań do wycofanych modeli i API.
Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.
Co robi i kiedy
Podpowiada schemat bazy JFR z profilu Jeffrey, nazwy pól zdarzeń i pułapki, które zwracają błędny wynik bez komunikatu.
Oryginalny opis (EN)
Load before the first jfr_executeQuery or jfr_queryEvents call, whichever skill is driving the analysis: the schema of a Jeffrey profile's JFR database (events, event_types, threads, stacktraces, frames), the field names per event type, and the traps that return a wrong answer without an error — events_raw's truncated fields, ->> binding tighter than casts, root-first stacks, the row cap. Use for what no flamegraph, trace, heap or jvm dashboard tool answers: how many distinct values a field takes, a distribution over time, a correlation between event types.
Jak działa
- Query events, never eventsraw
- Do not guess column names
- The tables
- Idioms that matter
- Event types worth knowing
- GC and JIT: try the jvm family first
- JIT compilation and deoptimisation
- Link what you name
- Results are capped
- One statement, and no way out of the database
Podgląd SKILL.md
---
name: jfr-sql
description: "Load before the first jfr_executeQuery or jfr_queryEvents call, whichever skill is driving the analysis: the schema of a Jeffrey profile's JFR database (events, event_types, threads, stacktraces, frames), the field names per event type, and the traps that return a wrong answer without an error — events_raw's truncated fields, ->> binding tighter than casts, root-first stacks, the row cap. Use for what no flamegraph, trace, heap or jvm dashboard tool answers: how many distinct values a field takes, a distribution over time, a correlation between event types."
allowed-tools: mcp__plugin_microscope_jeffrey__* mcp__jeffrey__*
---
# Querying a profile's JFR database
Each profile is one DuckDB database. Reach it with `jfr_executeQuery` (SELECT/WITH only, row-capped)
or `jfr_queryEvents` for the common "give me events of this type" case.
Tool names below omit the prefix your client puts in front of them — `mcp__plugin_microscope_jeffrey__` in Claude Code with the `microscope` plugin, `mcp__jeffrey__` in Codex or wherever the server is registered by hand as `jeffrey`, `mcp_jeffrey_` in Gemini CLI; the rest of the name is exact camelCase.
Prefer a purpose-built tool where one exists — `flamegraph_export` beats reconstructing a call tree
by hand, and the `heap_` family beats querying the heap index. SQL is for the questions they do not
cover: distributions over time, correlations between event types, cardinality of a field.
## Query `events`, never `events_raw`
`jfr_listTables` shows both. `events` is a view over `events_raw` that splices back the one large
string field the parser pools out of each row. Querying `events_raw` silently returns truncated JSON
in `fields`, with no error to warn you.
## Do not guess column names
Call `jfr_describeTable('events')` first. In particular the duration column is `duration` — not
`duration_ns`, not `duration_ms`.
## The tables
- **`events`** — one row per event. `event_type`, `start_timestamp` (TIMESTAMPTZ),
`start_timestamp_from_beginning` (BIGINT millis since recording start — sargable, prefer it for
relative time filters), `duration`, `samples`, `weight`, `weight_entity`, `stacktrace_hash`,
`thread_hash`, `fields` (JSON).
- **`event_types`** — `name`, `label`, `description`, `categories`, `subtype`,
`has_stacktrace`, `extras`, plus two columns worth knowing about because `jfr_listEventTypes`
does not return them:
- **`columns`** — a JSON array of `{field, header, type, description}`: the *declared fields* of
that event type. `SELECT columns FROM event_types WHERE name = 'jdk.Deoptimization'` is the
answer to "what is in `fields` for this type", instead of guessing at key names.
- **`settings`** — how the recording was configured for that type. This is what settles "was
this event switched off, or did it genuinely not happen": a threshold-gated type
(`jdk.Compilation`, the I/O and monitor events) with no rows may simply have had nothing cross
its threshold.
- **`threads`** — `thread_hash` (PK), `name`, `os_id` (null for virtual threads), `java_id`, `is_virtual`.
- **`stacktraces`** — `stacktrace_hash` (PK), `type_id`, `frame_hashes` (BIGINT array, ordered
**root-first** — see the stacks idiom below), `tag_ids` (INTEGER array; `0` marks an idle stack,
`1` an unsafe allocation — the tags the flamegraph's `excludeIdle` option filters on, matched with
`list_has_any(tag_ids, [0])`).
- **`frames`** — `frame_hash` (PK), `class_name`, `method_name`, `frame_type`, `line_number`,
`bytecode_index`, `hidden_class_id`.
Derived from the events when the profile carries Jeffrey Tracing, and empty otherwise:
- **`traces`** — one row per trace: `trace_id` (PK), `root_name`, `root_kind`, `root_event_type`
(together the operation), `start_timestamp`, `start_timestamp_from_beginning`, `duration`,
`span_count`, `error_count`.
- **`trace_spans`** — one row per span: `trace_id`, `span_id`, `parent_span_id`, `name`, `kind`,
`status`, `error_type`, `start_timestamp_from_beginning`, `duration`, `self_duration`,
`thread_hash`, `event_type`, `attributes` (JSON text).
- **`trace_notifications`** — what the application said inside a trace: `trace_id`, `span_id`
(NULL when no span was open), `notification_id`, `start_timestamp_from_beginning`, `type`,
`severity`, `category`, `source`, `attributes`, and `message_ref` into
**`trace_notification_messages`** (`message_id`, `message_text`), which holds each distinct
sentence once. Prefer `traces_notifications` for the grouped reading; SQL is for a question it
does not shape, such as notifications per minute.
## Idioms that matter
- **Durations are nanoseconds.** `duration / 1000000` → milliseconds.
- **Event-specific data is JSON** in `fields`: `(fields->>'key')`, or `json_extract_string(fields, '$.key')`.
Cast before comparing numerically: `CAST(fields->>'bytesRead' AS BIGINT) > 100`.
- **Parenthesise every `->>` outside a function call.** DuckDB binds `::`, arithmetic and `||`
more tightly than `->>`, so `fields->>'size'::BIGINT` casts the key `'size'` (and fails with
"Could not convert string 'size' to INT64") and `fields->>'end' - fields->>'start'` subtracts a
document from a string. `->` is looser still, below comparisons, `AND` and `OR` too:
`fields->'id' = 1` reads as `fields->('id' = 1)`. Write `(fields->>'size')::BIGINT` and
`(fields->>'end') - (fields->>'start')`; inside `CAST(…)`, `SUM(…)` or any other call it is
already safe.
- **Timestamps**: `epoch_ms(start_timestamp)` for epoch millis.
- **Stacks**: `UNNEST(frame_hashes)` and join to `frames`. The array is stored **root-first**: the
first element is the thread entry point (`Thread.run`) and the **last** is the leaf that was
actually executing. Taking the first element as "the hot method" is the mistake to avoid — it
returns the entry point on every stack in the recording. Keep the position with
`generate_subscripts(frame_hashes, 1)` beside the `UNNEST`, and order by it descending for the
topmost-first reading a stack trace normally has:
```sql
WITH positioned AS (
SELECT UNNEST(frame_hashes) AS frame_hash,
generate_subscripts(frame_hashes, 1) AS depth
FROM stacktraces WHERE stacktrace_hash = ?
)
SELECT f.class_name, f.method_name FROM positioned p
JOIN frames f USING (frame_hash) ORDER BY p.depth DESC
```
For the leaf alone, `frame_hashes[-1]` — DuckDB lists are 1-based, and negative indices count
from the end.
- **Hidden classes**: `class_name` never carries the per-run address of a JEP 371 hidden class
(lambda proxy, method-handle form, indified string concat). The address lives in `hidden_class_id`,
so `hidden_class_id IS NOT NULL` selects hidden frames — a null check, not a `LIKE`.
- **GROUP BY**: every non-aggregated column in the SELECT must appear in it.
`SELECT event_type, COUNT(*) FROM events` is invalid; add `GROUP BY event_type`.
## Event types worth knowing
CPU `jdk.ExecutionSample`, `jdk.NativeMethodSample` · allocation `jdk.ObjectAllocationSample`,
`jdk.ObjectAllocationInNewTLAB`, `jdk.ObjectAllocationOutsideTLAB` · GC `jdk.GarbageCollection`,
`jdk.GCPhasePause`, `jdk.GCHeapSummary`, `jdk.YoungGarbageCollection`, `jdk.OldGarbageCollection`,
`jdk.G1GarbageCollection` · threading `jdk.ThreadPark`, `jdk.JavaMonitorEnter`,
`jdk.JavaMonitorWait` · I/O `jdk.FileRead`, `jdk.FileWrite`, `jdk.SocketRead`, `jdk.SocketWrite` ·
JIT `jdk.Compilation`, `jdk.Deoptimization`, `jdk.CompilerStatistics`, `jdk.CodeCacheStatistics` ·
tracing `jeffrey.Notification` (the application's own reports; its `fields` carry `traceId`,
`type`, `severity`, `message`), plus the span event types listed by `jfr_listEventTypes`.
`jfr_listEventTypes` gives the ones this profile actually recorded, with counts — use it rather than
assuming.
## GC and JIT: try the `jvm_` family first
`jvm_gc`, `jvm_safepoints` and `jvm_jit` render the dashboards the Jeffrey UI renders, from the
same tested builders, in one call. Everything below is the escape hatch for what they do not shape
— a distribution over time, a correlation with another event type, one field they do not carry.
Reproducing a dashboard here is slower and easier to get wrong.
`jdk.GarbageCollection` is one row per collection, carrying `gcId`, `name` (the collector, `…Full`
for a full GC), `cause`, `sumOfPauses` and `longestPause`.
**Rank by `sumOfPauses`, never by `duration`.** For ZGC, Shenandoah and G1's concurrent cycles the
event's `duration` spans phases the application ran straight through, so ordering by it reports
pauses that never happened. `sumOfPauses` and `longestPause` are the stop-the-world figures; use
`duration` only where they are absent.
```sql
SELECT fields->>'name' AS collector,
fields->>'cause' AS cause,
COUNT(*) AS collections,
SUM(CAST(fields->>'sumOfPauses' AS BIGINT)) / 1000000.0 AS total_pause_ms,
MAX(CAST(fields->>'longestPause' AS BIGINT)) / 1000000.0 AS worst_pause_ms
FROM events
WHERE event_type = 'jdk.GarbageCollection'
GROUP BY collector, cause
ORDER BY total_pause_ms DESC
```
Heap before and after each collection — `jdk.GCHeapSummary` is **two** rows per `gcId`, told apart
by `when`, so pivot rather than joining the table to itself:
```sql
WITH summaries AS (
SELECT CAST(fields->>'gcId' AS BIGINT) AS gc_id,
fields->>'when' AS phase,
CAST(fields->>'heapUsed' AS BIGINT) AS heap_used
FROM events WHERE event_type = 'jdk.GCHeapSummary'
)
SELECT gc_id,
MAX(heap_used) FILTER (WHERE phase = 'Before GC') AS before_bytes,
MAX(heap_used) FILTER (WHERE phase = 'After GC') AS after_bytes
FROM summaries
GROUP BY gc_id
ORDER BY gc_id
```
A live set that climbs across `after_bytes` is a retention problem — a heap dump answers it, this
recording does not.
Where a pause went inside one collection: `jdk.GCPhasePause` (children in `jdk.GCPhasePauseLevel1`
through `Level4`), grouped by `fields->>'name'` and filtered on `(fields->>'gcId')`.
`jdk.GCPhaseConcurrent` holds the phases outside the pause.
One row each, read before proposing a flag: `jdk.GCConfiguration`, `jdk.GCHeapConfiguration`,
`jdk.GCSurvivorConfiguration`, `jdk.GCTLABConfiguration`. Collector-specific pressure lives in
`jdk.ZAllocationStall` (threads that waited for memory — ZGC's latency symptom, not a pause),
`jdk.EvacuationFailed` and `jdk.TenuringDistribution`.
Nothing here names the code that produced the garbage. Once the pause budget shows GC matters, the
allocation flamegraph does — `flamegraph_export` on `jdk.ObjectAllocationSample` with
`useWeight: true`.
## JIT compilation and deoptimisation
`jvm_jit` already carries all of this aggregated. These are for the follow-up questions — a single
method's compilation history, deoptimisations bucketed by time, a join against another event type.
`jdk.Compilation` is one row per compilation: `method`, `compileLevel`, `compileId`, `codeSize`,
`isOsr`, `succeded` (JFR's own spelling — false means the method fell back to the interpreter).
Method-typed fields are flattened by the parser to `fully.qualified.Type#name`, in `jdk.Compilation`
and `jdk.Deoptimization` alike, so grouping by `fields->>'method'` gives a string that can be
grepped for in a checkout — no struct to unpack.
The event fires only above the recording's threshold setting, so no rows means nothing compiled
*slowly*; `jdk.CompilerStatistics` has the totals regardless (`compileCount`, `bailoutCount`,
`invalidatedCount`, `osrCompileCount`, `totalTimeSpent`).
```sql
SELECT fields->>'method' AS method,
CAST(fields->>'compileLevel' AS INTEGER) AS level,
duration / 1000000.0 AS compile_ms,
CAST(fields->>'codeSize' AS BIGINT) AS code_size,
fields->>'succeded' AS succeeded
FROM events
WHERE event_type = 'jdk.Compilation'
ORDER BY duration DESC
LIMIT 25
```
Deoptimisation is where the JIT turns into a latency problem. Group by method **and** reason: one
method deoptimised repeatedly ran interpreted for part of the recording, and the reason
(`unstable_if`, `class_check`, `null_check`) says what to look for in the source.
```sql
SELECT fields->>'method' AS method,
fields->>'reason' AS reason,
fields->>'action' AS action,
COUNT(*) AS deopts
FROM events
WHERE event_type = 'jdk.Deoptimization'
GROUP BY method, reason, action
ORDER BY deopts DESC
LIMIT 25
```
`jdk.CodeCacheStatistics` and `jdk.CodeCacheFull` answer whether compilation stopped altogether —
a full code cache leaves the application at interpreted speed for the rest of the run — and
`jdk.CompilerQueueUtilization` shows the queues backing up, which reads as a slow warm-up rather
than a steady-state cost.
## Link what you name
Whenever a reply names something Microscope can open — the profile, a flamegraph, an operation, a
trace, an endpoint, a statement group, a GC, JIT or thread page, a heap class or object, a
differential, a catalogue row, a ledger entry — make the name a Markdown link marked with `↗`:
`[trace 7e34c5994dc96208 ↗](<its uiLink>)`, and in a table a column of `[↗ open](<its uiLink>)`.
Every link carries the mark — every profile named too, the analysed one and its baseline alike, and
a page named in passing ("the exceptions page"), a profile named under **Not assessed** or **Open
leads**, and a figure's source given as the page it was read off (`[slowest traces ↗](<its uiLink>)`) — the
links most often left bare: `[checkout candidate ↗](<its uiLink>)` — and never the bare URL. The `↗` is what makes it a link the
user sees: a terminal that renders links shows the text alone, clickable, with the URL behind it,
and an unmarked name reads as plain words. That holds in every reply, not only in a written report:
a finding, a ranked list, a follow-up offer, a sentence in passing. **Before you send a reply, check
it: every `](` closes a link text that holds `↗`.**
- Take the link from the answer the thing came from. A row with its own `uiLink` — a trace, an
operation, an exemplar — links with that one; otherwise the answer's `uiLink` is the page that
shows the row. Quote the `uiLinkNote` beside it when there is one.
- No link in hand for one of the profile's pages (GC, JIT, threads, the heap views, …) →
`profiles_viewLink`, one cheap call; the profile itself → `profiles_link`.
- Never build, edit or guess a URL, and never fetch one: the link is for the user, and one typed by
hand opens the wrong page or none.
- An area not yet analysed is not a thing and needs no link, and a result no page shows — a raw SQL
query — has none; say so rather than inventing one.
- A tool's name is never link text. The link is the page, by its name — `[GC page ↗](<its uiLink>)` — and
a tool stays in backticks beside it, plain. A tool you did not call — a follow-up on offer, a check under
**Not assessed** — is named that way and not linked at all: `` `jvm_safepoints` `` alone, never
`` [`jvm_safepoints`](<a link>) `` and never the profile's own link in place of a page you have no link for.
## Results are capped
`jfr_executeQuery` caps rows (`rowCap`, 1,000) and total characters, and says which in `truncation`:
`COMPLETE`, `ROW_LIMIT` or `OUTPUT_SIZE_LIMIT`. Its answer is JSON — `columns`, then `rows` with every
cell as a string and SQL `NULL` as `null`, and `returned`. `jfr_queryEvents` answers the same shape
with a `status` of `OK`, `NO_EVENTS` or `NO_MATCH`; an event type the profile never recorded is an
error. Aggregate in SQL rather than pulling rows back to count them.
## One statement, and no way out of the database
The `jfr_` family has no write tool: every call reads, and a profile's data is never rewritten
through MCP. Two rules follow from how the read tools are sandboxed, and both fail loudly rather
than silently:
- **One statement per call.** Anything after a semicolon is refused before the query runs. Send the
`SELECT` on its own.
- **The engine has no filesystem.** The profile database is opened with DuckDB's external file
access off, so `read_text`, `read_csv`, `read_parquet`, `glob` and `ATTACH` all fail. Nothing is
lost for analysis — every table you need is in this database — but a query that reaches for one
comes back with a permission error rather than a confusing empty result.
## Before writing a query against an unfamiliar event type
`jfr_describeTable` can only tell you that `events` has a JSON `fields` column. What is *inside* it
differs per event type, and the names are JFR's rather than Jeffrey's — `sumOfPauses` not `duration`,
`allocationSize` not `size`. **`jfr_describeEventType`** lists them with their labels and types, and
also says whether the type carries a stack trace, which is what decides whether it can be drawn as a
flamegraph at all.
Call it once for an event type you have not queried before. Guessing a JFR field name is how a query
comes back empty against a recording that holds exactly the data you asked for.
Wersje
| Repozytorium | Wynik | Licencja |
|---|---|---|
| Ta strona · petrbouda/jeffrey | 100 | Apache-2.0 |
| petrbouda/jeffrey | 100 | Apache-2.0 |
Migawka bazy z 13.11.2026 · wersja paczki c0fb89c6f124 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 100heap-sql
Podaje schemat indeksu zrzutu sterty Jeffrey i pułapki zapytań SQL, które zwracają błędny wynik bez komunikatu o błędzie. - 100analyze-jfr
Analizuje profil JVM z Jeffrey Microscope: procesor, alokacje, blokady, GC i konfigurację, by wyjaśnić, dlaczego aplikacja działa wolno. - 90jvm-profiling
Profiluje usługi JVM przez async-profiler i JFR, rozdzielając dowody dotyczące CPU, alokacji i blokad, bezpiecznie na produkcji.