There are four types of tests: write-heavy, scan-heavy, point-query and inlist-query. Performance within each group is usually similar. Reading the summary below the results for modern InnoDB are mostly an improvement. This is good news because these tests are limited to low-concurrency and the many improvements in modern InnoDB don't help here.
tl;dr - performance for InnoDB in 8.0.3 compared to 5.0.96
- Full index scans were faster on the i5 NUC and both slower & faster on the i3 NUC. Shorter scans from read-write and read-only were always faster.
- QPS was about the same on update-inlist, but decreased on update-one, update-index, update-nonindex and insert.
- QPS increased on point-query
- QPS increased on inlist-query for the i5 NUC and frequently increased for the i3 NUC
tl;dr - performance for InnoDB in 8.0.3 compared to 5.6.35
- Full index scans were faster on the i5 NUC and slower on the i3 NUC. Shorter scans from read-write and read-only were always faster.
- QPS was about the same on update-inlist, decreased on update-one, increased on update-index, about the same on update-nonindex and decreased on insert.
- QPS increased on point-query
- QPS increased on inlist-query for the i5 NUC and frequently increased for the i3 NUC
Configuration
The tests used InnoDB 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. My use of sysbench is described here. The my.cnf files are here for the i3 NUC and i5 NUC. I tried to tune my.cnf for all engines including: set innodb_purge_threads=1 to reduce mutex contention, 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 built-in InnoDB is used for MySQL 5.0 and 5.1.
Sysbench is run with 2 tables, 80M rows/table on the i3 NUC and 160M rows/table on the i5 NUC. The database is larger than RAM. Each test is repeated for 1 and 2 clients. Each test runs for 600 seconds except for the insert-only test which runs for 300 seconds. The database is larger than RAM.
The i5 NUC has more RAM, a faster SSD and faster CPU than the i3 NUC, but I disabled turbo boost on the i5 NUC many months ago to reduce variance in performance and with that the difference in CPU performance between these servers is much smaller. Assuming I will always disable turbo boost in the future I might as well stick with an i3 NUC for my next purchase.
Results
All of the data for the tests is on github for the i3 NUC and the i5 NUC. Results for each test are listed separately below.The tests are explained here.
The graphs and tables that follow present the relative QPS. The relative QPS is the QPS for the test divided by the QPS for the base case. The base case is the QPS for InnoDB from either MySQL 5.0.96 or 5.6.35. When the relative QPS is less than one than the engine is slower than the base case.
Graphs
There are 4 types of tests and I provided a graph for each type: write-heavy, scan-heavy, point-query, inlist-query. The results within each group are not as similar as for the in-memory tests, so I provide extra graphs here. The tests are explained here. The graphs have the QPS relative to InnoDB 5.0.96.
The write-heavy group includes update-inlist, update-one, update-index, update-nonindex, delete and insert. The graphs are for update-nonindex and update-index. To keep this from getting out of hand I save the analysis for the per-test sections.
The scan-heavy group includes a full scan of the PK index, read-write with range-size set to 100 and 10,000 and then read-only with range-size set to 100 and 10,000. The graphs are for read-write with range-size=100, read-only with range-size=10,000 and the full scan with the QPS relative to InnoDB 5.0.96. The results for read-only and the full scan are from the tests run after the write-heavy tests.
Scan performance for InnoDB improved a lot starting in MySQL 5.7 but QPS and throughput weren't always better in modern InnoDB. The per-test sections have more details.
The point-query group includes the point-query test run before and then after the write-heavy tests. The graph is for the test run after the write-heavy tests using the QPS relative to InnoDB 5.0.96.
The inlist-query group includes the hot-points test and the random-points tests run before and then after the write-heavy tests. The graph is for the test run after the write-heavy tests using the QPS relative to InnoDB 5.0.96.
full scan
The full scan of the PK index is done before and after the write-heavy tests. Full scan on the i5 NUC is much faster starting with InnoDB 5.7. For the i3 NUC that isn't true. Results for InnoDB 5.5 are an outlier, and 5.5 is odd on many tests. Overheads that impact this include whether page write-back is in progress and fragmentation that causes random IO and/or prevents read ahead. This impact might be larger on the i3 NUC because it has a slower SSD and is less likely to finish write-back before the scan test starts, and more sensitive to the loss of IO capacity from write-back.
The scan throughput for InnoDB in MySQL 8.0 relative to 5.0:
- Is 3.47 on the i5 NUC and 0.44 on the i3 NUC for the scan before write-heavy tests
- Is 2.78 on the i5 NUC and 1.41 on the i3 NUC for the scan after write-heavy tests
The scan throughput for InnoDB in MySQL 8.0 relative to 5.6:
- Is 1.40 on the i5 NUC and 0.32 on the i3 NUC for the scan before write-heavy tests
- Is 1.40 on the i5 NUC and 0.82 on the i3 NUC for the scan after write-heavy tests
i3 NUC i5 NUC
Mrps ratio Mrps ratio engine
0.737 1.00 0.714 1.00 5.0.96
0.758 1.03 0.718 1.01 5.1.72
1.280 1.74 2.176 3.05 5.5.51
1.019 1.38 1.766 2.47 5.6.35
0.320 0.43 2.424 3.39 5.7.17
0.327 0.44 2.480 3.47 8.0.3
full scan - after write-heavy
Mrps ratio Mrps ratio engine
0.533 1.00 0.898 1.00 5.0.96
0.583 1.09 0.910 1.01 5.1.72
1.523 2.86 2.132 2.37 5.5.51
0.914 1.71 1.786 1.99 5.6.35
0.829 1.56 2.406 2.68 5.7.17
0.751 1.41 2.500 2.78 8.0.3
update-inlist
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is ~0.94. Relative to MySQL 5.6 it is 1.08 on the i5 NUC and 0.94 on the i3 NUC. Compared to the other update-only tests, this one spends more time in the storage engine per update statement and it is less sensitive to new overheads in parse and optimize.
The regression here for InnoDB 5.7 and 8.x is smaller than for in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
211 1.00 359 1.00 5.0.96
208 0.99 378 1.05 5.1.72
190 0.90 342 0.95 5.5.51
212 1.00 307 0.86 5.6.35
213 1.01 392 1.09 5.7.17
199 0.94 335 0.93 8.0.3
update-one
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 0.52 on the i5 NUC and 0.42 on the i3 NUC. Relative to MySQL 5.6 it is 0.73 on the i5 NUC and 0.66 on the i3 NUC. While this is an IO-bound benchmark, this test is not read IO-bound because it updates the same row and that data should remain cached.
The result here for InnoDB 5.7 and 8.x is similar to the result for in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
14308 1.00 15488 1.00 5.0.96
12052 0.84 13224 0.85 5.1.72
8584 0.60 10431 0.67 5.5.51
9120 0.64 10988 0.71 5.6.35
7839 0.55 9570 0.62 5.7.17
5992 0.42 8046 0.52 8.0.3
update-index
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 0.80 on the i5 NUC and 0.59 on the i3 NUC. Relative to MySQL 5.6 it is 1.04 on the i5 NUC and 1.44 on the i3 NUC. I assume that new CPU overhead in parse and optimize explains the regression from 5.0.96 to 8.03. This test is more IO-bound than the update-nonindex test that follows because it must do secondary index maintenance. The i3 NUC has a slower SSD and that might explain why the i3 NUC regression here is worse than the i5 NUC, but they are similar on update-nonindex.
The result here for InnoDB 5.7 and 8.x is similar to the result for in-memory sysbench on the i5 NUC. But the i3 NUC regression is larger here.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
537 1.00 1103 1.00 5.0.96
518 0.96 1076 0.98 5.1.72
186 0.35 585 0.53 5.5.51
220 0.41 850 0.77 5.6.35
312 0.58 924 0.84 5.7.17
319 0.59 883 0.80 8.0.3
update-nonindex
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 0.88. Relative to MySQL 5.6 it is ~0.95. The small regression might be explained by new CPU overheads in modern MySQL.
The regression here for InnoDB 5.7 and 8.x is smaller than for in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
1665 1.00 2882 1.00 5.0.96
1616 0.97 2759 0.96 5.1.72
1471 0.88 2582 0.90 5.5.51
1526 0.92 2683 0.93 5.6.35
1556 0.93 2773 0.96 5.7.17
1458 0.88 2541 0.88 8.0.3
delete
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.36 on the i5 NUC and 1.15 on the i3 NUC. Relative to MySQL 5.6 it is 0.94. The small regression after 5.6.35 might be explained by new CPU overheads in modern MySQL.
Modern InnoDB was also faster than older InnoDB on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
2242 1.00 3527 1.00 5.0.96
2256 1.01 3551 1.01 5.1.72
2055 0.92 3334 0.95 5.5.51
2729 1.22 5068 1.44 5.6.35
2763 1.23 5115 1.45 5.7.17
2576 1.15 4794 1.36 8.0.3
read-write with --range-size=100
The QPS for InnoDB in MySQL 8.0 relative to both 5.0 and 5.6 is ~1.08 on the i5 NUC and ~1.02 on the i3 NUC.
Modern InnoDB was also faster than older InnoDB on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
2089 1.00 2826 1.00 5.0.96
2043 0.98 2695 0.95 5.1.72
1810 0.87 2606 0.92 5.5.51
2028 0.97 2799 0.99 5.6.35
2208 1.06 3077 1.09 5.7.17
2135 1.02 3038 1.08 8.0.3
read-write with --range-size=10000
The QPS for InnoDB is ~1.50 relative to both MySQL 5.0 and 5.6. InnoDB range scans are faster starting in MySQL 5.7. The benefit here is larger than for the test above because the range scan here is longer (10,000 rows vs 100).
Modern InnoDB was also faster than older InnoDB on in-memory sysbench but the improvement here is larger.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
168 1.00 219 1.00 5.0.96
167 0.99 216 0.99 5.1.72
170 1.01 214 0.98 5.5.51
169 1.01 217 0.99 5.6.35
257 1.53 335 1.53 5.7.17
247 1.47 328 1.50 8.0.3
read-only with --range-size=100
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.32 on the i5 NUC and 1.10 on the i3 NUC. Relative to MySQL 5.6 it is 1.32 on the i5 NUC and 1.10 on the i3 NUC. The speedup here for InnoDB 5.7 is larger than the speedup for InnoDB 5.7 on the read-write test with range-size=100.
Modern InnoDB was slower than older InnoDB on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
2903 1.00 3640 1.00 5.0.96
2831 0.98 3638 1.00 5.1.72
2892 1.00 3668 1.01 5.5.51
2885 0.99 3687 1.01 5.6.35
3272 1.13 4954 1.36 5.7.17
3207 1.10 4794 1.32 8.0.3
read-only.pre with --range-size=10000
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is ~1.40. Relative to MySQL 5.6 it is ~1.50. InnoDB range scans became faster starting in MySQL 5.7. The benefit here is larger than for the test above because the range scan here is longer (10,000 rows vs 100).
Modern InnoDB was also faster than older InnoDB on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
149 1.00 182 1.00 5.0.96
148 0.99 181 0.99 5.1.72
143 0.96 178 0.98 5.5.51
139 0.93 173 0.95 5.6.35
212 1.42 273 1.50 5.7.17
206 1.38 264 1.45 8.0.3
read-only with --range-size=10000
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.48 on the i5 NUC and 1.40 on the i3 NUC. Relative to MySQL 5.6 it is ~1.50. This is similar to the result above for read-write with range-size=10000.
This test is run after the write-heavy tests. The test in the previous section was run before the write-heavy tests. They have similar QPS and b-tree fragmentation didn't reduce QPS on this test.
Modern InnoDB was also faster than older InnoDB on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
149 1.00 181 1.00 5.0.96
149 1.00 179 0.99 5.1.72
143 0.96 178 0.98 5.5.51
140 0.94 178 0.98 5.6.35
210 1.41 275 1.52 5.7.17
208 1.40 267 1.48 8.0.3
point-query.pre
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.07 on the i5 NUC and 1.03 on the i3 NUC. Relative to MySQL 5.6 it is 1.11 on the i5 NUC and 1.06 on the i3 NUC.
Modern InnoDB is slightly faster than older InnoDB here but was much slower than it on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
3986 1.00 5478 1.00 5.0.96
3839 0.96 5276 0.96 5.1.72
3854 0.97 5092 0.93 5.5.51
3879 0.97 5250 0.96 5.6.35
4264 1.07 6198 1.13 5.7.17
4124 1.03 5873 1.07 8.0.3
point-query
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.08 on the i5 NUC and 0.98 on the i3 NUC. Relative to MySQL 5.6 it is 1.11 on the i5 NUC and 1.08 on the i3 NUC. It is good to see a small improvement since 5.6.35.
This test is run after the write-heavy tests. The test in the previous section was run before the write-heavy tests. They have similar QPS and b-tree fragmentation didn't reduce QPS on this test.
Modern InnoDB is about as fast as older InnoDB here but was much slower than it on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
4278 1.00 5464 1.00 5.0.96
4127 0.96 5256 0.96 5.1.72
3878 0.91 5022 0.92 5.5.51
3896 0.91 5310 0.97 5.6.35
4332 1.01 6155 1.13 5.7.17
4180 0.98 5891 1.08 8.0.3
random-points.pre
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.51 on the i5 NUC and 1.35 on the i3 NUC. Relative to MySQL 5.6 it is 2.13 on the i5 NUC and 1.18 on the i3 NUC.
The QPS improvement from 5.6.35 to 5.7.17 on the i5 NUC is large. Looking at vmstat and iostat output I see InnoDB 5.6 used ~33% more storage reads and and almost 3X more CPU per fetched row. There is also QPS improvement on the i3 NUC from 5.6 to 5.7 but it isn't as large.
This test is run before the write-heavy tests. The next section has results for the test run after write-heavy tests. They get similar QPS which means that b-tree fragmentation isn't an issue here for an in-memory workload.
Modern InnoDB is faster than older InnoDB here but was slower than it on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
49 1.00 70 1.00 5.0.96
49 1.00 64 0.91 5.1.72
53 1.08 51 0.73 5.5.51
56 1.14 50 0.71 5.6.35
69 1.41 105 1.50 5.7.17
66 1.35 106 1.51 8.0.3
random-points
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 1.69 on the i5 NUC and 1.00 on the i3 NUC. Relative to MySQL 5.6 it is 1.56 on the i5 NUC and 0.94 on the i3 NUC. I don't yet understand the large improvement starting in MySQL 5.7 both here and in the previous section.
Modern InnoDB is faster than older InnoDB here but was slower than it on in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
53 1.00 59 1.00 5.0.96
53 1.00 55 0.93 5.1.72
53 1.00 59 1.00 5.5.51
56 1.06 64 1.08 5.6.35
51 0.96 100 1.69 5.7.17
53 1.00 100 1.69 8.0.3
hot-points
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 0.78 on the i5 NUC and 0.66 on the i3 NUC. Relative to MySQL 5.6 it is 0.88 on the i5 NUC and 0.90 on the i3 NUC. This test is always in-memory as it fetches the same data per query. The results are similar to the results for in-memory sysbench.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
4925 1.00 4704 1.00 5.0.96
4352 0.88 4450 0.95 5.1.72
3982 0.81 4591 0.98 5.5.51
3609 0.73 4167 0.89 5.6.35
3455 0.70 3771 0.80 5.7.17
3246 0.66 3690 0.78 8.0.3
insert
The QPS for InnoDB in MySQL 8.0 relative to 5.0 is 0.86. Relative to MySQL 5.6 it is 0.81 on the i5 NUC and 0.99 on the i3 NUC. The regression since MySQL 5.0.96 is likely from new code above the storage engine (optimizer, parser).
The regression here for modern InnoDB is similar to the results on in-memory sysbench. My guess is that this test doesn't do many storage reads per insert so the new CPU overheads in modern MySQL are significant.
i3 NUC i5 NUC
QPS ratio QPS ratio engine
5868 1.00 7535 1.00 5.0.96
5665 0.97 7431 0.99 5.1.72
4479 0.76 6063 0.80 5.5.51
5097 0.87 7991 1.06 5.6.35
5730 0.98 7234 0.96 5.7.17
5043 0.86 6468 0.86 8.0.3
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment