kadamb kadamb - 1 month ago 19
Linux Question

MYSQL writes not visible on disk

enter image description here

As you can see in the image, I am getting ~14-15Mb/s(also reaching 50-60Mb/s) write from MySQL and in phpMyAdmin it showing about 400-500 write per second.
Also in the below image you can see the IO Graph
enter image description here

The issue is I cannot find where this data is being written. As I cannot see any insert/update query in mysql and also when I am checking directory sizes, it is also the same. So where is this data being written?

EDIT :RAM is also not being utilised,please check the screenshot below.
enter image description here

Answer

Just stumbled on this old question of mine and thought to give an answer/explanation.

Actually, I had deleted some 80-90 million rows a day before.I started deleting at 11pm and the query ran for about 6-7 hours to complete. As I was using InnoDB, it only deleted the data from memory and not from disk(at first). And once when it completed, it started erasing data from disk too. But as is the nature of InnoDB, it doesn't free up disk even after deletion of data, there was no change in the file size.

As I was seeing writes that time, and no running query(delete-query), I couldn't make it out that time, that it was deleting data from disk.

Great learning experience though(as production level services were affected due to slow response from MySQL).