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

Question

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.

Pasteurism

Windows Process Explorer might help you to debug. – Mclin

Answer 1

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

Trawick

Thanks, it says "trx_state: ROLLING BACK". How can I cancel this? – Pasteurism

I found this: "You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback". Can I prevent the rollback without dropping a table? It's kind of important table, and too big to export. – Pasteurism

Do you not have backups? Please tell me this isn't a production database running on Windows 10 client not a server? Ideally you would leave the roll-back to finish... – Trawick

No, it's more like a test thing of mine. Problem is it's on an SSD, those writes are murdering it, so I'm open to non-ideal solutions. – Pasteurism

You can try setting the force recovery to 3, dumping the table out to a file (or another table) using a select, then drop the original table and reset the force recovery flag, then re-fill the table. Bear in mind though the data may be in an inconsistent state. The transaction that it is trying to roll-back may be half-complete, which depending on what it was - could lead the data to be a bit strange.... I would highly recommend setting up some scheduled backups – Trawick

I'll try, thanks. I can't find "innodb_force_recovery" in my.ini, should I just add it? – Pasteurism

I am actually more familiar with MsSQL than MySQL however a quick glance at the docs (here: dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html) suggest that you should just add it, under the [mysqld] heading section – Trawick

Let us know how this went - Hopefully you get it restored okay! – Trawick

Answer 2

simply Increase InnoDB Buffer Pool Size if default 8MB just increase to 512MB

SET GLOBAL Innodb_buffer_pool_size = 5168709120

Naoma