- innodb_io_capacity - specifies the IO capacity of the server in IOPs. The default inherited from existing InnoDB behavior is 100. This is used to determine the rate of background IO. Prior to this patch, background IO was rate limited assuming the server could do 100 IOPs. It can help to increase the background IO rate on servers with many disks. This makes it easier for the background IO threads to keep up with a server during high rates of row changes. When this is increased the rates of the following may increase:
- flushing dirty pages
- applying changes from the insert buffer
- physically removing deleted rows
- innodb_extra_dirty_writes - the default for this is ON. When ON, dirty pages may be flushed to disk if the server is idle even when innodb_max_dirty_pages_pct has not been reached. When this is set to off, dirty pages may be written to disk less frequently.
- the order of output for SHOW INNODB STATUS has been changed so that transactions are printed last. The output from SHOW INNODB STATUS is limited in size and I prefer that the active transaction list get truncated rather than the other sections.
- A new section has been added to SHOW INNODB STATUS output that displays the busy-wait time for InnoDB spin locks and statistics on the work done by the main background IO thread.
- The main background IO thread synchronously forces the InnoDB transaction log to disk less frequently when innodb_flush_log_at_trx_commit != 1. I think this code might need to be changed so that the log flush is not done at all rather than getting done asynchronously in that case. Regardless, the main background IO thread forces the log to disk once per second in all cases.
show variables like 'Inno%';Example output added to SHOW INNODB STATUS
srv_master_thread loops: 1 1_second, 0 sleeps, 0 10_second, 0 background, 0 flush
srv_master_thread log flush: 0 sync, 0 async
srv_wait_thread_mics 0 microseconds, 0.0 seconds
spinlock delay for 5 delay 20 rounds is 4 mics