主键无序插入对性能的影响以及innodb buffer的效率指标分析

我们都知道，mysql的表是以主键为索引列的索引，在理想的情况下，我们希望对表的插入，是按照主键的顺...



我们都知道，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 有足够的内存,否则插入性能会越来越低。

作者历史文章：

有一种主键重复冲突叫自增字段溢出

Mysql的表中含有Blob字段对性能的影响有几何？

又一个有趣的mysql死锁测试与源码分析

Mysql5.7 的错误日志中最常见的note级别日志解释

pt-table-checksum检查mysql5.7主从一致性的小bug.

mysql5.6与mysql5.7的半同步对比测试

mysql性能分析

mysql再一个有趣的死锁现象--删除空行导致

Xtrabackup 是否支持mysql 5.7 ?

又一个有趣的mysql死锁测试与源码分析

Mysql的meta data lock 源码分析-初篇

mysql的purge线程知多少

replace into与insert into ...on duplicate key update的区别以及陷阱

在MYSQL中通过唯一性索引删除同一条纪录出现死锁的分析与总结

Mysql5.7半同步复制源码解析-last

MySQL5.7事务提交过程以及无损复制源码解析

从一个调试结果来看mysql5.7的无损复制

    关注 数据库随笔