innodb_lru_dump_restore

This patch provide the following new admin commands to control content of LRU through the information_schema. XTRADB_ADMIN_COMMAND.

• XTRA_LRU_DUMP : Dump the content of buffer pool (list of space_id and page_no) to the specific file 'ib_lru_dump' at datadir.

• XTRA_LRU_RESTORE : Read pages based on the 'ib_lru_dump' file.

command lines

....(startup)....

mysql> show status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 18    |
+-------------------------------+-------+
1 row in set (0.00 sec)

....(run workloads)....

mysql> show status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 6231  |
+-------------------------------+-------+
1 row in set (0.01 sec)

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;
+------------------------------+
| result_message               |
+------------------------------+
| XTRA_LRU_DUMP was succeeded. |
+------------------------------+
1 row in set (0.02 sec)

....(restart mysqld)....

mysql> show status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 22    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;
+---------------------------------+
| result_message                  |
+---------------------------------+
| XTRA_LRU_RESTORE was succeeded. |
+---------------------------------+
1 row in set (0.62 sec)  (<--- * It is fast, because it doesn't use direct_io...)

mysql> show status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 6231  |
+-------------------------------+-------+
1 row in set (0.00 sec)

.err file

....
091217 11:49:16 InnoDB: administration command 'XTRA_LRU_DUMP' was detected.
....
091217 11:51:44 InnoDB: administration command 'XTRA_LRU_RESTORE' was detected.
091217 11:51:45 InnoDB: reading pages based on the dumped LRU list was done. (requested: 6231, read: 6209)