MySQL之突然执行变慢

为何会有这种情况？

这种情况的发生，有可能是MySQL把内存中脏页的数据写入到磁盘中引起的。

那么何为脏页？

脏页的意思就是内存中的数据页跟磁盘中的数据页内容不一致，这内存中的页就被称为脏页。同理可得，如果内存中的数据页和磁盘中的数据页内容一致，就称为干净页。

抖的原因

这个可能就发生在把内存中脏页的数据更新到磁盘中数据页数据的过程。这个过程中涉及到redo log这个日志，关于这个redo log的相关文章可以看下这个Mysql角色与日志初体验

更新脏页的过程在什么情况下会出现?

• 第一种场景，redo log已经写满，这个时候系统会停止所有更新操作。把脏页数据刷新到磁盘中。
• 第二种场景，内存已满，当内存不够用的时候，这个时候就读不了数据页，所以需要把内存中的一些数据页给淘汰，如果淘汰的是脏页，就会把脏页数据刷新到磁盘中
• 第三种场景，MySQL空闲的时候，把内存中的数据刷新到磁盘中
• 第四种场景，MySQL正常关闭的时候，就会把内存中的数据刷新到磁盘中

上述四种场景对性能方面的影响?

第三、第四种场景。属于正常情况。所以对性能不影响，主要来看第一、第二种情况。

第一种情况，刷新脏页到磁盘中的时候，会导致整个系统不能再接受更新，性能影响很大。

第二种情况，内存不够用，要把数据页写到磁盘（淘汰）。InnoDB中用缓冲池（buffer pool）来管理内存，缓冲池中的数据页在内存中有三种状态：

• 还没有使用的内存页
• 干净页（在淘汰的时候，直接释放复用）
• 脏页（在淘汰的时候，先把数据写到磁盘中，再释放复用）

在一个查询中，如果数据页不在内存中，那就需要把磁盘中的数据页读取到内存中，这个时候就有可能引发内存不足的情况。如果这个查询导致淘汰的数据页太多，对性能的影响就很大。

InnoDB刷脏页的控制策略
先来看几个参数：

• innodb_io_capacity，这个参数能够告诉InnoDB的你主机的磁盘能力，这个值可设置为IOPS，通过以下指令来查询IOPS值：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

这个值的设置要适当，如果太低的话InnoDB会认为你的磁盘读写能力很差，这个时候将脏页数据同步到磁盘中的速度就会变得很慢。
虽然我们已经定义全力刷脏页的行为，但是毕竟磁盘不止是用来同步脏页的数据，还需要完成其他业务请求的。所以接下来我们来看看InnoDB关于控制刷脏页的速度的策略。

InnoDB控制刷脏页的速度的策略

首先说到速度，先来看看速度慢可能引发的情况：1、内存中脏页太多 2、redo log写满

根据上面的两种情况，可得出要考虑两个因素：1、内存中的脏页比例。2、redo log的写盘速度

MySQL会根据这两个因素单独算出两个数字，看看这两个数字怎么算出来的

1. 数字1：有一个参数innodb_max_dirty_pages_pct是脏页比例上限，默认是75%，Mysql会根据当前的脏页比例，算出一个在0-100之间的数字，这个就是数字1，记为F(1)。
2. 数字2：InnoDB每次写日志都有一个序号，写入的序号跟checkpoint之间的差值，设为N，InnoDB根据这个N算出一个范围在0-100之间的数字，这就是数字2，记为F(2)

可以看出，数字1是与内存中脏页的比例相关的，数字2是与redo log的写盘速度相关的。InnoDB会根据innodb_io_capacity的能力乘R%来控制脏页的速度。
上述中提到一个脏页比例，Mysql中可以通过以下方式来查看脏页比例。

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

MySQL中还有一个连坐机制，如果在同步一个脏页的时候，这个脏页的隔壁也是脏页，这个时候就会把隔壁的脏页也给同步了。这种行为可能会导致效率更低下，可以通过innodb_flush_neighbors参数来控制这个行为，值为0的时候表示不会连坐。

参考《MySQL实战》