MySQL主键无序插入对性能的影响及innodb… - 51ste.com
登录|注册

MySQL主键无序插入对性能的影响及innodb…

作者:fisher  发布时间:2019-07-03 22:04:47  关键字:innodb bbuffer效率指标 

我们都知道,mysql的表是以主键为索引列的索引,在理想的情况下,我们希望对表的插入,是按照主键的顺序进行的,这样在建立B+树的叶子节点的双向链表的时候,可以基本都在一个页上就完成了叶子节点连接的操作。 当表的行插入是完全无序的时候,性能是否会存在影响? 在什么情况下会急剧影响性能?

下面是作者在项目组性能测试时的截图:

第一张图,从04月/12日 11点28分开始,update的qps稳定在3000左右,insert的qps稳定在2000左右。

主键无序插入对性能的影响

当项目组持续测试,等到第二天再来测试,看到的是下面第二张图。

第二张图,从04月/13日 10点23分开始,update的qps稳定在1100左右,insert 的qps位于1k以下,大约在700-800之间。

主键无序插入对性能的影响

观察现象后的总结:上面两个性能图的测试方式跟方法完全一致,唯一的区别是mysql表的数据量增加,当表的数据文件大小涨到大约24G时,系统的吞吐量只有刚开始空表(数据量很少)测试时候的1/3。

[root@DCBS5PJQ batchdemo]# ls -lrt |tail -10
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 business_object.ibd
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 batch_package_execution_error_seq.ibd
-rw-r----- 1 mysql mysql      114688 Apr 12 10:59 batch_jobdyn_config.ibd
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 batch_bn_data_seq.ibd
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 t_sys_role.ibd
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 t_sys_auth.ibd
-rw-r----- 1 mysql mysql       98304 Apr 12 10:59 transinfo.ibd
-rw-r----- 1 mysql mysql   251658240 Apr 13 10:31 dts_transaction_log.ibd
-rw-r----- 1 mysql mysql 24704450560 Apr 13 12:05 flow_activity.ibd
-rw-r----- 1 mysql mysql   943718400 Apr 13 13:31 flow_instance.ibd

在此,特别说明一下,数据库设置的innodb buffer的大小为20000M,大约20G。

数据库的qps为啥就将下来呢?我们来看看另外一个非常重要的监控指标,这个指标容易被忽视。

主键无序插入对性能的影响

第一个指标:pool read requests ,从04/13 10点25分左右,稳定在50K左右。

第二个指标:pool reads , 因为这个指标跟上一个指标在同一个图表上展示,但因为数值相差巨大,所以pool reads 的曲线跟0坐标的水平线完全重合,但我们可以看数值,大概在39左右。这个指标的含义是:当请求某个数据页的时候,但该页并没有缓存命中,所以需要从物理磁盘中读取。当出现有较多的pool reads时,数据库的整体吞吐量必定不高。

上面的图片是第二张qps图(也就是吞吐量低)对应的innodb buffer efficency 的图表, 然后我们拿qps较高的时候(也就是第一张qps图)所对应的innodb buffer efficency 图表进行比较,图表如下

主键无序插入对性能的影响

我们看到下面的指标值:

第一个指标,pool read requests 从04/12 11点27分开始,稳定在100K以上,而第一张innodb buffer efficency 图的值是50K左右。

第二个指标,pool reads 的最高值为0.0344,而第一张图为39。

因此,通过这两张图的对比,我们发现当出现bool reads 现象的时候,bool reads 的值越高, pool read requests 的值越低, 也就是innodb buffer 的效率越低,原因就是物理读影响了innodb buffer 中数据页的访问速度。

但为什么qps少了,数据库的innodb buffer的物理读反而多了? 这是因为随着表的增大,数据页也越来越多,但因为该业务系统是对表的操作是无序的,因为B+树的特性,在建立链表的时候,需要访问的页面是离散的,不集中的,因此也就是不能被热点缓存的页面,因此导致了大量的物理读,降低了innodb buffer的效率。

再回到之前文章开头提出的问题------- 无序插入时,在什么情况下会急剧影响性能? 

当表的数据文件大小,接近数据库的innodb buffer的大小之后(我们假设是在这个数据库中,只操作这一个表,所有的buffer都被这个表使用),也就是innodb buffer无法缓存整个表的所有页面时,就会开始存在页面换入换出,性能开始急剧下降。如果表增大到数据库只能缓存30%的页面时,数据库则几乎不可使用,插入效率急剧降低,大约只有200左右的qps,几乎每个页面访问都需要物理读取之后才可以访问时,mysql几乎处于不可用状态。(当然,这个跟机器的硬件有关系,物理IO性能越高,则支持的qps越多)。

基于以上原因,对于无序插入的表,请务必控制表的大小,以及确保innodb bbuffer 有足够的内存,否则插入性能会越来越低。

本文源自公众号 数据库随笔

本文由51ste.com网友编辑,未经授权,不得转载使用上述作品盈利;个人转载,需标明原文作者及出处。

更多相关文章

说说看法查看全部评论(0)

微信二维码

部落微信公众号

QQ二维码

部落QQ群二维码