A read-ahead request is an I/O request to prefetch multiple pages in the buffer cache asynchronously, in anticipation that these pages will be needed soon. InnoDB has historically used two read-ahead algorithms to improve I/O performance.

Random read-ahead is done if a certain number of pages from the same extent (64 consecutive pages) are found in the buffer cache. In such cases, InnoDB asynchronously issues a request to prefetch the remaining pages of the extent. Random read-ahead added unnecessary complexity to the InnoDB code and often resulted in performance degradation rather than improvement. Starting with InnoDB storage engine 1.0.4, this feature has been removed from InnoDB, and users should generally see equivalent or improved performance.

Linear read-ahead is based on the access pattern of the pages in the buffer cache, not just their number. In releases before 1.0.4, if most pages belonging to some extent are accessed sequentially, InnoDB issues an asynchronous prefetch request for the entire next extent when it reads in the last page of the current extent. Beginning with InnoDB storage engine 1.0.4, users can control when InnoDB performs a read-ahead operation, by adjusting the number of sequential page accesses required to trigger an asynchronous read request using the new configuration parameter innodb_read_ahead_threshold.

If the number of pages read from an extent of 64 pages is greater or equal to innodb_read_ahead_threshold, InnoDB initiates an asynchronous read-ahead operation of the entire following extent. Thus, this parameter controls how sensitive InnoDB is to the pattern of page accesses within an extent in deciding whether to read the following extent asynchronously. The higher the value, the more strict the access pattern check. For example, if you set the value to 48, InnoDB triggers a linear read-ahead request only when 48 pages in the current extent have been accessed sequentially. If the value is 8, InnoDB would trigger an asynchronous read-ahead even if as few as 8 pages in the extent were accessed sequentially.

The new configuration parameter innodb_read_ahead_threshold may be set to any value from 0-64. The default value is 56, meaning that an asynchronous read-ahead is performed only when 56 of the 64 pages in the extent are accessed sequentially. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini), or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

Starting with InnoDB storage engine 1.0.5 more statistics are provided through SHOW ENGINE INNODB STATUS command to measure the effectiveness of the read-ahead algorithm. See Section 8.9, “More Read Ahead Statistics” for more information.