4

We have a web application backed by MySQL serving hundreds of queries per second. I'm looking for a way to measure the "cost" of every query in production. I'm imagining some option where, for every query, MySQL returns the query results along with the CPU and I/O cost of executing that query.

The end goal is to aggregate those costs by endpoint (e.g. "/search") and by the logged-in user ID. That way, when we're having issues with site, we can quickly see if there's a particular action or user ID that is using up a large chunk of our MySQL resources.

Close but not quite (AFAICT):

This answer comes close: https://stackoverflow.com/a/12880997/163832

It describes the precision and accuracy problems with EXPLAIN and recommends an alternative that measures what actually happened rather than estimating what will happen.

The alternative does seem better for my use case, but there are still problems:

  • I looked at the available stats and can't find ones that measure CPU or I/O.
  • I don't think I can afford to do FLUSH STATUS and then SHOW SESSION STATUS ... on every query.
  • This doesn't work when many queries are running concurrently.
Kannan Goundan
  • 4,962
  • 3
  • 24
  • 31
  • I think the easiest for you will be to measure the time inside your database class - the method which actually executes the SQL queries. The time is easy to measure and will give you quite accurate picture of the slow queries. – IVO GELOV Jul 16 '20 at 06:28
  • With your existing workload of hundreds of queries per second, would you consider an external solution to avoid delaying your business purpose of serving data to your customers? – Wilson Hauck Jul 16 '20 at 16:57
  • 1
    @IVOGELOV: We currently do track that, and it is definitely useful. But I was hoping the DB stats could additional provide I/O information and more accurate CPU measurements. – Kannan Goundan Jul 19 '20 at 07:16
  • This URL will lead you to Performance Schema concepts and data availability since MySQL ~ 5.5.n. From Percona talent. Screen shot 35 specifically mentions query data that could be useful for your endeavor. https://www.slideshare.net/SvetaSmirnova/mysql-performance-schema-in-action-the-complete-tutorial Have fun, digging deeper. – Wilson Hauck Jul 19 '20 at 15:04

0 Answers0