[Shipped] Analyze Slow Database Performance with Query Insights

[ishkebab]

Hey everybody - I'm Ash from the Performance PM team! 👋

Today the team is excited to introduce the Queries workflow as the latest addition to our Performance product!

Database slowdowns can creep in silently, degrading application responsiveness over time. With the new Queries workflow, you can quickly identify and analyze those slow queries consuming the most time in your database. As of today, you will find the Queries feature tab just under Performance in the sidebar.

On the Queries landing page, you are presented with a list of database Queries from your application, ordered by total run time. By clicking into any of these queries, you can zoom into the details for the selected query and investigate sample events to better understand the context for any performance bottlenecks.

Please take a look and provide your feedback!

We would love to better understand:

• Is the Queries workflow intuitive and useful for investigating your database performance?
• What kinds of problems have you experienced with database performance? Have you been able to use Queries to address those problems?
• What other capabilities would you like to see?

Please Note:
To use the Queries tool, you will need to set up our Performance product. Please take a look at our setup guide.

[chrisOldfield]

We would love to better understand:

Is the Queries workflow intuitive and useful for investigating your database performance?

- They are indeed intuitive but in my context they are used in dashboard that highlight the calls, procedures, and internal server scripts that are correlated with active incidents, QA, and general UX improvements. So it's a fantastic overlay but with the kerbernetes and other more efficient DB's entering enterprise. Super useful, but it would be great to combine it with the incoming or outgoing traffic/requests and compare to the usage pain points.

`What` kinds of problems have you experienced with database performance? Have you been able to use Queries to address those problems? 

- It's been incredibly helpful in consolidating development concepts across all teams in that we have ~15 integrations with crm sync engine in parallel with those other sync operations. I know we're all familiar with 'this is that team's issue not on our end' dynamics. Having access to the information their requesting and what's the operations are to provide it and address inefficiencies just allows for contestant product improvement with the route to address any issue essentially fed to the admin's. From there it's ours to act on (haha)

What other capabilities would you like to see?

_- Other than what I stated above, I think correlating those out-of-scope or problem queries would be far faster for incident response and RCA._

Happy to show you some examples offline if interested.

[eli-amazing]

Hey there,

First off, this is a great feature that can help in diagnostic of queries specifically and in database use and performance in general.

Few questions:

1. each query counts as a span?
2. I saw in the docs, that this is only integrated as part of ORM's, is it possible to use it directly?
3. if so, query generalization should be done on the client side?
   3.1 why? some queries vary in performance by parameters, when generalized, it would be less easy to find them.

For example:

one user has project containing 5 issues.
These can be easily loaded for issue list and filters would be swift.
In another he has 200 issues, the effect is still small but significant when performing the same actions (WHERE, SORT clauses).

To simplify and have some numbers:

Project 1: only unresolved (3/5 items) 60ms
Project 2: only unresolved (75/200 items) 145ms

(Numbers are guess-timated)

Now there's always the part of sensitive data that shouldn't be shared but some might be useful and you'll do the grouping on your end (generating key based on query template etc.)

Which brings me to a different aspect, result count.

For example a paginated query should have limit but we as developers sometimes assume that the where will be sufficient (e.g. you wouldn't have too many alerts which are active)

Other than that, when analyzing general throughput of database, statistics can be helpful in understanding what are "busy" tables such as high read or high write, and also might me marked as cache missing (the reason for query)

If you could also provide an image of how the single query page is, it would help understand and maybe raise further discussion

Keep up the good work

[indragiek]

@eli-amazing Thanks for the feedback!

To answer your questions:

1. Yes, each query is a a span
2. If you want to send your own database query spans you can send them manually by setting the span op to db.sql.query
3. We do query generalization/parametrization on both the client and the server. The client implementation depends on the specific SDK, but the ORMs do some generalization for security purposes and then we apply additional processing on the server to improve grouping or fix inconsistencies in the data sent by the SDKs

[chdsbd]

I think average duration is interesting, but I'd love to see more options, like 95th-percentile and max duration

[indragiek]

@chdsbd Are you interested in p95 for specific queries, or for the top level charts for the database as a whole?

[chdsbd]

In the table that lists all queries, I think it would be useful to support sorting by p95, p99, max

[samuelhwilliams]

Finding this to be very interesting feature - nice improvement in featureset 👏

I would love to see an environment filter added :)

[indragiek]

Noted! We are looking into adding support for an environment filter. Thanks for the feedback.

[gggritso]

Update, we have looked into it, and have shipped it! The environment selector now appears in the usual place, next to the date range selector. Thank you for your feedback 👍🏻

[samuelhwilliams]

Amazing 👏 ❤️ Thanks!

[shaedrich]

As the text already suggests, it would be cool, having the result of EXPLAIN already in sentry in some shape or form. That might lead to some tips based on common patterns. That could also be visualized as some kind of pie chart to break down which tables are the bottlenecks.

[ishkebab]

@shaedrich I agree! That is definitely one of the items on our development roadmap moving forward. Will definitely post again once we have any updates on Query plans.

[kobeyy]

Really useful feature!
The reason i found this discussions was that we wanted to compare query performance between environments.
We're having performance issues on our develop environment where queries take significantly longer than in our other environments. It would be great if we could compare query performance between environments to find missing indexes, bad hardware or bad networking causing slower database performance.
The Transaction detail tag view proved very useful for such use cases. It would be great to have something similar on the query level.

[gggritso]

Interesting use-case, thanks for bringing this up!