[玩转MySQL之十]InnoDB Buffer Pool详解

一、前言

MySQ InnoDB Buffer Pool，从字面意思理解就是:MySQL InnoDB缓冲池，既然是缓冲池，那么里面应该缓存着大量的数据，使CPU读取或者写入数据时，不直接和低速的磁盘打交道，直接和缓冲区进行交互，从而解决了因为磁盘性能慢导致的数据库性能差的问题，弥补了两者之间的速度差异。但小编就会有些疑问:

• Innodb buffer pool有哪些需要提前了解的基础知识？
• Innodb buffer pool中存的具体是什么内容？
• Innodb bufferp pool中的数据是如何加载进去的？
• InnoDB如何管理buffer pool的？
• Innodb Buffer Pool 有哪些配置项？

二、基础知识

2.1 Buffer Pool Instance

Buffer Pool实例，大小等于innodb_buffer_pool_size/innodb_buffer_pool_instances，每个Buffer Pool Instance都有自己的锁，信号量，物理块(Buffer chunks)以及逻辑链表(List)。即各个instance之间没有竞争关系，可以并发读取与写入。所有instance的物理块(Buffer chunks)在数据库启动的时候被分配，直到数据库关闭内存才予以释放。每个Buffer Pool Instance有一个page hash链表，通过它，使用space_id和page_no就能快速找到已经被读入内存的数据页，而不用线性遍历LRU List去查找。注意这个hash表不是InnoDB的自适应哈希，自适应哈希是为了减少Btree的扫描，而page hash是为了避免扫描LRU List。

当innodb_buffer_pool_size小于1GB时候，innodb_buffer_pool_instances被重置为1，主要是防止有太多小的instance从而导致性能问题。

2.2 数据页

InnoDB中，数据管理的最小单位为页，默认是16KB，页中除了存储用户数据，还可以存储控制信息的数据。InnoDB IO子系统的读写最小单位也是页。

2.3 Buffer Chunks

包括两部分：数据页和数据页对应的控制体，控制体中有指针指向数据页。Buffer Chunks是最低层的物理块，在启动阶段从操作系统申请，直到数据库关闭才释放。通过遍历chunks可以访问几乎所有的数据页，有两种状态的数据页除外：没有被解压的压缩页(BUF_BLOCK_ZIP_PAGE)以及被修改过且解压页已经被驱逐的压缩页(BUF_BLOCK_ZIP_DIRTY)。此外数据页里面不一定都存的是用户数据，开始是控制信息，比如行锁，自适应哈希等。

2.4 逻辑链表

链表节点是数据页的控制体(控制体中有指针指向真正的数据页)，链表中的所有节点都有同一的属性，引入其的目的是方便管理。Innodb Buffer Pool 相关的链表有:

2.4.1 Free List

其上的节点都是未被使用的节点，如果需要从数据库中分配新的数据页，直接从上获取即可。InnoDB需要保证Free List有足够的节点，提供给用户线程用，否则需要从FLU List或者LRU List淘汰一定的节点。InnoDB初始化后，Buffer Chunks中的所有数据页都被加入到Free List，表示所有节点都可用。

2.4.2 LRU List

近期最少使用链表(Least Recently Used)，这个是InnoDB中最重要的链表。所有新读取进来的数据页都被放在上面。链表按照最近最少使用算法排序，最近最少使用的节点被放在链表末尾，如果Free List里面没有节点了，就会从中淘汰末尾的节点。LRU List还包含没有被解压的压缩页，这些压缩页刚从磁盘读取出来，还没来得及被解压。LRU List被分为两部分，默认前5/8为young list，存储经常被使用的热点page，后3/8为old list。新读入的page默认被加在old list头，只有满足一定条件后，才被移到young list上，主要是为了预读的数据页和全表扫描污染buffer pool。

2.4.3 FLU List

这个链表中的所有节点都是脏页，也就是说这些数据页都被修改过，但是还没来得及被刷新到磁盘上。在FLU List上的页面一定在LRU List上，但是反之则不成立。一个数据页可能会在不同的时刻被修改多次，在数据页上记录了最老(也就是第一次)的一次修改的lsn，即oldest_modification。不同数据页有不同的oldest_modification，FLU List中的节点按照oldest_modification排序，链表尾是最小的，也就是最早被修改的数据页，当需要从FLU List中淘汰页面时候，从链表尾部开始淘汰。加入FLU List，需要使用flush_list_mutex保护，所以能保证FLU List中节点的顺序。

2.4.4 Unzip LRU List

这个链表中存储的数据页都是解压页，也就是说，这个数据页是从一个压缩页通过解压而来的。

2.4.5 Zip Clean List

这个链表只在Debug模式下有，主要是存储没有被解压的压缩页。这些压缩页刚刚从磁盘读取出来，还没来的及被解压，一旦被解压后，就从此链表中删除，然后加入到Unzip LRU List中。

2.4.6 Zip Free

压缩页有不同的大小，比如8K，4K，InnoDB使用了类似内存管理的伙伴系统来管理压缩页。Zip Free可以理解为由5个链表构成的一个二维数组，每个链表分别存储了对应大小的内存碎片，例如8K的链表里存储的都是8K的碎片，如果新读入一个8K的页面，首先从这个链表中查找，如果有则直接返回，如果没有则从16K的链表中分裂出两个8K的块，一个被使用，另外一个放入8K链表中。

2.6 Frame

帧，16K的虚拟地址空间， 在缓冲池的管理上，整个缓冲区是是以大小为16k的frame(可以理解为数据块)为单位来进行的，frame是innodb中页的大小。

2.7 Page

页，16K的物理内存， page上存的是需要保存到磁盘上的数据， 这些数据可能是数据记录信息， 也可以是索引信息或其他的元数据等；

2.8 Control Block

控制块，对于每个frame, 对应一个block， block上的信息是专门用于进行frame控制的管理信息， 但是这些信息不需要记录到磁盘，而是根据读入数据块在内存中的状态动态生成的， 主要包括：

• 1. 页面管理的普通信息，互斥锁， 页面的状态等
• 2. 脏回写(flush)管理信息
• 3. lru控制信息
• 4. 快速查找的管理信息， 为了便于快速的超找某一个block或frame， 缓冲区里面的block被组织到一些hash表中; 缓冲区中的block数量是一定的， innodb缓冲区对所管理的block用lru(last recently used)策略进行替换。

2.9 Buffer Pool分配方式

MySQL使用mmap分配Buffer Pool，但是都是虚存，在top命令中占用VIRT这一列，而不是RES这一列，只有相应的内存被真正使用到了，才会被统计到RES中，从而提高内存使用率。这就是为什么常常看到MySQL一启动就被分配了很多的VIRT，而RES却是慢慢涨上来的原因。这里大家可能有个疑问，为啥不用malloc。其实查阅malloc文档，可以发现，当请求的内存数量大于MMAP_THRESHOLD(默认为128KB)时候，malloc底层就是调用了mmap。在InnoDB中，默认使用mmap来分配。 分配完了内存，buf_chunk_init函数中，把这片内存划分为两个部分，前一部分是数据页控制体(buf_block_t)，后一部分是真正的数据页，按照UNIV_PAGE_SIZE分隔。假设page大小为16KB，则数据页控制体占的内存:数据页约等于1:38.6，也就是说如果innodb_buffer_pool_size被配置为40G，则需要额外的1G多空间来存数据页的控制体。 划分完空间后，遍历数据页控制体，设置buf_block_t::frame指针，指向真正的数据页，然后把这些数据页加入到Free List中即可。初始化完Buffer Chunks的内存，还需要初始化BUF_BLOCK_POOL_WATCH类型的数据页控制块，page hash的结构体，zip hash的结构体(所有被压缩页的伙伴系统分配走的数据页面会加入到这个哈希表中)。注意这些内存是额外分配的，不包含在Buffer Chunks中。

2.10 互斥访问

缓冲池的整个缓冲区一个数据结构buf_pool进行管理和控制， 一个专门的mutex保护着， 这个mutex是用来保护buf_pool这个控制结构中的数据域的， 并不保护缓冲区中的数据frame以及用于管理的block, 缓冲区里block或者frame中的访问是由专门的读写锁来保护的， 每个block/frame一个。在5.1以前， 每个block是没专门的mutex保护的，如果需要进行互斥保护，直接使用缓冲区的mutex, 结果导致很高的争用； 5.1以后，每个block一个mutex对其进行保护， 从而在很大程度上解缓了对buf_pool的mutex的争用。

三、 Buffer Pool 存储内容

BUffer Pool中缓存的数据页类型有: 索引页、数据页、undo页、插入缓冲（insert buffer)、自适应哈希索引（adaptive hash index)、InnoDB存储的锁信息（lock info)、数据字典信息（data dictionary)等。

四、Buffer Pool 数据加载

4.1 Buffer Pool预热

MySQL在重启后，Buffer Pool里面没有什么数据，这个时候业务上对数据库的数据操作，MySQL就只能从磁盘中读取数据到内存中，这个过程可能需要很久才能是内存中的数据是业务频繁使用的。Buffer Pool中数据从无到业务频繁使用热数据的过程称之为预热。所以在预热这个过程中，MySQL数据库的性能不会特别好，并且Buffer Pool越大，预热过程越长。

为了减短这个预热过程，在MySQL关闭前，把Buffer Pool中的页面信息保存到磁盘，等到MySQL启动时，再根据之前保存的信息把磁盘中的数据加载到Buffer Pool中即可。

4.1.1 Buffer Pool Dump

遍历所有Buffer Pool Instance的LRU List，对于其中的每个数据页，按照space_id和page_no组成一个64位的数字，写到外部文件中

4.1.2 Buffer Pool Load

读取指定的外部文件，把所有的数据读入内存后，使用归并排序对数据排序，以64个数据页为单位进行IO合并，然后发起一次真正的读取操作。排序的作用就是便于IO合并。

4.2 预读机制

InnoDB在I/O的优化上有个比较重要的特性为预读，预读请求是一个i/o请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。InnoDB以64个page为一个extent，那么InnoDB的预读是以page为单位还是以extent？

数据库请求数据的时候，会将读请求交给文件系统，放入请求队列中；相关进程从请求队列中将读请求取出，根据需求到相关数据区(内存、磁盘)读取数据；取出的数据，放入响应队列中，最后数据库就会从响应队列中将数据取走，完成一次数据读操作过程。

接着进程继续处理请求队列，(如果数据库是全表扫描的话，数据读请求将会占满请求队列)，判断后面几个数据读请求的数据是否相邻，再根据自身系统IO带宽处理量， 进行预读，进行读请求的合并处理 ，一次性读取多块数据放入响应队列中，再被数据库取走。(如此，一次物理读操作，实现多页数据读取，rrqm>0（ # iostat -x ），假设是4个读请求合并，则rrqm参数显示的就是4)

InnoDB使用两种预读算法来提高I/O性能： 线性预读（linear read-ahead）和随机预读（randomread-ahead）

为了区分这两种预读的方式，我们可以把线性预读以extent为单位，而随机预读以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

4.2.1 线性预读（linear read-ahead）

线性预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数 innodb_read_ahead_threshold ，控制触发innodb执行预读操作的时间。

如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值(因为一个extent中也就只有64页)，默认值为56，值越高，访问模式检查越严格。

mysql> show variables like 'innodb_read_ahead_threshold';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+

例如 ，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。

可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。

在没有该变量之前，当访问到extent的最后一个page的时候，innodb会决定是否将下一个extent放入到buffer pool中。

4.2.2 随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中。

mysql> show variables like 'innodb_random_read_ahead';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_random_read_ahead | OFF   |
+--------------------------+-------+

由于随机预读方式给innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式 废弃，默认是OFF 。若要启用此功能，即将配置变量设置innodb_random_read_ahead为ON。

4.2.3 监控Innodb的预读

1.可以通过show engine innodb status\G显示统计信息

mysql> show engine innodb status\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
……

• Pages read ahead：表示每秒读入的pages；
• evicted without access：表示每秒读出的pages；
• 一般随机预读都是关闭的，也就是0。

2.通过两个状态值，评估预读算法的有效性

mysql> show global status like '%read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 2303  |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
+---------------------------------------+-------+
3 rows in set (0.01 sec)

• Innodb_buffer_pool_read_ahead：通过预读(后台线程)读入innodb buffer pool中数据页数
• Innodb_buffer_pool_read_ahead_evicted：通过预读来的数据页没有被查询访问就被清理的pages，无效预读页数

五、Innodb 如何管理Buffer Pool

5.1 Buffer Pool的工作机制

buffer pool两个最主要的功能：一个是加速读，一个是加速写。加速读呢？ 就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，这个是buffer pool后台刷新线程来完成的。

在实现上面两个功能的同时，需要考虑客观条件的限制，因为机器的内存大小是有限的，所以MySQL的innodb buffer pool的大小同样是有限的。在通常的情况下，当数据库的数据量比较大的时候，缓存池并不能缓存所有的数据页，所以也就可能会出现，当需要访问的某个页面时，该页面却不在缓存池中的情况，这个时候就需要从磁盘中将这个页面读出来，加载到缓存池中，然后再去访问。这样就涉及到随机的物理io，也就延长了访问页面所消耗的时间。

这样的情况是一个bad case，是我们期望尽量避免的——因此需要想办法来提高缓存的命中率。 innodb buffer pool采用经典的LRU列表算法来进行页面淘汰，以提高缓存命中率。将缓存的页面按照最近使用的次数跟时间进行排序，队列最末尾的页面将会最先被淘汰。同时，在LRU列表的中间位置打了一个old标识，可以简单的理解为将LRU列表分为两个部分，这个标记到LRU列表头部的页面称之为young的页面，这个标志到LRU列表尾部的页面称之为old页面。再进行抽象的话，我们简单地理解为缓存池被分成两个池子，一个叫young池子，一个叫old池子。当一个页面从磁盘上加载缓存池中的时候，会将它排放在这个old标识之后的第一个位置，也就是说放在了old池子中。这个机制的作用就是，在做大表的一次性全表扫描的时候，大量新进来的页面，是存放在old池子中的，当old池子的大小不够缓存新进来的页面的时候，也只是在old池子中内部进行循环冲洗，这样就不会冲洗young池子中的热点页面，从而保护了热点页面。这就是LRU列表的简单机制。

另外，前面我们讲到页面更新是在缓存池中先进行的，所以需要考虑这些被修改的页面什么时候刷新到磁盘？以什么样的顺序刷新到磁盘？在innodb buffer pool中，采用的方式是将页面在缓存中的按照第一次修改时间，也就是变成脏页的时间进行排序，flush列表进行排序，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。

简单介绍了buffer pool的工作机制，我们现在来看buffer pool里面最重要的几个列表，前面已经讲了两个列表，LRU列表以及flush列表，也就是脏页刷新列表。现在再补充一个列表—空闲（free）列表。空闲列表中的内存块，是没有存放任何数据页的内存块。当没有在缓存池中的页面需要被访问时，它需要先被加载到缓存池中，从而需要从空闲列表中取出一个空闲内存块来缓存这个页面。

所以，总结一下。为了管理buffer pool，每个buffer pool instance使用如下几个链表来管理：

• lru链表：缓存了所有读入内存的数据页，包含三类
• 1）未修改的页面，可以从该列表中摘除，然后移到free列表中。
• 2）已修改还未刷新到磁盘的页面。
• 3）已修改且已经刷新到磁盘的页面，可并为第一类。

• free链表：空闲内存页（块）列表，需要装载（缓存）磁盘上的数据页的时候，从此列表取内存块。
• flush链表：在内存中被修改但还没有刷新到磁盘的数据页列表，就是所谓的脏页列表，内存中的数据跟对应的磁盘上的数据不一致，属于该列表的页面同样存在于lru列表中，但反之未必。
• unzip_lru链表：包含所有解压页。

5.2 LRU（least recently used）

InnoDB管理buffer poll是将buffer pool作为一个list管理，基于LRU算法的管理。当有新的页信息要读入到buffer pool里面的时候，buffer pool就将最近最少使用的页信息从buffer pool当中驱逐出去，并且将新页加入到list的中间位置，这就是所谓的“中点插入策略”。一般情况下list头部存放的是热数据，就是所谓的young page（最近经常访问的数据），list尾部存放的就是old page（最近不被访问的数据）。这个算法就保证了最近经常使用的page信息会被保存在最近访问的sublist中，相反的不被经常访问的就会保存在old sublist，而old sublist当中的page信息都是会被在新数据写入的时候被驱逐的。

LRU算法有以下的标准算法：1）3/8的list信息是作为old list，这些信息是被驱逐的对象。2）list的中点就是我们所谓的old list头部和new list尾部的连接点，相当于一个界限。3）新数据的读入首先会插入到old list的头部。4）如果是old list的数据被访问到了，这个页信息就会变成new list，变成young page，就会将数据页信息移动到new sublist的头部。5）在数据库的buffer pool里面，不管是new sublist还是old sublist的数据如果不会被访问到，最后都会被移动到list的尾部作为牺牲者。

一般情况下，页信息会被查询语句立马查询到而被移动到new sublist，这就意味着他们会在buffer pool里面保留很长一段时间。表扫描（包括mysqldump或者没有where条件的select等操作）等操作将会刷入大量的数据进入buffer pool，同时也会将更多的buffer pool当中的信息刷出去，即使这个操作可能只会使用到一次而已。同样的，如果read-ahead（线性预读）后台进程读入大量数据的情况下也是会造成buffer pool大量高频的刷新数据页，但是这些操作是可控的，下面3，4会说得到。read-ahead操作简单说一下就是MySQL的一个后台预读进程，能够保证MySQL预读入数据进入buffer pool当中

当你做backup或者report的时候，可以频繁的往buffer pool里面读取数据，不用有太多的顾虑。

InnoDB采用的是一种不是像LRU那么严格的方法来保证将最近访问的数据写入到buffer pool里面，并且最大可能的降低减少数据的带入量。这个语句是全表扫描或者以后这个数据将不会再被访问到，但是缓冲数据还是会写入到buffer pool里面。

新写入的数据会被插入到LRU list的中间位置，默认会插入到从list尾部算起来的3/8的位置，当这些写入的数据在buffer pool中被第一次访问的时候，在list中的位置就会向前移动，这样其实就会在list保留两个位置，老的位置并不会被立即清除，直到老的LRU list的位置被标记为old的时候，才会在下一次插入数据的时候被作为牺牲者清除掉。

我们本身是可以指定插入LRU list的位置，并且也可以设置当索引扫描或者是全表扫描的时候是不是采用这个相同的优化方法。innodb_old_blocks_pct这个参数设置的是插入的位置，默认的值是37，我们可以设置的值是5-95之间，其余部分并不用来保存热数据。但是还有一个严重的问题就是当一个全表扫描或者索引的扫描经常被访问的时候，就会存储很大的数据到buffer pool里面，我们都知道这是很危险的一件事。所以MySQL给我们以下参数来设置保留在buffer pool里面的数据在插入时候没有被改变list位置的时候的保存时间innodb_old_blocks_time，单位是毫秒，这个值的默认值是1000。如果增大这个值的话，就会让buffer pool里面很多页信息变老的速度变快，这个很好理解把，因为这些数据不会很快被内存中擦除的话，就会变成热数据而挤掉原有缓存的数据。

以上的两个参数都是可以动态设置的，当然也可以在my.cnf里面设置。当然设置这些前一定要对机器配置，表信息，负载情况有充分的了解才能进行设置，生产库尽量不要随便修改。如果OLTP系统中有大量的大查询的话，设置innodb_old_blocks_time能够较大的提供系统的稳定性。如果当一个大查询很大不足够存储到buffer pool当中的时候，我们可以指定innodb_old_blocks_pct的值小一点，以保证这些数据只会被读取一次，比如说设置为5的时候，就限制了一次读取数据最多只能被读取到buffer pool当中5%。当然一些小表并且是经常访问到的数据的话就可以适当设置较大的值，比如50。当然设置这两个值的时候一定要建立在你充分了解你的数据负载的基础上，不然千万不要乱改。

5.3 数据页访问机制

下面我们来看一下一个数据页的访问流程。

1. 当访问的页面在缓存池中命中，则直接从缓冲池中访问该页面。另外为了避免查询数据页时扫描LRU，还为每个buffer pool instance维护了一个page hash，通过space id和page no可以直接找到对应的page。一般情况下，当我们需要读入一个Page时，首先根据space id和page no找到对应的buffer pool instance。然后查询page hash，如果page hash中没有，则表示需要从磁盘读取。
2. 如果没有命中，则需要将这个页面从磁盘上加载到缓存池中，因此需要在缓存池中的空闲列表中找一个空闲的内存块来缓存这个从磁盘读入的页面。
3. 但存在空闲内存块被使用完的情况，不保证一定有空闲的内存块。假如空闲列表为空，没有空闲的内存块，则需要想办法去产生空闲的内存块。
4. 首先去LRU列表中找可以替换的内存页面，查找方向是从列表的尾部开始找，如果找到可以替换的页面，将其从LRU列表中摘除，加入空闲列表，然后再去空闲列表中找空闲的内存块。第一次查找最多只扫描100个页面，循环进行到第二次时，会查找深度就是整个LRU列表。这就是LRU列表中的页面淘汰机制。
5. 如果在LRU列表中没有找到可以替换的页，则进行单页刷新，将脏页刷新到磁盘之后，然后将释放的内存块加入到空闲列表。然后再去空闲列表中取。为什么只做单页刷新呢？因为这个函数的目的是获取空闲内存页，进行脏页刷新是不得已而为之，所以只会进行一个页面的刷新，目的是为了尽快的获取空闲内存块。

因为空闲列表是一个公共的列表，所有的用户线程都可以使用，存在争用的情况。因此，自己产生的空闲内存块有可能会刚好被其他线程所使用，所以用户线程可能会重复执行上面的查找流程，直到找到空闲的内存块为止。

通过数据页访问机制，可以知道其中当无空闲页时产生空闲页就成为一个必须要做的事情了。如果需要刷新脏页来产生空闲页面或者需要扫描整个LRU列表来产生空闲页面的时候，查找空闲内存块的时间就会延长，这个是一个bad case，是我们希望尽量避免的。因此，innodb buffer pool中存在大量可以替换的页面，或者free列表中一直存在着空闲内存块，对快速获取到空闲内存块起决定性的作用。在innodb buffer pool的机制中，是采用何种方式来产生的空闲内存块，以及可以替换的内存页的呢？这就是我们下面要讲的内容——通过后台刷新机制来产生空闲的内存块以及可以替换的页面。

5.4 缓冲池刷新策略

InnoDB会在后台执行某些任务，包括从缓冲池刷新脏页（那些已更改但尚未写入数据库文件的页）。

当启用innodb_max_dirty_pages_pct_lwm（默认值0）参数时，表示启用了脏页面预刷新行为，以控制脏页面占比。也是为了防止脏页占有率超过innodb_max_dirty_pages_pct（默认值75%）的设定值。默认禁用“预刷新”行为。如果当脏页的占有率达到了innodb_max_dirty_pages_pct的设定值的时候，InnoDB就会强制刷新buffer pool pages。另外当free列表小于innodb_lru_scan_depth值时也会触发刷新机制，innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024。

后台刷新的动作由后台刷新协调线程触发，该线程的所有工作内容均由buf_flush_page_cleaner_coordinator函数完成，我们后面简称它为协调函数。接下来，来看后台刷新协调函数的主体流程。

1. 调用page_cleaner_flush_pages_recommendation建议函数，对每个缓冲池实例生成脏页刷新数量的建议。在执行刷新之前，会用建议函数生成每个buffer pool需要刷新多少个脏页的建议。
2. 生成刷新建议之后，通过设置事件的方式，向刷新线程（Page Cleaner线程）发出刷新请求。后台刷新线程在收到请求刷新的事件后，会执行pc_flush_slot函数对某个缓存池进行刷新，刷新的过程首先是对lru列表进行刷新，执行的函数为buf_flush_LRU_list，完成LRU列表的刷新之后，就会根据建议函数生成的建议对脏页列表进行刷新，执行的函数为buf_flush_do_batch。
3. 后台刷新的协调线程会作为刷新调度总负责人的角色，它会确保每个buffer pool都已经开始执行刷新。如果哪个buffer pool的刷新请求还没有被处理，则由刷新协调线程亲自刷新，且直到所有的buffer pool instance都已开始/进行了刷新，才退出这个while循环。
4. 当所有的buffer pool instance的刷新请求都已经开始处理之后，协调函数（或协调线程）就等待所有buffer pool instance的刷新的完成，等待函数为pc_wait_finished。如果这次刷新的总耗时超过4000ms，下次循环之前，会在数据库的错误日志记录相关的超时信息。它期望每秒钟对buffer pool进行一次刷新调度。如果相邻两次刷新调度的间隔超过4000ms ，也就是4秒钟，MySQL的错误日志中会记录相关信息，意思就是“本来预计1000ms的循环花费了超过4000ms的时间。

前面我们反复讲到，每个buffer pool需要刷新多少页面是由建议函数生成的，它在做刷新建议的时候，具体考虑了哪些因素？现在我们来详细解析。

在讲这段内容之前，我们先来了解两个参数：innodb_io_capacity与innodb_io_capacity_max，这两个参数大部分朋友都不陌生，设置这个参数的目的，是告诉MySQL数据库，它所在服务器的磁盘的随机IO能力。MySQL数据库目前还没有去自己评估服务器磁盘IO能力的功能，所以磁盘io能力大小由这个参数提供，以便让数据库知道磁盘的实际IO能力。这个参数将直接影响建议刷新的页面的数量。

建议函数它会计算当前的脏页刷新平均速度（也就是一秒钟刷新了多少脏页）以及重做日志的生成平均速度。但这个函数并不是每次被调用时，都计算一次平均速度。它是多久计算一次的呢？这个是由数据库参数innodb_flushing_avg_loops来决定的，默认是30，当这个函数被调用了30次之后或者经过30秒之后，重新计算一次平均值。我们暂且简单理解为30秒钟。计算规则是当前的平均速度加上最近30秒钟期间的平均速度再除以2得出新的平均速度。两个平均值相加再平均，得出新的平均值。这样的平均值能明显的体现出最近30秒的速度的变化。

平均值计算规则就是新平均速度=(当前的平均速度+最近这段期间平均速度)%2。

接下来，它会根据innodb buffer pool的脏页百分比来计算innodb_io_capacity的百分比。然后会根据重做日志中的活跃日志量的大小，也就是lsn的age，最近生成量，占重做日志文件大小的百分比来计算innodb_io_capacity的百分比。调用相关函数根据脏页百分比来计算io_capacity的百分比，用变量pct_for_dirty保存，然后根据活跃日志量的大小来计算io_capacity的百分比，用变量pct_for_lsn来保存，这个值后面会被是使用到，用来决定每个buffer pool是建议刷新相同的数量的脏页，还是刷新不同的数量。当pct_for_lsn<30的时候，建议每个buffer刷新相同数量的页面。否则，建议刷新不同数量的页面。最后比较这两个变量的大小，大的值作为最终的io_capacity的百分比，用变量pct_total保存。假如计算出来的得到pctl_total为90，而数据库参数innodb_io_capacity设置为1000，则根据这两个因素再结合所设置的磁盘io能力，得出的建议就为刷新900个脏页，所以innodb_io_capacity参数也是刷新多少的一个重要参数。接下来我们将来看看是如何具体跟据这两项来计算io_capacity的百分比的。

5.4.1 如何根据脏页百分比来计算innodb_io_capacity百分比？

首先获取缓存池的脏页百分比，然后根据这个值进行判断。如果参数最大脏页百分比的低水位设置为0(默认值），当dirty_pct大于参数innodb_max_dirty_pages_pct（默认值75%），则返回100，否则返回0。如果设置了最大脏页百分比的低水位，当脏页百分比超过该值时，则返回相应的比例。当脏页百分比越接近最大脏页百分比，返回比例越接近100。否则为0。

5.4.2 如何根据重做日志活跃日志量来计算innodb_io_capacity百分比？

如果活跃日志量占日志文件大小的百分比小于参数innodb_adaptive_flushing_lwm，即自适应刷新的低水位，默认是10，则直接返回0。如果没有设置自适应刷新参数innodb_adaptive_flushing，（InnoDB 1.0引入的参数，自适应刷新，就是我们这里讲的刷新方式。旧的刷新方式时只有脏页在缓冲池中占的比例大于innodb_max_dirty_pages_pct参数时就会刷新100个脏页），默认为on，则需要等待活跃的日志量大于max_async_age的值，才会返回相应的百分比，否则返回0。可以简单的理解为，如果没有开启自适应刷新，则必须等待活跃日志量的过大，大到存在危害数据库的可用性风险时，才开始考虑基于活跃日志量的大小来进行脏页刷新。如果开启了自适应刷新，活跃日志量所占百分比大于自适应刷新的低水位时，返回相应的百分比。

然后，会根据前面计算的重做日志生成的平均速度，来计算建议每个buffer pool instance刷新多少脏页以及所有pool buffer的刷新总量。之所以会基于这个因素来考虑，我认为是这样的：新产生的重做日志是活跃的重做日志，根据活跃日志的生成速度来计算需要刷新的脏页的数量，从而将使活跃日志的过期速度跟生成速度达到一个均衡，这样控制了活跃的重做日志在一个正常的范围，保障了重做日志文件一直有可以使用的空间，不然就会有问题（可以看MySQL InnoDB checkpoint）。在这里简单说明一下活跃的重做日志跟不活跃的重做日志的区别：活跃日志是指其记录的被修改的脏页还没有被刷新到磁盘，当MySQL实例crash之后，需要使用这些日志来做实例恢复。

5.4.3 如何计算每个buffer pool instance需要刷新的页面？

首先，根据前面计算得出的lsn_avg_rate，即重做日志产生的平均速度，计算出一个target_lsn号。

然后从每一个buffer pool的脏页列表的队尾开始取出脏页，将脏页的old_modifiaction(最小的lsn)跟target_lsn进行比较，这里简单的说明一下脏页的oldest_modification的含义，它表示的是脏页第一次修改时的lsn号，也就是脏页的最小lsn号。如果它小于target_lsn，然后将其作为刷新对象进行计数，否则，退出这个buffer pool内的循环。因为刷新列表时按照脏页的最小lsn号进行排序的，前面的脏页的最小lsn都大于target_lsn ，所以不需要再继续找下去。

从上面的计算方式可以看出，当重做日志生成的平均速度越大，target_lsn就越大，同时，如果buffer_pool中的脏页的old_modition小于target_lsn的数量越多，也就是老的脏页越多，被建议刷新的页面就越多。

再接下来，通过上面的计算，我们从不同维度分别得出三个建议刷新的数量：分别为当前的脏页刷新的平均速度，也就是一秒钟刷新了多少脏页；根据脏页百分比，以及活跃日志量的大小，以及所设置的innodb_io_capacity参数所得出建议刷新的数量；以及根据重做日志产生速度计算得出的建议刷新数量。将这三个值相加之后再平均，得出的就是考虑了上面所有因素的一个综合建议，由变量n_pages保存。接下来，这个建议刷新的总量n_pages会跟innodb_io_capacity_max这个参数进行比较，也就是建议刷新的总量最大不能超过所设置的磁盘最大随机io能力。

最后，生成最终的刷新建议。生成最终的刷新建议时，会考虑当前数据库的活跃日志量的大小，当前活跃日志比较少的时候，认为重做日志文件有足够可以使用的空间(以变量pct_for_lsn小于30为依据），则不需要考虑每个buffer pool之间的脏页年龄分布不均的情况，每个buffer pool刷新相同的数量，数量就刷新总量除以buffer pool的个数。如果活跃日志比较多(以变量pct_for_lsn大于等于30为依据），则需要考虑脏页的年龄在每个buffer pool的分布不同，每个buffer刷新不同的数量的脏页，老的脏页比较多的buffer pool instance刷新的数量也就多。

5.4.4 生成最终刷新建议后的刷新逻辑？

当生成刷新建议之后，就设置刷新请求事件，请求刷新线程进行脏页批量刷新。请求函数pc_request也很简单。

1. 将所有buffer pool instances的刷新状态设置为PAGE_CLEANER_STATE_REQUESTED，即申请刷新。
2. 通过设置事件，唤醒/触发page cleaner线程调用pc_flush_slot函数来进行buffer pool的批量刷新。

Page_cleaner线程收到刷新请求之后，就开始进行批量刷新。

1. 寻找一个状态为申请刷新的缓存池实例，然后选为刷新对象，将状态修改为flushing.。然后执行后面的刷新。
2. 执行buf_flush_LRU_list函数进行LRU列表的刷新。
3. 执行buf_flush_do_batch批量刷新脏页列表，该buffer pool instance建议刷新的数量slot->n_pages_requested作为该函数参数值，也就是依据建议刷新的页面数来进行刷新。

对于LRU列表的刷新的函数buf_flush_LRU_list将scan_depth变量传递最终传递给buf_flush_LRU_list_batch函数，在通常情况下，可以简单的理解scan_depth的值来自于数据库参数innodb_lru_scan_deptch（简单理解innodb_lru_scan_depth参数控制LRU列表中可用页的数量，该值默认为1024）参数。接下来看buf_flush_LRU_list_batch函数，这个函数一个重要点就是如果free列表的长度大于innodb_lru_scan_depth参数值，则终止内部循序。否则就往下走，然后就该判断如果是一个可替换的页，则将从LRU列表中摘除，其加入free列表。如果是脏页，则进行刷新，直到满足小于innodb_lru_scan_depth的条件则终止循环体。由此我们可以看出innodb_lru_scan_depth参数，在此起非常关键的作用，实际上也直接影响了buffer bool instance中的free列表的长度。

刷新协调函数的执行一个刷新循环的最后一步，就是设置事件等待，等待所有buffer pool instance刷新完成的事件触发。刷新完成之后，然后开始下一轮循环，如果刷新在1秒之内完成，则刷新协调线程会有短暂的sleep才会发起下一次刷新。期望是1秒钟进行一次所有buffer pool instance的批量刷新。

六、Buffer Pool 状态查询

InnoDB缓冲池将表的索引和数据进行缓存，缓冲池允许从内存直接处理频繁使用的数据，这加快了处理速度。在专用数据库服务器上，通常将多达80％的物理内存分配给InnoDB缓冲池。因为InnoDB的存储引擎的工作方式总是将数据库文件按页读取到缓冲池，每个页16k默认（innodb_page_size=16k），在MySQL 5.7中增加了32KB和64KB页面大小的支持，之前版本是不允许大于16k的；但你只能在初始化MySQL实例之前进行配置，一旦设置了一个实例的页面大小，就不能改变它，具体看innodb_page_size参数。

然后按最近最少使用（LRU）算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页（发生修改后，该页即为脏页），然后再按照一定的频率将缓冲池的脏也刷新到文件中。可以通过show engine innodb status来查看innodb_buffer_pool的具体使用情况。一个buffer pool可能会分成好几个buffer pool instance，为了提高性能减少争用，在MySQL 5.7中，如果不显式设置innodb_buffer_pool_instances这个参数，当innodb buffer size大于1G的时候，就会默认会分成8个instances，如果小于1G，就只有1个instance。如下:

mysql> show engine innodb status\G
Per second averages calculated from the last 38 seconds（以下信息来之过去的38秒）
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 59957
Buffer pool size   65536
Free buffers  65371
Database pages  165
Old database pages  3
Modified db pages  9
..........

在Buffer pool size中可以看到内存池的使用情况：

• Total memory allocated：为缓冲池分配的总内存（以字节为单位）。
• Dictionary memory allocated：分配给InnoDB数据字典的总内存（以字节为单位）。
• Buffer pool size：分配给缓冲池的页面总数量（数量*页面大小=缓冲池大小），默认每个Page为16k。
• Free buffers：缓冲池中空闲列表的页面总数量（Buffer pool size – Database pages）。
• Database pages：缓冲池中LRU LIST的页面总数量（可以理解为已经使用的页面）。
• Old database pages：缓冲池中LRU old SUBLIST的页面总大小（可以理解为不经常访问的页面，即将可能被LRU算法淘汰的页面）。
• Modified db pages：缓冲池中已经修改了的页数，所谓脏数据。

所以这里一共分配了63336*16/1024=1G内存的缓冲池，空闲65371个页面，已经使用了165个页面，不经常修改的数据页有3个（一般占用内存的1/3），脏页的页面有9个，这些数据能分析当前数据库的压力值。

七、InnoDB Buffer Pool配置项

• innodb_buffer_pool_size

指定缓冲池的大小。 如果缓冲池很小并且有足够的内存，那么通过减少查询访问InnoDB表所需的磁盘I / O量可以提高缓冲池的性能，从而提高性能。 innodb_buffer_pool_size选项是动态的，允许在不重新启动服务器的情况下配置缓冲池大小。

• innodb_buffer_pool_chunk_size

定义InnoDB缓冲池调整大小操作的块大小

• innodb_buffer_pool_instances

将缓冲池划分为用户指定数量的单独区域，每个区域都有自己的LRU列表和相关数据结构，以减少并发内存读取和写入操作期间的争用。 只有将innodb_buffer_pool_size设置为1GB或更大的值时，此选项才会生效。 您指定的总大小被分配到所有缓冲池中。 为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1 GB。

• innodb_old_blocks_pct

指定InnoDB用于旧块子列表的缓冲池的近似百分比。 值的范围是5到95.默认值是37（即池的3/8）。

• innodb_old_blocks_time

指定插入到旧子列表中的页面在第一次访问后必须在毫秒（ms）内保留多长时间，然后才能移动到新子列表中。 如果值为0，则无论插入后何时发生访问，插入旧子列表的页面在第一次访问时都会立即移至新子列表。 如果该值大于0，则页面将保留在旧的子列表中，直到在第一次访问后至少几毫秒内发生访问。 例如，值为1000（默认值）会导致页面在第一次访问后停留在旧子表中1秒钟，然后才有资格移动到新子列表。

将innodb_old_blocks_time设置为大于0可防止一次性表扫描使用仅用于扫描的页面填充新的子列表。 读取的扫描页面中的行会连续快速访问多次，但该页面在此之后未被使用。 如果innodb_old_blocks_time设置为大于处理页面的时间的值，则该页面保留在“旧”子列表中，并且老化到列表的尾部以被快速驱逐。 这样，仅用于一次扫描的页面不会影响新子列表中大量使用的页面。

innodb_old_blocks_time可以在运行时设置。

• innodb_read_ahead_threshold

控制InnoDB用于预取页面到缓冲池中的线性预读的灵敏度。

• innodb_random_read_ahead

使用随机预读技术将页面预取到缓冲池中。 随机预读是一项技术，可根据缓冲池中已有的页面快速预测何时需要页面，而不管这些页面的读取顺序如何。 innodb_random_read_ahead默认是禁用的。

• innodb_adaptive_flushing

指定是否根据工作负载动态调整缓冲池中刷新脏页的速率。 动态调整冲刷速率旨在避免I / O活动的爆发。 该设置默认启用。

• innodb_flush_neighbors

指定是否从缓冲池中清除页面也会在相同范围内刷新其他脏页面。默认是1 启用。

• innodb_flushing_avg_loops

InnoDB保存之前计算的刷新状态快照的迭代次数，控制自适应刷新对更改工作负载的响应速度。默认是 30

• innodb_lru_scan_depth

影响缓冲池刷新操作的算法和启发式的参数。 性能主要关注调整I / O密集型工作负载。 它为每个缓冲池实例指定了缓冲池LRU下多远，page_cleaner线程扫描寻找要刷新的脏页。默认是1024

• innodb_max_dirty_pages_pct

InnoDB会尝试从缓冲池中刷新数据，以便脏页面的百分比不超过此值。 指定范围从0到99的整数。默认值为75。

• innodb_max_dirty_pages_pct_lwm

低水位的百分比表示启用来控制脏页预flush的比率。 默认值为0将完全禁用预冲洗行为。默认是0

• innodb_buffer_pool_filename

指定保存由innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now生成的表空间ID和页面ID列表的文件的名称。

• innodb_buffer_pool_dump_at_shutdown

指定在MySQL服务器关闭时是否记录缓冲池中缓存的页面，以缩短下次重新启动时的预热过程。默认是启用

• innodb_buffer_pool_load_at_startup

指定在MySQL服务器启动时，通过加载它在先前保存的相同页面，缓冲池自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。默认是on

• innodb_buffer_pool_dump_now

立即记录在缓冲池中缓存的页面。默认是OFF

• innodb_buffer_pool_load_now

立即通过加载一组数据页面来加热缓冲池，而无需等待服务器重新启动。 在基准测试期间将高速缓存恢复到已知状态，或者在运行查询报告或维护后，使MySQL服务器恢复正常工作负载，可能会很有用。 通常与innodb_buffer_pool_dump_now一起使用。

• innodb_buffer_pool_dump_pct

指定每个缓冲池读出和转储的最近使用页面的百分比。 范围是1到100。默认是25

• innodb_buffer_pool_load_abort

中断由innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲池内容恢复过程。默认是OFF

八、配置InnoDB缓冲池案例

以配置InnoDB缓冲池的各个方面来提高性能。

• 理想情况下，你将缓冲池的大小设置为尽可能大的值，一般buffer pool的大小为物理内存的70%-80%。缓冲池越大，InnoDB内存数据库的行为越多，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。
• 对于具有大内存的64位系统，你可以将缓冲池拆分成多个实例（默认8个），以最大限度地减少并发操作中内存结构的争用。

8.1 配置InnoDB缓冲池大小

8.1.1 在线配置InnoDB缓冲池大小

缓冲池支持脱机和联机两种配置方式，当增加或减少innodb_buffer_pool_size时，操作以块（chunk）形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值128M。

在线配置InnoDB缓冲池大小，该innodb_buffer_pool_size配置选项可以动态使用设置SET声明，让你调整缓冲池无需重新启动服务器。例如：

mysql> SET GLOBAL innodb_buffer_pool_size=8589934592;

缓冲池大小配置必须始终等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。如果配置innodb_buffer_pool_size为不等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于或不小于指定缓冲池大小的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

在以下示例中， innodb_buffer_pool_size设置为8G，innodb_buffer_pool_instances设置为16，innodb_buffer_pool_chunk_size是128M，这是默认值。8G是一个有效的innodb_buffer_pool_size值，因为它是innodb_buffer_pool_instances=16乘以innodb_buffer_pool_chunk_size=128M的倍数。

mysql> select 8*1024 / (16*128);
+-------------------+
| 8*1024 / (16*128) |
+-------------------+
|            4.0000 |
+-------------------+
1 row in set (0.00 sec)

如果innodb_buffer_pool_size设置为9G，innodb_buffer_pool_instances设置为16，innodb_buffer_pool_chunk_size是128M，这是默认值。在这种情况下，9G不是innodb_buffer_pool_instances=16*innodb_buffer_pool_chunk_size=128M的倍数 ，所以innodb_buffer_pool_size被调整为10G，这是不小于指定缓冲池大小的下一个innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

8.1.2 监控在线缓冲池调整大小进度

该Innodb_buffer_pool_resize_status报告缓冲池大小调整的进展。例如：

mysql> SHOW STATUS WHERE Variable_name ='InnoDB_buffer_pool_resize_status';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_resize_status |       |
+----------------------------------+-------+
1 row in set (0.01 sec)

8.2 脱机配置InnoDB缓冲池块（chunk）大小

innodb_buffer_pool_chunk_size可以在1MB（1048576字节）单位中增加或减少，但只能在启动时，在命令行字符串或MySQL配置文件中进行修改。

[mysqld]
innodb_buffer_pool_chunk_size = 134217728

修改innodb_buffer_pool_chunk_size时适用以下条件：

如果新innodb_buffer_pool_chunk_size值乘以innodb_buffer_pool_instances大于初始化缓冲池大小时， innodb_buffer_pool_chunk_size则截断为innodb_buffer_pool_size / innodb_buffer_pool_instances。

例如，如果缓冲池初始化大小为2GB（2147483648字节）， 4个缓冲池实例和块大小1GB（1073741824字节），则块大小将被截断为等于innodb_buffer_pool_size / innodb_buffer_pool_instances，值为：

mysql> select 2147483648 / 4;
+----------------+
| 2147483648 / 4 |
+----------------+
| 536870912.0000 |
+----------------+
1 row in set (0.00 sec)

缓冲池大小必须始终等于或不小于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果更改innodb_buffer_pool_chunk_size，innodb_buffer_pool_size则会自动调整为等于或不小于当前缓冲池大小的innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。缓冲池初始化时会发生调整。

更改时应小心innodb_buffer_pool_chunk_size，因为更改此值可以增加缓冲池的大小，如上面的示例所示。在更改innodb_buffer_pool_chunk_size之前，计算innodb_buffer_pool_size以确保生成的缓冲池大小是可接受的。

8.3 配置多个缓冲池实例

对于具有多GB级缓冲池的系统，将缓冲池划分为单独的实例可以通过减少不同线程读取和写入缓存页面的争用来提高并发性。此功能通常适用于缓冲池大小在千兆字节范围内的系统。使用innodb_buffer_pool_instances配置选项配置多个缓冲池实例，你也可以调整该innodb_buffer_pool_size值。

当InnoDB缓冲池大时，可以通过从内存检索来满足许多数据请求。你可能会遇到多个请求一次访问缓冲池的线程的瓶颈。你可以启用多个缓冲池以最小化此争用。使用散列函数，将缓冲池中存储或读取的每个页面随机分配给其中一个缓冲池。每个缓冲池管理自己的空闲列表，刷新列表，LRU和连接到缓冲池的所有其他数据结构，并由其自己的缓冲池互斥锁保护。

要启用多个缓冲池实例，请将innodb_buffer_pool_instances配置选项设置为大于1（默认值）高达64（最大值）的值。此选项仅在设置innodb_buffer_pool_size为1GB或更大的大小时生效。你指定的总大小在所有缓冲池之间分配，为了获得最佳效率，指定的组合innodb_buffer_pool_instances和innodb_buffer_pool_size，使得每个缓冲池实例是至少为1GB。在MySQL 5.7中，如果不显式设置innodb_buffer_pool_instances这个参数，当innodb buffer size大于1G的时候，就会默认会分成8个instances，如果小于1G，就只有1个instance。

8.4 配置InnoDB缓冲池预读

InnoDB在io的优化上有个比较重要的特性为预读，预读请求是一个i/o请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。InnoDB以64个page为一个extent，那么InnoDB的预读是以page为单位还是以extent？

这样就进入了下面的话题，InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

8.4.1 线性预读（linear read-ahead）

它可以根据顺序访问缓冲池中的页面，预测哪些页面可能需要很快。通过使用配置参数innodb_read_ahead_threshold，通过调整触发异步读取请求所需的顺序页访问数，可以控制Innodb执行提前读操作的时间。在添加此参数之前，InnoDB只会计算当在当前范围的最后一页中读取整个下一个区段时是否发出异步预取请求。

线性预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，可以控制Innodb执行预读操作的时间。如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值，默认值为56，值越高，访问模式检查越严格。

mysql> show global variables like '%innodb_read_ahead_threshold%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+
1 row in set (0.00 sec)

例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。你可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。

在没有该变量之前，当访问到extent的最后一个page的时候，Innodb会决定是否将下一个extent放入到buffer pool中。

8.4.2 随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中，由于随机预读方式给Innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置innodb_random_read_ahead为ON。

mysql> show global variables like '%innodb_random_read_ahead%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_random_read_ahead | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

在监控Innodb的预读时候，我们可以通过SHOW ENGINE INNODB STATUS命令显示统计信息，通过Pages read ahead和evicted without access两个值来观察预读的情况，或者通过两个状态值，以帮助您评估预读算法的有效性。

mysql> show global status like '%Innodb_buffer_pool_read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
+---------------------------------------+-------+
3 rows in set (0.00 sec)

而通过SHOW ENGINE INNODB STATUS得到的Pages read ahead和evicted without access则表示每秒读入和读出的pages：Pages read ahead 1.00/s, evicted without access 9.99/s。

当微调innodb_random_read_ahead设置时，此信息可能很有用 。

8.5 配置InnoDB缓冲池刷新

上面详细说了缓冲池刷新机制，简单来说基本原理可以总结如下：

当启用innodb_max_dirty_pages_pct_lwm（默认值0）参数时，表示启用了脏页面预刷新行为，以控制脏页面占比。也是为了防止脏页占有率超过innodb_max_dirty_pages_pct（默认值75%）的设定值。默认禁用“预刷新”行为。如果当脏页的占有率达到了innodb_max_dirty_pages_pct的设定值的时候，InnoDB就会强制刷新buffer pool pages。另外当free列表小于innodb_lru_scan_depth值时也会触发刷新机制，innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024。

InnoDB采用一种基于redo log的最近生成量（活跃日志量的大小，也就是lsn的age）和脏页刷新平均速度（一秒刷新了多少次）及innodb_io_capacity参数的算法来决定刷新脏页数量。这样的算法可以保证数据库的刷新不会影响到数据库的性能，也能保证数据库buffer pool中的数据的脏数据的占用比。这种自动调整刷新速率有助于避免过多的缓冲池刷新限制了普通读写请求可用的I/O容量，从而避免吞吐量突然下降，但还是对正常IO有影响。内部基准测试显示，该算法随着时间的推移可以显著提高整体吞吐量。这种算法是经得住考验的，所以说千万不要随便设置，最好是默认值。

配置参数innodb_flush_neighbors（刷新邻接页），innodb_lru_scan_depth可以让你微调缓冲池刷新过程的某些方面，这些选项主要是帮助写密集型的工作负载。如果DML操作较为严重，如果没有较高的值，则刷新可能会下降，会导致缓冲池中的内存过多。或者，如果这种机制过于激进，磁盘写入将会使你的I/O容量饱和，理想的设置取决于你的工作负载，数据访问模式和存储配置（例如数据是否存储在HDD或SSD设备上）。

InnoDB存储引挚提供了flush Neighbor page（刷新邻接页）的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引挚会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统的机械硬盘下有着显著的优势。但需要考虑到下面两个问题：

1. 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页？
2. 固态硬盘有着较高的IOPS，是否还需要这个特性？

为此，InnoDB存储引挚从1.2.x版本开始提供了参数innodb_flush_neighbors，用来控制是否开启这个特性。对于传统的机械硬盘建议开启该特性，而对于固态硬盘有着超高的IOPS性能，则建议将该参数设置为0，即关闭此特性。

InnoDB对于具有不断繁重工作负载的系统或者工作负载波动很大的系统，可以使用下面几个配置选项来调整表的刷新行为：

• innodb_adaptive_flushing_lwm：默认值10，指定重做日志容量的“ 低水位 ”百分比，当该阈值越过时，InnoDB即使没有开启innodb_adaptive_flushing选项也会自动启用自适应刷新。
• innodb_max_dirty_pages_pct_lwm：默认值0，InnoDB尝试从缓冲池中刷新数据，以使脏页面的百分比不超过该值innodb_max_dirty_pages_pct。默认值为75。该innodb_max_dirty_pages_pct_lwm选项是用来指定“ 低水位 ”值，其表示使用预冲洗来控制脏页比例的百分比，防止脏页的百分比达到innodb_max_dirty_pages_pct的值，innodb_max_dirty_pages_pct_lwm默认0，禁用“ 预冲洗”行为。
• innodb_io_capacity_max，默认2000，如果刷新动作远远落后，InnoDB刷新脏页量可以超出innodb_io_capacity值。innodb_io_capacity_max表示在这种紧急情况下使用的I/O容量的上限，以便I/O中的尖峰消耗不到服务器的所有容量。
• innodb_flushing_avg_loops，默认30，定义了innodb保留先前计算的刷新状态快照的迭代次数, 它控制了自适应刷新对此前负载更改的响应速度。为innodb_flushing_avg_loops设置高值意味着innodb保留以前计算的快照的时间更长，因此自适应刷新的响应速度更慢，高值还可以减少前台和后台工作之间的正面反馈。但是，在设置高值时，确保innodb重做日志利用率不达到75% (异步刷新开始时的硬编码限制) 和innodb_max_dirty_pages_pct设置将脏页的数量保持为适合于工作负荷的级别是很重要的。

上面提到的大多数选项最适用于长时间运行写入繁重工作负载的服务器。

8.6 保存和恢复缓冲池状态

8.6.1 在关闭时保存缓冲池状态并在启动时恢复缓冲池状态

可以配置在MySQL关闭之前，保存InnoDB当前的缓冲池的状态，以避免在服务器重新启动后，还要经历一个预热的暖机时间。通过innodb_buffer_pool_dump_at_shutdown（服务器关闭前设置）来设置，当设置这个参数以后MySQL就会在机器关闭时保存InnoDB当前的状态信息到磁盘上。

当启动MySQL服务器时要恢复服务器缓冲池状态，请在启动服务器时开启innodb_buffer_pool_load_at_startup参数。个人认为这个值还是需要配置一下的，MySQL 5.7.6版本之前这两个值默认是关闭的，但从MySQL 5.7.7版本开始这两个值就默认为开启状态了。这些数据是从磁盘重新读取到buffer pool当中的，这会花费一些时间，并且恢复时新的DML操作是不能够进行操作的。这些数据是怎么恢复呢？其实INNODB_BUFFER_PAGE_LRU表（INFORMATION_SCHEMA）会记录缓存的tablespace ID和page ID，通过这个来恢复。另外缓冲池状态保存文件默认在数据目录下，名为”ib_buffer_pool”，可以使用innodb_buffer_pool_filename参数来修改文件名和位置。

8.6.2 配置缓冲池页面保存的百分比

在加载数据进入buffer pool之前，可以通过设置innodb_buffer_pool_dump_pct参数来决定恢复buffer pool中多少数据。MySQL 5.7.6版本之前的默认值是100，恢复全部，从MySQL 5.7.7版本之后默认调整为25了。可以动态设置此参数：

mysql> SET GLOBAL innodb_buffer_pool_dump_pct = 40;

8.6.3 在线保存和恢复缓冲池状态

要在运行MySQL服务器时保存缓冲池的状态，请发出以下语句：

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

要在MySQL运行时恢复缓冲池状态，请发出以下语句：

mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;

如果要终止buffer pool加载，可以指定运行：

mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;

8.6.4 显示缓冲池保存和加载进度

显示将缓冲池状态保存到磁盘时的进度：

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+------------------------------------+
| Variable_name                  | Value                              |
+--------------------------------+------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
+--------------------------------+------------------------------------+
1 row in set (0.03 sec)

显示加载缓冲池时的进度:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170428 16:13:21 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

以通过innodb的performance schema监控buffer pool的LOAD状态，打开或者关闭stage/innodb/buffer pool load。

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/buffer%';

启动events_stages_current，events_stages_history，events_stages_history_long表监控。

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

通过启用保存当前的缓冲池状态来获取最近的buffer pool状态。

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_dump_status
        Value: Buffer pool(s) dump completed at 170525 18:41:06
1 row in set (0.01 sec)

通过启用恢复当前的缓冲池状态来获取最近加载到buffer pool状态。

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.00 sec)

通过查询性能模式events_stages_current表来检查缓冲池加载操作的当前状态，该WORK_COMPLETED列显示加载的缓冲池页数，该WORK_ESTIMATED列提供剩余工作的估计，以页为单位。

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           5353 |           7167 |