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

This has results for in-memory sysbench with InnoDB and MySQL versions 5.0, 5.1, 5.5, 5.6, 5.7 and 8.0. The previous post covered MyISAM. There are four types of tests: write-heavy, scan-heavy, point-query and inlist-query. For MyISAM the results for the tests within each group were similar. That is less true for InnoDB.

tl;dr - because the other tl;dr are too long

• InnoDB in 5.5 frequently has lousy performance for write-heavy tests. I didn't debug it.
• InnoDB 5.7 and 8.0 have great scan performance, are much better for high-concurrency workloads (to be covered in a future post), but suffer from more CPU overhead for low-concurrency workloads.

tl;dr - from 5.0.96 to 8.0.3

• For update-nonindex QPS decreased 41% on the i5 NUC and 52% on the i3 NUC. Results for update-one and update-inlist were similar. The regression was smaller for update-index and insert. For delete 8.0.3 gets more QPS than 5.0.96.
• For scan-heavy tests the QPS for MySQL 8.0.3 is between 1.05X and 1.26X better than for 5.0.96 on all tests except read-only with range-size=100 where it is 2% to 5% worse. 
• For point-query the QPS decreased 36% on the i5 NUC and 46% on the i3 NUC
• For the inlist-heavy tests the QPS decreased ~22%.
• The QPS regression is larger for point-query tests than for inlist-heavy. New MySQL releases bring new features which brings new CPU overheads. For point-query there is 1 row fetched per SELECT versus 100 per select for the inlist-heavy tests, so the inlist-heavy amortize that overhead over more rows.

tl;dr - from 5.6.35 to 8.0.3

• For update-nonindex QPS decreased 23% on the i5 NUC and 30% on the i3 NUC. Results for update-one and update-inlist were similar. The regression was smaller for update-index. Unlike the 5.0 to 8.0 result QPS decreased by ~25% for delete and ~25% for insert.
• For scan-heavy tests InnoDB 5.7.17 has the best QPS. While there have been regressions since then, scan performance in InnoDB 8.0.3 is still better than in 5.6.35 by up to 1.5X.
• For point-query the QPS decreased ~11% on the i5 NUC and 17% on the i3 NUC
• For the inlist-heavy tests the QPS decreased ~7%

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

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 InnoDB 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 QPS decreased 41% on the i5 NUC and 52% on the i3 NUC
• From MySQL 5.6.35 to 8.0.3 QPS decreased 23% on the i5 NUC and 30% on the i3 NUC
• For the update-index test the QPS decreased ~20% from 5.0.96 to 8.0.3. From 5.6.35 to 8.0.3 it decreased 6% for the i5 NUC and 13% for the i3 NUC.
• For the delete test 8.0.3 gets more QPS than 5.0.96. But from 5.6.35 to 8.0.3 the QPS decreased by ~25%.
• For the insert test the QPS decreased 9% for the i5 NUC and 15% for the i3 NUC from 5.0.96 to 8.0.3. From 5.6.35 to 8.0.3 it decreased 22% for the i5 NUC and 27% for the i3 NUC. 

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 QPS for MySQL 8.0.3 is between 1.05X and 1.26X better than for 5.0.96 on all tests except read-only with range-size=100 where it is 2% to 5% worse. 
• InnoDB 5.7.17 has the best range scan performance. While there have been regressions since then, scan performance in InnoDB 8.0.3 is still better than in 5.6.35 and up to 1.5X better.

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 decreased 36% on the i5 NUC and 46% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 the decreased ~11% on the i5 NUC and 17% on the i3 NUC. 

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 for each MySQL version relative to the QPS for MySQL 5.0.96. From MySQL 5.0.96 to 8.0.3 the QPS decreased ~22%. From MySQL 5.6.35 to 8.0.3 the QPS decreased ~7%.

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 38% on the i5 NUC and 41% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS decreased by 20% on the i5 NUC and 11% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3306    1.00    4335    1.00    5.0.96
2718    0.82    4051    0.93    5.1.72
 645    0.20    1155    0.27    5.5.51
2201    0.67    3349    0.77    5.6.35
2050    0.62    3192    0.74    5.7.17
2037    0.62    3086    0.71    8.0.1
1960    0.59    2709    0.62    8.0.2
1963    0.59    2693    0.62    8.0.3

update-one

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 26% on the i5 NUC and 33% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
13748   1.00    15689   1.00    5.0.96
12084   0.88    13269   0.85    5.1.72
 9065   0.66    10343   0.66    5.5.51
 9650   0.70    10969   0.70    5.6.35
 8097   0.59     9648   0.61    5.7.17
 7552   0.55     8876   0.57    8.0.1
 6491   0.47     8222   0.52    8.0.2
 6468   0.47     8147   0.52    8.0.3

update-index

From MySQL 5.0.96 to 8.0.3 QPS decreased by 17% on the i5 NUC and 20% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS decreased by 6% on the i5 NUC and 13% on the i3 NUC. The regression here is much smaller than for the other update-heavy tests.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 3414   1.00    6185    1.00    5.0.96
 3323   0.97    5394    0.87    5.1.72
 3265   0.96    5173    0.84    5.5.51
 3134   0.92    5489    0.89    5.6.35
 2983   0.87    5861    0.95    5.7.17
 2910   0.85    5494    0.89    8.0.1
 2798   0.82    5170    0.84    8.0.2
 2721   0.80    5160    0.83    8.0.3

update-nonindex

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 24% on the i5 NUC and 31% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
11321   1.00    13146   1.00    5.0.96
 9965   0.88    11392   0.87    5.1.72
 4543   0.40     7294   0.55    5.5.51
 7860   0.69    10148   0.77    5.6.35
 6337   0.56     9135   0.69    5.7.17
 6295   0.56     8514   0.65    8.0.1
 5499   0.49     7812   0.59    8.0.2
 5398   0.48     7745   0.59    8.0.3

delete

From MySQL 5.0.96 to 8.0.3 QPS is 1.1X better on the i5 NUC and ~1.5X better on the i3 NUC. But from MySQL 5.6.35 to 8.0.3 QPS decreased by ~25%.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 7484   1.00    13819   1.00    5.0.96
 7547   1.01    12725   0.92    5.1.72
 9950   1.33    14830   1.07    5.5.51
14590   1.95    19900   1.44    5.6.35
12758   1.70    17621   1.28    5.7.17
12143   1.62    16539   1.20    8.0.1
11104   1.48    15260   1.10    8.0.2
11085   1.48    15202   1.10    8.0.3

read-write with --range-size=100

From MySQL 5.0.96 to 8.0.3 QPS is 1.04X better on the i5 NUC and 1.17X better on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS decreased by 1% on the i5 NUC and 2% on the i3 NUC. From the peak in MySQL 5.7.17 to 8.0.3 QPS decreased by 7% on the i5 NUC and 5% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
 8208   1.00    11038   1.00    5.0.96
 7875   0.96    10126   0.92    5.1.72
 8904   1.08    11275   1.02    5.5.51
 9844   1.20    11591   1.05    5.6.35
10152   1.24    12290   1.11    5.7.17
 9694   1.18    11698   1.06    8.0.1
 9578   1.17    11570   1.05    8.0.2
 9610   1.17    11481   1.04    8.0.3

read-write with --range-size=10000

From MySQL 5.0.96 to 8.0.3 QPS is ~1.27X better. From MySQL 5.6.35 to 8.0.3 QPS is ~1.5X better. From the peak in MySQL 5.7.17 to 8.0.3 QPS decreased by 3% on the i5 NUC and 5% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
330     1.00    387     1.00    5.0.96
316     0.96    376     0.97    5.1.72
293     0.89    363     0.94    5.5.51
283     0.86    335     0.87    5.6.35
442     1.34    500     1.29    5.7.17
434     1.32    489     1.26    8.0.1
423     1.28    491     1.27    8.0.2
421     1.28    486     1.26    8.0.3

read-only with --range-size=100

From MySQL 5.0.96 to 8.0.3 QPS decreased by 5% on the i5 NUC and 2% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS is ~1.07X better. From the peak in MySQL 5.7.17 to 8.0.3 QPS decreased by 3% on the i5 NUC and 8% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
10748   1.00    12490   1.00    5.0.96
 9858   0.92    11816   0.95    5.1.72
10100   0.94    11396   0.91    5.5.51
 9644   0.90    11120   0.89    5.6.35
11369   1.06    12247   0.98    5.7.17
10516   0.98    11750   0.94    8.0.1
10398   0.97    11980   0.96    8.0.2
10509   0.98    11874   0.95    8.0.3

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

From MySQL 5.0.96 to 8.0.3 QPS is 1.22X better on the i5 NUC and 1.3X better on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS is ~1.5X better. From the peak in MySQL 5.7.17 to 8.0.3 QPS decreased by 2% on the i5 NUC and 5% on the i3 NUC.

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.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
264     1.00    317     1.00    5.0.96
255     0.97    305     0.96    5.1.72
239     0.91    294     0.93    5.5.51
226     0.86    272     0.86    5.6.35
358     1.36    394     1.24    5.7.17
351     1.33    392     1.24    8.0.1
341     1.30    384     1.21    8.0.2
341     1.30    387     1.22    8.0.3

read-only with --range-size=10000

From MySQL 5.0.96 to 8.0.3 QPS is 1.22X better on the i5 NUC and 1.28X better on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS is ~1.5X better. From the peak in MySQL 5.7.17 to 8.0.3 QPS decreased by 7% on the i5 NUC and 5% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
268     1.00    316     1.00    5.0.96
256     0.96    303     0.96    5.1.72
239     0.89    295     0.93    5.5.51
221     0.82    272     0.86    5.6.35
359     1.34    393     1.24    5.7.17
352     1.31    387     1.22    8.0.1
345     1.29    388     1.23    8.0.2
343     1.28    385     1.22    8.0.3

point-query.pre

From MySQL 5.0.96 to 8.0.3 QPS decreased ~40%. From MySQL 5.6.35 to 8.0.3 QPS decreased ~30%.

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.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
28360   1.00    27125   1.00    5.0.96
22444   0.79    23049   0.85    5.1.72
18938   0.67    20004   0.74    5.5.51
18329   0.65    20505   0.76    5.6.35
16592   0.59    18126   0.67    5.7.17
16010   0.56    16402   0.60    8.0.1
15031   0.53    16175   0.60    8.0.2
15208   0.54    16188   0.60    8.0.3

point-query

From MySQL 5.0.96 to 8.0.3 QPS decreased ~40%. From MySQL 5.6.35 to 8.0.3 QPS decreased by 11% on the i5 NUC and 17% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
28733   1.00    26883   1.00    5.0.96
21941   0.76    23487   0.83    5.1.72
18541   0.65    20287   0.75    5.5.51
18554   0.65    19257   0.72    5.6.35
16883   0.59    17723   0.66    5.7.17
16163   0.56    16828   0.63    8.0.1
15005   0.52    16626   0.62    8.0.2
15492   0.54    17121   0.64    8.0.3

random-points.pre

From MySQL 5.0.96 to 8.0.3 QPS decreased ~20%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~6%. The reduction for point-query tests above is larger than the reduction for random-points here. I assume that point-query suffers more from the new CPU overhead in new MySQL releases (more features == more code in network, parse and optimize). The point query test pays that overhead cost per row fetched. For the random-points test here, 100 rows are fetched per SELECT statement.

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.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3665    1.00    3801    1.00    5.0.96
3516    0.96    3707    0.98    5.1.72
3267    0.89    3468    0.91    5.5.51
3040    0.83    3203    0.84    5.6.35
2931    0.80    3121    0.82    5.7.17
2810    0.77    3006    0.79    8.0.1
2817    0.77    3030    0.80    8.0.2
2832    0.77    3023    0.80    8.0.3

random-points

From MySQL 5.0.96 to 8.0.3 QPS decreased ~21%. From MySQL 5.6.35 to 8.0.3 QPS decreased by 7%.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
3693    1.00    3793    1.00    5.0.96
3498    0.95    3684    0.97    5.1.72
3269    0.89    3434    0.91    5.5.51
3036    0.82    3223    0.85    5.6.35
2947    0.80    3123    0.82    5.7.17
2801    0.76    3009    0.79    8.0.1
2839    0.77    3042    0.80    8.0.2
2833    0.77    2998    0.79    8.0.3

hot-points

From MySQL 5.0.96 to 8.0.3 QPS decreased 32%. From MySQL 5.6.35 to 8.0.3 QPS decreased by ~10%.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
4918    1.00    5525    1.00    5.0.96
4388    0.89    5036    0.91    5.1.72
3931    0.80    4595    0.83    5.5.51
3666    0.75    4242    0.77    5.6.35
3458    0.70    3898    0.71    5.7.17
3314    0.67    3753    0.68    8.0.1
3335    0.68    3764    0.68    8.0.2
3338    0.68    3764    0.68    8.0.3

insert

From MySQL 5.0.96 to 8.0.3 QPS decreased 9% on the i5 NUC and 15% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS decreased by 22% on the i5 NUC and 27% on the i3 NUC.

i3 NUC          i5 NUC
QPS     ratio   QPS     ratio   engine
8027    1.00     9681   1.00    5.0.96
7726    0.96     9215   0.95    5.1.72
6932    0.86     8851   0.91    5.5.51
9340    1.16    11244   1.16    5.6.35
7853    0.98     9892   1.02    5.7.17
7413    0.92     9257   0.96    8.0.1
6941    0.86     8567   0.88    8.0.2
6829    0.85     8822   0.91    8.0.3