mysqldatabaseserverdiskutilization

What is MySQL doing?? 100% disk utilization from boot


I have a large database on a Win10 machine, mysqld.exe does a lot of disk I/O, 100%, for hours and hours 100MB/s consistently - mostly writes - persists after numerous reboots. How can I find out what the hell it is actually doing, and stop it? I know the database is not being used at the moment, I want to figure out where this I/O comes from and stop it. The only solutions I found on the internet were general configuration advice, I don't need that, I need to shut this thing down now!

show processlist shows nothing.

UPDATE: The problem was a huge background rollback operation on a table. The solution is:

1) kill mysqld.exe
2) add innodb_force_recovery=3 to my.ini
3) start mysqld.exe
4) export the table (96GB table resulted in about 40GB .sql file)
5) drop the table
6) kill mysqld.exe
7) set innodb_force_recovery=0 to my.ini
8) reboot and import the table back

No idea about data integrity yet, but seems fine.

Thanks to Milney.


Solution

  • If you view the Disk tab of resource monitor from Task Manager you can see which files are being written, this will hint you as to which Database it is;

    You can then use something like SELECT * FROM information_schema.innodb_trx\G to view open Transactions and see which statements are causing this

    Resource Monitor