Menu
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.
406
Close background applications Running lots of applications at once may cause 100% disk usage in Windows 10. Uninstalling these programs often isn't possible, because you may need them. Manually pausing the background apps is only a short-term solution, as many will restart upon bootup or run quietly in the background.
Disk usage by InnoDB tables and how to reduce it if your disk is getting full. Written by Sami. Aiven for MySQL is configured to use innodb_file_per_table=ON which means that for every table its data and indexes are stored together in a separate .
First of all, there are 3 major cases when MySQL will crash due to running out of memory: MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix.
Note that the above DDL operation is performed via online DDL, meaning MySQL permits concurrent DML operations while the rebuilding is ongoing. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.
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
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
