# wait rate very high due to mysql server activity

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?

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.

• Very informative! Can you recommend any tool to monitor the io service time? – Chris Jan 5 '12 at 11:15
• For example diskstats plugin in munin. But I suppose any other widely used monitoring tool has a plugin for it (for exaple nagios). – Marki555 Jan 5 '12 at 11:39

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.

• Is it really possible to look at the I/O activities other virtual machines are producing with sar? Yes, you're right, I should change to a dedicated server. – Chris Dec 25 '11 at 18:43
• @chris I didnt to say sar would give you io stats of other vm's. Sar output should help you with stats of io activity for your virtual machine for say past 30 days . This should give fair idea of how good or bad things are. – bagavadhar Dec 26 '11 at 4:01