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

Friday, November 3, 2017

Insert benchmark, IO-bound, Intel NUC: MyRocks vs InnoDB

This post explains the IO-bound insert benchmark on Intel NUC servers with results for MyRocks and InnoDB. The previous post explained an in-memory insert benchmark.

tl;dr
  • MyRocks gets better insert rates while using less HW per insert: ~8X faster insert rates on the i3 NUC and ~5X faster on the i5 NUC.
  • InnoDB writes ~20X more to storage per insert compared to MyRocks.
  • Uncompressed InnoDB uses ~1.6X more space than uncompressed MyRocks
  • MyRocks is up to 1.3X slower than InnoDB for secondary index scans and up to 1.7X slower for PK scans. The gap is larger for PK indexes because InnoDB secondary indexes suffer from fragmentation in this test. MyRocks suffers from using more CPU per row, so it can't drive storage as fast as InnoDB. 
  • InnoDB suffers more write stalls on the read-write test
  • MyRocks does much better for reads on the read-write tests because it is able to cache the secondary indexes. This is a benefit of being more space efficient. In the future I need to make the database larger to avoid comparing cached MyRocks vs not-cached InnoDB on this benchmark.

Configuration

The tests used a MyRocks build from FB MySQL 5.6.35 and August 15 with git hash 0d76ae and then upstream InnoDB 5.6.35 and 5.7.17. 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,  use the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention on the small servers. For all tests the binlog was enabled but fsync was disabled for the binlog and I set flush_log_at_trx_commit=2 for both engines. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

The database is larger than RAM. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark used 1 table and loaded 250M rows on the i3 NUC and 500M rows on the i5 NUC, 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.

The MyRocks tests were done without compression and with zstd compression for the max LSM level. These are labeled MyRocks.none and MyRocks.zstd below.

Results

All of the data for the graphs is here. Note that Linux iostat can overstate bytes written by RocksDB by 2X because it counts bytes trimmed as bytes written. The data in github does not adjust for that. The numbers here for wKB/i does by dividing the measured value by 2 -- which is an estimate. Note that even if my adjustment were exact this value is still missing bytes written by flash GC and when I measured that in the past it was much worse for InnoDB.

Results: load

This section has results for the load.

This graph has the insert rate for each engine relative to the rate for MyRocks. I am not surprised that MyRocks has a better insert rate than InnoDB because it does less work per insert than InnoDB. MyRocks secondary index maintenance is read free and MyRocks has much less write amplification. For the in-memory insert benchmark there is a regression in the insert rate for InnoDB from 5.6 to 5.7 but that does not occur here.

These tables have more data for the i3 NUC including the average insert rate (IPS), the CPU overhead per insert, CPU/i, KB written to and read from storage per insert, wKB/i & rKB/i, and the size of the database when the load finishes, size(GB). The first table has the absolute values and the second the values relative to MyRocks without compression (MyRocks.none). By all metrics MyRocks is more efficient with hardware:
  • InnoDB writes ~20X more to storage per insert which helps my SSD devices last longer.
  • InnoDB reads between 10X and 20X more from storage per insert because it reads leaf pages for secondary index maintenance while MyRocks reads for compaction but secondary index maintenance is read free. 
  • InnoDB uses more CPU per insert.
  • InnoDB uses ~1.6X more space than MyRocks when neither use compression. InnoDB suffers from B-Tree fragmentation for the secondary indexes and MyRocks benefits from key prefix compression.

                Absolute values
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    22122   2699     1.79   0.25    28
MyRocks.zstd    20169   3243     1.36   0.14    13
InnoDB-5.6       2665   4934    39.08   6.10    46
InnoDB-5.7       3242   3526    34.24   2.54    46

                Values relative to MyRocks
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    1.00    1.00     1.00    1.00   1.00
MyRocks.zstd    0.91    1.20     0.76    0.56   0.46
InnoDB-5.6      0.12    1.83    21.83   24.40   1.64
InnoDB-5.7      0.15    1.31    19.13   10.16   1.64

These tables have more data for the i5 NUC. The insert rate difference is smaller here than for the i3 NUC. InnoDB suffers more from the slower storage device on the i3 NUC. Similar to the i3 NUC, InnoDB writes ~20X more to storage per insert and ~1.6X more space compared to uncompressed MyRocks.

                Absolute values
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    26837   2278     1.75   0.12    56
MyRocks.zstd    24811   2770     1.52   0.06    25
InnoDB-5.6       5978   4265    44.50   2.10    90
InnoDB-5.7       5634   4690    44.92   1.26    90

                Values relative to MyRocks
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    1.00    1.00     1.00    1.00   1.00
MyRocks.zstd    0.92    1.22     0.87    0.50   0.45
InnoDB-5.6      0.22    1.87    25.43   17.50   1.61
InnoDB-5.7      0.21    2.06    25.67   10.50   1.61

Results: scan

This section has results for the index scans. There are 5 scans: 1 is the PK, 2/3/4 are the secondary indexes and then 5 is the PK again. The 2+3+4 column has the time to scan all secondary indexes. The gap between MyRocks and InnoDB is much smaller here than it was on the in-memory full index scan. I assume the CPU overhead from MyRocks is less significant here where the scan is IO-bound.

I wrote above that I used a MyRocks build from August 15. Unfortunately for the scan test I used one for June 16 with git hash 52e058.

The result below includes results (MyRocks.none.ra, MyRocks.zstd.ra) that used a RocksDB option to make it more likely that filesystem readahead would be enabled. The option is rocksdb_advise_random_on_open and when set to 0 RocksDB uses POSIX_FADV_NORMAL rather than POSIX_FADV_RANDOM when opening SST files for user reads. I don't think this option is ready for production, but it can be used on benchmarks.

Some notes from the results:
  • Full-index scans are faster with InnoDB. The gap is larger for the PK index because InnoDB secondary indexes suffer from fragmentation on this test. MyRocks suffers from using more CPU per row, so it can't drive storage as fast as InnoDB. MyRocks is up to 1.3X slower than InnoDB for secondary index scans and up to 1.7X slower for PK scans.
  • The 2+3+4 result for MyRocks is close to the time for InnoDB in MySQL 5.6 when MyRocks uses filesystem readahead. I assume we can do more to get better readahead. Index scans on a fragmented B-Tree generate a lot of random reads. A full index scan from an LSM can do large reads from each LSM level to avoid that.
  • Something was done for InnoDB in MySQL 5.7 to make range scans faster. Thank you to the InnoDB team.
#seconds for each index scan
 1      2       3       4       5       2+3+4   index/engine
 219    202     222     224     207      648    MyRocks.none
 182    189     203     197     168      589    MyRocks.none.ra
 241    206     230     228     230      664    MyRocks.zstd
 241    195     215     212     201      622    MyRocks.zstd.ra
 292    181     194     212     127      587    InnoDB-5.6
 419    148     157     203      99      508    InnoDB-5.7

Results for the i5 NUC are similar to the i3 NUC.

#seconds for each index scan
 1      2       3       4       5       2-4     index/engine
 394    345     381     366     342     1092    MyRocks.none
 331    318     344     331     285      993    MyRocks.none.ra
 385    346     383     386     372     1115    MyRocks.zstd
 359    327     360     356     341     1043    MyRocks.zstd.ra
 475    290     302     314     225      906    InnoDB-5.6
 417    250     267     249     164      766    InnoDB-5.7

Next are hardware efficiency metrics that help explain the performance differences. There are many interesting things in here. The columns are explained here.
  • InnoDB gets more read IO throughput than MyRocks. I need to understand this better but part of the problem is that MyRocks uses more CPU per row so it isn't able to consume data from storage as fast as InnoDB.
  • The InnoDB PK scan gets more read IO throughput than the InnoDB secondary index scan because the secondary index is fragmented and the PK is not courtesy of the workload (PK inserts are in PK order, secondary index inserts are not).
  • Filesystem readahead gets ~10% more read IO throughput for MyRocks.
  • InnoDB in MySQL 5.7 uses much less CPU than in MySQL 5.6 (thanks InnoDB team). I have yet to confirm whether the work done to make InnoDB scans faster in 5.7 are InnoDB-only or will benefit other engines.
  • The gap between InnoDB and MyRocks is larger for the PK scan than the secondary index scan. This might occur because of secondary index fragmentation for InnoDB.
  • The CPU overhead from zstd compression for MyRocks is small.

Metrics for q4: the scan of a secondary index
secs    rMB/s   rKB/o   rGB     MCPU/o  engine
366     32.3    0.024   12      16.872  MyRocks.none
331     35.7    0.024   12      17.044  MyRocks.none.ra
386     13.1    0.010    5      18.684  MyRocks.zstd
356     14.0    0.010    5      17.764  MyRocks.zstd.ra
314     44.2    0.028   13      10.748  InnoDB-5.6
249     56.0    0.028   14       7.592  InnoDB-5.7

Metrics for q5 - the second scan of the PK
secs    rMB/s   rKB/o   rGB     MCPU/o
342      72.7   0.051   24      16.064  MyRocks.none
285      87.3   0.051   24      14.612  MyRocks.none.ra
372      30.6   0.023   11      17.820  MyRocks.zstd
341      33.3   0.023   11      17.396  MyRocks.zstd.ra
225     147.0   0.067   32      14.732  InnoDB-5.6
164     198.1   0.067   32       8.900  InnoDB-5.6

And a graph of the results from the 2+3+4 column because some readers like graphs.

Results: read-write, 1000 writes/second

Disclaimer - MyRocks is able to cache the secondary indexes during this test while InnoDB is not. I didn't plan this and in the future I need to prevent that from happening. While being more efficient with space is a benefit of MyRocks, even without compression, there is a step function in performance when the database is cached that distorts performance differences. MyRocks needs less space for secondary indexes because it doesn't suffer fragmentation like a B-Tree and it uses key prefix compression.

This section has results for the read-write tests where the writer does 1000 inserts/second. The tables below have the most interesting data. The  metrics are explained here. 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. There were more write stalls on InnoDB than MyRocks based on the 99th percentile insert rate as ips.99 is lower for InnoDB. QPS is much better for MyRocks but see the disclaimer above. InnoDB writes ~20X more to storage per insert compared to MyRocks.

                Absolute values for the i3 NUC
                IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    999     998      1.30   3349    2377      0.37   7912
MyRocks.zstd    999     998      1.70   3450    2506      0.25   7986
InnoDB-5.6      999     977     26.20    258     156    101.61  37834
InnoDB-5.7      999     977     30.70    311     238     98.68  26829

                Absolute values for the i5 NUC
                IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    999     998      2.23   3818    2395     0.47    6945
MyRocks.zstd    999     998      1.96   3986    2858     0.28    6788
InnoDB-5.6      999     987     73.09    992     626    59.45   16798
InnoDB-5.7      999     988     71.22   1042     738    61.07   16115

Results: read-write, 100 writes/second

See the disclaimer in the previous section.

This section has results for the read-write tests where the writer does 100 inserts/second. The metrics are explained here. The analysis from the previous section is relevant here.

                Absolute values for the i3 NUC
                IPS.av  IPS.99  QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    100     100     3419    3184     0.01    7173
MyRocks.zstd    100     100     3549    3270     0.01    6969
InnoDB-5.6      100     100      827     534    66.49   13675
InnoDB-5.7      100     100      900     566    69.56   10269

                Absolute values for the i5 NUC
                IPS.av  IPS.99  QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    100     100     3911    3740     0       6202
MyRocks.zstd    100     100     4090    3841     0.01    5938
InnoDB-5.6      100     100     1334     951    50.42   11546
InnoDB-5.7      100     100     1383     996    52.50   10807

I did not include a graph. See the disclaimer above.


Thursday, November 2, 2017

Insert benchmark, in-memory, Intel NUC: MyRocks vs InnoDB

I repeated my performance tests using more storage engines, newer versions of MyRocks and possibly better my.cnf settings. This post explains the in-memory insert benchmark on Intel NUC servers with results for MyRocks and InnoDB.

tl;dr
  • MyRocks sustains higher insert rates
  • InnoDB writes about 10X more per insert during the load and more than 30X per insert during one of the read-write tests compared to MyRocks. Better write efficiency with MyRocks means that SSD devices last longer and I replaced many SSDs this year.
  • Index scans are about 2X faster on InnoDB compared to MyRocks.
  • InnoDB suffers more write stalls during the read-write test on the i3 NUC
  • InnoDB gets more read QPS during the read-write test compared to MyRocks but there is a regression from 5.6 to 5.7

Configuration

The tests used a MyRocks build from FB MySQL 5.6.35 and August 15 with git hash 0d76ae and then upstream InnoDB 5.6.35 and 5.7.17. 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. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, use the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention on the small servers. For all tests the binlog was enabled but fsync was disabled for the binlog and I set flush_log_at_trx_commit=2 for both engines. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

In this test the database is cached by MyRocks and InnoDB. The insert benchmark loaded 10M rows into one table, 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 graphs is here. Note that Linux iostat can overstate bytes written by RocksDB by 2X because it counts bytes trimmed as bytes written. The data in github does not adjust for that. The numbers here for wKB/i does by dividing the measured value by 2 -- which is an estimate. Note that even if my adjustment were exact this value is still missing bytes written by flash GC and when I measured that in the past it was much worse for InnoDB.

Results: load

This section has results for the load.

This graph has the insert rate for each engine relative to the rate for MyRocks. I am not surprised that MyRocks has a better insert rate than InnoDB because it does less work per insert than InnoDB. MyRocks secondary index maintenance is read free and MyRocks has much less write amplification. There is a regression in the insert rate for InnoDB from 5.6 to 5.7 and I will cover that in detail in another post.
These tables have more data for the i3 NUC including the average insert rate (IPS), the CPU overhead per insert (CPU/i) and KB written to storage per insert (wKB/i). The first table has the absolute values and the second the values relative to MyRocks. InnoDB writes ~10X more to storage per insert compared to MyRocks. I appreciate that MyRocks helps my SSD devices last longer. The variance in CPU/i values are harder to explain but the CPU overhead includes foreground and background processing as well as anything else running on the test server.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         24570   2004     0.78
InnoDB-5.6      18797   1737     8.87
InnoDB-5.7      15083   3497    10.36

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.77    0.87    11.37
InnoDB-5.7      0.61    1.75    13.28

These tables have more data for the i5 NUC. There is less difference between the engines here than on the i3 NUC. But InnoDB continues to write more than 10X to storage per insert compared to MyRocks.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         31847   1526     0.77
InnoDB-5.6      29586   1461     8.06
InnoDB-5.7      25907   1491     9.66

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.93    0.96    10.47
InnoDB-5.7      0.81    0.98    12.55

Results: scan

This section has results for the index scans. There are 5 scans: 1 is the PK, 2/3/4 are the secondary indexes and then 5 is the PK again. The 2+3+4 column has the time to scan all secondary indexes. This time is about 2X worse for MyRocks and we expect to make that better.  The PK scan is also slower I will write more about that in another post.

Index scans are slower on MyRocks than InnoDB. I assume there is more CPU overhead from merging iterators from different levels of the LSM tree. In this case all data is cached by the database so there are no reads from storage.

#seconds for each index scan
1       2       3       4       5       2+3+4   index/engine
-       -       -       -       -       -----   ------------
7       8       8       8       5       24      MyRocks
4       4       4       4       4       12      InnoDB-5.6
3       4       7       3       3       14      InnoDB-5.7

Results for the i5 NUC are similar to the i3 NUC.

#seconds for each index scan
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       7       8       7       5       22      MyRocks
3       4       3       4       3       11      InnoDB-5.6
3       3       3       2       3        8      InnoDB-5.7

And a graph of the results from the 2+3+4 column because some readers like graphs.
Results: read-write, 1000 writes/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The tables below have the most interesting data. The  metrics are explained here. 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. There were more write stalls on InnoDB than MyRocks based on the 99th percentile insert rate as ips.99 is lower for InnoDB on the i3 NUC. InnoDB sustained a better query rate than MyRocks, although there is a regression from 5.6 to 5.7 but that is a topic for another post. InnoDB writes more than 30X to storage per insert compared to MyRocks.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     3684    3329     1.24    7307   1.8    2.5   26.9  MyRocks
999    994     4817    4454    43.90    5970   3.4   43.8   28.8  InnoDB-5.6
999    995     3748    3540    40.27   13834   3.4   40.2   51.8  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     4390    4006     1.26    6004   1.8    2.5   26.4  MyRocks
999    998     5798    5410    44.12    4846   3.4   44.1   28.1  InnoDB-5.6
999    998     5184    4917    40.62    5186   3.4   40.6   26.9  InnoDB-5.7

Results: read-write, 100 writes/second

This section has results for the read-write tests where the writer does 100 inserts/second. The metrics are explained here. Because the write rate is lower than in the previous section, the reads account for more of the overhead. The Mcpu/q result is larger for MyRocks than for InnoDB-5.6 which might explain why InnoDB does more read QPS. Again, InnoDB writes more to storage per insert and there is a regression from 5.6 to 5.7.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     3683    3416     1.35    6706   1.8   0.3    24.7  MyRocks
100    100     4729    4499    51.85    5457   3.4   5.2    25.8  InnoDB-5.6
100    100     3906    3717    52.94   12636   3.4   5.3    49.4  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     4444    4159     1.38    5538   1.8    0.3   24.6  MyRocks
100    100     5937    5548    51.95    4309   3.4    5.2   25.6  InnoDB-5.6
100    100     5349    4976    55.24    4803   3.4    5.5   25.7  InnoDB-5.7

These graphs show the read QPS relative to MyRocks for the read-write tests from above.



Saturday, October 21, 2017

Wearing out an SSD

I use Intel NUC servers at home to test open-source databases for performance and efficiency. The servers have an SSD for the database and that is either a Samsung 960 EVO or a Samsung 850 EVO. It is time to replace the 960 EVO after about 5 months of heavy usage. I test MySQL (MyRocks, InnoDB, MyISAM) and MongoDB (MongoRocks, WiredTiger and mmapv1). If I limited myself to MyRocks and MongoRocks then the storage devices would last much longer courtesy of better write efficiency of an LSM versus a B-Tree.

I have 3 servers with the 960 EVO and I will replace the SSD in all of them at the same time. I assume that device performance changes as it ages, but I have never tried to quantify that. For the 850 EVO I will buy extra spares and will upgrade from the 120gb device to a 250gb device because they cost the same and the 120gb device is hard to find. I just hope the devices I use right now will last long enough to finish my current round of testing. One day I will switch to EC2 and GCE and wear out their devices, but I like the predictability I get from my home servers.

I use Ubuntu 16.04 and its version of smartctl doesn't yet support NVMe devices so I used the nvme utility. Percona has a useful blog post on this. The percentage_used value is 250% which means the estimated device endurance has been greatly exceeded. The value of critical_warning is 0x4 which means NVM subsystem reliability has been degraded due to significant media related errors or any internal error that degrades NVM subsystem reliability per the NVMe spec. The data_units_written value is the number of 512 bytes units written and is reported in thousands. The value 1,400,550,163 means that 652TB has been written to the device. The device is 250GB which is about 2700 full device writes. If I wave my hands and expect 2000 full device writes from 3D NAND and ignore overprovisioning (OP) then it seems reasonable that the device is done. I assume that OP is 10% based on available_spare_threshold. The warranty on the 250gb 960 EVO is 3 years or 100 TBW and I wrote 652TB so I am happy about that. My previous post on this is here.

This is from the 960 EVO.

$ sudo nvme smart-log /dev/nvme0
Smart Log for NVME device:nvme0 namespace-id:ffffffff
critical_warning                    : 0x4
temperature                         : 32 C
available_spare                     : 100%
available_spare_threshold           : 10%
percentage_used                     : 250%
data_units_read                     : 159,094,604
data_units_written                  : 1,400,550,163
host_read_commands                  : 4,698,541,096
host_write_commands                 : 19,986,018,997
controller_busy_time                : 32,775
power_cycles                        : 30
power_on_hours                      : 3,039
unsafe_shutdowns                    : 7
media_errors                        : 0
num_err_log_entries                 : 0
Warning Temperature Time            : 0
Critical Composite Temperature Time : 0
Temperature Sensor 1                : 32 C
Temperature Sensor 2                : 45 C
Temperature Sensor 3                : 0 C
Temperature Sensor 4                : 0 C
Temperature Sensor 5                : 0 C
Temperature Sensor 6                : 0 C
Temperature Sensor 7                : 0 C
Temperature Sensor 8                : 0 C

This is from the 850 EVO and the server with the largest value for Total_LBAs_Written. The device has a 512 byte sector and with Total_LBAs_Written at 739353756925 then 344TB has been written to the 120gb device. That is about 2900 full device writes assuming no OP. Once again, I should be happy that the device lasted this long. The warranty on the 120gb 850 EVO is 5 years or 75TBW. I wrote a lot more than 75TB. The Wear_Leveling_Count value is 3335 and that is the average number of P/E cycles. That value is similar to my estimate of 2900 full device writes. I assume that I will get about 2000 from 3D NAND and I exceeded that.

$ sudo smartctl --all /dev/sdb
...

SMART Attributes Data Structure revision number: 1
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
  5 Reallocated_Sector_Ct   0x0033   100   100   010    Pre-fail  Always       -       0
  9 Power_On_Hours          0x0032   099   099   000    Old_age   Always       -       4430
 12 Power_Cycle_Count       0x0032   099   099   000    Old_age   Always       -       11
177 Wear_Leveling_Count     0x0013   001   001   000    Pre-fail  Always       -       3335
179 Used_Rsvd_Blk_Cnt_Tot   0x0013   100   100   010    Pre-fail  Always       -       0
181 Program_Fail_Cnt_Total  0x0032   100   100   010    Old_age   Always       -       0
182 Erase_Fail_Count_Total  0x0032   100   100   010    Old_age   Always       -       0
183 Runtime_Bad_Block       0x0013   100   100   010    Pre-fail  Always       -       0
187 Reported_Uncorrect      0x0032   100   100   000    Old_age   Always       -       0
190 Airflow_Temperature_Cel 0x0032   047   038   000    Old_age   Always       -       53
195 Hardware_ECC_Recovered  0x001a   200   200   000    Old_age   Always       -       0
199 UDMA_CRC_Error_Count    0x003e   100   100   000    Old_age   Always       -       0
235 Unknown_Attribute       0x0012   099   099   000    Old_age   Always       -       2
241 Total_LBAs_Written      0x0032   099   099   000    Old_age   Always       -       739353756925

SMART Error Log Version: 1
No Errors Logged

Tuesday, September 19, 2017

Linkbench: IO-bound, large server

This has results for IO-bound Linkbench on a large server. I previously shared results for in-memory & low-concurrency Linkbench.

tl;dr
  • Compression doesn't hurt MyRocks performance
  • MyRocks matches or beats InnoDB performance with much better efficiency

Configuration

I used my Linkbench repo and helper scripts to run sysbench with maxid1=1B, loaders=4 and requestors=16 so there will be 4+1 concurrent connections doing the load (4 for link/count tables, 1 for node table) and 16 connections running transactions after the load finishes. My linkbench repo has a recent commit that changes the Linkbench workload and results with that change are labeled new config while results without the change are labeled old config. I share both. The test pattern is 1) load and 2) transactions. The transactions were run in 12 1-hour loops and I share results from the last hour. The test server has 48 HW threads, fast SSD and 50gb of RAM available to the OS page cache and database.

Tests were run for MyRocks, InnoDB from upstream MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here.
  • MyRocks was compiled on August 15 with git hash 0d76ae. The block cache was set to 10gb. Tests were done without compression and then with no compression for L0/L1/L2, LZ4 for L3 to the next to last level and then Zstandard for the max level.
  • InnoDB was from upstream 5.6.35, 5.7.17 and 8.0.2.  The buffer pool was set to 35gb. The performance schema was enabled. Compression was not used.
  • TokuDB was from Percona Server 5.7.17. The db cache was set to 10gb. Tests were done without compression and then zlib.
Load Results

All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config.
  • MyRocks has the best insert rate and compression doesn't hurt the rate
  • There is a regression for InnoDB from 5.7 to 8.0.2
  • Write efficiency (wKB/i) is best for TokuDB and similar between MyRocks and InnoDB
  • CPU efficiency (Mcpu/i) is similar for MyRocks and InnoDB
  • Space efficiency (size) is best for MyRocks and TokuDB. Uncompressed MyRocks uses much less space than uncompressed InnoDB

ips     wKB/i   Mcpu/i  size    rss     rMB/s   wMB/s   cpu     engine
142812  2.03     79      948     7.5    0.3     290.3   11.3    myrocks.none
144232  2.55     87      439     5.0    1.9     367.7   12.6    myrocks.zstd
132297  1.04     75     15xx    42.7    0       136.6    9.9    inno5635
138218  1.04     81     15xx    39.4    0.1     144.0   11.2    inno5717
122495  1.05     96     15xx    39.5    0.1     128.8   11.8    inno802
 36737  1.68    246     12xx    11.1    0.1      61.6    9.0    toku5717.none
 37032  1.18    257      442    11.6    0.1      43.8    9.5    toku5717.zlib

legend:
* ips - inserts/second
* wKB/i - iostat KB written per insert
* Mcpu/i - normalized CPU time per insert
* wMB/s, rMB/s - iostat write MB/s & read MB/s
* size - database size in GB at test end
* cpu - average value of vmstat us + sy columns
* rss - mysqld RSS in GB

Transaction Results

These are results from the 12th 1-hour loop of the transaction phase. All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. I will explain them. 
  • MyRocks and InnoDB have the best transaction rates
  • Write efficiency (wKB/t) is much better for MyRocks than for InnoDB
  • CPU efficiency (Mcpu/t) is similar for MyRocks and InnoDB
  • Space efficiency (size) is best for MyRocks and TokuDB (see here)
  • MyRocks has the the best response times and compression doesn't hurt it

tps     r/t     rKB/t   wKB/t   Mcpu/t  ul      gl      rMB/s   wMB/s   cpu     engine
34701   1.16     22.41   1.27    659      1      0.9     777.7   44.2   22.9    myrocks.none
34622   1.08     13.12   1.27    758      1      0.9     454.4   44.1   26.2    myrocks.zstd
25775   1.16     18.50  12.05    672      6      4       476.8  310.5   17.3    inno5635
34539   1.15     18.39  11.91    609      3      2       635.0  411.4   21.0    inno5717
33578   1.14     18.23  11.97    641      3      2       612.1  401.9   21.5    inno802
 8982   3.07    188.97   5.64   2685     13     12      1697.3   50.7   24.1    toku5717.none
12448   1.20     17.42   2.84   2644      6      3       216.9   35.4   32.9    toku5717.zlib

legend:
* tps - transactions/second
* r/t - iostat reads per transaction
* rKB/t, wKB/t - iostat KB read & written per transaction
* Mcpu/t - normalized CPU time per transaction
* size - database size in GB at test end
* un, gn, ul, gl - 99th percentile response time in millisecs for UpdateNode,
                   GetNode, UpdateList and GetLinkedList transactions
* rMB/s, wMB/s - iostat read MB/s & write MB/s
* cpu - average CPU utilization from vmstat us & sy

Charts

Charts for load and transaction throughput using the ips and tps columns from the tables above.

Wednesday, September 13, 2017

Making it easier to read Linux perf output

This is a short note to myself the next time I need to remember things I had to do to enhance the readability of Linux perf output.
  • trim trailing spaces -> perf report --stdio | awk 'sub(/ *$/, "")'
  • don't forget the children when using perf record -g -> perf report --no-children


Friday, September 8, 2017

Linkbench - in-memory, low-concurrency

After a few weeks of sysbench it is now time for more complex workloads and the first one is Linkbench with a cached database and low-concurrency. I prefer to start with cached & low-concurrency configurations before trying IO-bound & high-concurrency.

tl;dr:
  • InnoDB from MySQL 5.6 had the best throughput
  • CPU efficiency is similar for MyRocks and InnoDB
  • There is a CPU regression from MySQL 5.6 to 5.7 to 8.x
  • Write efficiency was similar for all engines on the load test but much better for MyRocks and TokuDB on the transaction test.

Configuration

I used my Linkbench repo and helper scripts to run sysbench with maxid1=10M, loaders=1 and requestors=1 so there will be 2 concurrent connections doing the load and 1 connection running transactions after the load finishes. My linkbench repo has a recent commit that changes the Linkbench workload and results with that change are labeled new config while results without the change are labeled old config. I share both. The test pattern is 1) load and 2) transactions. The transactions were run in 12 1-hour loops and I share results from the last hour. The test server has 48 HW threads, fast SSD and 256gb of RAM.

Tests were run for MyRocks, InnoDB from upstream MySQL and TokuDB. The binlog was enabled but sync on commit was disabled for the binlog and database log. All engines used jemalloc. Mostly accurate my.cnf files are here but the database cache was made large enough to cache the ~10gb database.
  • MyRocks was compiled on August 15 with git hash 0d76ae. Compression was not used.
  • InnoDB was from upstream 5.6.35, 5.7.17 and 8.0.2.  The performance schema was enabled.
  • TokuDB was from Percona Server 5.7.17. Compression was not used.

Load Results

All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. The results for the old config are similar.
  • InnoDB 5.6 has the best insert rate but there is a regression from InnoDB in 5.6 to 5.7 to 8.0.2
  • Write efficiency (wkb/i) is similar for all engines
  • CPU efficiency (Mcpu/i) is similar for MyRocks and InnoDB

ips     wkb/i   Mcpu/i  size    wMB/s   cpu     engine
 54283  1.60     83     14       86.7    4.5    myrocks
 64402  1.02     72     16       65.6    4.6    inno5635
 56414  1.03     77     16       58.2    4.3    inno5717
 42954  1.02     97     16       45.1    4.2    inno802
 21611  1.42    179     14       30.7    3.9    toku5717

legend:
* ips - inserts/second
* wkb/i - iostat KB written per insert
* Mcpu/i - normalized CPU time per insert
* wMB/s - iostat write MB/s, average
* size - database size in GB at test end
* cpu - average value of vmstat us + sy columns


Transaction Results

These are results from the 12th 1-hour loop of the transaction phase. All of the data is here and note that I did not adjust metrics computed from iostat to account for iostat overstating bytes written by up to 2X for RocksDB. The table below has a subset of the results for the new config. I will explain them. The results for the old config are similar.
  • InnoDB 5.6 has the best transaction rate but there is a regression from 5.6 to 5.7 to 8.0.2
  • Write efficiency (wkb/t) is much better for TokuDB and MyRocks than for InnoDB
  • CPU efficiency (Mcpu/t) is similar for MyRocks and InnoDB
  • Response times are similar between MyRocks and InnoDB

tps     wkb/t   Mcpu/t  size  un    gn    ul    gl    wMB/s  engine
5489    0.78     642    15    0.3   0.1   0.5   0.5    4.3   myrocks
7239    5.15     524    26    0.3   0.1   0.4   0.2   37.3   inno5635
6463    5.17     580    26    0.3   0.1   0.5   0.3   33.4   inno5717
5855    5.25     623    25    0.3   0.1   0.6   0.3   30.7   inno802
3333    0.08    1043    18    0.6   0.2   1.0   0.8   10.0   toku5717

legend:
* tps - transactions/second
* wkb/t - iostat KB written per transaction
* Mcpu/t - normalized CPU time per transaction
* size - database size in GB at test end
* un, gn, ul, gl - 99th percentile response time in millisecs for UpdateNode, GetNode, UpdateList and GetLinkedList transactions
* wMB/s - iostat write MB/s, average


Charts

Charts for load and transaction throughput using the ips and tps columns from the tables above.


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