innodb_buffer_pool特性和参数调整学习

一、Innodb buffer pool prefetch技术

为减少IO次数，提高mysql性能，innodb利用局部性原理，使用了两种page预取技术（预取page时，会读取整个extent，并且是异步读取）：Linear read-ahead 和 Random read-ahead。（问题：若是之前读取的page很随机，这种预取技术是否反而带来负面效果？）

1、Linear read-ahead 

innodb_read_ahead_threshold(0-64,默认是56)：innodb访问buffer pool中的pages，并且是顺序访问（being accessed sequentially），当达到一定的page数量后，即>=该参数设置的值时就会触发一个异步的prefetch，读取下一个extent（因为是sequential page access，因此能确定下一个extent）。例如：当值为56时，innodb在顺序读取了56个page后才会触发一次prefetch；而当值为8时，在顺序读取了8个page后就会触发一次prefect。因此，这个值越高，限制越严格。该参数可以在配置文件中添加，也可以用set global设置（需要super权限）。在没有这个参数的时候，innodb只会在读完当前extent的最后一个page的时候才会做出是否需要读取下一个extent的判断。

2、Random read-ahead

innodb_random_read_ahead（ON/OFF）：该技术不需要考虑读取page的顺序，而是根据已经在buffer pool中的page来预测什么时候需要从磁盘读取page。如果能找到buffer pool中某个extent的连续13个page（13 consecutive pages），innodb就会触发一个异步的prefetch，将该extent的剩余pages全读进内存。只需将该参数设置成ON或OFF即可。

另外，统计变量： Innodb_buffer_pool_read_ahead、Innodb_buffer_pool_read_ahead_evicted和Innodb_buffer_pool_read_ahead_rnd有助于调优Random read-ahead




二、buffer pool的adaptive flushing

当buffer pool中的脏页超过innodb_max_dirty_pages_pct.时就会开启后台线程来刷部分脏页。5.7.5及以后，当buffer pool中的脏页数量>=buffer pool的innodb_max_dirty_pages_pct时就会开始flushing。

innodb可以根据redo log的产生速度和buffer pool中刷脏页的数量来控制每秒刷的量，目的是为了刷脏页的速度能跟上对于clean page的需求。动态调整速率的好处是可以减少excessive式的刷新对IO的影响以及对读写的影响。

innodb的redo log会被循环使用，当需要循环使用一部分redo log，即某些redo需要被覆盖的时候，innodb会将那些涉及的redo记录对应的buffer pool中的dirty page数据全部刷到磁盘上，这被称为一次sharp checkpoint的过程。如果是写入密集型的服务，将会产生大量的redo并写到redo log中，如果redo log所有可用空间被用完就会触发一次sharp checkpoint，这会造成吞吐量的短暂下降。当需要进行sharp checkpoint，即便没达到innodb_max_dirty_pages_pct的值也会进行。

innodb_adaptive_flushing（true/false）：该参数可以在配置文件中设置，也可以通过set global 方式设置，默认值为TRUE。




三、buffer pool 的冷数据剔除策略

比起使用严格的LRU算法，innodb使用了一种技术用来减少拷贝到buffer pool中不经常被访问的数据的数量，目标是让经常被访问的热数据尽量保持在buffer pool中。该技术的原理是：所有被读进buffer pool的page默认都会被插入到LRU list的从尾部开始的3/8位置处，当他们第一次被访问的时候会被移到列表的头部（经常被访问的地方），这样从来没有被访问的page就不会到达列表的头部。从列表的插入点开始到列表的尾部这部分就成为列表的old区，根据LRU算法很有可能会被剔除。控制该插入点的参数为：innodb_old_blocks_pct。默认是37，即3/8，取值范围从5-95（随着值的增大，会越来越接近我们熟悉的LRU算法，热点数据总是占少数）。

当发生table scan或者index scan时，很多page读进buffer pool访问一次以后就不再访问了，对于这种情况，应该让这些page尽量待在old区，而不是直接移到列表头部。参数 innodb_old_blocks_time 可以用来设置被插入list的第一次被访问的page经过多长时间被移到list头部，这个值设置的越大，冷数据被剔除得就越快，默认值是1000。

上面两个参数可以配置也可以动态改。SHOW ENGINE INNODB STATUS 列出的统计信息可以帮助我们如何设定这两个参数

Total memory allocated 1107296256; in additional pool allocated 0
Dictionary memory allocated 80360
Buffer pool size   65535
Free buffers       0
Database pages     63920
Old database pages 23600
Modified db pages  34969
Pending reads 32
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 414946, not young 2930673
1274.75 youngs/s, 16521.90 non-youngs/s
Pages read 486005, created 3178, written 160585
2132.37 reads/s, 3.40 creates/s, 323.74 writes/s
Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000
Pages read ahead 1510.10/s, evicted without access 0.00/s
LRU len: 63920, unzip_LRU len: 0
I/O sum[43690]:cur[221], unzip sum[0]:cur[0]

• Old database pages is the number of pages in the “old” segment of the LRU list.

• Pages made young and not young is the total number of “old” pages that have been made young or not respectively.

• youngs/s and non-young/s is the rate at which page accesses to the “old” pages have resulted in making such pages young or otherwise respectively since the last invocation of the command.

• young-making rate and not provides the same rate but in terms of overall buffer pool accesses instead of accesses just to the “old” pages.

当有周期性的批量统计查询时，设置   innodb_old_blocks_time 可以将平常的热点数据都保持在buffer pool中。

当scan大表时，可将inndb_old_blocks_pct设置成一个较小的值，比如5，使得冷数据不会占用大部分的buffer pool。（但是这样scan大表就更慢了？）

当scan小表时（意味着可以将数据全部放到buffer中），可以设置成默认值，甚至50。