wait rate very high due to mysql server activity

Question

Sometimes the server is very slow and needs a lot of time for serving the requests. iotop shows a disk read rate of 1-2 M/S on average for some minutes (which is not that much actually), after that the server is very fast again. while being very slow the wait rate is about 60-90% according to top.

All mysql caches are more than OK according to mysql tuning primer. So I don't know why the mysql server does so much disk reads. Is there any way to find out what causes so much I/O read in mysql?

I have to say that it is a virtual server, so could it be that another customer is using the whole I/O capacities?

Answer 1

It is good to understand the underlying storage layer you have. Do you have a single physical disk? RAID1 or RAID5 of few disks or large storage array where the LUNs are made from many (40+) physical drives. Each physical drive can give you approximately 150-200 requests/s (depending on rorational speed).

So the MB/s figure isn't important in the iostat/sar/dstat output, because for sequential reads/writes, modern drives can do more than 100 MB/s, but for random requests with for example 8kB size, it will give you only 150*8kB = 1.2 MB/s. Requests from database server are almost always random.

The best metric to look at is always the io service time - that is the time it takes for the storage to service your read or write request. You don't need to worry about how many disks you have, if the service time is lower for example than 15-20 ms (milli-seconds), then you know your storage is performing well. On almost idle server with battery-backed cache (BBWC) you should see write service times less than 1ms and read times under 5 ms. This metric is also good for VPS as it will show you high service times even if the storage is busy servicing other clients.

Answer 2

use sar to find out history of io actvitiy on the server. I think sar is already configured and running on that server. If not do it. Then there might be other Virtual machine making heavy io calls and your mysql is busy waiting for io. I would recommend not to have mysql or any database with even moderate traffic to be on a VPS solution. If it is serving real traffic then it should be on a physical server, as io on VPS tends to be bad and mysql with bad io performs badly and the whole point of having db is affected.