InnoDB Buffer Pool特性详解

  Buffer pool是innodb缓存数据及索引的区域，以达到快速访问热点数据的目的。Buffer pool会尽量让热点数据留在内存中，而不经常访问的数据则会逐渐刷出内存。数据的加载和刷出采用一种特殊LRU算法管理。

在专用的数据库服务器中，buffer pool建议设置为物理内存大小的60%~80%，以便缓存足够的数据，缓存的数据越多，性能越像内存数据库。

Buffer pool的管理算法：

Buffer pool由两个子链表组成，一个是new sublist，存储最近访问的数据。一个是old sublist，存储不是经常访问的数据。两个子表特征如下：

• 默认情况下，old sublist 占buffer pool的3/8(由innodb_old_blocks_pct控制，默认值37).
• old sublist的头部与new sublist 尾部相接，当数据首次被读入buffer pool时，会放在old sublist的头部而不是new sublist的头部（与普通LRU算法不同的地方）。
• 当访问old sublist中的数据时，数据会被移动到new sublist的头部，此动作被称作make 'young'。当数据是由用户请求加载入内存时，访问动作会立刻产生，数据页会被移到new sublist。当数据是由预读取操作而加载入内存，可能一直都不会被访问，数据则会呆在old sublist直至被刷出内存。

此为buffer pool的示意图，buffer pool的大小由innodb_buffer_pool_size控制。

配置多个buffer pool：

当buffer pool的大小达到数个GB的时候，可将其拆分成多个buffer pool来减少多线程操作时的争用，以提高系统性能。拆分成多个buffer pool时，每个buffer pool都是独立管理，当数据被加载入内存时候，会采用哈希算法，随机的分配到一个buffer pool中。

多个buffer pool由innodb_buffer_pool_instaces(buffer pool数量)和innodb_buffer_pool_size(所有buffer pool总大小)控制，注意Innodb_buffer_pool_instances只有在innodb_buffer_pool_size大于1GB时才会生效，buffer pool会按照配置的个数平分大小。建议保持每个buffer pool的大小都不低于1GB。

buffer pool数据加载调优：

通常情况下，由查询发起的数据加载，会由于访问动作而直接移动到new sublist的头部。同时也会导致相同数量的页被刷出内存。如果是因为全表扫描而将数据放到new sublist的头部，可能导致大量的热点数据被挤出buffer pool，而innodb_old_blocks_time正是为了解决此问题而存在的，此参数默认值为1000(ms)，代表数据被make young之前必须在old sublist停留的时间。当超过此时长后，数据再次被访问，才会被移入到new sublist。如此那些只访问一次，后续不再访问的数据则会一直呆在old sublist直至被刷出内存。

innodb_old_blocks_pct参数用来配置old sublist的占比，默认值为37，可调整范围为[5,95]。此参数可以在系统运行时使用set global命令动态调整（需要super权限）。当需要运行大表扫描，而后续不会频繁访问时，我们可以将innodb_old_blocks_pct设置为5，让这种只访问一次的数据降低内存占比并快速刷出内存。

buffer pool的预读取:

预读取是innodb根据当前页的访问规律来预测那些数据页将来可能用到，如果满足条件，则将数据页所在的区(extent)全部读入内存，是提升系统性能的一种方式。预读总共有2种：

• 线性预读(Linear read-ahead)

当一个extent中被顺序读取的页的达到一定数量时，系统会认为这个extent中剩下的页很快也会被用到，因此会发出一个异步的读取指令，将该extent所有的数据页都读入buffer pool。这个顺序读取的数量由参数innodb_read_ahead_threshold控制，默认值为56，代表一个extent中当56个页被顺序读取时，则会触发针对该extent的预读取。该参数取值范围为[0,64]。越大代表预读取条件越严格。此特性多在范围扫描表时触发。

• 随机预读(Random read-ahead)

随机预读取是根据当前已经存在buffer pool中的页来预测哪些数据将会被使用到。如果在buffer pool中，存在13个连续的页来自同一个extent时(不考虑页的读取顺序)，系统会发出异步预读取指令，将该页剩下的页全部读入buffer pool。该特性由innodb_random_read_ahead控制，默认是off，如果打开需要将参数设置为ON。此特性多在随机访问一个表的大部分数据时触发（非顺序扫描）。

对于预读取的功能，Innodb提供了3个状态变量进行监控：

innodb_buffer_pool_read_ahead_rnd 随机预读取的页数

innodb_buffer_pool_read_ahead 预读取的页数

innodb_buffer_pool_read_ahead_evicted 预读取后没有被访问又被刷出buffer pool的页数

通过以上三个参数可以判断预读取的效率，如果innodb_buffer_pool_read_ahead_evicted占比过高（代表大量预读取数据未被访问），可以将预读取条件设置的更严格一些。

buffer pool的刷新调整：

数据库运行时，读入buffer pool的数据页被不断修改，这些被修改后但是没有刷新到磁盘的即叫做脏页。参数innodb_max_dirty_pages_pct_lwm可以控制当脏页占buffer pool的比例到达多少时，开始刷新buffer pool，防止buffer pool中脏页达到innodb_max_dirty_pages_pct(默认75)设定的值。即当脏页比例达到75%时，系统会采用一种激进的方式将脏页刷回磁盘。短时间内大量的磁盘I/O可能导致系统性能下降。

在刷新buffer pool时，可以用innodb_flush_neighbors细致调整页刷新动作，控制在刷新页时是否将改页同一个extent的页一起刷回磁盘，此参数有3个值：

• 0，刷新时不会刷新同一个extent中的页。
• 1，刷新时会将同一个extent中连续的页刷新回磁盘(默认)。
• 2，刷新时将extent中所有的页刷新回磁盘

对于普通HDD来说，磁盘寻道是十分昂贵的操作，因此将同一个extent中一起刷回可以减少I/O压力。而对于SSD磁盘，寻道时间可以忽略不计，因此可以将extent页分开刷新,减少每次刷新的页数。

在写密集的系统，大量的写操作会伴随产生大量的redo。当发生redo日志重用时，目标日志中所有的redo对应的脏页都要立刻刷新回磁盘，会产生陡然的性能下降现象(sharp checkpoint)。为解决这类问题，除了监控buffer pool中脏页的比例，innodb还会监控redo日志产生的速度，根据这两项因素自动调整脏页的刷新速度，此特性叫做自适应刷新。

自适应刷新由参数innodb_adaptive_flusing和innodb_adaptive_flusing_lwm控制，默认是启用的。当buffer pool中脏页的比例超过innodb_adaptive_flusing_lwm设定的比例时，自适应刷新特性会被触发，系统开始自动调整刷新速度，防止出现sharp checkpoint。

快速恢复buffer pool状态:

经过一段时间运行后，系统已经知道哪些数据经常访问并在buffer pool中保存了大量的热点数据，如果此时重启数据库，则buffer pool的状态会全部丢失。特对是针对buffer pool比较大的数据库，数据要重新被读入buffer pool,需要相当长的时间让系统"热"起来。

而InnoDB可以在关闭前将buffer pool的状态保存下来，重启后再快速将其恢复，让系统快速恢复至正常运行状态。

通过设置打开参数innodb_buffer_pool_dump_at_shutdown，可以告诉innodb在关闭时将buffer pool状态保存至磁盘文件（虽然buffer pool可能有数个GB，但保存到磁盘上的实际是只是表空间和页的编号，并不保存实际数据）：

 再次启动时，通过指定参数innodb-buffer-pool-load-at-startup=on来告诉innodb读取之前保存的状态。

 你还可以通过innodb_buffer_pool_dump_now、innodb_buffer_pool_load_now在系统运行时保存和加载buffer pool状态。并通过状态变量Innodb_buffer_pool_dump_status和Innodb_buffer_pool_load_status来实时观察进度。