iiBench results
This page contains results comparing WiredTiger to InnoDB using the Indexed Insertion benchmark developed by Tokutek.
The benchmark uses a WiredTiger storage engine plugin for MySQL.
The Indexed Insertion Benchmark (iiBench) uses a single table with three secondary indexes defined. The benchmark uses five threads:
- One thread doing inserts into the primary table.
- Four threads executing queries: one thread for the primary table and one thread per secondary index.
- Each query retrieves 1000 entries (i.e is of the form
SELECT X FROM TABLE_OR_INDEX WHERE X LIMIT 1000).
WiredTiger maintained over 2.4x the insert rate of InnoDB and 4x the query rate of InnoDB.
| Engine | Total Run Time (seconds) | Average Inserts Per Second | Average Queries Per Second |
|---|---|---|---|
| WiredTiger | 16,463 | 30,370 | 2073 |
| InnoDB | 40,048 | 12,484 | 472 |
Following are graphs comparing the insert and query throughput for WiredTiger and InnoDB.
Doing a cost comparison between WiredTiger and InnoDB is possible for the benchmark that was run on an AWS server. The instance type used to run the benchmark is a c3.8xlarge which has 32 modern vCPUs, 60GB memory and 2 320GB SSD drives. The current cost of a c3.4xlarge instance is $1.680 per Hour.
| Metric | WiredTiger | InnoDB |
|---|---|---|
| Benchmark run cost | $6.44 | $12.88 |
| Cost per billion inserts* | $20.30 | $40.60 |
- This is the cost while executing an insert/read workload with an insert/read ratio of 70/1. That is one query for every 70 inserts - each query retrieving 1000 rows from the database.
WiredTiger maintained over 3x the insert rate of InnoDB and 3x the query rate of InnoDB.
| Engine | Total Run Time (seconds) | Average Inserts Per Second | Average Queries Per Second |
|---|---|---|---|
| WiredTiger | 13,812 | 36,200 | 535 |
| InnoDB | 43,492 | 11,496 | 163 |
Following are graphs comparing the insert and query throughput for WiredTiger and InnoDB.
The raw data that was used to produce the above graphs can be downloaded here.
This benchmark is designed to test out-of-cache performance for both WiredTiger and InnoDB. The cache size used is 10GB. The data size is much larger than the configured cache size - around 80GB. The insert rate graph above shows the point at which the InnoDB cache becomes full - after approximately 100 million inserts - and the insert rate decreases significantly.
The WiredTiger query rate tends to decrease for a period, and then jump back up again. This is due to the Log Structured Merge (LSM) tree data structure being used to store the table indexes. LSM trees maintain a set of read only sub-files and a single file being updated at any time. The read only files are periodically merged together in the background - queries become much more efficient once a merge has completed.
- c3.8xlarge instance type
- 32 cores Intel Xeon E5-2670 v2
- 60 GB RAM
- 2x 320GB SSD drives, configured in striped RAID with XFS.
- 4 x Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (24 cores)
- 140 GB RAM
- 400GB Intel S3700 SSD
MySQL compiled from source. Version 5.7.4-m14. Configured with release optimizations.
WiredTiger Develop branch at: 2bc0cbc86217b50dbd6b9337fb01f26903ae93dd We re-ran the benchmark using the WiredTiger 2.3.1 release and generated equivalent results.
Configured with ./configure --enable-zlib
A prototype MySQL storage engine implementation for WiredTiger was used. If you are interested in reproducing these results, contact us at info@wiredtiger.com and we'll be happy to share the source code.
WiredTiger uses:
- Logging enabled
- transaction_sync set to dsync
- Checkpoints every 100 seconds
- A btree for the primary index
- LSM trees for each of the secondary indexes
- 512 byte allocation_size
- 50MB memory_page_max
- zlib compression for the primary index
- 32kb leaf_page_max for primary index
- 8kb leaf_page_max for secondary indexes
- 24 bloom bits and 12 bloom hashes for secondary indexes
- LSM chunk size of 50MB for secondary indexes
- 3 merge threads for LSM trees
WiredTiger and InnoDB shared the same MySQL configuration file as included below.
In order to reproduce run:
$ echo "CREATE DATABASE iibench;" | ./bin/mysql --port=4242 -u root -S tmpdata/mysql.sock
$ python ./iibench.py --db_name=iibench --db_user=root --db_socket=/home/alexg/work/mysql/mysql-5.6.14/mysql-install/tmpdata/mysql.sock --engine=WIREDTIGER --setup
$ python ./iibench.py --db_name=iibench --db_user=root --db_socket=/home/alexg/work/mysql/mysql-5.6.14/mysql-install/tmpdata/mysql.sock --engine=WIREDTIGER --max_rows=500000000
Using a clean MySQL database. Varying --engine= between WIREDTIGER and INNODB.
The server is started with the following configuration file for both WiredTiger and InnoDB:
max_connections=4000
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16
# files
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group = 3
innodb_open_files=4000
# buffers
innodb_buffer_pool_size=5000M
innodb_buffer_pool_instances=32
# I get: unknown variable 'innodb_additional_mem_pool_size=20M'
# innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=32K
# innodb
innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent = 1
# This setting was:
# innodb_spin_wait_delay= 6/96
innodb_spin_wait_delay= 96
# wiredtiger
wiredtiger_connection_config=cache_size=5000MB,mmap=false
wiredtiger_table_config=prefix_compression=false
# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0
# monitoring
innodb_monitor_enable = '%'
performance_schema=OFF