Sysbench: in-memory, MyISAM and a small server, MySQL 5.0 to 8.0

This has results for in-memory sysbench with MyISAM and MySQL versions 5.0, 5.1, 5.5, 5.6, 5.7 and 8.0. I will publish results for InnoDB tomorrow.

tl;dr - from 5.0.96 to 8.0.3

• For write-heavy tests QPS decreased by more than 50%
• For scan-heavy tests QPS decreased by no more than 14%
• For point-query tests QPS decreased by 30% on the i5 NUC and 40% on the i3 NUC
• For inlist-query tests QPS decreased by 25% on the i5 NUC and 21% on the i3 NUC. There is a large reduction in QPS between MySQL 5.5.51 and 5.6.35. Maybe the optimizer overhead grew for queries with large in-lists.

tl;dr - from 5.6.35 to 8.0.3

• For write-heavy tests QPS decreased by ~30%
• For scan-heavy tests QPS did not decrease
• For point-query tests QPS decreased by ~15%
• For inlist-query tests QPS increased by 9% on the i5 NUC and decreased by 5% on the i3 NUC

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. 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: 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.

Sysbench is run with 2 tables and 2M rows per table. 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 fits in RAM as the i3 NUC has 8gb of RAM and the i5 NUC has 16gb.

I repeat tests on an i5 NUC and i3 NUC. 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.

Why MyISAM

Results with MyISAM are a great complement to results with InnoDB. InnoDB has changed significantly across releases. I doubt that MyISAM has. Results with MyISAM make it easier to isolate the impact of the code that runs about MyISAM including parse, optimize and network handling.

I also like MyISAM even though I never depended on it in production.

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. For all tests but scan the result has the QPS for the test with 1 client relative to the QPS for MyISAM in MySQL 5.0.96. The tests are explained here.

Graphs

There are 4 types of tests and I provided a graph for each type: write-heavy, scan-heavy, point-query, inlist-query. Fortunately the results within each group are similar and one graph per group is sufficient. The tests are explained here.

The write-heavy group includes update-inlist, update-one, update-index, update-nonindex, delete and insert. The graph is for update-nonindex using the QPS for each MySQL version relative to the QPS for MySQL 5.0.96. From MySQL 5.0.96 to 8.0.3 the QPS decrease is more than 50%. From MySQL 5.6.35 to 8.0.3 the decrease is ~30%. I assume the QPS decrease is from the CPU overhead of code above MyISAM.

The scan-heavy group includes 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 graph is for read-write with range-size=100 using the QPS for each MySQL version relative to the QPS for MySQL 5.0.96. The regression for this test is worse than for the other scan-heavy tests. From MySQL 5.0.96 to 8.0.3 the QPS decrease is 10% on the i5 NUC and 14% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 there is no decrease.

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 for each MySQL version relative to the QPS for MySQL 5.0.96. From MySQL 5.0.96 to 8.0.3 the QPS decrease is 30% on the i5 NUC and 40% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 the decrease is ~15%.

The inlist-query group includes the random-points 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 for each MySQL version relative to the QPS for MySQL 5.0.96. From MySQL 5.0.96 to 8.0.3 the QPS decrease is 25% on the i5 NUC and 21% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 the increase is 9% on the i5 NUC and the decrease is 5% on the i3 NUC.

 

scan

I don't have much to write. The scan takes 0 or 1 seconds when the time is rounded to a whole number and it is hard to use that for comparisons.

update-inlist

Here and the sections that follow have the QPS and QPS ratio for each MySQL release on the i3 and i5 NUC. The QPS ratio is the QPS for the release relative to the QPS for MySQL 5.0.96 using the test with 1 client.

From MySQL 5.0.96 to 8.0.3 QPS decreased by more than 50%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~38%. All of the update-only tests show a similar regression. QPS on the i5 NUC is not much better than on the i3 NUC probably because I disabled turbo boost.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
9099    1.00    9191    1.00    5.0.96
8361    0.92    8483    0.92    5.1.72
7711    0.85    7892    0.86    5.5.51
6590    0.72    7008    0.76    5.6.35
5171    0.57    5663    0.62    5.7.17
5143    0.57    5468    0.59    8.0.1
4122    0.45    4477    0.49    8.0.2
4134    0.45    4352    0.47    8.0.3

update-one

From MySQL 5.0.96 to 8.0.3 QPS decreased by more than 50%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~25%. All of the update-only tests show a similar regression.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
21569   1.00    21117   1.00    5.0.96
18491   0.86    18254   0.86    5.1.72
15775   0.73    15884   0.75    5.5.51
12947   0.60    13329   0.63    5.6.35
11019   0.51    11734   0.56    5.7.17
10098   0.47    10773   0.51    8.0.1
 9197   0.43    10093   0.48    8.0.2
 8983   0.42     9920   0.47    8.0.3

update-index

From MySQL 5.0.96 to 8.0.3 QPS decreased by ~50%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~25%. All of the update-only tests show a similar regression from 5.0.96 to 8.0.3.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
18757   1.00    17301   1.00    5.0.96
15413   0.82    15654   0.90    5.1.72
13545   0.72    13793   0.80    5.5.51
11288   0.60    11519   0.67    5.6.35
 9504   0.51    10066   0.58    5.7.17
 8798   0.47     9407   0.54    8.0.1
 8038   0.43     8851   0.51    8.0.2
 8039   0.43     8744   0.51    8.0.3

update-nonindex

From MySQL 5.0.96 to 8.0.3 QPS decreased by more than 50%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~27%. All of the update-only tests show a similar regression from 5.0.96 to 8.0.3.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
21501   1.00    20200   1.00    5.0.96
17892   0.83    17926   0.89    5.1.72
15533   0.72    16066   0.80    5.5.51
12502   0.58    13353   0.66    5.6.35
10709   0.50    11458   0.57    5.7.17
 9811   0.46    10621   0.53    8.0.1
 8819   0.41     9667   0.48    8.0.2
 8852   0.41     9704   0.48    8.0.3

delete

From MySQL 5.0.96 to 8.0.3 QPS decreased by ~50%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~25%. The regression for delete is similar to the update-only tests above.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
19216   1.00    18615   1.00    5.0.96
16669   0.87    16932   0.91    5.1.72
14918   0.78    15045   0.81    5.5.51
12444   0.65    13008   0.80    5.6.35
11184   0.58    11624   0.62    5.7.17
10175   0.53    10779   0.58    8.0.1
 9387   0.49    10080   0.54    8.0.2
 9138   0.48     9989   0.54    8.0.3

read-write with --range-size=100

Of all the scan heavy tests that follow this one has the worst regression. But 10% on the i5 NUC and 14% on the i3 NUC over so many releases is not a big deal. The scans here are smaller than for the next test, so the regression from writes is more significant here.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 9359   1.00    9941    1.00    5.0.96
 8711   0.93    9595    0.97    5.1.72
 8860   0.95    9546    0.96    5.5.51
 7945   0.85    8842    0.89    5.6.35
 8304   0.89    9226    0.93    5.7.17
 8100   0.87    8848    0.89    8.0.1
 8089   0.86    8968    0.90    8.0.2
 8059   0.86    8919    0.90    8.0.3

read-write with --range-size=10000

The regression from 5.0.96 to 8.0.3 is small to non-existent. See the comment for the previous result.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
227     1.00    240     1.00    5.0.96
225     0.99    239     1.00    5.1.72
209     0.92    236     0.98    5.5.51
193     0.85    217     0.90    5.6.35
254     1.12    283     1.18    5.7.17
216     0.95    242     1.01    8.0.1
215     0.95    238     0.99    8.0.2
214     0.94    238     0.99    8.0.3

read-only with --range-size=100

The regression here is small. Compared to the result for read-write above with range-size=100, this test doesn't suffer the CPU regression from writes.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
8510    1.00    9549    1.00    5.0.96
8202    0.96    8939    0.94    5.1.72
8268    0.97    8937    0.94    5.5.51
7613    0.89    8324    0.87    5.6.35
8370    0.98    9121    0.96    5.7.17
8287    0.87    9307    0.97    8.0.1
8458    0.99    9374    0.98    8.0.2
8367    0.98    9129    0.96    8.0.3

read-only.pre with --range-size=10000

No regression here as QPS is flat to increasing from 5.0 to 8.0. This test is run before and after the write-heavy tests to determine the impact from fragmentation. If you compare the QPS from this test and the one that follows there is an impact as the QPS here is slightly larger.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
178     1.00    214     1.00    5.0.96
179     1.01    214     1.00    5.1.72
168     0.94    197     0.92    5.5.51
160     0.90    190     0.89    5.6.35
216     1.21    253     1.18    5.7.17
199     1.12    224     1.05    8.0.1
187     1.05    212     0.99    8.0.2
188     1.06    215     1.00    8.0.3

read-only with --range-size=10000

The regression here from 5.0 to 8.0 is small.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
175     1.00    188     1.00    5.0.96
173     0.99    187     0.99    5.1.72
162     0.93    186     0.99    5.5.51
150     0.86    170     0.90    5.6.35
199     1.14    223     1.19    5.7.17
169     0.97    189     1.01    8.0.1
168     0.96    186     0.99    8.0.2
168     0.96    186     0.99    8.0.3

point-query.pre

This test is run before the write-heavy tests. The result in the next section are from the same workload run after the write-heavy tests. The QPS difference between the two is small. From MySQL 5.0.96 to 8.0.3 the QPS decreased by 29% on the i5 NUC and 41% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 the QPS decreased by ~15%.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
27525   1.00    44193   1.00    5.0.96
23924   0.87    43199   0.98    5.1.72
20545   0.75    40376   0.91    5.5.51
19287   0.70    37051   0.84    5.6.35
17562   0.64    34449   0.78    5.7.17
17206   0.63    32156   0.73    8.0.1
16713   0.61    31871   0.72    8.0.2
16241   0.59    31504   0.71    8.0.3

point-query

From MySQL 5.0.96 to 8.0.3 the QPS decreased by 30% for the i5 NUC and 40% for the i3 NUC. From MySQL 5.6.35 to 8.0.3 the QPS decreased by ~14%.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
27032   1.00    44978   1.00    5.0.96
22935   0.85    43018   0.96    5.1.72
20339   0.75    39985   0.89    5.5.51
18974   0.70    36666   0.82    5.6.35
17837   0.66    34467   0.77    5.7.17
16810   0.62    32055   0.71    8.0.1
16327   0.60    31946   0.71    8.0.2
16352   0.60    31389   0.70    8.0.3

random-points.pre

This test is run before the write-heavy tests. The result in the next section are from the same workload run after the write-heavy tests. There is little difference between the two. From MySQL 5.0.96 to 8.0.3 the QPS decreased by 33% for the i5 NUC and 21% for the i3 NUC. From MySQL 5.6.35 to 8.0.3 the QPS decreased by ~5%. The biggest decrease in QPS comes between 5.5.51 and 5.6.35. The regression here is smaller than for the point-query tests above. The point-query does a lot of work (network, parse, optimize) to fetch 1 row. That overhead here is amortized over more rows as this uses an in-list to fetch 100 rows. But the regression here is larger than I expected perhaps because of the regression from 5.5 to 5.6.


i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
1971    1.00    3321    1.00    5.0.96
1951    0.99    3478    1.05    5.1.72
1941    0.98    3285    0.99    5.5.51
1682    0.85    2311    0.70    5.6.35
1625    0.82    2149    0.65    5.7.17
1591    0.81    2206    0.66    8.0.1
1584    0.80    2206    0.66    8.0.2
1559    0.79    2219    0.67    8.0.3

random-points

See the comment above. From MySQL 5.0.96 to 8.0.3 the QPS decreased by 25% for the i5 NUC and 21% for the i3 NUC. From MySQL 5.6.35 to 8.0.3 the QPS increased by 8% on the i5 NUC and decreased by 5% on the i3 NUC. The biggest decrease in QPS comes between 5.5.51 and 5.6.35.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
1917    1.00    3151    1.00    5.0.96
1895    0.99    3146    1.00    5.1.72
1844    0.96    3109    0.99    5.5.51
1584    0.83    2168    0.69    5.6.35
1589    0.83    2109    0.67    5.7.17
1559    0.81    2064    0.66    8.0.1
1530    0.80    2088    0.66    8.0.2
1512    0.79    2351    0.75    8.0.3

hot-points

From MySQL 5.0.96 to 8.0.3 the QPS decreased by ~25%. From MySQL 5.6.35 to 8.0.3 the QPS decreased by ~10%. The biggest decrease in QPS comes between 5.5.51 and 5.6.35 which is similar to the results above for random-points. Both use large in-lists.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   release
2689    1.00    2910    1.00    5.0.96
2570    0.96    2831    0.97    5.1.72
2559    0.95    2850    0.98    5.5.51
2192    0.82    2407    0.83    5.6.35
2113    0.79    2301    0.79    5.7.17
2057    0.76    2265    0.78    8.0.1
2015    0.75    2228    0.77    8.0.2
1937    0.72    2184    0.75    8.0.3

insert

From MySQL 5.0.96 to 8.0.3 the QPS decreased by ~50%. From MySQL 5.6.35 to 8.0.3 the QPS decreased by ~27%. This is similar to the other write-heavy tests.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
17603   1.00    16862   1.00    5.0.96
18724   1.06    19231   1.14    5.1.72
13510   0.77    14043   0.83    5.5.51
12056   0.68    12830   0.76    5.6.35
10471   0.59    10963   0.65    5.7.17
 9719   0.55    10147   0.60    8.0.1
 8906   0.51     9473   0.56    8.0.2
 8718   0.50     9316   0.55    8.0.3