How do you know when InnoDB gets behind on IO?

Hopefully, someone will write a book about InnoDB one day because it is beautiful. Most of the complexity is hidden by adaptive algorithms that work. Sometimes you need to understand some of the complexity. Peter Zaitsev has written about this as well. The blog post is worth reading.

InnoDB uses an insert buffer to reduce IO for secondary index maintenance on updates and inserts. This is a persistent index of pending changes that must be done for secondary indexes. When a server gets busy, the insert buffer may grow. This line from SHOW INNODB STATUS displays the size of the insert buffer. When it is large, there is more pending IO to be done to flush changes from the insert buffer to the secondary indexes.

 Ibuf: size 37, free list len 70989, seg size 71027,

InnoDB does not remove deleted rows from a table until they cannot be read by any other transaction. This is done by a background thread. Deleted rows that have not been purged artificially increase the size of a table. They also increase the overhead of table scan operations. This line from SHOW INNODB STATUS displays the amount of work pending for the purge operation.

History list length 30

There are two times when you might want to know the value of these variables. When these values are growing, there is more pending IO to be done by background threads. The rate at which the background threads perform this IO is determined by whether the server is currently busy or idle and by an assumption that the server can do 100 IOs per second.

1. When an application scans a table in primary key order concurrent with another application that deletes rows in primary key order, the scanning application will encounter rows that have been deleted but not yet removed. If the purge by the background thread does not keep up with the delete application, then the scan application may run much slower than expected. The innodb_max_purge_lag configuration variable may help with this. But the first time you have this problem, it might take a while to recognize.
2. When batch application run concurrent with online applications, it is good to rate limit the work done by the batch apps and these variables can be used to determine when the batch app show slow down.

Finally, there are a couple of changes that can make this easier:

1. Display the history list length value in SHOW STATUS so we don't have to parse SHOW INNODB STATUS.
2. Display the insert buffer size in SHOW STATUS as well.
3. Make the IO capacity of the server a configurable value and derive the max rate of background IO from these threads. This is hardwired to be 100 IOs per second today. Because of this, a server with many disks will take much longer than necessary to flush pending IOs from the insert buffer and to purge deleted rows.