7.7. Changes in the Read Ahead Algorithm

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 in the near future. InnoDB has historically used two read ahead algorithms to improve I/O performance.

Random read-ahead is a technique that predicts when pages might be needed soon based on pages already in the buffer pool, regardless of the order in which those pages were read. If 13 consecutive pages from the same extent are found in the buffer pool, InnoDB asynchronously issues a request to prefetch the remaining pages of the extent. This feature was initially removed from InnoDB starting with InnoDB Plugin 1.0.4 and turned off with MySQL 5.5. It is available once again starting in MySQL 5.1.59 and 5.5.16 and higher, turned off by default. To enable this feature, set the configuration variable innodb_random_read_ahead.

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 will issue an asynchronous prefetch request for the entire next extent when it reads in the last page of the current extent. Beginning with InnoDB Plugin 1.0.4, users can control when InnoDB performs a read ahead, 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 will initiate an asynchronous read ahead 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 will be the access pattern check. For example, if you set the value to 48, InnoDB will trigger 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 Plugin 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.

User Comments
Sign Up Login You must be logged in to post a comment.