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

为评估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

Handler_external_lock

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

id: 1

select_type: SIMPLE

type: ref

key_len: 610

ref: const,const

rows: 12

Extra: Using where

执行计划中partitions和filtered的差异,是由于explain命令默认选项不同造成的。

在5.7以前的版本中,想要显示partitions需要使用explain partitions命令;要显示filtered需要使用explain extended命令。在5.7版本后,默认explain直接显示partitions和filtered中的信息。

 

3、16并发线程压测平均CPU使用率(usr%),MySQL5.7在QPS小于MySQL5.6的情况下,用户CPU使用率却高于5.6

 

4、CPU高消耗的函数调用栈不同,主要差异在于MySQL5.7.23在execute_sqlcom_select函数中调用handle_query,而MySQL5.6.21为handle_select,代码本身逻辑做了修改。

MySQL5.7

MySQL5.6

 

数据库运维经验分享&MySQL源码学习漫漫路

posted @ 2019-03-14 18:14  wangdong  阅读(1833)  评论(0编辑  收藏