也说快速关闭MySQL/InnoDB

如果用的引擎是InnoDB,每次敲下mysqladmin -uroot -p shutdown关闭数据库的时候,总是很难预测这个命令会执行多久,实际经验表明,短则五秒,长则三十分钟一小时都有可能。也分享一下我的经验吧。

1. 为什么InnoDB关闭会慢?

事实上,并不是每次关闭InnoDB都很慢的。Why?InnoDB较之MyISAM,一个重要特性是InnoDB会在内存中开辟一个Buffer Pool来存储最近访问的数据块/索引块,使得下次再次访问这个块时速度能够很快。当InnoDB对需要修改数据块的时候,会先记录修改日志,然后直接对Buffer_Pool中的数据块的操作。记录日志是顺序写,对数据块的操作是内存操作,这让InnoDB在很多场景下有这很好的速度优势。

上面对内存块修改完成后,InnoDB就向客户端返回了。可这时实际磁盘上的数据块,还并没有被更新,我们把这样的page称为Dirty Page。在InnoDB的后台有一个专门的线程来做将内存数据块Flush到磁盘的工作。这个Flush操作就是主要影响InnoDB关闭时间的因素。在关闭MySQL/InnoDB时,所有的Dirty_Page都需要Flush,所以Dirty_Page越多,要Flush的数据块也就越多,意味着InnoDB关闭时间越长

我们可以通过下面的命令来观察Dirty Page的数量:

mysqladmin -uroot ext -i 1 |grep “Innodb_buffer_pool_pages_dirty”
2. 参数innodb_max_dirty_pages_pct

Buffer_Pool中Dirty_Page所占的数量,直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct可以直接控制了Dirty_Page在Buffer_Pool中所占的比率,而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。

所以,在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小,强制数据块Flush一段时间,则能够大大缩短MySQL关闭的时间。

set global innodb_max_dirty_pages_pct=0;

一般执行了上面的命令之后,Dirty_Page的Flush仍需要一段时间,所以要稍等一会儿,再关闭MySQL才有效果。

3. 关闭前做些什么

有时候,就算你改变innodb_max_dirty_pages_pct=0,仍然不能保证InnoDB快速关闭。还有一些注意事项。

设置数据库为只读:如果数据库一直是活跃的,有连接在向里面写数据,那么Dirty Page则还可能不断的产生。

如果是备库,在innodb_max_dirty_pages_pct设置成0的同时,最好先stop slave:这个很关键,而且对关闭时间影响也会很大。第一,主动stop slave后,MySQL在关闭时,需要停止的线程其实是更少了的。第二,如果slave的SQL线程还在执行的话,Buffer Pool则还在活动,Dirty Page也可能还会不断的增多。

一般,如果注意到了上面三点:

set global innodb_max_dirty_pages_pct=0
set global read_only=1
stop slave

关闭数据库,就会很快了。如果你把上面三步做完,然后观察Dirty Page的数量,当数量很少时,再执行命令关库,这样总能保证以较快的速度完成关库命令。

4. 一个注意事项

这里需要注意的是,你不需等到Dirty Page的数量到零,才开始关闭MySQL。因为有时候,即使已经没有活动的会话时,InnoDB的Insert Buffer的合并仍然会产生一些Dirty Page,所以这时你可能会发现,等了很久很久很久Dirty Page的数量仍然大于零。

其实这时,你已经可以快速的关闭数据库了。我怎么判断这种情况呢?这时我们可以通过InnoDB的LSN来判断,下面是SHOW InnoDB Status里面获取的信息:

Log sequence number 814 3121743145 Log flushed up to 814 3121092043 Last checkpoint at 814 2826361389

这里看到,当前的LSN是814 3121743145,最后一个检查点在814 2826361389,也就是说两者相差了3121743145-2826361389=295381756,那么意味着InnoDB还有很多Dirty Page需要Flush。

下面是另一个库的LSN信息:

Log sequence number 0 1519256161 Log flushed up to 0 1519256161 Last checkpoint at 0 1519256161

可以看到,这里的Dirty Page都已经Flush了,那么关闭InnoDB也就很快了。

一般,并不需要等到最后检查点和当前LSN相等才关闭,两者只要相差不多(<1000)关闭起来就很快了。

5. 最后再罗嗦一下

我这个人写博客很罗嗦的,各位看官在忍耐一下吧。

其实,像上面这样折腾,整个关库的过程有可能并不比你直接执行mysqladmin -uroot shutdown快,但是执行上面的步骤,会让你清楚关库到底与多久,可以让你的关库命令能够在一个可以预期的时间内完成。简单的说,会让关库的时间心里有个底。

当你希望一切都心里有底的时候,那你就需要注意上面提到的一些细节。

参考文档

1. How to decrease InnoDB shutdown times

2. 测试快速关闭innodb的方法

3. MySQL/InnoDB Manual

8 responses to “也说快速关闭MySQL/InnoDB”

  1. 别老写mysql,写点八卦吧

  2. M4

    SHOW InnoDB Status 在数据库访问量很大的情况下容易导致mysql crash的吧

  3. @M4 crash? 没这个风险吧? 还没有遇到,是不是MySQL有问题

  4. ruochen

    业务高峰期的时候,show master/slave status可能导致db host hang

  5. xupengfey

    mysql 是不是根据dirty page的数量、pecentage以及io的负荷情况,自动调整flush的数量,这样是不是会比较好呢。

  6. justlooks

    mysql planet上貌似也看到过类似的文章,另外dirty page 应该是计算 flushed 到的LSN和当前LSN的差值,而不是检查点到当前LSN的差值,因为在检查点之后还存在flush to disk的操作呀

  7. 确实遇到过此类问题,找机会试一下效果。顶一个!

  8. 表锁的时候,会导致hang很久的哦

    http://www.sklinux.com求个友情链接

Leave a Reply

Your email address will not be published. Required fields are marked *