There is still a large regression from MySQL 5.0 to 8.0 and I will continue to document that and expect to help reduce it. I expect 86215 to be closed as not a bug.
tl;dr for in-memory sysbench at low concurrency
- MySQL 5.7 and 8.0 lose 10% to 20% of QPS versus 5.6 for tests not dominated by range scans
- InnoDB range scans were made faster starting in MySQL 5.7
- In the worst case, MySQL 5.0 gets more than 2X the QPS versus 5.7 and 8.0.
- The largest QPS regression occurs between MySQL 5.5 and 5.6.
Why
I should have noticed earlier in the process that SSL was the problem. I frequently rant about the value of explaining performance results and this is a reminder of that value. I didn't explain why MySQL 5.7 and 8.0 did worse than 5.6 and it turned out that my results were wrong. I spent some time debugging the slowdown using PMP and Linux perf but clearly I should have spent more time on that.
The short story is that I was trying to do too much and didn't have time to focus. Too much in this case meant I was too busy running tests and collecting results and didn't have time to explain the results. Lesson learned.
Configuration
I repeated tests for 5.7 and 8.0 with SSL disabled and share results for in-memory sysbench in this post.
I tested MySQL with upstream 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. I disabled SSL for 5.7.17 and 8.0.1 by adding ssl=0 to my.cnf. I used the i5 NUC servers described here and the my.cnf used are here. I run mysqld and the sysbench client on the same server. The binlog is enabled but sync-on-commit is disabled. I also set innodb_purge_threads to 1 to avoid mutex contention on a small server.
Sysbench is run with 4 tables and 1M rows per table. Tests are repeated for 1, 2 and 4 clients. The database fits in the InnoDB buffer pool. My usage of sysbench is described here. That explains the helper scripts that invoke sysbench and collect performance metrics.
Results
The first table is the QPS for MySQL 5.7.17 and 8.0.1 relative to 5.6.35 for sysbench with one client. The QPS for 5.7.17 and 8.0.1 is better than 5.6.35 when the value is greater than 1 as it is for tests that do longer range scans because something was done to make that faster in modern InnoDB. In the tests not dominated by range scans the QPS for 5.7.17 and 8.0.1 are 10% to 20% less than for 5.6.35 and the loss from 5.6 to 5.7 is similar to the loss from 5.7 to 8.0.
QPS relative to 5.6.35
----------------------
5717 801 test
1.04 .96 update-index
.90 .84 update-nonindex
.91 .83 delete
.90 .82 write-only
1.06 1.03 read-write.range100
1.51 1.49 read-write.range10000
.94 .86 read-only.range10
1.11 1.01 read-only.range100
1.49 1.42 read-only.range1000
1.44 1.41 read-only.range10000
.95 .85 point-query
.89 .82 insert
The next table is the QPS for 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1 relative to 5.0.96 for the same tests using 1 sysbench client. In the worst case the QPS for 5.0.96 is 2.5X better than 8.0.1 and more than 2X better than 5.7.17. The worst case is for the update-only (update-index, update-nonindex) and point-query tests.
QPS relative to 5.0.96
----------------------
5172 5551 5635 5717 801 test
.89 .73 .45 .46 .43 update-index
.89 .59 .47 .42 .39 update-nonindex
.92 .92 .75 .68 .62 delete
.93 .93 .76 .68 .62 write-only
.92 .99 .65 .69 .67 read-write.range100
.95 .92 .55 .84 .82 read-write.range10000
.92 .89 .58 .55 .50 read-only.range10
.94 .91 .57 .63 .57 read-only.range100
.96 .92 .56 .83 .80 read-only.range1000
.95 .92 .55 .80 .78 read-only.range10000
.85 .77 .47 .45 .40 point-query
.95 .93 .81 .72 .67 insert
The data below has the QPS for each test. Each test was run for 1, 2 and 4 clients and my usage of sysbench is explained in a previous post.
update-index
1 2 4 concurrency/version
13047 19521 22763 inno5096
11592 18439 19833 inno5172
9486 14393 16389 inno5551
5806 9837 12354 inno5635
6013 10242 13498 inno5717
5585 9509 12771 inno801
update-nonindex
1 2 4 concurrency/version
22333 32560 32961 inno5096
19914 29050 31829 inno5172
13082 19164 21049 inno5551
10435 15680 18487 inno5635
9430 14120 19012 inno5717
8730 13277 17746 inno801
delete
1 2 4 concurrency/version
25893 35659 38785 inno5096
23777 38591 41418 inno5171
23726 33260 38085 inno5551
19461 28797 35684 inno5635
17621 26110 34634 inno5717
16146 24270 32245 inno801
write-only
1 2 4 concurrency/version
22364 32090 36125 inno5096
20907 32503 35141 inno5172
20727 29856 33173 inno5551
16892 25376 30915 inno5635
15176 22631 29425 inno5717
13778 20834 27262 inno801
read-write.range100
1 2 4 concurrency/version
17972 30414 42474 inno5096
16527 30055 41973 inno5172
17857 28714 41496 inno5551
11653 18109 25325 inno5635
12387 18359 25379 inno5717
12002 17727 24253 inno801
read-write.range10000
1 2 4 concurrency/version
608 1089 1556 inno5096
580 1033 1513 inno5171
557 996 1410 inno5551
337 604 849 inno5635
509 873 1067 inno5717
501 858 1039 inno801
read-only.range10
1 2 4 concurrency/version
30016 48794 78703 inno5096
27554 49464 76052 inno5172
26863 48108 77370 inno5551
17372 30663 50570 inno5635
16383 28695 48139 inno5717
14861 26625 45225 inno801
read-only.range100
1 2 4 concurrency/version
19891 34307 56572 inno5096
18636 34808 54051 inno5172
18125 33561 51729 inno5551
11247 20922 32930 inno5635
12453 22441 34585 inno5717
11387 21221 32934 inno801
read-only.range1000
1 2 4 concurrency/version
4623 8304 12115 inno5096
4429 8114 11757 inno5171
4272 7851 11167 inno5551
2590 4840 6816 inno5635
3859 7041 8764 inno5717
3682 6877 8395 inno801
read-only.range10000
1 2 4 concurrency/version
492 882 1259 inno5096
468 827 1222 inno5172
452 808 1147 inno5551
273 497 686 inno5635
392 700 865 inno5717
386 693 841 inno801
point-query
1 2 4 concurrency/version
41951 68718 123988 inno5096
35785 66515 111602 inno5172
32385 56441 81061 inno5551
19674 36269 55266 inno5635
18749 32669 55340 inno5717
16694 30372 51878 inno801
insert
1 2 4 concurrency/version
13932 14182 12795 inno5096
13194 15157 12521 inno5171
12968 17106 14447 inno5551
11288 16268 19355 inno5635
10039 15428 19922 inno5717
9306 14391 18763 inno801
