加载中…
个人资料
果果爸
果果爸
  • 博客等级:
  • 博客积分:0
  • 博客访问:233,583
  • 关注人气:29
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

XtraDB feature: save / restore buffer pool

(2010-09-09 16:19:59)
标签:

mysql

innodb/xtradb

buffer

pool

杂谈

分类: mysql

We recently released XtraDB-9, and while we did not highlight it in announcement, the release-making feature is ability to save and restore InnoDB buffer pool.
The idea is not new and was originally developed by Jeremy Cole (sorry, I do not have the link on hands) some time ago, and now we implemented it in XtraDB.

Why would we need to save and restore content of buffer pool ?
There are several reasons.
First, it’s not rate on modern servers to have 32GB+ of RAM, with allocated InnoDB buffer_pool 26GB or more. When you do restart of server, it may take long time to populate cache with useful data before you can bring it back to serve production load. It’s not rare to see
maintenance cycle takes two or more hours, mainly because the slave need to catchup with master and to warm cache.
In case with the server crash, it is even worse, you need to wait possible long time on InnoDB
recovery (we have the patch for that too, in that post you can see InnoDB recovery took 1h to accomplish) and after that warm caches.

Second, it is useful for some HA schemas, like DRBD, when, in case of failover, you need to start passive instance on cold.

So let’s see what results we have.
Details about patch you can get there http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_lru_dump_restore (Yasufumi names it LRU dump/restore, because he thinks about buffer pool as about LRU list, which how it is internally).

To save buffer pool you execute
select * from information_schema.XTRADB_ADMIN_COMMAND ;

and to restore
select * from information_schema.XTRADB_ADMIN_COMMAND ;

it will create/read file ib_lru_dump from your database directory.

You may want to sort ib_lru_dump in order of pages in tablespaces, so RESTORE will be
performed in most sequential way. The small python script lrusort.py to sort ib_lru_dump is available
in our Launchpad branch lp:~percona-dev/percona-xtradb/extensions-1.0.6

I made small tpcc benchmark to show effect with restored buffer_pool (the condition of
benchmarks are the same as in my runs on fast storages, and I
used RAID10 to store InnoDB files).
First run (xtradb cold) I made just after restart and ran it for 1h.
After that I saved buffer_pool, restarted mysqld, restored buffer_pool ( it took about 4 min
to load 26GB worth of data), and run (xtradb warm) tpcc again.

Here is graphical results (results in New Transactions Per 10 sec, more is better):

tpcc_1000w

As you see in the cold run it took 1500-1800 sec to enter into stable mode, while
it warm run it happened almost from start. There was some period of unstable results, but it
did not affect ability to serve load.

You are welcome to test it, it is available in XtraDB-9 release and also in MariaDB 5.1.41-RC.

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有