Interrogate the Trace: Perfetto SQL for Android Performance Debugging

Stop eyeballing flame charts

Stop eyeballing flame charts. Write a query.

A system trace is a SQLite-flavored database. Every slice, thread, scheduling decision and frame the device recorded sits in a table you can join, filter and aggregate.

Yet most of us still open the Perfetto UI, drag a timeline around, squint at a red bar and say “that looks long.” Squinting does not scale. It does not diff two builds, it does not run in CI, and it does not survive a teammate asking “how do you know?” A query does all three.

This post walks through the queries I reach for first when a screen feels slow, in the order I usually need them, and then shows how to turn them into a regression gate.

The running example is a grocery-list app called Pantry (com.example.pantry). Its "Weekly Plan" screen janks on scroll and takes too long to settle after launch. All numbers in the infographics are illustrative; the queries are the real part.

A note on versions: Perfetto’s schema and standard library evolve. Column names below match recent releases, but verify them against the trace_processor version you pin, because that mismatch is the number one reason a copy-pasted query returns nothing.

Five tables and one chain of keys

You can answer most performance questions with five tables:

• process and thread identify who ran. Join them on upid.
• thread_track maps a thread to the lane that holds its slices. Join it on utid.
• slice holds the named intervals: Choreographer#doFrame, binder transaction, inflate, and so on. Join it to thread_track on track_id = thread_track.id.
• thread_state records what the kernel scheduler did with each thread: running, runnable, sleeping, blocked on I/O.

Start with a query that exercises the whole chain. It lists the ten longest slices on any thread, with the owner’s names attached:

SELECT p.name AS process, t.tid, t.name AS thread, s.name AS slice, s.depth, s.dur = -1 AS unfinished, CASE WHEN s.dur = -1 THEN (SELECT end_ts FROM trace_bounds) - s.ts ELSE s.dur END / 1e6 AS dur_ms FROM slice s JOIN thread_track tt ON s.track_id = tt.id JOIN thread t ON t.utid = tt.utid LEFT JOIN process p ON p.upid = t.upid ORDER BY dur_ms DESC LIMIT 10;

Four habits will save you hours later.

• Always join on utid and upid, never on tid or pid. The kernel recycles thread and process ids. A long trace can contain several different threads that all report tid 8123. utid is unique per trace; tid is not.
• Remember units. ts and dur are nanoseconds. Divide by 1e6 for milliseconds. Nothing is more embarrassing than reporting a 48,000,000 ms stall.
• Expect a thread to own more than one track. The thread_track join is correct, but a thread can have several tracks, so an unfiltered join can fan out. If you see duplicated slice rows, check thread_track.name and the track's type before blaming the data.
• Know what dur = -1 means. A slice that began but never ended before the trace stopped has dur = -1. Left alone, it sorts to the bottom of every "longest" list. Filtering with dur > 0 is no better, because it silently discards exactly the slice that was still running when the capture ended, which is often the stall that ended your session. Clamp it to the end of the trace instead, as the query above does with trace_bounds, and keep the unfinished flag so you can see which rows are lower bounds.

Failure mode: the thread you want is not in thread_track

Async slices, such as app startup phases and many system-wide events, live on process tracks or global tracks, not thread tracks. An inner join through thread_track will not return them, and the query will look correct while returning nothing.

When a slice you can see in the UI is missing from your results, query slice by name first, check its track_id, and then work out which track table owns it.

The ten longest frames

If your device is Android 12 or newer and the trace captured the frame timeline data source, actual_frame_timeline_slice is the best place to start. Each row is a frame as the system judged it, complete with a jank classification.

SELECT a.name AS vsync_id, a.layer_name, a.jank_type, a.ts, a.dur / 1e6 AS dur_ms FROM actual_frame_timeline_slice a JOIN process p ON p.upid = a.upid WHERE p.name = 'com.example.pantry' ORDER BY a.dur DESC LIMIT 10;

The chart tells you something a flame chart cannot: six of the ten worst frames are the app’s own fault (App Deadline Missed), two are buffer stuffing, and two belong to the compositor (SurfaceFlinger CPU Deadline Missed). Those need different fixes. Without the classification you might spend a day optimizing code that was never the bottleneck.

Once you have a suspect frame, drill down instead of scrolling to it. Find the matching main-thread slice and walk its descendants:

SELECT d.name, d.depth, d.dur / 1e6 AS dur_ms FROM descendant_slice(91234) d -- slice id of the Choreographer#doFrame in question ORDER BY d.dur DESC LIMIT 8;

Failure mode: an empty result that looks like a clean bill of health

On older Android versions, or when the trace config did not include the frame timeline data source, actual_frame_timeline_slice is simply empty. You get zero rows and no error. Guard against this by asserting that the table is non-empty before trusting any "no jank found" conclusion.

As a fallback, rank Choreographer#doFrame% slices on the main thread (the process's main thread is the one where tid = pid):

SELECT s.id, s.ts, s.dur / 1e6 AS dur_ms FROM slice s JOIN thread_track tt ON s.track_id = tt.id JOIN thread t ON t.utid = tt.utid JOIN process p ON p.upid = t.upid WHERE p.name = 'com.example.pantry' AND t.tid = p.pid AND s.name LIKE 'Choreographer#doFrame%' ORDER BY s.dur DESC LIMIT 10;

That fallback measures app-side work only, so it will never show compositor-caused jank.

A word on t.tid = p.pid, which I use throughout to mean "the main thread." Raw tid and pid values are recycled by the kernel, so they are only safe because every query here reaches the thread through upid and utid, which pins the match to one process instance. thread.is_main_thread exists too and is an equivalent filter, but it is not populated for every trace, so I default to tid = pid and switch to the flag only when I have confirmed it is set in my captures.

Slow synchronous Binder transactions on the main thread

The main thread calling into another process is a classic hidden stall. A synchronous Binder call blocks the caller until the server replies, so the caller’s slice lasts as long as the server’s work plus the scheduling delay in both directions.

Find every main-thread synchronous transaction above a threshold, along with the slice that was running when it started:

SELECT s.ts, s.dur = -1 AS unfinished, CASE WHEN s.dur = -1 THEN (SELECT end_ts FROM trace_bounds) - s.ts ELSE s.dur END / 1e6 AS dur_ms, caller.name AS enclosing_slice FROM slice s JOIN thread_track tt ON s.track_id = tt.id JOIN thread t ON t.utid = tt.utid JOIN process p ON p.upid = t.upid LEFT JOIN slice caller ON caller.id = s.parent_id WHERE p.name = 'com.example.pantry' AND t.tid = p.pid AND s.name = 'binder transaction' AND (s.dur > 2e6 OR s.dur = -1) ORDER BY dur_ms DESC;

The OR s.dur = -1 matters: a transaction still in flight when the trace ended has no recorded duration, and it is precisely the one you most want to see.

The enclosing slice is the useful part. A transaction inside Choreographer#doFrame is a jank bug. One inside Activity.onCreate is a startup bug. The same call is a different problem depending on where it hangs.

To see the other side of the conversation, try the flow table, which on traces that captured binder events can link a client transaction to the server's reply slice. Whether those edges exist depends on your Perfetto version and trace config, so check before you trust it:

SELECT COUNT(*) FROM flow should be non-zero, and the query below should return rows for a transaction you can see in the UI with an arrow attached. If it comes back empty, use the standard-library binder module (INCLUDE PERFETTO MODULE android.binder;) instead of fighting the raw join. Plain ftrace binder events do not always produce slice-to-slice flows, so an empty flow table is common, not a bug in your query.

SELECT c.dur / 1e6 AS client_ms, r.dur / 1e6 AS server_ms, rt.name AS server_thread, rp.name AS server_process FROM slice c JOIN flow f ON f.slice_out = c.id JOIN slice r ON r.id = f.slice_in JOIN thread_track rtt ON r.track_id = rtt.id JOIN thread rt ON rt.utid = rtt.utid LEFT JOIN process rp ON rp.upid = rt.upid WHERE c.name = 'binder transaction' ORDER BY c.dur DESC LIMIT 10;

If client_ms far exceeds server_ms, the time went to scheduling, not work: the server thread waited for a CPU, or the client was slow to wake. If they are close, the fix lives in the other process.

Failure modes

• Async transactions are a different slice name (binder transaction async). They do not block the caller, so excluding them is correct here, but a loose LIKE 'binder%' filter will blend the two and inflate your numbers.
• Flows may be absent. Without the binder ftrace categories in the trace config, or on a processor version that models binder differently, flow has no usable edges for these transactions and the join returns nothing. An empty result here means "unlinked," not "fast."
• Prefer the standard-library binder module for anything that must be reliable. It precomputes client/server pairs, which makes it the sturdier basis for a CI metric. Pin your trace_processor version, because its column set has changed between releases.

Time to fully drawn

Time to initial display ends at the first frame. Users do not care about the first frame; they care about when the content they came for is on screen. Android gives apps reportFullyDrawn() for exactly this, and the call leaves a slice in the trace.

Anchor the start to the launch the system detected, and the end to the app’s own call:

INCLUDE PERFETTO MODULE android.startup.startups; SELECT st.startup_id, st.package, st.startup_type, st.dur / 1e6 AS ttid_ms, (rfd.ts - st.ts) / 1e6 AS ttfd_ms FROM android_startups st JOIN android_startup_processes sp ON sp.startup_id = st.startup_id JOIN process p ON p.upid = sp.upid JOIN thread t ON t.upid = p.upid AND t.tid = p.pid JOIN thread_track tt ON tt.utid = t.utid JOIN slice rfd ON rfd.track_id = tt.id WHERE rfd.name LIKE 'reportFullyDrawn%' AND rfd.ts >= st.ts ORDER BY st.ts;

On the app side, make the call at the honest moment. With Jetpack Compose that usually means after the first real content has composed:

@Composable fun WeeklyPlanScreen(state: PlanUiState) { PlanList(state) // Enters composition only once real content (not the skeleton) is on screen. if (state is PlanUiState.Loaded && state.items.isNotEmpty()) { ReportDrawn() } }

ReportDrawn() (from androidx.activity.compose) tells the activity's fully-drawn reporter that the screen is ready when it enters composition, so the slice appears in the trace at the moment your UI state says the content is there. I prefer this over ReportDrawnWhen { ... } for plain state like the above: ReportDrawnWhen observes Compose snapshot state, so a predicate that reads a non-observable object can fail to re-evaluate and never fire. Branching on a parameter and emitting ReportDrawn() keeps the trigger tied to recomposition you can already reason about. Make sure PlanUiState is a stable, immutable type, and that state is driven by observable state (for example collectAsStateWithLifecycle() in the caller), or the branch will not re-run when the data arrives.

Failure modes

• Apps that never call reportFullyDrawn() produce no slice, so the join drops the launch entirely. Count launches before and after the join and alert if they differ.
• Multiple launches in one trace are why the query constrains rfd.ts >= st.ts and orders by start. Without that constraint a warm start can pair with a cold start's report call.
• Declaring victory too early. A loading skeleton is not content. If your predicate fires on an empty list, the metric improves while the experience does not.
• Slice names and module paths shift between OS and Perfetto releases. Treat the LIKE filter as the contract and verify it with a quick SELECT DISTINCT name before wiring this into automation.

Joining slices with thread state to find lock contention

A long slice tells you that a thread was slow. It does not tell you why. The answer is in what the scheduler thought the thread was doing during that window: executing, waiting for a CPU, sleeping, or stuck in uninterruptible I/O. Join the two and the stall explains itself.

The recipe is interval overlap: for each slice, find every thread-state row that intersects it, clamp each row to the slice’s window, and sum the overlap per state.

WITH contention AS (SELECT s.id, s.name, s.ts, s.ts + s.dur AS te, s.dur, tt.utid FROM slice s JOIN thread_track tt ON s.track_id = tt.id JOIN thread t ON t.utid = tt.utid JOIN process p ON p.upid = t.upid WHERE p.name = 'com.example.pantry' AND t.tid = p.pid AND (s.name LIKE 'monitor contention with owner%' OR s.name LIKE 'Lock contention on%') AND s.dur > 1e6), overlap AS (SELECT c.id AS slice_id, c.name, c.dur, st.state, MIN(c.te, st.ts + st.dur) - MAX(c.ts, st.ts) AS ov FROM contention c JOIN thread_state st ON st.utid = c.utid AND st.ts < c.te AND st.ts + st.dur > c.ts) SELECT slice_id, substr(name, 1, 90) AS contention, dur / 1e6 AS stall_ms, SUM(CASE WHEN state = 'S' THEN ov END) / 1e6 AS sleeping_ms, SUM(CASE WHEN state LIKE 'D%' THEN ov END) / 1e6 AS io_wait_ms, SUM(CASE WHEN state IN ('R', 'R+') THEN ov END) / 1e6 AS runnable_ms, SUM(CASE WHEN state = 'Running' THEN ov END) / 1e6