tl;dr - from 5.0.96 to 8.0.3
- Regressions are frequently larger on the i3 NUC than the i5 NUC. Maybe modern MySQL and the core i3 NUC aren't great together because regressions are also larger on the i3 NUC for InnoDB.
- The insert rate decreased by 16% on the i5 NUC and 20% on the i3 NUC
- The query rate decreased by 6% on the i5 NUC and 10% on the i5 NUC for the test with 100 inserts/second
tl;dr - from 5.6.35 to 8.0.3
- Most of the drop in performance from 5.0 to 8.0 occurs between 5.6.35 and 8.0.3. The drop is similar for MyISAM and InnoDB. I assume the drop is from code above the storage engine.
- The insert rate decreased by 11% on the i5 NUC and 9% on the i3 NUC
- The query rate decreased by 8% on the i5 NUC and 9% on the i3 NUC for the test with 100 inserts/second
Configuration
The tests used MyISAM from upstream MySQL versions 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.3. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. The i5 NUC has more RAM, faster CPUs and faster storage than the i3 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, used the same charset and collation. For all tests the binlog was enabled but fsync was disabled for the binlog and database redo log. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.
The database fits in RAM as the test table has ~10M rows. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark loaded the table with 10M rows, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.
Results
All of the data for the tests is here.
Results: load
The graph below has the insert rate for each release relative to the rate for MyISAM in 5.0.96. There is a small regression over time in the insert rate. The loss from 5.7 to 8.0 is the largest. Fortunately, 8.0 is not GA yet and maybe this can be improved.
Additional metrics help to explain performance. The metrics are explained here. The CPU overhead per insert (Mcpu/i) has increased with each release (more features == more instructions to execute) and that explains the decrease in the insert rate. Otherwise the metrics look good. The increase in Mcpu/i from 5.0.96 to 8.0.3 is 19% for the i3 NUC and 16% for the i5 NUC. This matches the change in the insert rate.
i3 NUC
IPS wKB/i Mcpu/i size(GB)
5.0.96 27174 1.01 1404 2.1
5.1.72 28249 1.06 1372 2.1
5.5.51 24691 1.08 1555 2.1
5.6.35 23866 1.11 1592 1.7
5.7.17 24691 0.94 1543 1.7
8.0.3 21645 0.93 1675 1.7
i5 NUC
IPS wKB/i Mcpu/i size(GB)
5.0.96 33113 0.64 1123 1.7
5.1.72 34843 0.59 1108 1.7
5.5.51 31348 0.61 1192 1.8
5.6.35 31250 0.70 1193 2.1
5.7.17 29674 0.65 1236 1.9
8.0.3 27701 0.65 1305 2.0
Results: scan
Below are tables that show the number of seconds for each full index scan: 1 is the PK, 2/3/4 are the secondary indexes and 5 is the PK again. The scan doesn't take long and the result is rounded to a whole number so the numbers aren't that useful. If there is a regression from 5.0 to 8.0 it isn't apparent in this result.
#seconds to scan an index, i3 NUC
1 2 3 4 5 2+3+4 engine
- - - - - ----- ------
2 2 3 5 2 10 5.0.96
2 3 2 6 1 11 5.1.72
2 2 3 5 2 10 5.5.51
2 3 3 6 2 12 5.6.35
2 3 3 5 2 11 5.7.17
2 3 3 6 2 12 8.0.3
#seconds to scan an index, i5 NUC
1 2 3 4 5 2+3+4 engine
- - - - - ----- ------
1 3 2 4 2 9 5.0.96
1 2 3 4 2 9 5.1.72
1 2 3 4 2 9 5.5.51
2 2 3 5 2 10 5.6.35
1 3 2 5 1 10 5.7.17
2 2 3 4 2 9 8.0.3
Results: read-write, 1000 inserts/second
This section has results for the read-write tests where the writer does 1000 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 13% for the i3 NUC and 8% for the i5 NUC. That is small which is good.
i3 NUC
IPS.av IPS.99 wKB/i QPS.av QPS.99 CPU/q engine
999 998 10.28 5158 4706 5160 5.0.96
999 998 9.97 5443 5028 4816 5.1.72
999 998 10.09 5192 4980 5107 5.5.51
999 998 10.01 4956 4757 5456 5.6.35
999 998 10.00 4672 4464 5792 5.7.17
999 998 9.93 4470 4306 6049 8.0.3
i5 NUC
IPS.av IPS.99 wKB/i QPS.av QPS.99 CPU/q engine
999 998 10.79 5764 5383 4574 5.0.96
999 998 10.79 6144 5702 4215 5.1.72
999 998 10.84 5850 5694 4465 5.5.51
999 998 10.67 5744 5551 4613 5.6.35
999 998 10.58 5481 5285 4824 5.7.17
999 998 10.64 5284 5094 4994 8.0.3
Results: read-write, 100 inserts/second
This section has results for the read-write tests where the writer does 100 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 10% on the i3 NUC and 6% on the i5 NUC. That is small which is good.
All of the engines were able to sustain the target insert rate on average (ips.av) and write stalls were not a problem. Additional metrics help explain the performance and more detail on the metrics is here. The increase in the CPU overhead per query (CPU/q) is 12% for the i3 NUC and 7% for the i5 NUC. More CPU overhead probably explains the drop in QPS.
i3 NUC
IPS.av IPS.99 wKB/i QPS.av QPS.99 CPU/q engine
100 100 13.89 5102 4734 4952 5.0.96
100 100 14.54 5321 5091 4687 5.1.72
100 100 15.59 5291 5077 4730 5.5.51
100 100 15.41 5076 4829 5058 5.6.35
100 100 15.61 4792 4555 5379 5.7.17
100 100 13.34 4596 4394 5569 8.0.3
i5 NUC
IPS.av IPS.99 wKB/i QPS.av QPS.99 CPU/q engine
100 100 13.69 5719 5445 4366 5.0.96
100 100 13.49 5988 5770 4187 5.1.72
100 100 13.53 5992 5786 4214 5.5.51
100 100 13.41 5812 5621 4322 5.6.35
100 100 13.54 5476 5351 4612 5.7.17
100 100 13.56 5353 5188 4693 8.0.3
No comments:
Post a Comment