Interrogate the Trace: Perfetto SQL for Android Performance Debugging

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.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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         AS running_ms
FROM overlap
GROUP BY slice_id
ORDER BY stall_ms DESC
LIMIT 10;

Read the output like a diagnosis:

• Mostly sleeping_ms: the thread parked waiting for a lock holder. Look at the owner named in the slice. In the example, the owner was inside SharedPreferences.commit(), a synchronous disk write held under a lock. The fix is to stop doing disk I/O under a lock the UI thread can touch.
• Mostly runnable_ms: CPU starvation, not a lock problem. Look at what else was running on those cores.
• Mostly io_wait_ms: the stall is storage. The lock is a red herring.
• Mostly running_ms: the thread was busy, perhaps spinning or doing real work inside the slice. Profile the code, not the lock.

The same slice, four different remedies. This is the reason to join instead of guessing.

Failure modes

• thread_state is empty unless the trace captured scheduler events. A config without sched/sched_switch yields no rows, and an inner join then drops every contention slice, which reads as "no contention." Check that thread_state has rows for your thread first.
• Open-ended final state. The last state row for a thread can have an unfinished duration. Clamp it, or exclude it, so one bad row does not distort a sum.
• Overlap arithmetic is easy to get wrong. Use two-argument MIN/MAX for the clamp exactly as written above, and sanity-check that the per-state pieces add up to roughly stall_ms. If they do not, your join is dropping or duplicating rows.
• A standard-library monitor-contention module can attribute blocking and blocked methods directly. Use it when available, but the manual overlap query keeps working on a pinned older processor.

6. Saving queries and reusing them across traces in CI

A query you typed once in the UI is trivia. A query in version control is an instrument. Keep each question in its own .sql file with a one-line comment stating what it measures and what unit it returns, review changes to it like code, and run the same files against every trace.

On a developer machine or a plain CI shell you can use the command-line processor directly:

trace_processor_shell \
  --query-file sql/frame_durations.sql \
  build/traces/weekly_plan_scroll.perfetto-trace

If your traces come from Macrobenchmark runs, you can stay in Kotlin. The androidx.benchmark:benchmark-traceprocessor artifact wraps the trace processor, so the same test that records the trace can query it. The entry point is androidx.benchmark.traceprocessor.TraceProcessor; older tutorials show it under its former name, PerfettoTraceProcessor in androidx.benchmark.perfetto, which may not match current releases. The API is still opt-in experimental, so pin the library version and check the signatures against it:

import androidx.benchmark.traceprocessor.ExperimentalTraceProcessorApi
import androidx.benchmark.traceprocessor.PerfettoTrace
import androidx.benchmark.traceprocessor.TraceProcessor

object SqlLibrary {
    operator fun get(name: String): String =
        requireNotNull(SqlLibrary::class.java.getResource("/sql/$name.sql")) {
            "Missing saved query: $name"
        }.readText()
}

data class TraceMetrics(
    val frameDurationsMs: List<Double>,
    val ttfdMs: Double?,
    val mainThreadBinderMs: Double,
)

@OptIn(ExperimentalTraceProcessorApi::class)
fun measure(tracePath: String): TraceMetrics =
    TraceProcessor.runServer {
        loadTrace(PerfettoTrace(tracePath)) {
            val dataLoss = query(SqlLibrary["trace_health"]).sumOf { it.long("value") }
            check(dataLoss == 0L) {
                "Trace lost $dataLoss events; refusing to compare lossy data"
            }

            val frames = query(SqlLibrary["frame_durations"])
                .map { it.double("dur_ms") }
                .toList()
            check(frames.isNotEmpty()) { "No frames found; was the frame timeline captured?" }

            TraceMetrics(
                frameDurationsMs = frames,
                ttfdMs = query(SqlLibrary["time_to_fully_drawn"])
                    .map { it.double("ttfd_ms") }
                    .firstOrNull(),
                mainThreadBinderMs = query(SqlLibrary["main_thread_binder"])
                    .sumOf { it.double("dur_ms") },
            )
        }
    }

The health query referenced above keeps you from comparing apples to a truncated orange:

-- trace_health.sql: events the processor reports as lost
SELECT COALESCE(SUM(value), 0) AS value
FROM stats
WHERE severity = 'data_loss'
  AND value > 0;

Failure modes that bite in CI

• Version drift. The trace processor in your laptop’s browser UI and the one in CI are not the same binary. A new release can rename a column or change how a slice is parsed. Pin a specific trace_processor version in CI, and bump it deliberately with a diff of metric outputs on a fixed set of reference traces.
• Lossy traces. A ring buffer that wrapped, or a recorder that dropped events, silently shortens the data. Make “data lost” a hard failure, not a warning. Tune the filter for your fleet, because some error-severity stats are benign noise and will cry wolf if included.
• Queries that return zero rows. An empty result is data, not success. Assert row counts, as the check(frames.isNotEmpty()) call does, so a broken capture cannot pass as a perfect build.
• Absolute timestamps. ts is on the device's boot clock and means nothing across traces. Compare durations and counts, never raw timestamps.

7. Comparing before and after, numerically

“It feels smoother” is not a result. A pair of traces can mislead too, because one run is one sample from a noisy distribution. The fix is the same as for any measurement: collect several runs per build, reduce each to percentiles, and compare the percentiles.

The chart shows the typical shape of a good fix to a tail problem: the median moves a little, the p90 drops under the 16.7 ms budget, and the p99 nearly halves. Averages would have hidden almost all of that, which is why you should rarely gate on a mean.

SQLite has no percentile function, so return raw durations from SQL and compute percentiles in Kotlin, where the logic is testable:

fun List<Double>.percentile(p: Double): Double {
    require(isNotEmpty()) { "Cannot take a percentile of no samples" }
    require(p in 0.0..100.0) { "Percentile out of range: $p" }
    val sorted = sorted()
    val rank = kotlin.math.ceil(p / 100.0 * sorted.size).toInt().coerceIn(1, sorted.size)
    return sorted[rank - 1]
}

data class FrameSummary(val p50: Double, val p90: Double, val p99: Double, val samples: Int) {
    companion object {
        fun of(durationsMs: List<Double>) = FrameSummary(
            p50 = durationsMs.percentile(50.0),
            p90 = durationsMs.percentile(90.0),
            p99 = durationsMs.percentile(99.0),
            samples = durationsMs.size,
        )
    }
}

data class Delta(val metric: String, val before: Double, val after: Double) {
    val changePct: Double get() = (after - before) / before * 100.0
}

sealed interface Verdict {
    data object Clean : Verdict
    data class Regressed(val worse: List<Delta>) : Verdict
}

fun compare(
    before: FrameSummary,
    after: FrameSummary,
    tolerancePct: Double = 5.0,
    minSamples: Int = 300,
): Verdict {
    require(before.samples >= minSamples && after.samples >= minSamples) {
        "Too few frames to compare (${before.samples} vs ${after.samples}); " +
            "a short scroll cannot support a p99"
    }
    val worse = listOf(
        Delta("p50", before.p50, after.p50),
        Delta("p90", before.p90, after.p90),
        Delta("p99", before.p99, after.p99),
    ).filter { it.changePct > tolerancePct }

    return if (worse.isEmpty()) Verdict.Clean else Verdict.Regressed(worse)
}

Turn the verdict into an exit code and the gate from the pipeline infographic is done. Keep the decision in a pure function that returns the code, and call exitProcess only in a standalone main:

fun runGate(beforeDir: String, afterDir: String): Int {
    val before = FrameSummary.of(loadAllFrames(beforeDir))
    val after = FrameSummary.of(loadAllFrames(afterDir))

    return when (val verdict = compare(before, after)) {
        Verdict.Clean -> {
            println("No frame-time regression.")
            0
        }
        is Verdict.Regressed -> {
            verdict.worse.forEach {
                System.err.println(
                    "%s regressed %.1f%% (%.1f ms -> %.1f ms)"
                        .format(it.metric, it.changePct, it.before, it.after)
                )
            }
            1
        }
    }
}

fun main(args: Array<String>) {
    val (beforeDir, afterDir) = args
    kotlin.system.exitProcess(runGate(beforeDir, afterDir))
}

Run this as its own JVM process from a CI step. Do not call exitProcess from inside a Gradle test or benchmark worker: it kills the executor before reports are written. In that setting, call runGate and assert on the returned code instead, so the test framework can finish and publish its results.

Failure modes that fool your numbers

• Too few samples for the tail. A p99 from 60 frames is the single worst frame wearing a statistical costume. The minSamples guard exists to refuse that comparison instead of reporting it.
• Pooling frames across runs hides run-to-run variance. If you can afford it, compute a p90 per run and compare the medians of those per-run values too. A change that looks like a win in pooled data but not per run is noise.
• Thermal and frequency state. A phone that has been benchmarking for twenty minutes throttles. Interleave the baseline and candidate builds on the same device, in the same order of warmth, or you will measure the heat and call it your code.
• Different work, not different speed. If the “after” trace scrolled a list that now loads fewer items, you compared two different workloads. Assert that the interaction (frames rendered, scroll distance, launches counted) matches before trusting any delta.
• Tolerances that are too tight. A 1% gate on a noisy device will fail builds at random and train everyone to ignore it. Measure your own run-to-run spread first and set the tolerance above it.

What changes when you work this way

The habit shift is small and the payoff compounds. The UI is still the right tool for finding the surprising thing. Once you know the question, though, encode it, run it on every trace, and let the database do the counting.

A short checklist to take away:

1. Join through utid and upid, and never filter out dur = -1: clamp unfinished slices to the end of the trace with trace_bounds and keep an unfinished flag.
2. Assert that your tables are non-empty before believing an empty answer.
3. Classify before you optimize: app deadline, compositor, binder, lock, I/O, CPU starvation.
4. Join slices to thread state whenever a slice is slow and the reason is not obvious.
5. Store queries in version control, pin the trace processor, and fail the build on lossy traces.
6. Compare percentiles across several runs, never single traces, and never a mean.

The next time someone says “that screen feels slow,” do not open a timeline. Ask it a question.

🧵 Stop Treating Coroutines Like Magic Keywords

If “just add launch and hope” describes how your team handles concurrency, I’d recommend “Kotlin Coroutines Under the Hood” — a 25-chapter deep dive that goes past the syntax and down to the bytecode: the Continuation-Passing Style transform, the compiler-generated state machine, and exactly why a suspended coroutine costs a few dozen bytes while a blocked thread costs a megabyte.

It walks through structured concurrency and Job hierarchies, dispatcher internals (Default vs IO vs Unconfined), cooperative cancellation and exception aggregation, Flow’s cold-stream semantics and the full operator vocabulary, StateFlow/SharedFlow’s hot-broadcast model, Channels and fan-in/fan-out patterns, Mutex/Semaphore/actors for shared state, and a complete Android case study retrofitting a legacy callback-driven app onto coroutines and Flow — including a 100,000-coroutines-vs-100,000-threads benchmark lab you can run yourself.

• E-book: Available on Google Play
• Kindle Edition: Available on Amazon
• Topmate: Available on Topmate
• Also available in Paperback & Hardcover.

---

Originally published on Medium.