Showing entries 1 to 10 of 95
10 Older Entries »
Displaying posts with tag: RocksDB (reset)
Sysbench: IO-bound, InnoDB, a small server, MySQL 5.0 to 8.0

This has results for IO-bound sysbench with InnoDB and MySQL versions 5.0, 5.1, 5.5, 5.6, 5.7 and 8.0. The results here don't always match the results from in-memory sysbench. One source of variance is the differing performance of the SSDs used on the i3 and i5 NUC.

There are four types of tests: write-heavy, scan-heavy, point-query and inlist-query. Performance within each group is usually similar. Reading the summary below the results for modern InnoDB are mostly an improvement. This is good news because these tests are limited to low-concurrency and the many improvements in modern InnoDB don't help here.

tl;dr - performance for InnoDB in 8.0.3 compared to 5.0.96

  • Full index scans were faster on the i5 NUC and both slower & faster on the i3 NUC. Shorter scans from read-write and …
[Read more]
Sysbench, in-memory, small server: MyRocks over time

In this post I compare four MyRocks releases from February to October using in-memory sysbench and a small server. The goal is understand where we have made MyRocks faster and slower this year.

tl;dr

  • For many tests there is no decrease in QPS from February to October
  • For some tests the QPS decreased by 3% to 8%
  • The largest regression is for read-heavy tests that run after write-heavy tests. Querying an LSM tree after many updates usually costs more in CPU and/or IO compared to querying it before the updates. But the CPU overhead might have increased since February.


Configuration

The tests used MyRocks from FB MySQL which is currently based on 5.6.35. Builds were done using HEAD from February 10, April 14, June 16, August 15 and October 16. The git hashes for these builds …

[Read more]
Sysbench, in-memory, small server: InnoDB & MyRocks

In this post I compare MyRocks and InnoDB using in-memory sysbench and a small server. Previous posts explained performance for MySQL from 5.0 to 8.0 for MyISAM and InnoDB. In this post I limit the results to MySQL 5.6.35 for MyRocks and both 5.6.35 and 5.7.17 for InnoDB.

tl;dr - the QPS for MyRocks relative to InnoDB in MySQL 5.6 where 1.0 means they are equal

  • For write heavy tests MyRocks gets ~0.80 vs InnoDB except on the update-index test where it benefits from read-free secondary index maintenance
  • For scan heavy tests MyRocks gets between 0.70 and 0.94 vs InnoDB …
[Read more]
Sysbench: in-memory, InnoDB and a small server, MySQL 5.0 to 8.0

This has results for in-memory sysbench with InnoDB and MySQL versions 5.0, 5.1, 5.5, 5.6, 5.7 and 8.0. The previous post covered MyISAM. There are four types of tests: write-heavy, scan-heavy, point-query and inlist-query. For MyISAM the results for the tests within each group were similar. That is less true for InnoDB.

tl;dr - because the other tl;dr are too long

  • InnoDB in 5.5 frequently has lousy performance for write-heavy tests. I didn't debug it.
  • InnoDB 5.7 and 8.0 have great scan performance, are much better for high-concurrency workloads (to be covered in a future post), but suffer from more CPU overhead for low-concurrency workloads.


tl;dr - from 5.0.96 to 8.0.3

  • For update-nonindex QPS decreased 41% on the i5 NUC and 52% on the i3 NUC. …
[Read more]
Sysbench: in-memory, MyISAM and a small server, MySQL 5.0 to 8.0

This has results for in-memory sysbench with MyISAM and MySQL versions 5.0, 5.1, 5.5, 5.6, 5.7 and 8.0. I will publish results for InnoDB tomorrow.

tl;dr - from 5.0.96 to 8.0.3

  • For write-heavy tests QPS decreased by more than 50%
  • For scan-heavy tests QPS decreased by no more than 14%
  • For point-query tests QPS decreased by 30% on the i5 NUC and 40% on the i3 NUC
  • For inlist-query tests QPS decreased by 25% on the i5 NUC and 21% on the i3 NUC. There is a large reduction in QPS between MySQL 5.5.51 and 5.6.35. Maybe the optimizer overhead grew for queries with large in-lists.


tl;dr - from 5.6.35 to 8.0.3

  • For write-heavy tests QPS decreased by ~30%
  • For scan-heavy tests QPS did not decrease
  • For point-query tests QPS decreased by ~15%
  • For inlist-query tests QPS increased by 9% on the i5 NUC and …
[Read more]
IO-bound insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with InnoDB from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This uses an IO-bound workload with small servers. The previous post covered an in-memory workload for InnoDB.

tl;dr - from 5.0.96 to 8.0.3

  • Insert performance improved by ~3X after MySQL 5.5 when I switching from built-in InnoDB to modern InnoDB.
  • Write stalls are a problem but I need better metrics for reporting this.
  • Secondary index scans are 1.09X faster on the i5 NUC and 1.64X faster on the i3 NUC
  • PK index scans are 1.88X faster on the i5 NUC and 1.96X faster on the i3 NUC
  • QPS increased by 5% on the i5 NUC and decreased by 25% on the i3 NUC in the read-write test that does 100 …
[Read more]
In-memory insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

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

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 MyISAM.
  • Insert performance improved by ~3X after MySQL 5.5 when I switched from built-in InnoDB to modern InnoDB.
  • The query rate decreased by 15% on …
[Read more]
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 …
[Read more]
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 …
[Read more]
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
[Read more]
Showing entries 1 to 10 of 95
10 Older Entries »