Monday, November 6, 2017

Insert benchmark for MyISAM from MySQL 5.0 to 8.0

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

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 InnoDB.
  • The insert rate decreased by 16% on the i5 NUC and 20% on the i3 NUC
  • The query rate decreased by 6% on the i5 NUC and 10% on the i5 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 11% on the i5 NUC and 9% on the i3 NUC
  • The query rate decreased by 8% on the i5 NUC and 9% on the i3 NUC for the test with 100 inserts/second

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. 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. 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 MyISAM in 5.0.96. There is a small regression over time in the insert rate. The loss from 5.7 to 8.0 is the largest. Fortunately, 8.0 is not GA yet and maybe this can be improved.

Additional metrics help to explain performance. The metrics are explained here. The CPU overhead per insert (Mcpu/i) has increased with each release (more features == more instructions to execute) and that explains the decrease in the insert rate. Otherwise the metrics look good. The increase in Mcpu/i from 5.0.96 to 8.0.3 is 19% for the i3 NUC and 16% for the i5 NUC. This matches the change in the insert rate.

i3 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96  27174   1.01    1404    2.1
5.1.72  28249   1.06    1372    2.1
5.5.51  24691   1.08    1555    2.1
5.6.35  23866   1.11    1592    1.7
5.7.17  24691   0.94    1543    1.7
8.0.3   21645   0.93    1675    1.7

i5 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96  33113   0.64    1123    1.7
5.1.72  34843   0.59    1108    1.7
5.5.51  31348   0.61    1192    1.8
5.6.35  31250   0.70    1193    2.1
5.7.17  29674   0.65    1236    1.9
8.0.3   27701   0.65    1305    2.0

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.

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

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
1       3       2       4       2        9      5.0.96
1       2       3       4       2        9      5.1.72
1       2       3       4       2        9      5.5.51
2       2       3       5       2       10      5.6.35
1       3       2       5       1       10      5.7.17
2       2       3       4       2        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 13% for the i3 NUC and 8% for the i5 NUC. That is small which is good.
All of the engines were able to sustain the target insert rate on average (ips.av). The value is 999 rather than 1000 because of implementation artifacts. The 99th percentile insert rate is 998 which means there were few write stalls. Additional metrics help explain the performance and more detail on the metrics is here. The increase in the CPU overhead per query (CPU/q) is 17% for the i3 NUC and 9% for the i5 NUC. More CPU overhead probably explains the drop in QPS.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     998     10.28   5158    4706    5160    5.0.96
999     998      9.97   5443    5028    4816    5.1.72
999     998     10.09   5192    4980    5107    5.5.51
999     998     10.01   4956    4757    5456    5.6.35
999     998     10.00   4672    4464    5792    5.7.17
999     998      9.93   4470    4306    6049    8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     998     10.79   5764    5383    4574    5.0.96
999     998     10.79   6144    5702    4215    5.1.72
999     998     10.84   5850    5694    4465    5.5.51
999     998     10.67   5744    5551    4613    5.6.35
999     998     10.58   5481    5285    4824    5.7.17
999     998     10.64   5284    5094    4994    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 QPS regression from MySQL 5.0.96 to 8.0.3 is 10% on the i3 NUC and 6% on the i5 NUC. That is small which is good.

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 and more detail on the metrics is here. The increase in the CPU overhead per query (CPU/q) is 12% for the i3 NUC and 7% for the i5 NUC. 
More CPU overhead probably explains the drop in QPS.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     13.89   5102    4734    4952    5.0.96
100     100     14.54   5321    5091    4687    5.1.72
100     100     15.59   5291    5077    4730    5.5.51
100     100     15.41   5076    4829    5058    5.6.35
100     100     15.61   4792    4555    5379    5.7.17
100     100     13.34   4596    4394    5569    8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     13.69   5719    5445    4366    5.0.96
100     100     13.49   5988    5770    4187    5.1.72
100     100     13.53   5992    5786    4214    5.5.51
100     100     13.41   5812    5621    4322    5.6.35
100     100     13.54   5476    5351    4612    5.7.17
100     100     13.56   5353    5188    4693    8.0.3

No comments:

Post a Comment