josefbacik/systing/systing-analyze

Analyzing systing traces

Systing stores traces in DuckDB. The systing-analyze MCP server exposes structured tools to query them. This skill tells you which tool to reach for and how the data is laid out.

Recommended workflow

1. `trace_info` — Always start here. Pass the path to the .duckdb file. Returns trace IDs, time range, per-trace system/platform info (kernel, arch, hypervisor, cpufreq driver, sampling event/period), non-empty tables with row counts, and the top 25 processes by thread count. This also caches the DB so later calls can omit path.
2. `list_tables` / `describe_table` — Discover schema for ad-hoc queries.
3. High-level tools for common questions — see below.
4. `query` — For anything the high-level tools don't cover, write SQL. Results cap at 10k rows (truncated: true when hit); use LIMIT/OFFSET for more. The DB is opened read-only.

Tool cheatsheet

There is also a CLI with the same analyses: systing-analyze query|stacktrace flamegraph|sched stats|sched cpu-stats|sched aggregate|network connections|network interfaces|network socket-pairs (and systing-analyze mcp to start this server).

A server started with systing-analyze mcp -d <trace.duckdb> --restrict-to-database serves that one database: omit path on every call (that database's own path is accepted too), and any other path is refused without being opened.

Key schema for query

Timestamps

All ts columns are nanoseconds from an arbitrary epoch. Convert durations: dur / 1e6 → ms, dur / 1e9 → sec.

Thread / process identity

• utid / upid are internal IDs (dense, unique within DB).
• Join to thread (utid → tid, name, upid, py_name) and process (upid → pid, name) for the Linux IDs. thread.name is the kernel's name for the thread (comm); thread.py_name (from schema 24) is the name its Python process gave it (threading.Thread(name=...), e.g. Thread-1 (worker)), filled by the task-stacks recorder for Python 3.13+ processes with --task-stacks-frames python|all and NULL for every other thread.

Stack traces

Two representations exist:

Interned (the normal one) — stack_sample → stack → frame:

• stack_sample(ts, utid, cpu, stack_id, stack_event_type, task_context_id, go_goid, go_labels_id). stack_event_type: 0 = uninterruptible sleep, 1 = CPU, 2 = interruptible sleep. task_context_id (from schema 25) is the id of what the sampled thread's program said it was working on, see below; NULL for none, and in every row of a trace recorded without --include-task-context. go_goid and go_labels_id (from schema 30) are a Go program's goroutine and the id of its profiler label set, see below; NULL in every row of a trace recorded without --include-go-context.
• stack(id, frame_ids BIGINT[], depth, leaf_name). `frame_ids` is root-to-leaf (outermost caller first, innermost executing frame last); leaf_name is the last frame's name.
• frame(id, name) — interned strings, dense per-trace ids.
• frame_file(frame_id, file) — the full source path of the frames that have one (from schema 23: Python frames, and the native and kernel frames of task-stacks stacks where debug info has the directory, which is the build machine's path): frame.name has only the file's name. LEFT JOIN frame_file ff ON ff.trace_id = f.trace_id AND ff.frame_id = f.id.
• Join on both trace_id and the id: JOIN stack s ON s.trace_id = ss.trace_id AND s.id = ss.stack_id.
• The stack_frames view reconstructs a frame_names VARCHAR[] column (root-to-leaf) for ad-hoc queries — convenient but slower than joining frame directly.

-- Top 10 hottest leaf functions (on-CPU samples) — leaf_name avoids any join
SELECT leaf_name, count(*) AS samples
FROM stack_sample ss
JOIN stack s ON s.trace_id = ss.trace_id AND s.id = ss.stack_id
WHERE ss.stack_event_type = 1  -- 1=cpu, 0=uninterruptible, 2=interruptible
GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
 
-- Samples containing a given frame anywhere in the stack
SELECT count(*)
FROM stack_sample ss
JOIN stack_frames sf ON sf.trace_id = ss.trace_id AND sf.id = ss.stack_id
WHERE list_contains(sf.frame_names, 'do_futex_wait');

Python function calls — python_function_slice (the python-function-trace recorder, from schema 34):

• python_function_slice(ts, dur, utid, depth, function, file, line, end_kind): one row per run of a Python frame, with end_kind one of return, yield, unwound, open; a generator has a row per resumption. sysinfo.python_function_* says how many processes were traced, what was lost, and in python_function_notes what was refused and why.
• dur includes the probes' own cost of a few microseconds a call, so rankings favour functions called often: compare counts as well as times.

Task-stacks snapshots — task_stack_event → stack (the task-stacks recorder, from schema 23):

• task_stack_event(ts, dur, utid, start_iteration, end_iteration, utime_delta_ns, stime_delta_ns, runtime_delta_ns, state, stack_id, task_context_id, syscall_nr, syscall, in_iowait, holds_gil): a thread as a periodic snapshot found it, for as long as it stayed that way. Snapshots (iterations) are numbered from 1; a thread that had not run and was still in the same state is not re-recorded, its row is extended through end_iteration, so a thread blocked for a minute is one row. state is the one-letter task state (R, S, D, ...); an R row that spans iterations is a thread that waited for a CPU through them, not one that ran, whatever its runtime_delta_ns, which is the time before the row; utime_delta_ns / stime_delta_ns are CPU time since the thread's previous row, which advance by scheduler ticks and so read 0 for a short run; runtime_delta_ns is the time on a CPU since then to the nanosecond, the one to use for "did it run"; stack_id is NULL when the thread had no frames. task_context_id (from schema 27, --include-task-context beside the recorder) is the id of what the thread said it was working on when its row was recorded, joined to task_context on utid and id as for stack_sample; NULL for none. The thread's names are on thread: name (the kernel's) and py_name (its Python process's). Before reading short user stacks as threads that were idle or unwalkable, read sysinfo.task_stacks_remote_reads (from schema 29): off:kernel-release means the capture ran on an aarch64 kernel where the recorder does not read other tasks' memory, so every user stack is its first frame alone and there are no Python frames and no task_context_id, by design; no row says so by itself; with Python frames alone such a capture loads no iterator, so the table is empty and this value is what says why. NULL there means the recorder was not asked for or the capture predates the column (unknown, never on).
• A user stack can be short because the recorder stood back, whatever the thread itself was doing. It never waits at a process's memory-map lock: while that is held for writing or has a queue, a record is cut short at that point (user frames, Python frames and task_context_id), for every thread of the process that has a record due; another thread at an mmap_lock wait in its kernel frames is the sign to look for. A thread that sleeps through it gets a new row once the lock is free, with the whole stack and runtime_delta_ns 0, so a row like that follows the cut one. In Python-only mode the thread has no row in between, and the CPU time of that record is lost. It also follows a frame pointer only while that stays on the thread's stack, so frames beneath a switch of stacks are missing: a goroutine's beneath cgo or systemstack, or those beneath a signal handler on an alternate stack. No row says either, so read the counts before trusting a short stack (from schema 33; NULL means unknown, never 0): sysinfo.task_stacks_cut_records and sysinfo.task_stacks_off_stack_records, each out of sysinfo.task_stacks_full_records, which is one per task_stack_event row (in Python-only mode a walk refused outright has no row and still counts as cut, so the cut count can pass it).
• These are snapshots, not samples of CPU time: every targeted thread has a row whatever it was doing, so this is the table for "what was each thread blocked in" rather than for CPU flamegraphs.
• Idle or busy (from schema 31; all four NULL in an older trace, which means not recorded): most blocked threads are parked, not stuck, and these columns tell which without reading frames. syscall / syscall_nr is the system call the thread was in (futex, epoll_wait, nanosleep, accept are where parked threads sit; read, pread64, fsync, connect are work), NULL when it was in none or was on a CPU as the snapshot reached it (most R rows). in_iowait is the kernel's mark of a thread blocked on disk I/O. holds_gil is whether the thread held its Python interpreter's GIL, NULL without Python frames. state = 'R' is py-spy's default "not idle" view and holds_gil its --gil; a row that is not R and has holds_gil is a call that sleeps without releasing the GIL. A thread in futex may be parked on a condition variable or blocked on a contended lock: the frames (stack.frame_names) say which.

-- What every thread of a process was doing at a time T
SELECT t.name, e.state, s.leaf_name
FROM task_stack_event e
JOIN thread t ON t.trace_id = e.trace_id AND t.utid = e.utid
JOIN stack s ON s.trace_id = e.trace_id AND s.id = e.stack_id
WHERE e.ts <= 123456789 AND 123456789 < e.ts + e.dur;
 
-- Time each thread spent blocked, by where
SELECT t.name, s.leaf_name, sum(e.dur) / 1e9 AS seconds
FROM task_stack_event e
JOIN thread t ON t.trace_id = e.trace_id AND t.utid = e.utid
JOIN stack s ON s.trace_id = e.trace_id AND s.id = e.stack_id
WHERE e.state IN ('S', 'D')
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 20;