Benchmarks vs. InnoDB

Links to the various benchmarks can be found here:

Additional details on the software settings for each test can also be found in the Appendix at the end of this page.


iiBench — Over 16x Faster Insertions back to top

iiBench is a popular open-source benchmark developed by Tokutek. It measures how fast a storage engine can insert rows while maintaining secondary indexes. This is often a critical performance measurement since maintaining the right indexes will dramatically improve query performance. The schema consists of short rows that model a retail point-of-sale transaction system. The results below show the insertion of 1 billion rows into a table while maintaining three multicolumn secondary indexes. At the end of the test, TokuDB’s insertion rate remained at 17,028 inserts/second whereas InnoDB had dropped to 1,050 inserts/second. That’s a difference of over 16x.

Platform: Ubuntu 10.10; 2x Xeon X5460; 16GB RAM; 8x 146GB 10k SAS in RAID10.

Replication — Eliminate Slave Lag back to top

MySQL’s single threaded design often leads to slave lag. With TokuDB, slave lag is eliminated. This insures replication can be used for read scaling, backups, and disaster recovery, without resorting to sharding, expensive hardware, or placing limits on what can be replicated.The graph below shows the slave trying to keep up with new orders in a TPCC-like environment. At 1,000 TPS there is no lag for InnoDB or TokuDB. Beyond that, MySQL with InnoDB begins to fall behind.

Platform: Master – Centos 5.6; 2x Xeon L5520; 72GB RAM; 8x 300GB 10k SAS in RAID10. Slave – Centos 5.7; 2x Xeon E5310; 8GB RAM; 6x 1TB SATA with 2 in RAID1 and 4 in RAID0.

Compression – Highest Compression back to top

Compression is an always-on feature of TokuDB. We tested InnoDB compression with two values of key_block_size (4k and 8k) and with compression disabled. To find the maximum compression, we loaded some web application performance data (log style data with stored procedure names, database instance names, begin and ending execution timestamps, duration row counts, and parameter values). TokuDB achieved 29x compression, far more than InnoDB.

Platform: Ubuntu 11.04; Intel Corei7/920 @ 3.6Ghz; 12GB RAM; 2x 7.2k SATA.

SysBench — Up to 70% Faster   back to top

This is a SysBench comparison of InnoDB 1.1.8  and TokuDB v6.0. Prior to the run we started the database from a cold back-up (the cache is empty at the beginning of the 1 client thread run) and ran for 1 hour at each number of client threads. The following graph shows a significant performance improvement at all levels of concurrency. The values shown are the average transactions per second for the final 15 minutes of the benchmark.

Platform: CentOS 5.7; 2x Xeon L5520; 72GB RAM; 8x 300GB 10k SAS in RAID10.

TPCC-like  back to top

This is a comparison of InnoDB 1.1.8 and TokuDB v5.2 on a 5000 warehouse database. The horizontal axis shows the number of clients and the vertical axis shows throughput (New Order Transactions/10 seconds). TokuDB has improved with the release of TokuDB v5.2 to be on parity to InnoDB 1.1.8 for this benchmark.

Platform: CentOS 5.7; 2x Xeon L5520; 72GB RAM; 8x 300GB 10k SAS in RAID10.

FastLoader – Speed Up of 3.5x for TPC-C Table Loads back to top

Percona’s TPC-C for MySQL toolset allows one to measure the query performance for an OLTP workload on various MySQL storage engines. The toolset includes a program to load the database tables, and a program to run queries and measure performance. We have found Percona’s TPC-C toolset to be extremely useful for tuning our software. Since Percona’s stock TPC-C toolset doesn’t know how to take advantage of TokuDB’s bulk load capability, we modified it so it could and benchmarked the results. Our experiments to show this used the latest version of TokuDB release 5.2. We observed a 3x increase in speed over InnoDB when loading the 5000 warehouse TPC-C database into TokuDB using the bulk loader.

Platform: CentOS 5.7; 2x Xeon L5520; 72GB RAM; 8x 300GB 10k SAS in RAID10.

Hot Schema – Schema Changes in Seconds, not Hours back to top

TokuDB v5.0 introduced Hot Column Addition (HCAD). You can add or delete columns from an existing table with minimal downtime — just the time for MySQL itself to close and reopen the table. The total downtime is seconds to minutes. We detailed an experiment that showed this in this blog. TokuDB v5.0 also introduced Hot Indexing. You can add an index to an existing table with minimal downtime. The total downtime is seconds to a few minutes, because when the index is finished being built, MySQL closes and reopens the table. This means that the downtime occurs not when the command is issued, but later on. Still, it is quite minimal, as we showed in this blog.

Platform: CentOS 5.5; 2x Xeon E5310; 4GB RAM; 4x 1TB 7.2k SATA in RAID0.

Appendix – Software Configuration Details  back to top

I. iiBench

Each data point is the average insertion rate for the last 2 million rows. Details on the iiBench benchmark can be found here.

II. Replication

Details on the replication benchmark can be found here.

III. Compression

Details on the compression benchmarks can be found here.

IV. Sysbench 

TokuDB MySQL Config File (TokuDB v5.2 on MySQL 5.1.52)
[mysqld]
max_connections=400
table_open_cache=2048

InnoDB MySQL Config File (InnoDB v1.1.8 on MySQL 5.5.16)
[mysqld]
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_log_file_size=1900M
innodb_log_files_in_group=2
innodb_file_per_table=true
innodb_log_buffer_size=16M
innodb_file_format=barracuda
innodb_buffer_pool_size=52G
innodb_flush_log_at_trx_commit=1
max_connections=400
table_open_cache=2048

Benchmark Command Line
sysbench --test sysbench-0.5/sysbench/tests/db/oltp.lua
--oltp_tables_count 16  --oltp-table-size 50000000 --rand-init on
--num-threads ${num_threads} --oltp-read-only off
--report-interval 10 --rand-type uniform --mysql-socket
/tmp/mysql.sock --mysql-table-engine tokudb --max-time 3600
--mysql-user root --mysql-password --mysql-db sbtest
--max-requests 0 --percentile 99 run

V. TPCC-like 

TokuDB MySQL Config File (TokuDB v5.2 on MySQL 5.1.52)
[mysqld]
max_connections=400
table_open_cache=2048

InnoDB MySQL Config File (InnoDB v1.1.8 on MySQL 5.5.16)
[mysqld]
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_log_file_size=1900M
innodb_log_files_in_group=2
innodb_file_per_table=true
innodb_log_buffer_size=16M
innodb_file_format=barracuda
innodb_buffer_pool_size=52G
innodb_flush_log_at_trx_commit=1
max_connections=400
table_open_cache=2048

Benchmark Command Line
tpcc_start $SERVER_NAME $DATABASE_NAME $MYSQL_USER 
"$MYSQL_PASSWORD" 5000 $num_threads 10 3600

VI. Fast Loader 

Tokutek’s fast loader software is open source and can be found here.

TokuDB MySQL Config File (TokuDB v5.2 on MySQL 5.1.52)
[mysqld]
max_connections=400
table_open_cache=2048

InnoDB MySQL Config File (InnoDB v1.1.8 on MySQL 5.5.16)
[mysqld]
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_log_file_size=1900M
innodb_log_files_in_group=2
innodb_file_per_table=true
innodb_log_buffer_size=16M
innodb_file_format=barracuda
innodb_buffer_pool_size=52G
innodb_flush_log_at_trx_commit=1
max_connections=400
table_open_cache=2048

VII. Hot Schema 

Hot Column Addition and Deletion (HCAD) details can be found here. Hot Indexing details can be found here.