（转）MySQL InnoDB缓冲池配置详解

一、InnoDB缓冲池

InnoDB维护一个称为缓冲池的内存存储区域 ，用于缓存内存中的数据和索引。了解InnoDB缓冲池的工作原理，并利用它来保存内存中经常访问的数据，这是MySQL调优的一个重要方面。有关InnoDB缓冲池如何工作的信息，请参阅InnoDB缓冲池LRU算法。

1.1 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。当然设置这两个值的时候一定要建立在你充分了解你的数据负载的基础上，不然千万不要乱改。

2.2 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的具体使用情况（默认是8个缓冲池实例），如下:


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
..........

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 SUBLIST的页面总大小（可以理解为不经常访问的页面，即将可能被LRU算法淘汰的页面）。

Modified db pages：在缓冲池中已经修改了的页数，所谓脏数据。

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

二、配置InnoDB缓冲池大小

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

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

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的倍数。

2.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)

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)
2.3 配置InnoDB缓冲池块（chunk）大小

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


[mysqld]
innodb_buffer_pool_chunk_size = 134217728
1
2
[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)

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以确保生成的缓冲池大小是可接受的。

2.4 在线调整缓冲池内部大小机制

调整大小的操作由后台线程执行，当增加缓冲池的大小时，调整大小操作：

添加页面chunks（chunks大小由innodb_buffer_pool_chunk_size定义）。
覆盖哈希表，列表和指针以在内存中使用新的地址。
将新页面添加到空闲列表中。
PS：当这些操作正在进行时，阻止其他线程访问缓冲池。

当减小缓冲池的大小时，调整大小操作：

对缓冲池进行碎片整理并提取空闲页面。
删除页面chunks（chunks大小由innodb_buffer_pool_chunk_size定义）。
转换哈希表，列表和指针以在内存中使用新的地址。
在这些操作中，只有对缓冲池进行碎片整理和撤销页面才允许其他线程同时访问缓冲池。

InnoDB在调整缓冲池大小之前，应完成通过API执行的活动事务和操作。启动调整大小操作时，在所有活动事务完成之前，操作都不会启动。一旦调整大小操作进行中，需要访问缓冲池的新事务和操作必须等到调整大小操作完成，但是允许在缓冲池进行碎片整理时缓冲池的并发访问。缓冲池大小减少时页面被撤销，允许并发访问的一个缺点是在页面被撤回时可能会导致可用页面暂时不足。

三、配置多个缓冲池实例

对于具有多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。

四、配置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中。

线性预读（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)

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中。

随机预读（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)

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)

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设置时，此信息可能很有用 。

五、配置InnoDB缓冲池刷新

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

InnoDB当缓冲池中脏页的百分比达到定义的低水位设置时，其实就是当缓冲池中的脏页占用比达到innodb_max_dirty_pages_pct_lwm的设定值的时候，就会自动将脏页清出buffer pool，这是为了保证buffer pool当中脏页的占有率，也是为了防止脏页占有率超过innodb_max_dirty_pages_pct的设定值，当脏页的占有率达到了innodb_max_dirty_pages_pct的设定值的时候，InnoDB就会强制刷新buffer pool pages。

InnoDB采用一种基于redo log的最近生成量和最近刷新频率的算法来决定冲洗速度，这样的算法可以保证数据库的冲洗不会影响到数据库的性能，也能保证数据库buffer pool中的数据的脏数据的占用比。这种自动调整刷新速率有助于避免过多的缓冲池刷新限制了普通读写请求可用的I/O容量，从而避免吞吐量突然下降，但还是对正常IO有影响。

我们知道InnoDB使用日志的方式是循环使用的，在重用前一个日志文件之前，InnoDB就会将这个日志这个日志记录相关的所有在buffer pool当中的数据刷新到磁盘，也就是所谓的sharp checkpoint，和sqlserver的checkpoint很像。当一个插入语句产生大量的redo信息需要记录的日志，当前redo log文件不能够完全存储，也会写入到当前的redo文件当中。当redo log当中的所有使用空间都被用完了的，就会触发sharp checkpoint，所以这个时候即使脏数据占有率没有达到innodb_max_dirty_pages_pct，还是会进行刷新。具体看MySQL InnoDB checkpoint。

内部基准测试显示，该算法随着时间的推移可以显著提高整体吞吐量。这种算法是经得住考验的，所以说千万不要随便设置，最好是默认值。但是我们从中也就会知道为什么redo log不能够记录两个事物的redo信息了。因为有这么多的好处，所以innodb_adaptive_flushing的值默认就是true的，默认开启自适应刷新策略。

六、微调InnoDB缓冲池刷新

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

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设置将脏页的数量保持为适合于工作负荷的级别是很重要的。
上面提到的大多数选项最适用于长时间运行写入繁重工作负载的服务器。

七、保存和恢复缓冲池状态

7.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参数来修改文件名和位置。

7.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;
1
mysql> SET GLOBAL innodb_buffer_pool_dump_pct = 40;
7.3 在线保存和恢复缓冲池状态

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


mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
1
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
要在MySQL运行时恢复缓冲池状态，请发出以下语句：


mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;
1
mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;
如果要终止buffer pool加载，可以指定运行：


mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;
1
mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;
7.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_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)

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%';
1
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%';
1
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)

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)
1
2
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 |
+-------------------------------+----------------+----------------+

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 |
+-------------------------------+----------------+----------------+
如果缓冲池加载操作已经完成，该表将返回一个空集合。在这种情况下，你可以检查events_stages_history表以查看已完成事件的数据。例如：


mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           7167 |           7167 |
+-------------------------------+----------------+----------------+
1
2
3
4
5
6
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+-------------------------------+----------------+----------------+
| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |
+-------------------------------+----------------+----------------+
| stage/innodb/buffer pool load |           7167 |           7167 |
+-------------------------------+----------------+----------------+
注意：在使用innodb_buffer_pool_load_at_startup启动时加载缓冲池时，还可以使用performance scheme来监视缓冲池负载进度，在这种情况下，需要开启stage/innodb/buffer pool load。有关更多信息，看：Section 25.3, “Performance Schema Startup Configuration”

需要留意的一点是如果是压缩表的话，在读取到buffer pool的时候还是会保持压缩的格式，直到被读取的时候才会调用解压程序进行解压。

MySQL 5.7.18版本相关参数的默认值如下：


# MySQL 5.7.18;
mysql> show global variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

# MySQL 5.7.18;
mysql> show global variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)