This post compares MyRocks, InnoDB and TokuDB on the time required to load and index a table for sysbench.
- 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
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
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
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
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
These charts have the same data as the tables above.