SOLVED Re: high iops with innodb_flush_log_at_trx_commit = 0
In case anyone else is experiencing the same issue, the solution was to set innodb_idle_flush_pct=0 (normally 100). After changing this setting, system iops dropped from the mid 60s to below 10, and often below 5, which is exactly the result I wanted. I also saw fsync system calls drop to a rate of about 2 per minute.
Here is what I think was happening:
The single application using the database was committing transactions at a rate of roughly one per second, with each transaction consisting of 0-5 DML statements. (Some transactions were simply BEGIN; COMMIT;.) The DML statements were simple and quickly completed, so the server spent most of its time idle. However, after executing each transaction, at least one page in the buffer pool was dirty. By default, any time the server is idle and there are dirty pages in the buffer pool, it will use up to innodb_io_capacity iops to flush dirty pages to disk, which should be close to the maximum that the storage hardware can handle.
To flush a dirty buffer pool page to disk, the engine has to perform a number of steps that require synchronous writes to different files:
* If the dirty page corresponds to an open transaction, an entry has to be created in the undo log so the modification can be undone if the server crashes before the transaction is committed, which requires a write and fsync.
* It might require a write and fsync to the redo log. Not too sure about this one.
* It requires a write and fsync to the doublewrite buffer in case the server crashes in the middle of writing the page to the tablespace file, leaving a corrupt page.
* Finally, it requires a write and fsync to the actual tablespace file.
The innodb_idle_flush_pct variable represents the percentage of innodb_io_capacity that should be used for flushing dirty pages when the server is idle. Setting it to 0 tells the server not to flush when it's idle, unless it has to for some other reason, such as the buffer pool becoming too full of dirty pages.
For anyone else who may be new to MySQL performance tuning like me, a few caveats:
* All I know for sure is that changing this setting solved the performance problem I had. Most of this comment is just post hoc theorizing and could be wrong.
* These settings could have negative side effects in other environments, which is probably why they are not defaults:
** After implementing these changes, mysqld took much longer to restart than normal. That is not a problem for me because I almost never restart the server, and long restarts aren't a problem for my application, but it might be a problem for others.
** Allowing dirty pages to accumulate in the buffer pool means the buffer pool could fill up with dirty pages if there were a sudden burst of DML from an application. My application does not do this, but if it were to happen, I imagine that server performance might degrade quite a bit.