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


No comments:

Post a Comment