InnoDB Prefetch Pages

The prefetch_pages command may be use to prefetch (retrieve from disk) index and data pages into the buffer pool. The command synchronously waits for all requested pages to be read (fetched) and returns the number of pages read. The goal of prefetching is to very quickly populate the buffer pool and other structures after a server restart. The list of tablespace and page numbers currently cached in the buffer pool can be retrieved using the INNODB_BUFFER_PAGE_BASIC or INNODB_BUFFER_PAGE tables.

The ENGINE_CONTROL function (added as part of the same feature, but more general to allow for additional commands in the future) is used to pass the prefetch_pages to InnoDB. The tablespace number (space_id) and page numbers (offsets) must be provided to prefetch_pages:

ENGINE_CONTROL(InnoDB, prefetch_pages, <tablespace>, <page_number>[, <page_number>...])

The return value of the function call is the number of pages fetched. For example, to prefetch page number 1 from tablespace 0:

mysql> SELECT ENGINE_CONTROL(InnoDB, prefetch_pages, 0, 1);
+----------------------------------------------+
| ENGINE_CONTROL(InnoDB, prefetch_pages, 0, 1) |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

To fetch multiple pages in a single command, pass a comma-delimited list of page numbers:

mysql> SELECT ENGINE_CONTROL(InnoDB, prefetch_pages, 0, 9, 11, 13, 17);
+----------------------------------------------------------+
| ENGINE_CONTROL(InnoDB, prefetch_pages, 0, 9, 11, 13, 17) |
+----------------------------------------------------------+
|                                                        4 |
+----------------------------------------------------------+
1 row in set (0.00 sec)

If an incorrect tablespace number or page number is passed to prefetch_pages, an error will be returned:

mysql> SELECT ENGINE_CONTROL(InnoDB, prefetch_pages, 999, 1);
ERROR 997 (HY000): Invalid control command argument

Normally a reasonably-sized batch of pages would be prefetched, such as a batch of 1,000 pages per tablespace. This allows for InnoDB to efficiently work with multiple background threads to fetch the pages requested.