InnoDB Buffer Pool巧配置全解

转载

叶金荣 2021-07-30 14:55:06

文章标签 数据 mysql 数据库脏数据默认值 文章分类 MySQL 数据库

作者：M哥

一、简介

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的时候还是会保持压缩的格式。直到被读取的时候才会调用解压程序进行解压。