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 old sublist must 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 a full 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能带来最大的提升效果。

上一个：mysql三种批量增加的性能分析
下一个：MySQL删除数据Delete与Truncate语句使用比较