Timing load & index for sysbench tables

存储架构 2017-09-07 阅读原文

This post compares MyRocks, InnoDB and TokuDB on the time required to load and index a table for sysbench.

tl;dr

  • MyRocks, InnoDB and TokuDB have similar load performance although there is a regression for InnoDB from 5.6 to 5.7 to 8.x
  • InnoDB create index is much faster starting in 5.7

Configuration

I used my sysbench helper scripts with my sysbench branch and configured it to create 1 table with 800M rows. The binlog was enabled but sync on commit was disabled for the binlog and database log. The sysbench client shared the host with mysqld. The host has 48 HW threads, 50gb of RAM for the OS and MySQL and fast SSD. The test table is larger than RAM but it will take me a few days to get details on that. The test was repeated for MyRocks, InnoDB and TokuDB. I continue to use the IO-bound setup asdescribed previously.

  • I compiled MyRocks on August 15 with git hash 0d76ae. The MyRocks tests were done without compression and with zstd compression.
  • I used TokuDB from Percona Server 5.7.17. The TokuDB tests were done without compression and with zlib compression. I tried tokudb_disable_prefetching ON and OFF, but I have been setting this to ON for my OLTP benchmarks.
  • I used InnoDB from upstream 5.6.35, 5.7.17, 8.0.1 and 8.0.2. For 8.x I used latin1/latin1_swedish_ci charset/collation. The performance_schema was enabled. The InnoDB tests did not use compression.

A sample command line for sysbench is:

bash all.sh 1 800000000 180 300 180 innodb 1 0 /bin/mysql none /sysbench10 /dbdir

Results


The load is in PK order and there are no secondary indexes. Engines have similar performance although there is a slow regression for InnoDB with each new release and there is a big regression from 8.0.1 to 8.0.2 which I hope will be fixed when 8.x approaches GA. The ratio is the time to load for the engine divided by the time to load for InnoDB from MySQL 5.6.35.

load load engine

secs ratio

7266 1.000 inno5635

7833 1.078 inno5717

8286 1.140 inno801

10516 1.447 inno802

7640 1.051 myrocks.none

7810 1.074 myrocks.zstd

7558 1.040 toku5717.none

7494 1.031 toku5717.none.prefetch

7726 1.063 toku5717.zlib

Create index performance has more diversity. The table is larger than RAM, some of it will be read from storage and engines with compression (MyRocks.zstd, toku5717.zlib) suffer from decompression latency. The prefetch option doesn't help TokuDB in this case. InnoDB create index performance got much faster starting in 5.7. The ratio is the create index time for the engine divided by the time for InnoDB from MySQL 5.6.35.

create create engine

secs ratio

3565 1.000 inno5635

1904 0.534 inno5717

1961 0.550 inno801

1966 0.551 inno802

3321 0.931 myrocks.none

3802 1.066 myrocks.zstd

9817 2.753 toku5717.none

9855 2.764 toku5717.none.prefetch

10731 3.010 toku5717.zlib

Charts

These charts have the same data as the tables above.

Small Datum

责编内容by:Small Datum阅读原文】。感谢您的支持!

您可能感兴趣的

MySQL 8’s Windowing Function Part 1 MySQL will have Windowing functions and CTEs which will mean it will be easier t...
Shinguz: MySQL spatial functionality – point... This week I was preparing the exercises for our MySQL/MariaDB for Beginners tra...
记录一次MySQL升级的运维实践 前言 MySQL5.7新增了不少新功能,比如:Online DDL、多源复制、增强半同步、表空间传输、sys 库、G...
Top Performance Metrics to Monitor on MySQL (Conne... As a DBA, your top priority is to keep your databases and dependent applications...
Quest Software Extends DevOps Reach to MySQL Datab... Thanks mainly to the rise of DevOps, the number of open-source database deployme...