MyISAM, small servers and sysbench at low concurrency

2017-06-30 10:27:35来源:作者:人点击



I am a big fan ofIntel 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 serversdescribed hereand 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 isdescribed herebut 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.



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

微信扫一扫

第七城市微信公众平台