7

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.

Daniel
  • 763
  • 1
  • 8
  • 25
  • [Windows Process Explorer](https://technet.microsoft.com/en-us/sysinternals/bb896653) might help you to debug. – Daniel W. Jan 12 '17 at 12:45

2 Answers2

7

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

Milney
  • 6,253
  • 2
  • 19
  • 33
  • Thanks, it says "trx_state: ROLLING BACK". How can I cancel this? – Daniel Jan 12 '17 at 15:55
  • 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. – Daniel Jan 12 '17 at 15:58
  • 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... – Milney Jan 12 '17 at 16:05
  • 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. – Daniel Jan 12 '17 at 16:07
  • 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 – Milney Jan 12 '17 at 16:13
  • I'll try, thanks. I can't find "innodb_force_recovery" in my.ini, should I just add it? – Daniel Jan 12 '17 at 16:17
  • 1
    I am actually more familiar with MsSQL than MySQL however a quick glance at the docs (here: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html) suggest that you should just add it, under the [mysqld] heading section – Milney Jan 12 '17 at 16:21
  • Let us know how this went - Hopefully you get it restored okay! – Milney Jan 13 '17 at 10:42
0

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

SET GLOBAL Innodb_buffer_pool_size = 5168709120
iminiki
  • 2,549
  • 12
  • 35
  • 45
Prakash Nallasamy
  • 1
  • 1