TokuDB® vs. InnoDB HDDs

iiBench Testing

Tokutek created the iiBench benchmark in 2008. The point of the benchmark is to measure the performance of indexed insertions over time. It uses an extremely simple schema, one table with a sequential insertion pattern for the primary key along with three integer fields storing random values. The table maintains 3 secondary indexes, each including several of the random integer fields. The iiBench application itself is currently maintained on Launchpad. The source code for iiBench is on GitHub.

iiBench: Over 16x Faster Insertions

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.

iiBench: Over 16x Faster Insertions

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

Replication: Eliminate Slave Lag

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.

Replication: Eliminate Slave Lag

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

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.

Compression: Highest Compression

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

SysBench: Up to 70% Faster

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.

Replication: Eliminate Slave Lag

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

TPCC-like

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.

TPCC-Like

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

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.

FastLoader: Speed Up of 3.5x for TPC-C Table Loads

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

Hot Schema: Schema Changes in Seconds, not Hours

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.

Hot Schema: Schema Changes in Seconds, not Hours

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

Appendix – Software Configuration Details

I. iiBench

Each data point is the average insertion rate for the last 2 million rows. Learn more about the iiBench benchmark.

II. Replication

Learn more about the replication benchmark.

III. Compression

Learn more about the compression benchmarks.

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

Learn more about Tokutek’s open-source fast loader software.

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

Learn more about Hot Column Addition and Deletion (HCAD) and Hot Indexing.