InnoDB Buffer Pool巧配置全解

一、简介

InnoDB 维护了一个缓存数据和索引信息到内存的存储区叫做 Buffer Pool，它会将最近访问的数据缓存到缓冲区。我们通过配置各个 Buffer Pool 的参数，可以显著提高 MySQL 的性能。

InnoDB 的 Buffer Pool 是基于 LRU 算法来实现的，下面我们可以简单了解一下 LRU 算法：

least recently used （LRU）,InnoDB 管理 Buffer Pool 是将 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 算法有以下的标准算法：

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

一般情况下，页信息会被查询语句立马查询到而被移动到 new sublist，这就意味着他们会在 Buffer Pool 里面保留很长一段时间。

表扫描（包括 mysqldump 或者没有 where 条件的 select 等操作）等操作将会刷入大量的数据进入 Buffer Pool，同时也会将更多的 Buffer Pool 当中的信息刷出去，即使这个操作可能只会使用到一次而已。同样的如果 read-ahead 后台进程读入大量数据的情况下也是会造成 Buffer Pool 大量高频的刷新数据页，但是这些操作是可控的，下面会讲到。read-ahead 操作简单说一下就是 MySQL 的一个后台预读进程，能够保证 MySQL 预读入数据进入 Buffer Pool当中。

二、参数一览表

下面先看下 InnoDB Buffer Pool 的一些相关参数信息，后面会详细解释一下：

1、innodb_buffer_pool_size：这个值是设置 InnoDB Buffer Pool 的总大小；

2、innodb_buffer_pool_chunk_size：InnoDB Buffer Pool 的执行单元 chunk size 的大小。这里面有个关系要确定一下，最好按照这个设置 innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances*N（N>=1）;

3、innodb_buffer_pool_instances：设置 InnoDB Buffer Pool 实例的个数，每一个实例都有自己独立的 list 管理Buffer Pool；

4、innodb_old_blocks_pct：默认 InnoDB Buffer Pool 中点的位置，默认值是37，最大100，也就是我们所谓的3/8的位置，可以自己设置；

5、innodb_old_blocks_time：设置保留在 Buffer Pool 里面的数据在插入时候没有被改变list位置的时候的保存时间；

6、innodb_read_ahead_threshold：参数控制 MySQL 何时进行预读，也可以控制 MySQL 预读数据时候对于数据的敏感度，如果 Buffer Pool 里面存储的数据页的频繁值大于 innodb_read_ahead_threshold 的值，InnoDB 就会启动一个异步的预读操作；

7、innodb_random_read_ahead：默认是 disabled，是控制预读方式的参数，开启的话将不使用线性预读而是使用随机预读；

8、innodb_adaptive_flushing：指定是否动态自适应刷新脏页到盘，这个是 MySQL 根据负载自己决定的。不过还是尽量不要设置，让 MySQL 自己来管理自己；

9、innodb_adaptive_flushing_lwm：关闭 adaptive_flushing 的话才会有用，用来标记 redo log 使用率的百分比的最低线，当达到这个值的时候就会刷新脏页，默认为10；

10、innodb_flush_neighbors：控制是否刷新 Buffer Pool 脏页的脏数据的时候将同一区的脏数据页一同刷新，默认值为1；

11、innodb_flushing_avg_loops：为 InnoDB 保存 InnoDB Buffer Pool 前几次的冲洗状态快照的迭代数，默认值为30，增大的话，冲洗就会变得缓慢。减小的话冲洗的频率就会变高；

12、innodb_lru_scan_depth：控制 LRU 算法的一个参数，用来控制 Buffer Pool 后台进程 page_cleaner 刷新脏页的位置；

13、innodb_max_dirty_pages_pct：参数会让 InnoDB Buffer Pool 刷新数据而不让脏数据的百分比超过这个值；

14、innodb_max_dirty_pages_pct_lwm：InnoDB 会自动维护后台作业自动从 Buffer Pool 当中清除脏数据，当Buffer Pool 中的脏页占用比 达到 innodb_max_dirty_pages_pct_lwm 的设定值的时候，就会自动将脏页清出 Buffer Pool；

15、innodb_buffer_pool_filename: 指定文件名字；

16、innodb_buffer_pool_dump_at_shutdown: 配置的 InnoDB 是否保留当前的缓冲池的状态，以避免在服务器重新启动后，还要经历一个漫长的暖机时间；

17、innodb_buffer_pool_load_at_startup：指定此参数启动，数据库重启以后会自动暖机，读入 Buffer Pool 重启前保存的信息；

18、innodb_buffer_pool_dump_now 和 innodb_buffer_pool_load_now 当数据库已经提起来的时候，我们忘了以前指定，也可以指定马上恢复；

19、innodb_buffer_pool_dump_pct：设置一下恢复 Buffer Pool 中多少数据；

20、innodb_buffer_pool_load_abort：终止 Buffer Pool 恢复，可以指定负载运行。

三、innoDB Buffer Pool 解读

1、Buffer Pool Size 设置和生效过程：

理想情况下，在给服务器的其他进程留下足够的内存空间的情况下，Buffer Pool Size 应该设置的尽可能大。当 Buffer Pool Size 设置的足够大时，整个数据库就相当于存储在内存当中，当读取一次数据到 Buffer Pool Size 以后，后续的读操作就不用再访问磁盘。

下面我们看一下 Buffer Pool Size 的设置方式：

当数据库已经启动的情况下，我们可以通过在线调整的方式修改 Buffer Pool Size 的大小。通过以下语句：

SET GLOBAL innodb_buffer_pool_size=402653184;

当执行这个语句以后，并不会立即生效，而是要等所有的事务全部执行成功以后才会生效；新的连接和事务必须等其他事务完全执行成功以后，Buffer Pool Size 设置生效以后才能够连接成功，不然会一直处于等待状态。

期间，Buffer Pool Size 要完成碎片整理，去除缓存 page 等等操作。在执行增加或者减少 Buffer Pool Size 的操作时，操作会作为一个执行块执行，innodb_buffer_pool_chunk_size 的大小会定义一个执行块的大小，默认的情况下，这个值是128M。

Buffer Pool Size 的大小最好设置为 innodb_buffer_pool_chunk_size innodb_buffer_pool_instances 的整数倍，而且是大于等于1。

如果你的机器配置的大小不是整数倍的话，Buffer Pool Size 的大小是会自适应修改为 innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances 的整数倍，会略小于你配置的 Buffer Pool Size 的大小。

比如以8G为例：

mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16，然后innodb_buffer_pool_instances=16 的大小刚好设置为16，是一个整数倍的关系。而且innodb_buffer_pool_chunk_size 的大小也是可以在my.cnf里面指定的。

还有一种情况是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 大于 buffer pool size 的情况下，innodb_buffer_pool_chunk_size 也会自适应为 Buffer Pool size/innodb_buffer_pool_instances，可见MySQL 的管理还是非常的智能的。

如果我们要查 Buffer Pool 的状态的话：

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

可以帮我们查看到状态。我们可以看一下增加 Buffer Pool 的时候的一个过程，再看一下减少的时候的日志，其实还是很好理解的，我们可以看成每次增大或者减少 Buffer Pool 的时候就是进行 innodb_buffer_pool_chunk 的增加或者释放，按照 innodb_buffer_pool_chunk_size 设定值的大小增加或者释放执行块。

增加的过程：增加执行块，指定新地址，将新加入的执行块加入到 free list（控制执行块的一个列表，可以这么理解）。

减少的过程：重新整理 Buffer Pool 和空闲页，将数据从块中移除，指定新地址。

2、Buffer Pool Instances

在64位操作系统的情况下，可以拆分缓冲池成多个部分，这样可以在高并发的情况下最大可能的减少争用。下面我们看一下怎么配置 Buffer Pool Instances？

配置多个 Buffer Pool Instances 能在很大程度上能够提高 MySQL 在高并发的情况下处理事物的性能，优化不同连接读取缓冲页的争用。

我们可以通过设置 innodb_buffer_pool_instances 来设置 Buffer Pool Instances。当 InnoDB Buffer Pool 足够大的时候，你能够从内存中读取时候能有一个较好的性能，但是也有可能碰到多个线程同时请求缓冲池的瓶颈。这个时候设置多个 Buffer Pool Instances 能够尽量减少连接的争用。

这能够保证每次从内存读取的页都对应一个 Buffer Pool Instances，而且这种对应关系是一个随机的关系。并不是热数据存放在一个 Buffer Pool Instances下，内部也是通过 hash 算法来实现这个随机数的。每一个 Buffer Pool Instances 都有自己的 free lists，LRU 和其他的一些 Buffer Poll 的数据结构，各个 Buffer Pool Instances 是相对独立的。

innodb_buffer_pool_instances 的设置必须大于1才算得上是多配置，但是这个功能起作用的前提是innodb_buffer_pool_size 的大小必须大于1G，理想情况下 innodb_buffer_pool_instances 的每一个 instance 都保证在1G以上。

3、innoDB Buffer Poll LRU 原理

你可以频繁的往 Buffer Pool 里面读取数据，当 backup 或者 report 的时候，不用有太多的顾虑。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。当然设置这两个值的时候一定要建立在你充分了解你的数据负载的基础上，不然千万不要乱改。

4、InnoDB Buffer Pool 预读

我们可以控制 MySQL 何时以何种方式预读数据进入 Buffer Pool。 预读就是 IO 异步读取多个页数据读入 Buffer Pool 的一个过程，并且这些页被认为是很快就会被读取到的，当需要读取这些数据的时候就会将需要的页放在一个区当中，InnoDB 就是通过两次预读的方式来提高 IO 读写的性能。

线性预读：能够预测将有那些数据很快能被读到的一种技术，因为 Buffer Pool 中的页数据是顺序访问的。我们可以通过设置 innodb_read_ahead_threshold 参数控制 MySQL 何时进行预读，也可以控制 MySQL 预读数据时候对于数据的敏感度，如果 Buffer Pool 里面存储的数据页的频繁值大于 innodb_read_ahead_threshold 的值，INNODB 就会启动一个异步的预读操作，innodb_read_ahead_threshold 的值可以设置为0-64的任何一个值。默认值是56，值设置的越高就会造成检索更加严格。当设置为8的时候，只有小于8个页数据被读进 Buffer Pool 中才会被检索。

随机预读：随机预读能够将在内存当中的将被读取到的页信息很快的组织到一个区中，而且这些页面的读取顺序不用分顺序，InnoDB 能够很快调度 Buffer Pool 当中相似的很多页的信息，并且发出请求。这些页可能并不是连续的，要想使用这个功能就要设置 innodb_random_read_ahead 这个参数为 ON。

5、动态调整 MySQL 后台进程刷新

我们可以控制 MySQL 后台进程何时刷新，或者根据 MySQL 负载的情况动态调整。 InnoDB 会自动维护后台作业自动从 Buffer Pool 当中清除脏数据，当 Buffer Pool 中的脏页占用比达到 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中的数据的脏数据的占用比。这种自动调节的方式还能够防止突然的并发 redo 变大，但是 flush 的时候将不能进行普通的 IO 读写操作。

我们知道 InnoDB 使用日志的方式是循环使用的，在重用前一个日志文件之前，InnoDB 就会将这个日志这个日志记录相关的所有在 Buffer Pool 当中的数据刷新到磁盘，也就是所谓的 sharp checkpoint，和 sqlserver 的 checkpoint很像。当一个插入语句产生大量的 redo 信息，需要记录的日志当前 redo log 文件不能够完全存储，也会写入到当前的 redo 文件当中。当 redo log 当中的所有使用空间都被用完了的，就会触发 sharp checkpoint，所以这个时候即使脏数据占有率没有达到 innodb_max_dirty_pages_pct ，还是会进行刷新。这种算法是经得住考验的，所以说千万不要随便设置，最好是默认值。但是我们从中也就会知道为什么一个事物的 redo 信息只能记录在一个 redo log 文件当中了。

因为有这么多的好处，所以 innodb_adaptive_flushing 的值默认就是 true 的。

6、buffer poll 缓冲保留

我们可以配置的 InnoDB 如何保留当前的缓冲池的状态，以避免在服务器重新启动后，还要经历一个漫长的暖机时间。

通过 innodb_buffer_pool_dump_at_shutdown 来设置，当设置这些参数以后，MySQL 就会在机器重启后快速恢复以前内存中的数据，当然这些数据是从磁盘重新读取到 Buffer Pool 当中的，个人认为这个值还是需要配置一下的，当然这会花费一些时间，在重新读取这些数据到内存当中的时候，新的 DML 操作是不能够进行操作的。

这些数据是怎么恢复呢？其实 InnoDB_BUFFER_PAGE_LRU 表（INFORMATION_SCHEMA ）会记录缓存的 table ID 和 page ID,通过这个来恢复。 在 LOAD 数据进入 Buffer Pool 之前，可以设置 Buffer Pool 恢复数据的百分比，当然默认值肯定是100，不设置默认就是全部恢复。

SET GLOBAL innodb_buffer_pool_dump_pct=40;

通过以下的语句，设置是否重启服务器的时候重新 LOAD 数据进入 Buffer Pool，默认是关闭的，还可以在启动时候指定或者在 my.cnf 当中指定：

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

如果我们保存了 Buffer Pool 的信息，也可以在启动 MySQL 时候指定重新 LOAD 数据：

SET GLOBAL innodb_buffer_pool_dump_now=ON;保存信息，在重启前要指定
SET GLOBAL innodb_buffer_pool_load_now=ON;LOAD信息

如果要终止 Buffer Pool 加载，可以指定负载运行：

SET GLOBAL innodb_buffer_pool_load_abort=ON;

也可以通过以下的命令查看状态：

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

而且我们可以通过 innodb 的 performance schema 监控 Buffer Pool 的 LOAD 状态：

打开或者关闭 stage/innodb/buffer pool load

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

打开以下参数来获取最近的Buffer Pool的dump状态：

SET GLOBAL innodb_buffer_pool_dump_now=ON; SET GLOBAL innodb_buffer_pool_load_now=ON; SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current; SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;

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

四、结语

InnoDB Buffer Pool 不可不说是 MySQL 的核心功能之一，合理的配置 InnoDB Buffer Pool 能够显著地提高我们数据库的性能。而且本身 InnoDB Buffer Pool 的配置也给予我们很高的可控性，我们可以根据自己的业务场景，负载等寻找较优的配置。

不过这些都要建立在你对自己的 MySQL 服务充分了解的基础上，不然可能会适得其反。