mysql 5.6 表分区_MySQL5.7.23 VS MySQL5.6.21 分区表性能对比测试

最新推荐文章于 2021-01-19 23:26:58 发布

泷小渊

最新推荐文章于 2021-01-19 23:26:58 发布

阅读量140

点赞数

文章标签： mysql 5.6 表分区

本文链接：https://blog.csdn.net/weixin_32184669/article/details/113194182

版权

为评估MySQL从5.6.21升级到5.7.23版本的性能，针对分区表的读写做了对比测试。

【测试环境】

1、两台HP380的物理机，配置一致，CPU：Intel(R) Xeon(R) CPU E5-2650 v3 @ 2.30GHz，内存：128G，磁盘：2*300G,10K(1) 8*900G,10K(10)

2、测试实例还原了生产集群的实例，测试分区表记录数约3800万

3、采用生产真实样本SQL语句，模拟并发线程16、32、64、128、256情况下，对比SQL执行1000万次的QPS和TPS性能。

4、MySQL的配置为各自版本的上线标准配置

【测试结论】

1、分区表在只读情况下，MySQL 5.7的性能略低于5.6的性能，约在15%左右，但随着并发线程数的增加，MySQL5.7的性能接逐渐近于5.6的性能

为什么MySQL5.7分区表的查询性能会有下降，后面我再进一步分析。

2、分区表在只写情况下，MySQL 5.7的性能明显好于5.6的性能，性能提升在40%以上，随着并发线程数的增加，MySQL5.7的性能比较稳定，但5.6的性能开始逐步下降

这个差异可能与5.7的配置参数调整有关，

innodb_io_capacity=1000

innodb_read_io_threads=8

innodb_write_io_threads=8

3、分区表在混合读写(读写比例约1:1)情况下，MySQL 5.7的性能明显好于5.6的性能，性能提升在30%以上，随着并发线程数的增加，MySQL5.7的性能比较稳定，但5.6的性能开始逐步下降

【进一步分析】

分区表在只读场景，并发线程不高的情况下，MySQL 5.7的性能略低于5.6的性能。下面从一些维度对比了两个版本下相同查询SQL的执行性能。

【两个版本差异的补充说明】

1、external_lock不同，按照文档上的说明计算，5.7分区锁定数为0，5.6分区锁定数为11

The server increments this variable for each call to its external_lock() function, which generally occurs at the beginning and end of access to a table instance. There might be differences among storage engines. This variable can be used, for example, to discover for a statement that accesses a partitioned table how many partitions were pruned before locking occurred: Check how much the counter increased for the statement, subtract 2 (2 calls for the table itself), then divide by 2 to get the number of partitions locked.

2、执行计划显示不同，在符合的分区partitions，优化器预估的记录条数rows，根据条件过滤到的记录百分比filtered，格外信息extra四个字段不一致

MySQL5.7

id: 1

select_type: SIMPLE

partitions: p20190223,p20190224,p20190225,p20190226,p20190227,pMax

type: ref

key_len: 610

ref: const,const

rows: 1

filtered: 100.00

Extra: NULL

MySQL5.6