Figuring out I/O problems

MySQL & MariaDB Other MySQL® Questions
1

I’m having constant IO problems on one of my servers, iotop tells me that the majority of IO is used by MySQL(Percona).

What’s the best way to identity who is using all of the IO from within MySQL?

I’ve been looking at BYTES_RECEIVED AND BYTES_SENT from INFORMATION_SCHEMA.USER_STATISTICS. Is there anything else I should be looking at that would tell me disk activity from a certain user?

2

I could be the cause and still not show up in users causing I/O, since all my queries can be satisfied from memory. It may be hard to identify a single cause, and maybe it’s a better idea to invest in SSD storage.

3

We are already running SSD, but thanks for the suggestion.

4

Three more suggestions :slight_smile:

  • There is a large variety of SSDs and RAID controllers. Try better ones.
  • Check how many tmp tables are created on disk. If many, use a ram drive as tmp storage location for MySQL.
  • Use pt-query-digest to see which users run the worst queries.