Tuesday, August 29, 2017

IO-bound sysbench on a smaller server

This is part 3 of my performance report on IO-bound sysbench. In part 1 and part 2 I used a large server with 48 HW threads. Here I use a core i5 NUC with 4 HW threads.

tl;dr
  • results are similar to the first post
  • MyRocks is competitive on the write-heavy tests
  • MyRocks is slower on the read-heavy tests
  • I don't attempt to show that MyRocks wins on space and write efficiency and that is why I like it

Configuration

I use my sysbench fork and helper scripts, release specific my.cnf files. The server is a core i5 NUC with 4 HW threads, 16gb of RAM and a fast SSD. The binlog was enabled and sync-on-commit was disabled for the binlog and database log. I remembered to disable SSL.

I tested MyRocks and InnoDB, with buffered IO and a 4g block cache for MyRocks and O_DIRECT and a 12gb buffer pool for InnoDB. The server is shared by the sysbench client and mysqld. For MyRocks I used a build from August 15 with git hash 0d76ae. For InnoDB I used upstream 5.6.35, 5.7.17 and 8.0.2. For InnoDB 8.0.2 I used latin1 charset and latin1_swedish_ci collation. Compression was not used for InnoDB or MyRocks. Note that this description is correct where it disagrees with the my.cnf files that I shared.

The test used 4 tables with 80M rows/table. My use of sysbench is explained here. Tests are run in an interesting pattern -- load, write-heavy, read-only, insert-only. On the core i5 NUC each test is run for 1 and then 2 concurrent connections for either 5 or 10 minutes per concurrency level. So each test runs for either 10 or 20 minutes total and I hope that is long enough to get the database into a steady state. An example command line to run the test with my helper scripts is:
bash all.sh 4 80000000 600 600 300 innodb 1 0 /orig5717/bin/mysql none /sysbench.new

Results without charts

All of the data is here. The results here are mostly similar to the results from the large server however MyRocks does worse here on some of the read-heavy tests. The QPS ratio for point-query is 0.651 for MyRocks here versus 0.850 on the large server. Note that the hot-points workload is not IO-bound, regardless we need to make MyRocks more effective on it. MySQL did something to make InnoDB range scans more efficient starting in 5.7. I don't know whether the problem for MyRocks is CPU or IO overhead in the range-scan heavy tests (read-write.*, read-only.*).

QPS ratio:
* rocks = myrocks.none / inno5635
* inno = inno5717 / inno5635
* value less than 1.0 means that InnoDB 5.6 is faster

1 connection
rocks   inno
2.909   1.074   update-index
1.068   1.095   update-nonindex
1.006   0.935   update-nonindex-special
1.682   0.988   delete-only
1.053   0.961   read-write.range100
0.881   1.554   read-write.range10000
0.776   1.348   read-only.range100
0.898   1.584   read-only.range10000
0.651   1.197   point-query
1.000   1.285   random-points
0.267   0.943   hot-points
0.989   0.941   insert-only

Results with charts

Sorry, no charts this time. Charts from the previous post are close enough.

Monday, August 28, 2017

Sysbench, a large server and fast SSD vs MyRocks, InnoDB and TokuDB: part 2

This provide more data using the setup in my previous post. The difference is that the previous post used 8 tables with 100M rows/table and this post uses 1 table with 800M rows/table, so this test has more opportunity for contention.

tl;dr
  • Results here are mostly similar to results from the previous test but ...
  • MyRocks does better here relative to other engines, so it suffers less from contention.
  • InnoDB and TokuDB don't do as well on this test as on the previous test so they suffer more from contention.

Results without pictures

All of the data is here. The data below is the QPS ratio comparing the QPS for MyRocks, InnoDB 5.7.17 and TokuDB 5.7.17 with InnoDB 5.6.35. A value less than one means the QPS for the engine is less than for InnoDB 5.6.35.

QPS ratio:
* rocks = myrocks.none / inno5635
* inno = inno5717 / inno5635
* toku = toku5717.none / inno5635
1 connection
rocks   inno    toku    engine/test
1.765   1.306   0.995   update-index
1.049   1.122   0.421   update-nonindex
0.842   1.020   0.351   update-nonindex-special
4.415   1.821   1.111   delete-only
0.988   1.166   0.329   read-write.range100
1.189   1.300   1.339   read-write.range10000
0.910   1.092   0.358   read-only.range100
0.980   0.966   1.246   read-only.range10000
0.862   0.950   0.257   point-query
0.949   0.949   0.271   random-points
0.515   0.885   0.606   hot-points
0.963   0.991   0.324   insert-only

8 connections
rocks   inno    toku    engine/test
5.958   2.644   2.782   update-index
1.513   1.434   0.473   update-nonindex
1.289   1.422   0.365   update-nonindex-special
1.542   0.995   0.275   delete-only
1.206   1.306   0.253   read-write.range100
1.002   1.288   1.159   read-write.range10000
1.017   1.083   0.329   read-only.range100
0.871   1.069   1.191   read-only.range10000
0.862   0.855   0.204   point-query
0.915   0.859   0.208   random-points
0.890   1.194   0.758   hot-points
0.951   1.149   0.353   insert-only

48 connections
rocks   inno    toku    engine/test
3.836   2.340   1.492   update-index
2.425   2.520   0.312   update-nonindex
2.112   2.981   0.557   update-nonindex-special
3.028   1.950   0.306   delete-only
1.790   1.643   0.122   read-write.range100
1.488   1.883   0.768   read-write.range10000
0.820   1.005   0.093   read-only.range100
0.903   1.219   0.579   read-only.range10000
0.854   0.971   0.079   point-query
0.871   0.953   0.096   random-points
0.739   1.286   0.698   hot-points
1.768   1.579   0.491   insert-only

Results with pictures

Sorry, no charts this time. Charts from the previous post are close enough.

Thursday, August 24, 2017

Sysbench, a large server and fast SSD vs MyRocks, InnoDB and TokuDB

I have new performance results to share starting with modern sysbench, a large server, fast SSD and a database that is larger than RAM. Competitive read performance with much better space and write efficiency is why I like MyRocks and this is an example of that. However, we have work to do on read efficiency that will be apparent in future perf reports.

tl;dr
  • For write-heavy workloads MyRocks competes with InnoDB 5.7 and 8.0 and does better than 5.6 at all concurrency levels. 
  • For read-heavy workloads MyRocks is usually within 10% of InnoDB 5.6.
  • For write-heavy workloads InnoDB in 5.7 and 8.0 do better than 5.6.
  • For some read-heavy workloads InnoDB has a perf regression from 5.6 to 5.7/8.0.
  • TokuDB usually has the worst QPS

Configuration

I use my sysbench fork and helper scripts, release specific my.cnf files and a server with 48 HW threads, fast SSD and 256gb of RAM although only 50gb was available to the database and OS page cache. The binlog was enabled and sync-on-commit was disabled for the binlog and database log. I remembered to disable SSL.

I tested MyRocks, TokuDB and InnoDB, with buffered IO and a 10g database cache for MyRocks/TokuDB and O_DIRECT and a 35gb buffer pool for InnoDB. The server is shared by the sysbench client and mysqld. For MyRocks I used a build from August 15 with git hash 0d76ae and repeated the test first without compression and then no compression for L0/L1/L2, LZ4 for interior levels and zstd for the max level of the leveled LSM. For TokuDB I used Percona Server 5.7.17-12 and repeated the test first without compression and then with zlib compression. For InnoDB I used upstream 5.6.35, 5.7.17 and 8.0.2. For InnoDB 8.0.2 I used latin1 charset and latin1_swedish_ci collation. Compression was not used for InnoDB. More details are in the release specific my.cnf files and I used the same my.cnf for InnoDB with 8.0.1 and 8.0.2.

The test used 8 tables with 100M rows/table. My use of sysbench is explained here. Tests are run in an interesting pattern -- load, write-heavy, read-only, insert-only. On the large server each test is run for 1, 2, 4, 8, 16, 24, 32, 40, 48 and 64 concurrent connections for either 3 or 5 minutes per concurrency level. So each test runs for either 30 or 50 minutes total and I hope that is long enough to get the database into a steady state. An example command line to run the test with my helper scripts is:
bash all.sh 8 100000000 180 300 180 innodb 1 0 /orig5717/bin/mysql none /sysbench.new

Results without pictures

I have a lot of numbers to share and struggled with the presentation. All of the data is here. I will start with relative QPS - the QPS for an engine versus the QPS for InnoDB from upstream 5.6.35. There is one column for MyRocks without compression, one for TokuDB without compression and one for InnoDB from upstream 5.7.17. I did not include columns for compressed MyRocks, compressed TokuDB and InnoDB from upstream 8.0.2 to improve readability and because MyRocks/TokuDB QPS with compression is similar to it without compression and InnoDB QPS from 8.0.2 is similar to InnoDB from 5.7.17.

When the QPS ratio is greater than one then the engine is faster than InnoDB 5.6.35. I show ratios for 1, 8 and 48 connections to compare low, medium and high concurrency workloads. Things that I notice in the results include:
  • For write-heavy workloads MyRocks competes with InnoDB 5.7 and 8.0 and does better than 5.6 at all concurrency levels.
  • For read-heavy workloads MyRocks is usually within 10% of InnoDB 5.6. 
  • For write-heavy workloads InnoDB in 5.7 and 8.0 do better than 5.6.
  • For some read-heavy workloads InnoDB has a perf regression from 5.6 to 5.7/8.0.
  • TokuDB usually has the worst QPS

QPS ratio:
* rocks = myrocks.none / inno5635
* inno = inno5717 / inno5635
* toku = toku5717.none / inno5635

1 connection
rocks   inno    toku    engine/test
2.516   1.338   1.395   update-index
1.103   1.210   0.482   update-nonindex
0.907   1.067   0.376   update-nonindex-special
1.325   1.099   0.337   delete-only
0.942   1.149   0.317   read-write.range100
1.064   1.157   1.210   read-write.range10000
0.798   0.968   0.323   read-only.range100
0.980   0.946   1.280   read-only.range10000
0.850   0.917   0.226   point-query
0.933   0.900   0.300   random-points
0.457   0.904   0.608   hot-points
0.890   1.009   0.317   insert-only

8 connections
rocks   inno    toku    engine/test
4.892   2.862   2.304   update-index
1.769   1.681   0.169   update-nonindex
1.382   1.506   0.476   update-nonindex-special
1.409   1.178   0.250   delete-only
0.989   1.082   0.202   read-write.range100
0.893   1.141   1.055   read-write.range10000
0.838   0.895   0.279   read-only.range100
0.870   1.046   1.258   read-only.range10000
0.857   0.840   0.215   point-query
0.911   0.824   0.245   random-points
0.621   1.194   0.819   hot-points
0.828   1.056   0.313   insert-only

48 connections
rocks   inno    toku    engine/test
2.294   2.223   0.817   update-index
2.685   2.726   0.393   update-nonindex
2.172   3.031   0.734   update-nonindex-special
2.830   3.054   0.283   delete-only
2.758   2.706   0.192   read-write.range100
1.113   1.394   0.577   read-write.range10000
0.810   0.996   0.101   read-only.range100
0.893   1.203   0.589   read-only.range10000
0.845   0.954   0.089   point-query
0.864   0.941   0.125   random-points
1.412   3.166   2.055   hot-points
1.111   1.855   0.579   insert-only

Results with pictures

Some people prefer graphs, so there is the relative QPS as a chart. I truncated the x-axis at 2 to make it easier see differences. The first chart is from the test with 1 connection.
At 8 connections
At 48 connections

Tuesday, August 15, 2017

MyRocks my.cnf changes from June 16 to Aug 15

There have been several recent performance improvements to MyRocks that I want to test. The last build I did was from June 16. With the my.cnf options that I use there is one new option and 3 have been removed. The new option is rocksdb_max_background_jobs. The removed options are rocksdb_base_background_compactions, rocksdb_max_background_compactions and rocksdb_max_background_flushes.

Monday, July 10, 2017

I overstated the CPU regression in MySQL 5.7

My series of posts on the low-concurrency CPU regression greatly overstated the regression from MySQL 5.6 to 5.7. Most of the regression was from the use of SSL which is enabled by default starting with 5.7. My results for 5.7 and 8.0 were slower courtesy of SSL while earlier versions of MySQL were not using SSL. I repeated tests for 5.7 and 8.0 with SSL disabled (ssl=0 in my.cnf) and now show a QPS loss of 10% to 20% from 5.6 to 5.7/8.0.

There is still a large regression from MySQL 5.0 to 8.0 and I will continue to document that and expect to help reduce it. I expect 86215 to be closed as not a bug.

tl;dr for in-memory sysbench at low concurrency
  • MySQL 5.7 and 8.0 lose 10% to 20% of QPS versus 5.6 for tests not dominated by range scans
  • InnoDB range scans were made faster starting in MySQL 5.7
  • In the worst case, MySQL 5.0 gets more than 2X the QPS versus 5.7 and 8.0. 
  • The largest QPS regression occurs between MySQL 5.5 and 5.6.

Why

I should have noticed earlier in the process that SSL was the problem. I frequently rant about the value of explaining performance results and this is a reminder of that value. I didn't explain why MySQL 5.7 and 8.0 did worse than 5.6 and it turned out that my results were wrong. I spent some time debugging the slowdown using PMP and Linux perf but clearly I should have spent more time on that.

The short story is that I was trying to do too much and didn't have time to focus. Too much in this case meant I was too busy running tests and collecting results and didn't have time to explain the results. Lesson learned.

Configuration

I repeated tests for 5.7 and 8.0 with SSL disabled and share results for in-memory sysbench in this post.

I tested MySQL with upstream 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. I disabled SSL for 5.7.17 and 8.0.1 by adding ssl=0 to my.cnf. I used the i5 NUC servers described here and the my.cnf used are here. I run mysqld and the sysbench client on the same server. The binlog is enabled but sync-on-commit is disabled. I also set innodb_purge_threads to 1 to avoid mutex contention on a small server.

Sysbench is run with 4 tables and 1M rows per table. Tests are repeated for 1, 2 and 4 clients. The database fits in the InnoDB buffer pool. My usage of sysbench is described here. That explains the helper scripts that invoke sysbench and collect performance metrics.

Results

The first table is the QPS for MySQL 5.7.17 and 8.0.1 relative to 5.6.35 for sysbench with one client. The QPS for 5.7.17 and 8.0.1 is better than 5.6.35 when the value is greater than 1 as it is for tests that do longer range scans because something was done to make that faster in modern InnoDB. In the tests not dominated by range scans the QPS for 5.7.17 and 8.0.1 are 10% to 20% less than for 5.6.35 and the loss from 5.6 to 5.7 is similar to the loss from 5.7 to 8.0.

QPS relative to 5.6.35
----------------------
5717    801     test
1.04     .96    update-index
 .90     .84    update-nonindex
 .91     .83    delete
 .90     .82    write-only
1.06    1.03    read-write.range100
1.51    1.49    read-write.range10000
 .94     .86    read-only.range10
1.11    1.01    read-only.range100
1.49    1.42    read-only.range1000
1.44    1.41    read-only.range10000
 .95     .85    point-query
 .89     .82    insert

The next table is the QPS for 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1 relative to 5.0.96 for the same tests using 1 sysbench client. In the worst case the QPS for 5.0.96 is 2.5X better than 8.0.1 and more than 2X better than 5.7.17. The worst case is for the update-only (update-index, update-nonindex) and point-query tests.

QPS relative to 5.0.96
----------------------
5172    5551    5635    5717    801     test
.89     .73     .45     .46     .43     update-index
.89     .59     .47     .42     .39     update-nonindex
.92     .92     .75     .68     .62     delete
.93     .93     .76     .68     .62     write-only
.92     .99     .65     .69     .67     read-write.range100
.95     .92     .55     .84     .82     read-write.range10000
.92     .89     .58     .55     .50     read-only.range10
.94     .91     .57     .63     .57     read-only.range100
.96     .92     .56     .83     .80     read-only.range1000
.95     .92     .55     .80     .78     read-only.range10000
.85     .77     .47     .45     .40     point-query
.95     .93     .81     .72     .67     insert

The data below has the QPS for each test. Each test was run for 1, 2 and 4 clients and my usage of sysbench is explained in a previous post.

update-index
1       2       4       concurrency/version
13047   19521   22763   inno5096
11592   18439   19833   inno5172
 9486   14393   16389   inno5551
 5806    9837   12354   inno5635
 6013   10242   13498   inno5717
 5585    9509   12771   inno801

update-nonindex
1       2       4       concurrency/version
22333   32560   32961   inno5096
19914   29050   31829   inno5172
13082   19164   21049   inno5551
10435   15680   18487   inno5635
 9430   14120   19012   inno5717
 8730   13277   17746   inno801

delete
1       2       4       concurrency/version
25893   35659   38785   inno5096
23777   38591   41418   inno5171
23726   33260   38085   inno5551
19461   28797   35684   inno5635
17621   26110   34634   inno5717
16146   24270   32245   inno801

write-only
1       2       4       concurrency/version
22364   32090   36125   inno5096
20907   32503   35141   inno5172
20727   29856   33173   inno5551
16892   25376   30915   inno5635
15176   22631   29425   inno5717
13778   20834   27262   inno801

read-write.range100
1       2       4       concurrency/version
17972   30414   42474   inno5096
16527   30055   41973   inno5172
17857   28714   41496   inno5551
11653   18109   25325   inno5635
12387   18359   25379   inno5717
12002   17727   24253   inno801

read-write.range10000
1       2       4       concurrency/version
608     1089    1556    inno5096
580     1033    1513    inno5171
557     996     1410    inno5551
337     604      849    inno5635
509     873     1067    inno5717
501     858     1039    inno801

read-only.range10
1       2       4       concurrency/version
30016   48794   78703   inno5096
27554   49464   76052   inno5172
26863   48108   77370   inno5551
17372   30663   50570   inno5635
16383   28695   48139   inno5717
14861   26625   45225   inno801

read-only.range100
1       2       4       concurrency/version
19891   34307   56572   inno5096
18636   34808   54051   inno5172
18125   33561   51729   inno5551
11247   20922   32930   inno5635
12453   22441   34585   inno5717
11387   21221   32934   inno801

read-only.range1000
1       2       4       concurrency/version
4623    8304    12115   inno5096
4429    8114    11757   inno5171
4272    7851    11167   inno5551
2590    4840     6816   inno5635
3859    7041     8764   inno5717
3682    6877     8395   inno801

read-only.range10000
1       2       4       concurrency/version
492     882     1259    inno5096
468     827     1222    inno5172
452     808     1147    inno5551
273     497      686    inno5635
392     700      865    inno5717
386     693      841    inno801

point-query
1       2       4       concurrency/version
41951   68718   123988  inno5096
35785   66515   111602  inno5172
32385   56441    81061  inno5551
19674   36269    55266  inno5635
18749   32669    55340  inno5717
16694   30372    51878  inno801

insert
1       2       4       concurrency/version
13932   14182   12795   inno5096
13194   15157   12521   inno5171
12968   17106   14447   inno5551
11288   16268   19355   inno5635
10039   15428   19922   inno5717
 9306   14391   18763   inno801

Friday, June 30, 2017

One more time with sysbench, a small server & MySQL 5.6, 5.7 and 8.0

 Update - the regression isn't as bad as I have been reporting. Read this post to understand why.

The good news is that I hope to begin debugging this problem next week. After fixing a few problems to reduce variance I am repeating tests to document the performance regression from MySQL 5.6 to 8.0. The first problem was fixed by disabling turbo boost on my Intel NUC servers to avoid thermal throttling. The other problem was the impact from mutex contention for InnoDB purge threads and I repeated tests with it set to 1 and 4. This is part of my series on low-concurrency CPU regressions for bug 86215.

tl;dr for in-memory sysbench on a small server with a fast SSD
  • most of the regression is from 5.6.35 to 5.7.17, much less is from 5.7.1 to 8.0.1
  • innodb_purge_threads=4 costs 10% to 15% of the QPS for write-heavy tests
  • QPS is 30% less for 5.7.17 & 8.0.1 vs 5.6.35 on write-only tests
  • QPS is 30% to 40% less for 5.7.17 & 8.0.1 vs 5.6.35 on read-write tests
  • QPS is 40% to 50% less for 5.7.17 & 8.0.1 vs 5.6.35 on read-only tests
  • QPS is 40% less for 5.7.17 & 8.0.1 vs 5.6.35 for point-query
  • QPS is 30% less for 5.7.17 & 8.0.1 vs 5.6.35 for insert-only

Configuration

I tested MySQL with upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. I used the i5 NUC servers described here and the my.cnf used are here. I run mysqld and the sysbench client on the same server. The binlog is enabled but sync-on-commit is disabled.

Sysbench is run with 4 tables and 1M rows per table. The database fits in the InnoDB buffer pool. My usage of sysbench is described here. That explains the helper scripts that invoke sysbench and collect performance metrics. When I return home I will update this with the sysbench command lines that are generated by my helper scripts.

Results: write-only

Sorry, no graphs this time. I run sysbench for 1, 2 and 4 concurrent clients and share both the QPS for each test and then the QPS for MySQL 5.7.17 and 8.0.1 relative to 5.6.35. The ratio is less than 1 when the QPS is larger for 5.6.35.

All of these tests are run with innodb_purge_threads=1 which is the default for 5.6.35. The default for 5.7.17 and 8.0.1 is 4.

The first batch of results is from write-only tests. Most of the QPS regression is from MySQL 5.6.35 to 5.7.17. Excluding the update-index test, going from 5.6 to 5.7 loses about 30% of QPS.

update-index : QPS
1       2       4       concurrency/engine
5806    9837    12354   inno5635
5270    8798    11677   inno5717
4909    8176    10917   inno801

update-index : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.91     .89     .95     inno5717
.85     .83     .88     inno801

update-nonindex : QPS
1       2       4       concurrency/engine
10435   15680   18487   inno5635
 7691   11497   14989   inno5717
 7179   10845   14186   inno801

update-nonindex : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.74     .73     .81     inno5717
.69     .69     .77     inno801

delete : QPS
1       2       4       concurrency/engine
19461   28797   35684   inno5635
13525   19937   25466   inno5717
12551   18810   24023   inno801

delete : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.69     .69     .71     inno5717
.64     .65     .67     inno801

write-only : QPS
1       2       4       concurrency/engine
16892   25376   30915   inno5635
11765   17239   22061   inno5717
10729   16108   20682   inno801

write-only : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.70     .68     .71     inno5717
.64     .63     .67     inno801

Results: read-write

The next batch of results is from the classic read-write OLTP sysbench test. But I repeat it using different sizes for the range query. The regression is larger here than for the write-only tests above perhaps because of the regression for range scans. Going from 5.6.35 to 5.7.17 loses between 30% and 40% of the QPS. The regression is worse for longer range scans.

read-write.range100 : QPS
1       2       4       concurrency/engine
11653   18109   25325   inno5635
 7520   10871   14498   inno5717
 6965   10274   14098   inno801

read-write.range100 : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.65     .60     .57     inno5717
.60     .57     .56     inno801

read-write.range10000 : QPS
1       2       4       concurrency/engine
337     604     849     inno5635
202     386     443     inno5717
200     378     436     inno801

read-write.range10000 : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.60     .64     .52     inno5717
.59     .63     .51     inno801

Results: read-only
The next batch of results is from the classic read-only OLTP sysbench test. But I repeat it using different sizes for the range query. Most of the regression is from 5.6.35 to 5.7.17. Going from 5.6 to 5.7 loses between 40% and 50% of the QPS so the regression here is larger than above for the read-write tests. There isn't a larger regression for larger range queries.

read-only.range10 : QPS
1       2       4       concurrency/engine
17372   30663   50570   inno5635
10829   19021   25874   inno5717
10171   18743   25713   inno801

read-only.range10 : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.62     .62     .51     inno5717
.59     .61     .51     inno801

read-only.range100 : QPS
1       2       4       concurrency/engine
11247   20922   32930   inno5635
 6815   12823   16225   inno5717
 6475   12308   15834   inno801

read-only.range100 : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.61     .61     .49     inno5717
.58     .59     .48     inno801

read-only.range1000 : QPS
1       2       4       concurrency/engine
2590    4840    6816    inno5635
1591    2979    3408    inno5717
1552    2918    3363    inno801

read-only.range1000 : QPS relatie to MySQL 5.6.35
1       2       4       concurrency/engine
.61     .62     .50     inno5717
.60     .60     .49     inno801

read-only.range10000 : QPS
1       2       4       concurrency/engine
273     497     686     inno5635
161     304     355     inno5717
159     299     350     inno801

read-only.range10000 : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.59     .61     .52     inno5717
.68     .60     .51     inno801

Results: point-query and insert-only

Finally results for the last two tests -- point-query and insert-only. MySQL 5.7.17 loses about 40% of the QPS for point-query and 30% of the QPS for insert-only compared to 5.6.35.

point-query : QPS
1       2       4       concurrency/engine
19674   36269   55266   inno5635
11964   22941   29174   inno5717
11624   20679   29271   inno801

point-query : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.61     .63     .53     inno5717
.59     .57     .53     inno801

insert : QPS
1       2       4       concurrency/engine
11288   16268   19355   inno5635
 7951   12176   15660   inno5717
 7493   11277   14857   inno801

insert : QPS relative to MySQL 5.6.35
1       2       4       concurrency/engine
.70     .75     .81     inno5717
.66     .69     .77     inno801

innodb_purge_threads

Finally I repeated tests with innodb_purge_threads=4 to show the impact from that. On a small server (2 cores, 4 HW threads) there is too much mutex from innodb_purge_threads=4. As 4 is the default for 5.7.17 and 8.0.1 they suffer more than 5.6.35 when the default is used. The results above are for innodb_purge_threads=1 and then I repeated the tests with it set to 4. Here I show the QPS with purge_threads=4 / QPS with purge_threads=1. For the tests below QPS is reduced by 10% to 15% when innodb_purge_threads=4 on a small server. The insert-only test doesn't suffer, but there isn't anything to purge from the insert-only workload.

update-index
1       2       4       concurrency/engine
.85     .76     .75     inno5635
.76     .76     .77     inno5717
.89     .96     .89     inno801

update-nonindex
1       2       4       concurrency/engine
.82     .78     .88     inno5635
.77     .79     .86     inno5717
.86     .95     .91     inno801

delete
1       2       4       concurrency/engine
.84     .81     .82     inno5635
.84     .81     .87     inno5717
.87     .92     .94     inno801

write-only
1       2       4       concurrency/engine
.89     .85     .85     inno5635
.88     .86     .87     inno5717
.91     .95     .94     inno801

insert
1       2       4       concurrency/engine
.99     .99     .99     inno5635
.99     1.00    1.00    inno5717
1.01    1.01    1.00    inno801

Wednesday, June 28, 2017

MyISAM, small servers and sysbench at low concurrency

Update - the regression isn't as bad as I have been reporting. Read this post to understand why.

I am a big fan of Intel NUC servers but recently noticed that CPU performance varied frequently from thermal throttling for my NUC7i5bnh.  To prevent this I disabled turbo boost for the CPUs. My old NUC servers (NUC5i3ryh) don't suffer from this because that CPU does not have turbo boost. Now I need to repeat many tests. Today I share results for a quick run of sysbench using MyISAM and InnoDB in MySQL 5.6, 5.7 and 8.0. This is part of my series on low-concurrency CPU regressions for bug 86215.

tl;dr for in-memory sysbench:
  • the QPS regression is from MySQL 5.6 to 5.7
  • the QPS regression is similar for MyISAM and InnoDB
  • the default value for innodb_purge_threads, which is 4, can cause too much mutex contention and a loss in QPS on small servers. For sysbench update-only I lose 25% of updates/second with 5.7.17 and 15% with 8.0.1 when going from innodb_purge_threads=1 to =4.

Configuration

I tested MySQL with upstream 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. I used the i5 NUC servers described here and the my.cnf used are here. I run mysqld and the sysbench client on the same server. The binlog is enabled but sync-on-commit is disabled. Sysbench is run with 4 tables and 1M rows per table.

My usage of sysbench is described here but in this case I ran a shorter version of the test and here are the command lines for MyISAM and for InnoDB. I used 1 table with 1M rows for an in-memory workload and ran update-index, update-nonindex, read-only with 10 to 10,000 rows and then point-query. Tests were run for 1 client and 3 minutes duration. I did this to quickly get results for MyISAM and InnoDB.

I noticed mutex contention from InnoDB purge threads and had been using the default value (innodb_purge_threads=4) so I repeated tests with innodb_purge_threads=1.

Results

In the tables below for MyISAM: 5635 is 5.6.35, 5717 is 5.7.17, 801 is 8.0.1. In the tables below for InnoDB: 5635 is 5.6.35, 5717-pX is 5.7.17 with innodb_purge_threads set to X, 801-pX is 8.0.1 with innodb_purge_threads set to X.

The first table shows the QPS for MyISAM.

 5635    5717     801   release/test
11143    7995    7692   update-index
12572    8743    8106   update-nonindex
17177   10565   10403   read-only.range10
 9994    6287    6337   read-only.range100
 2088    1351    1299   read-only.range1000
  208     142     145   read-only.range10000
20369   12106   11177   point-query

The next table shows the QPS for MyISAM relative to MyISAM in MySQL 5.6.35. Most of the QPS regression is from MySQL 5.6 to 5.7.

5635   5717     801     release/test
1.00    .72     .69     update-index
1.00    .70     .64     update-nonindex
1.00    .62     .61     read-only.range10
1.00    .63     .63     read-only.range100
1.00    .65     .62     read-only.range1000
1.00    .68     .70     read-only.range10000
1.00    .59     .55     point-query

The next table shows the QPS for InnoDB.

5635    5717-p4 5717-p1 801-p4  801-p1
 5723    3949    5265    4434    4902   update-index
10565    5975    7770    6243    7287   update-nonindex
17408   11522   11020   10492   10717   read-only.range10
11262    6947    6887    6513    6726   read-only.range100
 2611    1578    1576    1577    1627   read-only.range1000
  277     163     166     161     164   read-only.range10000
20406   11671   11350   10978   11348   point-query

The last table shows the QPS for InnoDB relative to InnoDB in MySQL 5.6.35. Most of the QPS regression is from MySQL 5.6 to 5.7. For the update tests, InnoDB loses about 25% of QPS in MySQL 5.7.17 and about 15% in MySQL 8.0.1 with the default value of innodb_purge_threads, which is 4. Note that this server has 2 CPU cores and 4 hardware threads.

5635    5717-p4 5717-p1 801-p4  801-p1

1.00    .69     .92     .77     .86     update-index
1.00    .57     .74     .59     .69     update-nonindex
1.00    .66     .63     .60     .62     read-only.range10
1.00    .62     .61     .58     .60     read-only.range100
1.00    .60     .60     .60     .62     read-only.range1000
1.00    .59     .60     .58     .59     read-only.range10000


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