About the read-ahead mechanism for MySQL buffer pool

Pre-read mechanism

Two kinds of pre-reading algorithms

1. Linear Pre-reading

2. Random Pre-reading

Monitoring of pre-reading

First, pre-reading mechanism　

InnoDB a more important feature for I/O optimization is pre-read, a read-ahead request is an I/O request that asynchronously fetches multiple pages in the buffer pool, which is expected to be needed soon, and these requests are introduced to all pages within a range. InnoDB with 64 page as a extent, InnoDB is the pre-read in page or extent?

When the database requests the data, it will give the read request to the file system, put it into the request queue, and the related process will fetch the read request from the request queue, read the data according to the requirement to the relevant data area (memory, disk), take out the data, put it into the response queue, and finally the database takes the data Complete the data read operation process.

Then the process continues to process the request queue, (if the database is a full table scan, the data read request will fill the request queue), to determine the next few data read request data is adjacent, and then according to its own system IO bandwidth processing volume, pre-read, read request merge processing , One-time reading of multiple pieces of data into the response queue, and then taken away by the database. (So, a physical read operation that implements multiple-page data reads, rrqm>0 (# iostat-x), assuming 4 read-request merges, the RRQM parameter shows 4)

Two kinds of pre-reading algorithms

InnoDB uses two read-ahead algorithms to improve I/O performance: linear pre-reading (linear read-ahead) and random pre-reading (Randomread-ahead)

To distinguish between these two methods of pre-reading, we can put linear pre-reading in extent, and random pre-read into the page in extent. Linear pre-reading focuses on reading the next extent into the buffer pool ahead of time, while the random read-ahead focuses on reading the remaining page in the current extent into buffer pool.

1. Linear Pre-reading (linear read-ahead)

The linear read-ahead method has an important variable that controls whether the next extent is pre-read into the buffer pool, and the time that triggers InnoDB to perform a read-ahead operation is controlled by using the configuration parameter innodb_read_ahead_threshold.

If the sequentially-read page in a extent exceeds or equals the parameter variable, InnoDB asynchronously reads the next extent into the buffer pool, Innodb_read_ahead_ Threshold can be set to any value of 0-64 (because there is only 64 pages in a extent), the default value is 56, the higher the value, the more stringent the access mode check.

Mysql>Show variables like 'Innodb_read_ahead_threshold';+-----------------------------+-------+|Variable_name|Value|+-----------------------------+-------+|Innodb_read_ahead_threshold|  About    |+-----------------------------+-------+

　　For example , if you set the value to 48, INNODB only triggers a linear read-ahead request when the 48 pages in the current extent are sequentially accessed, and the next extent is read into memory. If the value is 8,innodb, the asynchronous read-ahead is triggered, even if only 8 pages in the program segment are accessed sequentially.

You can set the value of this parameter in the MySQL configuration file, or change the parameter dynamically using the command set global requires the Super permission.

Before the variable is reached, when the last page of extent is accessed, InnoDB decides whether to put the next extent in buffer pool.

2. Random pre-reading (Randomread-ahead)

Random pre-reading means that when some page in the same extent is found in the buffer pool, InnoDB will read the remaining page in the extent into the buffer pool.

Mysql>Show variables like 'Innodb_random_read_ahead';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Innodb_random_read_ahead| OFF   |+--------------------------+-------+

Due to the random pre-reading way to InnoDB code to bring some unnecessary complexity, but also in the performance of instability, in 5.5 has already discarded this method of pre-reading , the default is off. To enable this feature, the configuration variable setting Innodb_random_read_ahead is on.

Third, the monitoring InnoDB pre-reading

1. You can display statistics via show engine InnoDB status\g

 mysql>   show engine InnoDB status\g  -- -------------------- BUFFER POOL and   MEMORY  -- --------------------  ... Pages  read  ahead 0.00  / s, evicted without access 0.00  / s, Random read  ahead 0.00  / s ...  

1. Pages Read ahead: Indicates Pages read in per second;

2. Evicted without access: Indicates pages read out per second;

3, the general random pre-reading is closed, that is, 0.

2. Evaluate the validity of the pre-read algorithm through two status values

Mysql>Show Global Status like '%read_ahead%';+---------------------------------------+-------+|Variable_name|Value|+---------------------------------------+-------+|Innodb_buffer_pool_read_ahead_rnd| 0     ||Innodb_buffer_pool_read_ahead| 2303  ||innodb_buffer_pool_read_ahead_evicted| 0     |+---------------------------------------+-------+3Rowsinch Set(0.01Sec

1. Innodb_buffer_pool_read_ahead: Read the number of data pages in the INNODB buffer pool via pre-read (background thread)

2. innodb_buffer_pool_read_ahead_evicted: Pages that have been cleaned by pre-read data pages without being queried for access, invalid read-ahead pages

About the read-ahead mechanism for MySQL buffer pool