Warming up the InnoDB Buffer Pool during start-up

Abstract: Heating up the InnoDB Buffer Pool during the MySQL instance startup should significantly improve InnoDB Performance in the beginning of the life of the Instance. This is achieved by sequential scans of the needed data instead of random I/O reads which would happen when we just let the system work it out by itself.
How to find the database objects which can be loaded during MySQL start-up and how to load them automatically is described in this article.

New Table in the INFORMATION_SCHEMA

Some of my colleagues have already described methods on how to heat up a Slave after its startup. [1, 2, 3]

With the Release v5.6 of MySQL there is a new table in the INFORMATION_SCHEMA called INNODB_BUFFER_PAGE. This table contains the information about all pages currently located in the InnoDB Buffer Pool.

A rough overview over the InnoDB Buffer Pool you can get with the following statement:

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 5455   |
| Innodb_buffer_pool_pages_dirty   | 186    |
| Innodb_buffer_pool_pages_free    | 2134   |
| Innodb_buffer_pool_pages_misc    | 603    |
| Innodb_buffer_pool_pages_total   | 8192   |
+----------------------------------+--------+

Similar information you will also get with the following query:

SELECT page_state, COUNT(page_state)
  FROM innodb_buffer_page
 GROUP BY page_state;

+------------+-------------------+
| page_state | count(page_state) |
+------------+-------------------+
| FILE_PAGE  |              5455 |
| MEMORY     |               603 |
| NOT_USED   |              2134 |
+------------+-------------------+

Or in other words the 8192 pages times 16k (InnoDB block size) are equal to the size you specified for your InnoDB Buffer Pool (innodb_buffer_pool_size).

Gathering the data

With these information we can easily find out how many pages of which database object is currently located in the InnoDB Buffer Pool. The following Query gives us the top 10 database objects:

SELECT SUBSTRING_INDEX(table_name, '/', 1) AS schema_name
     , SUBSTRING_INDEX(table_name, '/', -1) AS table_name
     , IF(index_name = 'PRIMARY', SUBSTRING_INDEX(table_name, '/', -1)
     , IF(index_name = 'GEN_CLUST_INDEX', SUBSTRING_INDEX(table_name, '/', -1), index_name)) AS object_name
     , IF(index_name = 'PRIMARY', 'TABLE', IF(index_name = 'GEN_CLUST_INDEX', 'TABLE', 'INDEX')) AS object_type
     , COUNT(*) AS cnt
  FROM innodb_buffer_page
 WHERE SUBSTRING_INDEX(table_name, '/', 1) IS NOT NULL
 GROUP BY schema_name, table_name, object_name, object_type
 ORDER BY cnt DESC
 LIMIT 10;

+-------------+--------------+----------------+-------------+------+
| schema_name | table_name   | object_name    | object_type | cnt  |
+-------------+--------------+----------------+-------------+------+
| zabbix      | history_uint | history_uint   | TABLE       | 2190 |
| zabbix      | history_uint | history_uint_1 | INDEX       | 1202 |
| zabbix      | trends_uint  | trends_uint    | TABLE       |  362 |
| zabbix      | history      | history        | TABLE       |  205 |
| zabbix      | history      | history_1      | INDEX       |  199 |
| zabbix      | trends       | trends         | TABLE       |   66 |
| zabbix      | history_str  | history_str    | TABLE       |   54 |
| test        | test         | test           | TABLE       |   41 |
| zabbix      | history_str  | history_str_1  | INDEX       |   39 |
| zabbix      | auditlog     | auditlog       | TABLE       |   26 |
+-------------+--------------+----------------+-------------+------+

With the following command we can find the structure about how the table looks like:

SHOW CREATE TABLE zabbix.history_uint\G

CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

To load the index and the table we can run the following Queries:

SELECT * FROM history_uint;

and

SELECT itemid, clock FROM history_uint;

To verify that the queries do, what we think they do, we can verify the Query Execution Plan (QEP) with EXPLAIN:

+----+-------------+--------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows     | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | history_uint | ALL  | NULL          | NULL | NULL    | NULL | 21442045 |       |
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------+

+----+-------------+--------------+-------+---------------+----------------+---------+------+----------+-------------+
| id | select_type | table        | type  | possible_keys | key            | key_len | ref  | rows     | Extra       |
+----+-------------+--------------+-------+---------------+----------------+---------+------+----------+-------------+
|  1 | SIMPLE      | history_uint | index | NULL          | history_uint_1 | 12      | NULL | 21443371 | Using index |
+----+-------------+--------------+-------+---------------+----------------+---------+------+----------+-------------+

When we know that only a certain part of the table or index is used (in the InnoDB Buffer Pool) we can restrict the range with a WHERE. But this could have an impact on the QEP. So we possibly have to force the QEP with FORCE INDEX or IGNORE INDEX accordingly.

Hooking into MySQL start-up procedure

Now when we know which database objects (tables or indexes) should be loaded into the InnoDB Buffer Pool to heat it up during starting of the instance we can add them to a SQL script which we hook into the init_file start-up trigger of MySQL:

# my.cnf
[mysqld]
init_file = innodb_buffer_pool_heatup.sql

and

--
-- innodb_buffer_pool_heatup.sql
--
SELECT * FROM zabbix.history_uint;
SELECT itemid, clock FROM zabbix.history_uint;
SELECT * FROM zabbix.trends_uint;
SELECT * FROM zabbix.history;
SELECT itemid, clock FROM zabbix.history;

When we look at the I/O system during the instance start-up we see that the server does a sequential data read (31 Mbyte/s) wit a request size of 862 kbyte:

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00   74.00    0.00 31896.00     0.00   862.05     1.59   21.08   7.97  59.00

An average request size of 862 kbyte gives us roughly 54 x 16k InnoDB blocks. This is what we want: a sequential scan instead of a random block fetch.

Restrictions

Be careful to not overload your Buffer Pool otherwise you do just too much unnecessary work and flush just loaded pages again.

I have not found a way to get out these information from MariaDB 5.2.x or Percona Server 20.4 in the short time I spent for investigations. Please correct me when I am wrong. They have some InnoDB internal tables in the INFORMATION_SCHEMA but I did not figure out how collect the wanted information.