MySQL | Measure overall query cost (performance, cpu usage, memory usage)

Question

The Problem

You have a MySQL query, let's say a SELECT and you need to identify its overall cost in all aspect (Host Resource Usage, Query speed / performance ...) so that your result can be quantified and analyzed to determine whether or not the query is too expensive or not. This question is really about overall database management so, requires someone that knows and understand a database in a big scale, with high usage and identify the danger at glance of a query on top of a complex database system, possibly a DB Admin (I am not sure about the generic role name in the industry)

Scenario

The reason I am asking this, is because I am facing a struggle to evaluate my own query but also to prove that my query is actually well performant (that is, to other people), this is because it has some query and subquery and raised some eyebrows (but they also don't know whether this query can perform well or not), so in order to be able to show in a scientific approach with actual number and stats so that we can see with concrete numbers how well such query can do.

Let's keep in count that, is going to have a high usage with huge database data and huge amount of reads / requests, and therefore the benchmark has to push the query to the limit.

The only way I am measure it right now is the actual speed of the Query (less then 1/2 second) but I am not keeping in count, resource usage or other things it should be check in a query (hence this question). I am checking also with the EXPLAIN SELECT ... and set profiling=1; query...; show profile;... combo but to be fair, is not clear what is good/bad, I'd like actual number and stats (or better read those values)

What I am currently doing to measure cost/performance

I set up the database with the target tables with a huge amount of data.

1. Query speed without a WHERE/LIMIT (that's just as a general guidence to create the query structure, if the query lenght is more than 3s, than I discard it)
2. Query speed with final query including the WHERE/LIMIT, the query at its maximum allowed LIMIT (100_000) is currently at 53ms
3. Checking the query with EXPLAIN, but I am not really good at determine whether is a good result or not. I am mainly counting the total in rows
4. Checking the show profile with profiling set to true, same as point 3. I am not so good at understanding if is good or not.
5. Now I decided to use MySQL workbench and checking the Performance -> Dashboard tab

Question

For the questions, please keep in mind the above scenario (especially the high usage part)

• What elements/properties do we need to measure for determine a good agains a bad SQL query (speed, cpu usage, memory usage, writes etc..) and at what threshold of each elements we put, where the threshold is good -> bad.
• What tools can we use to determine the above elements?
• What as a DB Admin, would you do to determine that a query added to a project is good and won't affect performance? How do you know is going to work?

PS: I am not posting the actual query, because I don't want influence the possible answer as this is a problem that we may face for any query.

Answer 1

Be cautious of LIMIT; it makes little or no difference on these

  SELECT COUNT(*) FROM tbl LIMIT 1  -- There's only one row

  SELECT ... FROM tbl ORDER BY ... LIMIT 10  -- First collect all rows, then sort

  SELECT ... FROM tbl GROUP BY ... LIMIT 10  -- First collect all rows, then group

(There are special cases where an index may help with the last two cases.)

"Performance" questions in this Forum lead to

• Add a particular INDEX
• Use a composite index, with the columns in a particular order
• Reformulate a test. (cf sargable )
• Reformulate the query. (eg, avoid IN ( SELECT... ))
• Use FULLTEXT instead of LIKE (if appropriate).
• Change a datatype.
• Some queries are inherently complicated and take a long time. Even if you identify them, they cannot be "fixed".

Tools:

• PROFILE is rarely useful. (I don't think I have ever found useful output from it.)
• EXPLAIN Says how the query is being run, but fails to suggest how to improve it.
• EXPLAIN ANALYZE, if available.
• The best tool for identifying the "worst" queries is the SlowLog
• A tool for measuring the cost is as follows. It counts "rows referenced": http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts I use it to compare two formulations of a query.

Once you have identified the one or two "worst" queries (via the slowlog), post a specific question in stackoverflow.com. Be sure to include SHOW CREATE TABLE and EXPLAIN. If I spot it, we can discuss further why that query can or cannot be identified by the techniques you mentioned here.

You may argue that the slowlog provides only 'elapsed time' and you want more than that. I will counter by saying that it is the most important single metric, and it is influenced by most other metrics -- CPU, I/O, locks, transactions, RAM, etc. Speeding up the 'worst' query may actually speed up many other queries.