Recently we have seen a production issue – received multiple alerts high disk I/O on a server where production DB is running. CPU and Memory still fine though.. No weird logs in messages OR database logs.
# htop – shown multiple processes in “D” state.
#iostat -xtc 1 100 , atop shown the disk utilization is 100% but we still didn’t find the reason. No alerts at hard disk level..
A: Finally found the issue. Logged into the DB
mysql> show processlist;
- We could see the replication process which is running since quite a period time which is OK.
- We have also seen one more query which is running quite a bit of time exactly since the number of read and write requests increased..
- It is only read query but it locked tables
mysql> kill query <pid>
resolved the issue.