0

Using MySQL 8.0.30 on Rocky Linux 9

For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram memory speed ? (mainly storage vs cpu).

Maybe there are certain mysql status variables related to tracking this ?

For example I ran a slow query (~30 seconds) while looking at disk iotop read speed, and because I seen it never read faster than around 20% of maximum ssd read speed, I assume that faster disk would not help speeding up my queries too much, and that maybe CPU (processing the data, not reading it) was the bottleneck.

What are some better ways to do similar tests ?

I also tried similar queries on 2 different SSDs, one having double read speed than the other, but I seen no performance differences, I always thought the storage is "weakest link" but now I need to do more tests.

Improve this question

1 Answer 1

Reset to default
1

Set long_query_time = 1 and run the slowlog at least until some busy query ends. Then look at the slowlog.

Once you have identified the slow query, let's discuss it. Please provide the size of the table(s) involved and EXPLAIN for the query.

If you have a billion rows and are scanning all of them, plan on taking hours, regardless of the hardware. If it is only a thousand rows, then it might be a CROSS JOIN. For a million rows, there is a variety of possibilities.

What is the value of innodb_buffer_pool_size? What is the size of RAM?

Improve this answer
2
  • I appreciate your answer but I was hoping to find ways to see how much does the CPU vs storage slow down the query. I was afraid I would get the "OK, let's see your query" answer :))
    – adrianTNT
    Jan 17 at 0:34
  • 1
    @adrianTNT "but I was hoping to find ways to see how much does the CPU vs storage slow down the query. I was afraid I would get the "OK, let's see your query" answer" - That's because rarely hardware is the bottleneck, so your original question is kind of arbitrary and moot. Almost always, the root problem is a software one (how the query / database is architected, and / or how the query is choosing to execute - i.e. the query plan which is visible via EXPLAIN ANALYZE). Therefore the solution will almost always be a software solution.
    – J.D.
    Jan 17 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.