This issue is a placeholder for the conversations to be had. We'd like to find a good balance point where we can automatically gather useful trace information for "outlier" queries without slowing everything else down.

Andrew:

andrei we’ve been talking a bit about this SQL outliers thing that todd is working and, and one immediate thing that came up from a previous conversation I had with arul was that it’d be nice to get some tracing information from a slow query.

You and I have long talked about the value of “retrospective” tracing where you try to trace everything in a cheap way and then, when some interesting condition occurs, go back and fish that info out of some ring-buffers or something.

The question I have is: can we instead do something cheap where we don’t enable tracing out of the box, but instead in the context shove some timestamp, and after a certain duration from that timestamp has passed, start accumulating tracing information?

I imagine there’s some technical hurdles, but it feels like just having the tail of a trace for an outlier would be illuminating in many circumstances. Potentially we could also, at that point, dump any accumulated tracez info that we might have.

Andrei:

There's various things I believe we could do, including what you're suggesting: ask a span to start recording at some point in the future, if it's still around by then. At least half the work with any feature in the area is figuring out how the trace is collected and persisted or presented.

Other things we could do are always enable structured recording (as opposed to verbose recording). I've been wondering what the cost of that is. Combine this with more smarts about what we do when a trace exceeds its limits (limits in number of messages per span and number of children per span) - instead of simply throwing out data, we should be aggregating it. Aditya is adding some capabilities to the tracing library that are related to doing such aggregation.

There were two things on my list to discuss with the KV Obs team, who's nominally taking over the tracing library. Number one on my list is getting the info collection from the trace registry done by /tracez to work across the cluster. Number two is to talk about this issue of SQL query tail latency and getting some exemplars. But perhaps SQL Obs is a better victim.

Irfan:

The story I think I’m after is: I’m seeing a high p99.9 for my txns. I might be able to figure out which one if I’m maintaining good client-side metrics for each point of access, but would be useful also for the DB to tell me what the latency profiles are for individual statements and where the tail is sitting (is this thing possible today?). Once I have that, I want to look at a verbose trace for a statement execution that took longer than some threshold, to see if it’s due to waiting on locks, being blocked on lease acquisitions, something else.

Jira issue: CRDB-15559