Friday, November 10, 2017

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

Thursday, November 9, 2017

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

Advice on advice

Advice is free but sometimes you get what you pay for. In technology there is an abundance of great ideas and much of that free advice might be excellent. Thanks to my time with web-scale MySQL I have a lot of experience in receiving advice so I will offer advice on giving it. I realize that my advice on advice applies to my advice. I don't write many posts like this, my last might have been this one.

A big problem is that time is limited. We don't have enough time to evaluate all of the good ideas. Just like a modern query optimizer we can't do exhaustive search. Our innovation budget is small so we then have to find the best idea worth doing that can be implemented and deployed given the small budget. Another problem is uncertainty. The system we have is likely good enough today. It was probably good enough yesterday. Any benchmark is unlikely to capture the ability to adapt to changes over time in workload and hardware. That ability is valuable.

Finally, my advice. The list below is ordered from least to most effective ways to offer advice:
  • You should do X (and can you tell me more about your system?)
  • ... and I have taken the time to understand your system
  • ... and I will get the resources to evaluate it
  • ... and I will get the resources to implement it
  • ... and I will join the oncall for it when this is deployed
  • ... and my reputation is on the line when this goes horribly wrong
  • ... and I will stick around for a few years to fix all of the bugs that show up


Wednesday, November 8, 2017

IO-bound insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with InnoDB from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This uses an IO-bound workload with small servers. The previous post covered an in-memory workload for InnoDB.

tl;dr - from 5.0.96 to 8.0.3
  • Insert performance improved by ~3X after MySQL 5.5 when I switching from built-in InnoDB to modern InnoDB.
  • Write stalls are a problem but I need better metrics for reporting this.
  • Secondary index scans are 1.09X faster on the i5 NUC and 1.64X faster on the i3 NUC
  • PK index scans are 1.88X faster on the i5 NUC and 1.96X faster on the i3 NUC
  • QPS increased by 5% on the i5 NUC and decreased by 25% on the i3 NUC in the read-write test that does 100 writes/second
tl;dr - from 5.6.35 to 8.0.3
  • The insert rate decreased by 8% on the i5 NUC and increased by 19% on the i3 NUC
  • There is a big improvement to range scan performance starting in MySQL 5.7
  • Secondary index scans are 1.24X faster on the i5 NUC and 1.16X faster on the i3 NUC
  • PK index scans are 1.35X faster on the i5 NUC and 1.25X faster on the i3 NUC.
  • QPS didn't change on the i5 NUC and increased by 6% on the i3 NUC in the read-write test that does 100 writes/second

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. The built-in InnoDB (remember that?) was used for 5.0, 5.1 and 5.5 and then I switched to modern InnoDB starting with 5.6. 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 and set innodb_purge_threads=1 to reduce mutex contention. 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 is larger than RAM as the test table has ~250M rows for the i3 NUC and ~500M rows for the i5 NUC. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark loaded the table with 250M or 500M rows 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 InnoDB in MySQL 5.0.96. Performance improves dramatically in 5.6 thanks to switching from the built-in to modern InnoDB.

Additional metrics help to explain performance. The metrics are explained here. With the switch from built-in to modern InnoDB in MySQL 5.6 there are big improvements to the insert rate, write-amplification (wKB/i) and the CPU overhead per insert (Mcpu/i). From 5.6 to 8.0 on the i5 NUC the insert rate decreased by 8% while the CPU overhead/insert increased by 9%. I assume the new overhead is from code above InnoDB. From 5.6 to 8.0 on the i3 NUC the insert rate increased by 19% while the CPU overhead/insert decreased by 25%. I assume the change in CPU overhead explains the change in performance. While write-amplification decreased with modern InnoDB, the storage read overhead per insert (rKB/i) has increased. I haven't tried to explain that. Write stalls might be a problem because the 99th percentile insert rate is much worse than the average, but I need a better metric for reporting this.

i3 NUC
        ips.av  ips.99  rKB/i   wKB/i   Mcpu/i  size(GB)
5.0.96   864     502    0.38    106.56  10545    52
5.1.72   883     548    0.36    106.75  10850    52
5.5.51  1246     741    3.52     78.61   7791    47
5.6.35  2665    1185    6.10     39.08   4934    46
5.7.17  3242    1814    2.54     34.24   3526    46
8.0.3   3162    1771    2.63     35.08   3663    46

i5 NUC
        ips.av  ips.99  rKB/i   wKB/i   Mcpu/i  size(GB)
5.0.96  1892    1088    0.28    114.11   8533   102
5.1.72  1877    1116    0.29    114.33   8850   102
5.5.51  2516    1452    1.55     95.17   7754    94
5.6.35  5978    2431    2.10     44.50   4265    90
5.7.17  5634    3131    1.26     44.92   4690    90
8.0.3   5482    3017    1.29     45.93   4657    90

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 scanned index has 250M entries on the i3 NUC and 500M entries on the i5 NUC. The first PK index scan (#1) is slower than the second PK scan (#5) because writeback is in progress during the first scan and competes for storage IO. Writeback for a dirty page can also delay a read waiting for a clean page. Something was done to make range scans faster starting in MySQL 5.7 (thanks to the InnoDB team).

Comparing InnoDB between 5.0.96 and 8.0.3: secondary index scans are 1.09X faster on the i5 NUC and 1.64X faster on the i3 NUC, PK index scans are 1.88X faster on the i5 NUC and 1.96X faster on the i3 NUC.

Comparing InnoDB between 5.6.35 and 8.0.3: secondary index scans are 1.24X faster on the i5 NUC and 1.16X faster on the i3 NUC, PK index scans are 1.35X faster on the i5 NUC and 1.25X faster on the i3 NUC. Range scans are much faster for InnoDB starting in MySQL 5.7.

#seconds to scan an index, i3 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
1349    422     198     208     200      828    5.0.96
1161    601     200     216     134     1017    5.1.72
 319    184     197     223     101      604    5.5.51
 292    181     194     212     127      587    5.6.35
 419    148     157     203      99      508    5.7.17
 403    147     154     204     102      505    8.0.3

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
2377    255     271     273     313     799     5.0.96
2477    266     283     286     399     835     5.1.72
 425    296     317     307     174     920     5.5.51
 475    290     302     314     225     906     5.6.35
 417    250     267     249     164     766     5.7.17
 420    227     260     245     167     732     8.0.3

Hardware efficiency metrics help to understand the performance improvements between MySQL 5.6.35 and 5.7.17. First are results for index scan #4, which is a secondary index. In MySQL 5.7.17 the CPU overhead per row read (Mcpu/o) is lower and that might explain why the storage read IO rate (rMB/s) is larger.

i3 NUC
secs    rMB/s   rKB/o   rGB     Mcpu/o  engine
212     35.2    0.031   7       13.584  5.6.35
203     37.8    0.031   7       12.552  5.7.17

i5 NUC
secs    rMB/s   rKB/o   rGB     Mcpu/o  engine
314     44.2    0.028   13      10.748  5.6.35
249     56.0    0.028   14       7.592  5.7.17

And then metrics for index scan #5, which is the second scan of the PK. The improvement in HW efficiency here is similar to the i3 NUC results above.

i3 NUC
secs    rMB/s   rKB/o   rGB     Mcpu/o  engine
127     130.5   0.067   16      16.560  5.6.35
 99     161.7   0.065   15       9.728  5.7.17

i5 NUC
secs    rMB/s   rKB/o   rGB     Mcpu/o  engine
225     147.0   0.067   32      14.732  5.6.35
164     198.1   0.067   32       8.900  5.7.17

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. From MySQL 5.0.96 to 8.0.3 QPS increased by 24% on the i5 NUC and decreased by 22% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS increased by 2% on the i5 NUC and 18% on the i3 NUC. The results for MySQL 5.1.72 are an outlier in this test and in the test below with 100 inserts/second. It looks like InnoDB is able to cache the secondary indexes for 5.1.72, so it does less storage reads per query and the QPS is larger.
Additional metrics help explain the performance, although the results here are more confusing than for other tests. The metrics are explained here. The built-in InnoDB with MySQL 5.5.51 was unable to sustain the target insert rate on the i3 NUC. Write stalls were a problem for all releases based on the values in IPS.99 that are less than 999 and MySQL 5.5.51 was the worst. The CPU overhead per query (CPU/q) decreased starting with MySQL 5.6.35.

i3 NUC
        IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
5.0.96  999     972     65.75    389      98     31.45  31282
5.1.72  999     961     63.98   4562    2149      0.26   6488
5.5.51  948     761     81.24    161      33     78.49  63049
5.6.35  999     977     67.92    258     156    101.61  37834
5.7.17  999     977     65.64    311     238     98.68  26829
8.0.3   999     978     65.97    303     230     99.50  27868

i5 NUC
        IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
5.0.96  999     988     71.25    820     694    30.27   23455
5.1.72  999     994     65.48   5460    2619     0.25    5374
5.5.51  999     968     88.74    710     480    53.41   24597
5.6.35  999     987     73.09    992     626    59.45   16798
5.7.17  999     988     71.22   1042     738    61.07   16115
8.0.3   999     989     71.63   1019     722    61.36   16723

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. From MySQL 5.0.96 to 8.0.3 QPS increased by 5% on the i5 NUC and decreased by 25% on the i3 NUC. From MySQL 5.6.35 to 8.0.3 QPS didn't change on the i5 NUC and increased by 6% on the i3 NUC. The QPS for 5.1.72 is an outlier. Similar to above, it looks like InnoDB cached the indexes in that setup and QPS was much larger.
Additional metrics help explain the performance. The metrics are explained here. All of the releases sustained the target insert rate and write stalls were not a problem. The CPU overhead per query (CPU/q) decreased by 26% on the i5 NUC and 25% on the i3 NUC and that might explain the increase in the query rate.

i3 NUC
        IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
5.0.96  100     100     122.04  1171     810    21.97   14084
5.1.72  100     100      95.08  4881    4707     0.01    5278
5.5.51  100     100     165.01   670     369    66.26   16998
5.6.35  100     100     126.74   827     534    66.49   13675
5.7.17  100     100     128.32   900     566    69.56   10269
8.0.3   100     100     129.58   883     547    69.86   10625

i5 NUC
        IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
5.0.96  100     100     107.04  1267    1180    25.84   15237
5.1.72  100     100      91.49  5625    5455     0.01    4477
5.5.51  100     100     171.97  1139     918    51.67   14015
5.6.35  100     100     157.74  1334     951    50.42   11546
5.7.17  100     100     160.00  1383     996    52.50   10807
8.0.3   100     100     161.31  1334    1041    52.85   11333

Tuesday, November 7, 2017

In-memory insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with InnoDB from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This uses an in-memory workload with an i3 NUC and i5 NUC. The i5 NUC is newer and faster. The next post covers an IO-bound test for InnoDB.

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 MyISAM.
  • Insert performance improved by ~3X after MySQL 5.5 when I switched from built-in InnoDB to modern InnoDB.
  • The query rate decreased by 15% on the i5 NUC and 39% on the i3 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 12% on the i5 NUC and 26% on the i3 NUC
  • The query rate decreased by 13% on the i5 NUC and 22% on the i3 NUC for the test with 100 inserts/second
  • Scan performance improved

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. The built-in InnoDB (remember that?) was used for 5.0, 5.1 and 5.5 and then I switched to modern InnoDB starting with 5.6. 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 and set innodb_purge_threads=1 to reduce mutex contention. 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 InnoDB in 5.0.96. Performance improves dramatically in 5.6 thanks to switching from the built-in InnoDB to modern InnoDB. There are regressions post-5.6 that I hope get addressed.
Additional metrics help to explain performance. The metrics are explained here. With the switch from built-in InnoDB (5.0 to 5.5) to modern InnoDB (5.6, 5.7, 8.0) write-amplification (wKB/i) improved on the i3 and i5 NUC while CPU overhead per insert (Mcpu/i) decreased on the i5 NUC (i3 NUC results are odd). From 5.6 to 8.0 the insert rate dropped by 26% on the i3 NUC and 12% on the i5 NUC. The larger CPU overhead (Mcpu/i) probably explains this on the i3 NUC. It is harder to explain on the i5 NUC but note that the CPU overhead includes foreground (user threads) and background (write back, purge). In my previous post the regression for MyISAM was ~10% on both the i3 and i5 NUC. So the InnoDB regression is similar for the i5 NUC but not the i3 NUC. From this result and others I suspect the regression is from code that runs above InnoDB.

i3 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   4803   35.75   3069    2.5
5.1.72   4737   35.71   3129    2.5
5.5.51   4643   38.03   3427    2.5
5.6.35  18797    8.87   1737    2.4
5.7.17  15083   10.36   3497    2.4
8.0.3   13908   11.02   3783    2.4

i5 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   8368   35.69   2643    2.5
5.1.72   8326   35.70   2713    2.5
5.5.51   7628   37.98   2972    2.5
5.6.35  29586    8.06   1461    2.4
5.7.17  25907    9.66   1491    2.4
8.0.3   26178    9.60   1474    2.5

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. Scan performance got worse from 5.5.51 (builtin InnoDB) to 5.6.35 (modern InnoDB). But then it improved in 5.7.17 for the i5 NUC and for many other tests I do, but for some reason it didn't on the i3 NUC.

#seconds to scan an index, i3 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
6       3       2       3       6        8      5.0.96
3       3       2       3       3        8      5.1.72
3       3       3       3       3        9      5.5.51
4       4       4       4       4       12      5.6.35
3       4       7       3       3       14      5.7.17
4       4       3       4       3       11      8.0.3

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       2       2       2       5        6      5.0.96
3       2       3       2       2        7      5.1.72
3       2       3       3       2        8      5.5.51
3       4       3       4       3       11      5.6.35
3       3       3       2       3        8      5.7.17
2       3       3       3       3        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 41% for the i3 NUC and 19% for the i5 NUC. Most of the regression occurs after 5.6.35. The large regression on the i3 NUC is another example of odd results for the i3 NUC. The regression was much smaller for MyISAM in my previous post.
The built-in InnoDB used for 5.0, 5.1 and 5.5 was unable to sustain the target insert rate. Write-amplification was also larger with built-in InnoDB. But it was able to sustain a larger QPS. QPS drops by 25% on the i3 NUC and 12% on the i5 NUC from 5.6.35 to 8.0.3. Write stalls were worse for built-in InnoDB, but even modern InnoDB had some stalls given the values in IPS.99 that are less than 999. Additional metrics help explain the performance. The  metrics are explained here. The CPU overhead per query (CPU/q) more than doubles on the i3 NUC from 5.6.35 to 5.7.17. That helps to explain the QPS reduction. The CPU overhead per query increased on the i5 NUC by 11% from 5.6.35 to 8.0.3.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
992     770     64.13   5675    5277     5332   5.0.96
992     731     64.03   4990    4863     5910   5.1.72
998     943     67.66   4364    4249     6845   5.5.51
999     994     43.90   4817    4454     5970   5.6.35
999     995     40.27   3748    3540    13834   5.7.17
999     995     41.32   3527    3349    14750   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     993     64.31   6183    5534    4776    5.0.96
998     963     64.05   5646    5594    5118    5.1.72
999     993     70.40   5284    5052    5591    5.5.51
999     998     44.12   5798    5410    4846    5.6.35
999     998     40.62   5184    4917    5186    5.7.17
999     998     41.23   4995    4763    5389    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 regression from MySQL 5.0.96 to 8.0.3 is 39% on the i3 NUC and 15% on the i5 NUC. The regression from 5.6.35 to 8.0.3 is 22% for the i3 NUC and 13% for the i5 NUC. On the i5 NUC the CPU overhead per insert, Mcpu/i, increased by 18% from 5.0 to 8.0 and by 16% from 5.6 to 8.0 which probably explains the decrease in the insert rate.
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. The  metrics are explained here. 

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.34   6058    5877     4238   5.0.96
100     100     67.38   5033    4891     5013   5.1.72
100     100     98.90   4465    4274     5766   5.5.51
100     100     51.85   4729    4499     5457   5.6.35
100     100     52.94   3906    3717    12636   5.7.17
100     100     61.08   3681    3524    13407   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.54   6062    5543    4211    5.0.96
100     100     67.25   5655    5586    4439    5.1.72
100     100     98.98   5189    5035    4931    5.5.51
100     100     51.95   5937    5548    4309    5.6.35
100     100     55.24   5349    4976    4803    5.7.17
100     100     54.07   5148    4819    4978    8.0.3

Monday, November 6, 2017

Questions I have about workloads with high write rates

I am sure I borrowed some of these from co-workers. When I read discussions about write-heavy workloads I ask myself some questions. I assume VoltDB and Tarantool has answers, but these are hard problems. I know that many of my write heavy MySQL tests ignore many of these problems because I usually run them with replication disabled.
  1. How long do you expect the storage device to last for it? Is it OK to replace the storage device once per month because you need too many DWPD?
  2. Do you have network capacity to support that QPS load on a master? Assume all servers in the rack get that QPS and the QPS source is not rack local.
  3. Do you have network capacity to replicate that write load to a replica? Assume some replicas are far away.
  4. Can the replica replay those writes fast enough to avoid lag?
  5. Will you be able to keep enough replication log archives to support PITR?
  6. How will you take backups?
  7. Will restore ever finish applying logs during the catch up phase?

CPU-bound sysbench on a large server: Postgres 12 to 19 beta1

This has results from sysbench on a small server with Postgres versions 12 through 19 beta1. Sysbench is run with high concurrency (40 conne...