mysql 污表_mysqldump造成Buffer Pool污染的研究

前言：

最近Oracle MySQL在其官方Blog上贴出了 5.6中一些变量默认值的改。其中innodb_old_blocks_time 的默认值从0替换成了1000(即1s)

关于该参数的作用摘录如下：

how long in milliseconds (ms) a block inserted into the oldsublistmust stay there after its first access before it can be moved to the new sublist. Increasing this value protects against the buffer pool being filled up by data that is referenced only for a brief period, such as during afull table scan.

其实作用就是：减小单次的大批量数据查询(类似于mysqldump的行为)对于BufferPool(下称BP)的污染。

说到这里就不得不提一下BP的midpoint insert 机制。

下文就将对于这个机制做一定分析和讨论。

一、 Buffer Pool 的insert 机制

BP可以被认为是一条长链表。被分成young 和 old两个部分，其中old默认占37%的大小(由innodb_old_blocks_pct 配置)。靠近顶端的Page表示最近被放问。靠近尾端的Page表示长时间未被访问。而这两个部分的交汇处成为midpoint。每当有新版的Page需要加载到BP时，该page都会被插入到midpoint的位置，并声明为old-page。当old部分的page，被访问到时，该page会被提升到链表的顶端，标识为young。

由于table scan的操作是先load page，然后立即触发一次访问。所以当innodb_old_blocks_time =0 时，会导致table scan所需要的page不读的作为young page被添加到链表顶端。而一些使用较为不频繁的page就会被挤出BP，使得之后的SQL会产生磁盘IO，从而导致响应速度变慢。这也就是标题中所提到的BP污染。

二、 改innodb_old_blocks_time 的效果

percona之前也做过相关测试，其结论是time=0时，正常访问的吞吐量下降为10%；当time=1000时，吞吐量和没有备份时的性能一致。

是否真是如此呢，我们来亲自测试一下。

下面是测试结果：

其中concurrency代表sysbench中 --num-threads的数值。

OPT代表该环境下，没有mysqldump时的sysbench QPS。

余下两列分别代表有mysqldump时的sysbench QPS。

Concurrency

OPT

old_time=0

old_time=1000

1

17394

1836

2141

2

29703

3670

3981

3

47347

5683

6540

4

64717

6805

8337

5

83551

8676

15885

6

99396

12978

19893

7

112330

16491

26022

8

126600

23840

33346

9

138468

30760

39194

10

150365

39034

48925

11

163053

43174

60352

12

174916

52066

70180

13

174160

63853

78076

14

173786

65164

80661

15

174268

70965

90633

16

175044

80871

102629

17

175583

90689

103423

18

175939

94805

112629

19

175114

93303

120625

由结果可以看出，time=1000并没有给查询性能带来很大的提升。最佳情况下也只是比time=0时提高80%的性能。

为什么呢？

其实不难了解，表中的concurrency很大程度上决定了测试page的冷热程度。并发数越大，每面产生的并行请求就越多，从而每个page被访问的频率就越高，page在LRU链表中的位置也就越靠顶端。反之亦然。

那么我们来想想下高频率热点数据访问时的情况。这时虽然mysqldump访问的page会不断加载在LRU顶端，但是高频度的热点数据访问会以更快的速度把page再次抢占到LRU顶端。从而导致mysqldump加载入的page会被迅速刷下，并立即被evict(淘汰)。因此，time=0或1000对这种压力环境下的访问不会造成很大影响，因为dump的数据根本抢占不过热点数据。

同样，超低频率的数据访问也是一样的情况。由于数据访问频度很低，大量的page都处于LRU链表的尾端。所以无论dump的page被加载到head或是midpoint位置，都会在热点数据的前面。也就是说无论怎样，数据page都会被淘汰。所以，这种压力环境下的性能同样不会随着time值的配置变化有很大浮动。

真正能够享受到time带来的福利的是那些 处于midpoint边缘的不温不火的数据。

从下图也可以看出，性能提升最大的情况集中在中等访问量的情况下，也即 37%的位置上

三、 Mid Point位置带来的影响

从之前的分析也可以得出这样的结论：innodb_old_blocks_time的作用范围对page的冷热情况有直接联系。而innodb_old_blocks_pct 又决定了BP的数据分布。

那么 innodb_old_blocks_pct 的调节，能够左右 innodb_old_blocks_time的影响范围。

上图的曲线也证明了这样的观点。当innodb_old_blocks_pct 调节到60%时，波峰也相应平移到了 60%的位置。

总结：

1. innodb_old_blocks_time =1000 一定程度上可以降低mysqldump类型的访问对数据库性能带来的影响。

2. innodb_old_blocks_time =1000 的完善效果有限，对于处于midpoint附近的page能带来最大的提升效果。