mysql预热方式_快速预热Innodb Buffer Pool的方法

一：预热Buffer Pool

快速预热Innodb Buffer Pool的方法:

1.Mysql 5.0.x

最简单，只需要在information_schema.tables依次做一次count统计即可。

SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

select count(1) from table_name;

然后SHOW STATSU 查看buffer pool中free页，即Innodb_buffer_pool_pages_free参数是否减少！

2.Mysql 5.1.x

对于5.1,5.0的方法已经不适用了！

我们还是可以结合information_schema.tables！用动态sql以及游标组装出select * from db.tables limit 1; 但是最好使用select count(1) from table_schema.table_name;

于是我写了下面的存储过程来预热BP：

DELIMITER $$

USE `mysql`$$

DROPPROCEDURE IF EXISTS `Proc_MySQL_Warmup`$$

CREATE DEFINER=`root`@`%` PROCEDURE`Proc_MySQL_Warmup`(

)

COMMENT '预热mysql存储过程'

BEGIN

--############################################################

--#1.先从information_schema.tables中找出所有表

--#2.利用游标进行获取

--#3.动态语句组装sql

--#4.依次执行select *from db.tables limit 1;

--#############################################################

--#宣告游标变量

DECLARE p_c INT DEFAULT 0;

--#宣告db.table变量

DECLARE p_table VARCHAR(1024);

--#宣告游标

DECLARE cur1 CURSOR FOR SELECTCONCAT(TABLE_SCHEMA,".",table_name) FROM information_schema.tablesWHERE TABLE_TYPE='BASE TABLE';

--#如果获得'02000'状态，则表示游标已经获取到最后一个,将p_c变量置为NULL

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET p_c = NULL;

--#开启游标

OPEN cur1;

--#在开启循环之前，获取第一个值

FETCH cur1 INTO p_Table;

WHILE (p_c IS NOT NULL) DO

--#打印上一个游标值

--# select p_Table;

SET @p_Table= p_Table;

--#进行动态语句执行

SET @SQL=CONCAT("select count(1)from  ",p_Table);

PREPARE smtm FROM @SQL;

EXECUTE smtm ;

DEALLOCATE PREPARE smtm;

FETCH cur1 INTO p_Table;

END WHILE;

CLOSE cur1;

END$$

DELIMITER ;

3.Mysql 5.5.x

同上5.1.x 。

4.Mysql 5.6.x

MySQL 5.6中，可以将buffer pool的内容(文件页的索引)dump到文件中，然后快速load到buffer pool中。

MyISAM预热:

有专用命令进行预热：

LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

将t1的全部索引加入cache，t2出子叶以外的索引加入缓冲。

还可以对分区进行缓冲

LOAD INDEX INTO CACHE pt PARTITION (p1, p3);

具体可以参考http://dev.mysql.com/doc/refman/5.5/en/load-index.html

预热之后可以查看key_buffer_size中缓冲区使用情况。

查看Key_blocks_unused和Key_blocks_used变化情况。

二：其他方法

* 2.1根据实际的工作中总结了一些预热buffer pool的SQL语句，也就是通过人为模拟一些请求，尽可能地将我们所需的数据块和索引加载到内存中。

1.加载主键索引

select count(*) from tbl whereno_index_col=0;

2.加载非主键索引

select count(*) from tbl where index_col like“%0%”;

3.加载BLOB/TEXT列

select count(*) from tbl where blob_col like“%0%”；

4.分段加载大表数据

select count(*) from tbl where id between 1and 10000000 and no_index_col=0;

还有人使用blackhole引擎来进行预热操作，通过替换不同的索引字段进行排序查询，将所需数据加载。

create table temp_blackhole like tbl;

alter table temp_blackhole engine=blackhole;

select * from tbl order by id intotemp_blackhole;

2.2现在，Percona已经完全在XtraDB中实现这个功能，最初采用Share Memory Buffer Pool，目前采用Dump/Restore ofthe Buffer Pool。

1.Share Memory Buffer Pool

该方法在内存个划出一个共享段用于存储buffer pool，使得这块空间在mysqld启动关闭前后都能备份访问，不会因为mysqld关闭而将buffer pool的内容清除。但是，共享内存的使用必须遵守严格的要求：innodb引擎前后保持一致；innodb的page页大小前后保持一致；innodb_buffer_pool_size的大小前后保持一致。否则，会遇到问题，这时就不能在使用共享空间，需要手动将其删除。

innodb_buffer_pool_shm_key：用于是否开启共享内存的使用，0开启，1不开启。

innodb_buffer_pool_shm_checksum：检查共享空间中存储的buffer pool是否有损坏。

/proc/sys/kernel/shmmax― 内存共享空间的大小可以通过系统下的该文件去调整。

2.Dump/Restore of the Buffer Pool

从名字就可以知道这个预热方法，先将buffer pool的内容dump出来，在数据目录下会生成一个ib_lru_dump的文件，然后需要是再通过这个dump文件将其恢复回去，这也正是我们最初的一个想法。下面简单说下它的实现过程，先需要了解下Innodb buffer pool。

Innodb Buffer Pool是内存中的一段存储空间，由大小为16KB的page页组成，是一个列表页，该列表按照LRU的顺序进行排列，所以也被叫作LRU列表。buffer pool被分成两个子列，前半段被称为’young’，也就是经常被访问到的块，而后半段成为’old’，访问次数较少的块。当有新的块被加载进来的时候，会被插入到两端之间的位置，之后会随着被访问的频繁程度，是保持迁移还是不断后退，最后被替换出去。

在了解了Innodb Buffer Pool之后，让我么来看看dump操作。我们知道磁盘的查找更多是无序的随机的操作，而加载到内存中的page会被重新组织成有序的排列。XTRA_LRU_DUMP的操作并不是简单的将innodb_buffer_pool中的所有内容备份到ib_lru_dump文件中，这样会随着innodb_buffer _pool_size的大小而变得很大，进而备份恢复时间也会变得不很乐观，那么怎么做呢？事实上，只需备份page在buffer pool中的标志信息即可，即(space_id,page_no)这样一个列表，space_id表示page在buffer pool中LRU的排列顺序，page_no表示page在磁盘上的存储位置。恢复时，按照page_no去磁盘中找到对应的page，而这个可以按照page_no的顺序去查找，可以近似看作是顺序i/o，尽量避免随机i/o的消耗，然后将查找到的page按照space_id存到最初在LRU列表中的位置，最后实现恢复到关闭服务之前的状态。这个方法提供了自动和手动两种实现操作。

自动参数设置：

innodb_buffer_pool_restore_at_startup/innodb_auto_lru_dump― 该参数可以控制是否开启自动dump/restore的操作，只能通过手动完成；当为0时，表示不开启自动操作，当为非0时，表示按照指定的时间周期dump操作，重启后自动完成restore的操作。

P.S.:该特性的选项innodb_auto_lru_dump是在XtraDB的5.5.10-12.1版本中被引入，到5.5.10-20.1起将其重名为innodb_buffer_pool_restore_at_startup。

手动操作如下：

mysql> show status like'innodb_buffer_pool_pages_data';― 查看重启前后当前page页的个数

mysql> select * frominformation_schema.XTARDB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;― 备份innodb_buffer_pool

mysql> select * from information_schema.XTARDB_ADMIN_COMMAND/*!XTRA_LRU_RESTORE*/;― 恢复innodb_buffer_pool

P.S.:以上手动备份恢复的操作语句还没有加入到XtraDB的任何版本中，只是在Percona的test环境中实现了。