percona save/restore buffer pool特性記錄

shiri512003發表於2010-03-10

作者: 絃樂之花 | 可以轉載, 但必須以超連結形式標明文章原始出處和作者資訊及版權宣告

連結:http://shiri512003.itpub.net/post/37713/497336

[@more@]

以前一篇文章提到關於percona提供的 save / restore buffer pool 特性(http://shiri512003.itpub.net/post/37713/497158),這個特性可以快速hot系統,著實是個不錯的idea,這個特性暫時還沒有被innodb plugin引入,因為這個特性是plugin最新版本1.0.6之後釋出的啦,不過percona xtradb-1.0.6-9 based innodb plugin 1.0.6已經包含了這個補丁了。這裡記錄下關於這個特性使用的詳細說明。

注:以下內容版權歸percona所有

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

  • 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)  ( 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)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7591490/viewspace-1031724/,如需轉載,請註明出處,否則將追究法律責任。

相關文章