高负荷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变化情况