Friday, February 24, 2017

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

No comments:

Post a Comment

Battle of the Mallocators

If you use RocksDB and want to avoid OOM then use jemalloc or tcmalloc and avoid glibc malloc. That was true in 2015 and remains true in 202...