Part 2 - sysbench, MyRocks, InnoDB and a small server

This is part 2 of the performance report for sysbench, MyRocks, InnoDB and a small server. The first part covered an IO-bound workload where the database was larger than RAM. This part covers a cached workload where the database fits in the MyRocks block cache and InnoDB buffer pool.

tl;dr

• MyRocks is faster than InnoDB in the write-heavy tests.
• InnoDB in MySQL 5.6 is faster than MyRocks in the read-heavy tests. I can't reproduce this on the newer/faster CPUs I use at work. For this test I used an Intel NUC with a 5th generation core i3 CPU. I am still trying to figure this out.
• InnoDB in MySQL 5.6 is faster than in 5.7 for most write-heavy tests.
• InnoDB in MySQL 5.7 is faster than in 5.6 for most read-heavy tests. I am curious why this depends on read-heavy vs write-heavy.

Details

The previous blog post has all of the details and and shared results for the IO-bound test that used 4 tables with 40M rows per table. This test used 4 tables with 1M rows per table. Tests are run for 3 storage engines - MyRocks from FB MySQL merged to upstream MySQL 5.6.35 (myrocks-5635), InnoDB from upstream MySQL 5.6.35 (innodb-5635) and InnoDB from upstream MySQL 5.7.10 (innodb-5710). The sequence of tests is the same as described in the previous blog post.

A sample command line for the test is:
bash all_small.sh 4 1000000 600 600 300 innodb 1 0 \
    ~/b/orig5710/bin/mysql none ~/b/sysbench/share/sysbench

Update-only with secondary index maintenance

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
6795    10738   13212   myrocks-5635
3907     5912    7323   innodb-5635
3286     4820    6012   innodb-5710
- optimized my.cnf
6787    10735   13103   myrocks-5635
3616     6021    7393   innodb-5635
3122     4733    6131   innodb-5710

Summary:

• MyRocks is faster than InnoDB because secondary index maintenance is write-only (read-free). On the IO-bound test that avoided stalls from page reads. Here it saves on CPU.
• The optimized my.cnf doesn't change performance
• InnoDB in MySQL 5.6 is faster than in 5.7

Update-only without secondary index maintenance

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
7373    11510   14762   myrocks-5635
8050    10783   13185   innodb-5635
6528     8971   11338   innodb-5710
- optimized my.cnf
7335    11374   14609   myrocks-5635
8809    12240   15179   innodb-5635
6644     8935   11580   innodb-5710

Summary:

• InnoDB in MySQL 5.6 is slightly faster than MyRocks. The update statement in this test doesn't require secondary index maintenance so MyRocks loses that benefit.
• The optimized my.cnf helps InnoDB in MySQL 5.6
• InnoDB in MySQL 5.6 is much faster than in 5.7

Delete

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       concurrency
- default my.cnf
8303    myrocks-5635
7848    innodb-5635
6461    innodb-5710
- optimized my.cnf
8265    myrocks-5635
7819    innodb-5635
6215    innodb-5710

Summary:

• MyRocks is faster than InnoDB
• The optimized my.cnf doesn't change performance
• InnoDB in MySQL 5.6 is faster than in 5.7

Write-only

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
12547   19685   25236   myrocks-5635
11209   15429   18387   innodb-5635
 9701   13035   15308   innodb-5710
- optimized my.cnf
12197   19241   24533   myrocks-5635
13076   17780   21097   innodb-5635
10401   14496   17402   innodb-5710

Summary:

• MyRocks is faster than InnoDB at concurrency >= 2
• The optimized my.cnf helps InnoDB
• InnoDB in MySQL 5.6 is faster than in 5.7

Read-write with --range-size=100

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
5404    10126   13214   myrocks-5635
8507    12658   16867   innodb-5635
8983    13311   16806   innodb-5710
- optimized my.cnf
7426    12116   16076   myrocks-5635
10084   15236   20895   innodb-5635
10480   15666   20830   innodb-5710

Summary:

• InnoDB is faster than MyRocks because there is more CPU overhead in MyRocks for range queries
• The optimized my.cnf helps MyRocks and InnoDB
• InnoDB has similar performance for MySQL 5.6 and 5.7

Read-write with --range-size=10000

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
153     286     311     myrocks-5635
273     484     652     innodb-5635
434     733     863     innodb-5710
- optimized my.cnf
185     330     531     myrocks-5635
278     523     684     innodb-5635
449     784     902     innodb-5710

Summary:

• InnoDB is a lot faster than MyRocks  because of the CPU overhead for range queries
• The optimized my.cnf helps MyRocks and InnoDB
• InnoDB in MySQL 5.7 is much faster than in 5.6. I think something was done to make range queries more efficient in 5.7.

Read-only with --range-size=10

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
11260   19129   27348   myrocks-5635
13846   25056   37479   innodb-5635
14533   25412   37048   innodb-5710
- optimized my.cnf
13778   22240   31544   myrocks-5635
15348   27860   42859   innodb-5635
15320   27187   42294   innodb-5710

Summary:

• InnoDB is a lot faster than MyRocks. On my work servers with newer/faster CPUs I don't reproduce this and am still trying to understand the cause.
• The optimized my.cnf helps MyRocks and InnoDB
• InnoDB has similar performance for MySQL 5.6 and 5.7

Read-only with --range-size=100

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
4393     8245   10191   myrocks-5635
8943    16784   25504   innodb-5635
11288   19771   28019   innodb-5710
- optimized my.cnf
8134    14686   20881   myrocks-5635
9847    18361   26953   innodb-5635
11778   20905   30065   innodb-5710

Summary:

• InnoDB is a lot faster than MyRocks. See summary for read-only with --range-size=10
• InnoDB in MySQL 5.7 is faster than 5.6. I think range queries were made more efficient in 5.7

Read-only with --range-size=10000

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
106     209     242     myrocks-5635
214     411     536     innodb-5635
357     624     711     innodb-5710
- optimized my.cnf
199     380     486     myrocks-5635
227     424     556     innodb-5635
374     648     732     innodb-5710

Summary:

• InnoDB is a lot faster than MyRocks. See summary for read-only with --range-size=10
• InnoDB in MySQL 5.7 is faster than 5.6. I think range queries were made more efficient in 5.7

Point-select

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
16482   29111   36510   myrocks-5635
15840   30280   37841   innodb-5635
16347   29451   47411   innodb-5710
- optimized my.cnf
16411   28628   35344   myrocks-5635
18481   34911   43390   innodb-5635
19169   31806   53018   innodb-5710

Summary:

• InnoDB is a lot faster than MyRocks. See summary for read-only with --range-size=10
• It isn't clear whether InnoDB is faster in MYSQL 5.6 or 5.7

Insert-only

The numbers in the table are the QPS for 1, 2 and 4 threads.

1       2       4       concurrency
- default my.cnf
8003    11722   13323   myrocks-5635
9548    11549   13323   innodb-5635
8065     9541    9816   innodb-5710
- optimized my.cnf
8271    12312   13804   myrocks-5635
9712    12732   14917   innodb-5635
8357    10716   11985   innodb-5710

Summary:

• InnoDB in MySQL 5.6 is faster than MyRocks
• The optimized my.cnf helps performance
• InnoDB in MySQL 5.6 is faster than in 5.7