为评估MySQL从5.6.21升级到5.7.23版本的性能，针对分区表的读写做了对比测试。 n order to evaluate the performance of MySQL upgrade from version 5.6.21 to version 5.7.23, a comparative test was made for the read and write of the partition table.
[Test environment] 1. Two HP380 physical machines with the same configuration, CPU: Intel(R) Xeon(R) CPU E5-2650 v3 @ 2.30GHz, memory: 128G, disk: 2*300G, 10K(1) 8 *900G,10K(10)
2. The test instance restored the instance of the production cluster, and the number of records in the test partition table was about 38 million. 3. Using the production real sample SQL statement, simulating the concurrent threads 16, 32, 64, 128, 256, compared with the SQL execution 10 million times QPS and TPS performance.
4. The configuration of MySQL is the online standard configuration of the respective version [Test conclusion] 1. When the partition table is read-only, the performance of MySQL 5.7 is slightly lower than that of 5.6, about 15%, but with the increase of the number of concurrent threads With the increase, the performance of MySQL 5.7 is gradually approaching the performance of 5.6. Why does the query performance of MySQL 5.7 partition table decline? I will analyze it further later.
2. In the case of write-only partition tables, the performance of MySQL 5.7 is significantly better than that of 5.6, with a performance improvement of more than 40%. With the increase in the number of concurrent threads, the performance of MySQL 5.7 is relatively stable, but the performance of 5.6 is starting to gradually This difference may be related to the adjustment of configuration parameters in 5.7, innodb_io_capacity=1000 innodb_read_io_threads=8 innodb_write_io_threads=83. In the case of mixed read and write (read and write ratio of about 1:1), the performance of MySQL 5.7 is significantly better than that of 5.6. The increase is more than 30%. With the increase in the number of concurrent threads, the performance of MySQL 5.7 is relatively stable, but the performance of 5.6 has begun to gradually decline [Further analysis] Partitioned tables are in read-only scenarios and concurrent threads are not high, MySQL 5.7 The performance is slightly lower than the performance of 5.6. The following compares the execution performance of the same query SQL under the two versions from some dimensions.
[Supplementary description of the differences between the two versions] 1. The external_lock is different. According to the instructions on the document, the number of partition locks in 5.7 is 0, and the number of partition locks in 5.6 is 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. The execution plan is different. In the matching partitions, the number of records estimated by the optimizer rows, filtered according to the percentage of records filtered by conditions, and the extra information is inconsistent with the four fields of extra MySQL5.7id: 1select_type: SIMPLEpartitions: p20190223,p20190224 ,p20190225,p20190226,p20190227,pMaxtype: refkey_len: 610ref: const,constrows: 1filtered: 100.00Extra: NULLMySQL5.6id: 1select_type: SIMPLEtype: refkey_len: 610ref: const,constrows: 12Extra: Using where the difference between partitions and filtered in the execution plan , Is caused by the different default options of the explain command.
In versions prior to 5.7, you need to use the explain partitions command to display partitions; you need to use the explain extended command to display filtered. After version 5.7, explain directly displays the information in partitions and filtered by default.
3. The average CPU usage rate (usr%) of 16 concurrent threads is tested. When the QPS of MySQL 5.7 is less than MySQL 5.6, the user CPU usage rate is higher than 5.64. The function call stack with high CPU consumption is different. The main difference is MySQL5.7.23 calls handle_query in execute_sqlcom_select function, while MySQL5.6.21 uses handle_select, and the logic of the code itself has been modified.
MySQL5.7 MySQL5.6
【测试环境】
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
