mysql innodb 数据预热

nawawa001

　　高负荷mysql数据库突然崩溃之后，在一段时间内，特别是高峰期，整个应用感觉非常卡！
　　这个时候就需要我们对mysql进行预热了！
　　因为mysql热点数据都在innodb buffer pool中，如果mysql突然关闭，buffer pool被清空！所有数据都需要从硬盘加载至内存中的buffer pool，当需要某个数据时，才从硬盘拿取,导致速度异常慢！这个时候，我们可以将一些数据手动加载至buffer pool，称为人为预热！
　　当然，还有一种方式，在mysql正常关闭的时候，将内存中的buffer pool备份值硬盘！在下次开启时，将该备份导入至buffer pool！这是5.6的快速预热特性！前提是正常关闭，如果是突然关闭或者kill -9关闭，就需要手动预热了！
　　简单讲一下几个版本的预热方式！
　　5.0
　　最简单的，只需要在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参数是否减少！
　　5.1
　　对于5.1来说，5.0的方法已经不适用了！
　　当时我们还是可以结合information_schema.tables！用动态sql以及游标组装出select * from db.tables limit 1，写一个预热存储过程！(也可以通过shell/python脚本来写！)
　　提供一个存储预热存储过程！

• 　　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 INTDEFAULT 0;
  
• 　　# 宣告db.table变量
  
• 　　DECLARE p_table VARCHAR(1024);
  
• 　　# 宣告游标
  
• 　　DECLARE cur1 CURSORFOR
  
• 　　SELECT CONCAT(TABLE_SCHEMA,".",table_name) FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE';
  
• 　　# 如果获得'02000'状态，则表示游标已经获取到最后一个,将p_c变量置为NULL
  
• 　　DECLARECONTINUE HANDLER FOR SQLSTATE '02000'SET p_c = NULL;
  
• 　　# 开启游标
  
• 　　OPEN cur1;
  
• 　　# 在开启循环之前，获取第一个值
  
• 　　FETCH cur1 INTO p_Table;
  
• 　　WHILE (p_c ISNOTNULL) DO
  
• 　　# 打印上一个游标值
  
• 　　# select p_Table;
  
• 　　SET @p_Table= p_Table;
  
• 　　# 进行动态语句执行
  
• 　　SET @SQL=CONCAT("select *  from   ",p_Table, " limit 1 ");
  
• 　　PREPARE smtm FROM @SQL;
  
• 　　EXECUTE smtm ;
  
• 　　DEALLOCATEPREPARE smtm;
  
• 　　FETCH cur1 INTO p_Table;
  
• 　　END WHILE;
  
• 　　END$$
  
• 　　DELIMITER ;
  

　　有的时候上面这种该方法不管用，还是得用select count(1) from table_name;
　　5.5
　　和5.1类似的操作
　　5.6
　　如果是正常关闭,可以使用5.6的快速预热备份！
　　你只需在my.cnf里，加入如下：
　　innodb_buffer_pool_dump_at_shutdown = 1
　　解释：在关闭时把热数据dump到本地磁盘。
　　innodb_buffer_pool_dump_now = 1
　　解释：采用手工方式把热数据dump到本地磁盘。
　　innodb_buffer_pool_load_at_startup = 1
　　解释：在启动时把热数据加载到内存。
　　innodb_buffer_pool_load_now = 1
　　解释：采用手工方式把热数据加载到内存。
　　在关闭MySQL时，会把内存中的热数据保存在磁盘里ib_buffer_pool文件中，位于数据目录下。
　　如果是异常关闭，就只能用5.1的手动预热方法了！
　　预热后，查看innodb_buffer_pool是不是变化较大
　　SHOW GLOBAL STATUS LIKE '%innodb_buffer%';
　　Innodb_buffer_pool_pages_data和Innodb_buffer_pool_pages_free是否变化较大！
　　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变化情况