14.16.2.1 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

A read-ahead request is
an I/O request to prefetch multiple pages in the
buffer pool
asynchronously, in anticipation that these pages will be needed
soon. The requests bring in all the pages in one
extent. InnoDB uses two
read-ahead algorithms to improve I/O performance:

Linear read-ahead is a
technique that predicts what pages might be needed soon based on
pages in the buffer pool being accessed sequentially. You
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 configuration parameter
innodb_read_ahead_threshold.
Before this parameter was added, InnoDB would only calculate
whether to issue an asynchronous prefetch request for the entire
next extent when it read in the last page of the current extent.

The configuration parameter
innodb_read_ahead_threshold
controls how sensitive InnoDB is in detecting patterns of
sequential page access. If the number of pages read sequentially
from an extent is greater than or equal to
innodb_read_ahead_threshold,
InnoDB initiates an asynchronous read-ahead operation of the
entire following extent. It can be set to any value from 0-64.
The default value is 56. 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. You can set the value of this parameter in the
MySQL configuration
file, or change it dynamically with the SET
GLOBAL command, which requires the
SUPER privilege.

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.

Random read-ahead functionality was removed from the
InnoDB Plugin (version 1.0.4) and was
therefore not included in MySQL 5.5.0 when InnoDB
Plugin became the “built-in” version of
InnoDB. Random read-ahead was reintroduced in
MySQL 5.1.59 and 5.5.16 and higher along with the
innodb_random_read_ahead
configuration option, which is disabled by default. To enable
this feature, set the configuration variable
innodb_random_read_ahead to
ON.