Re: Innodb unexplained IOs

[James Day]

From the look of that you have 16070 / 72768 = 22% dirty pages. Main thread seems to be in state: purging. Try SET GLOBAL innodb_max_dirty_pages_pct=1; (do it in steps of a few percent at a time from 22 to 1 if this is a production server under load because it'll prompt a surge in disk activity). Wait for SHOW INNODB STATUS to show the writes/s level falling from the surge value back to a low level. Then use SET GLOBAL innodb_max_dirty_pages_pct=90; to return to the default value, or return to whatever value you normally use. You've just forced a near-complete purge of dirty (changed) InnoDB pages. How is the disk I/O now you've done that purge?

You might consider increasing the value of innodb_log_file_size if it's not already at about half of the innodb_buffer_pool_size value. InnoDB has to purge dirty pages once the log is filled so you can get a higher dirty page percentage with a larger log file size. That in turn can reduce the number of disk writes you need to do if you're repeatedly writing to the same pages. There's a catch: it also increases the time taken to return to full service after a fault (power failure or whatever) and restart because the log entries must be applied and you've increased the number to apply.