TokuDB vs. InnoDB in a Timeseries INSERT Benchmark

this post comes from vadim tkachenko at the mysql performance blog.

this post is a continuation of my research into tokudb’s storage engine to understand if it is suitable for timeseries workloads.

while inserting load data infile into an empty table shows great results for tokudb, it would be more interesting to see some realistic workloads.

so this time let’s take a look at the insert benchmark.

what i am going to do is insert data in 16 parallel threads into the table from the previous post:

create table `sensordata` (
  `ts` int(10) unsigned not null default '0',
  `sensor_id` int(10) unsigned not null,
  `data1` double not null,
  `data2` double not null,
  `data3` double not null,
  `data4` double not null,
  `data5` double not null,
  `cnt` int(10) unsigned not null,
  primary key (`sensor_id`,`ts`)
)

the inserts are bulk inserts with sequentially increasing ts and with sensor_id from 1 to 1000 .

while the inserts are not fully sequential, because the primary key is (sensor_id, ts) , it is enough to have in memory workload, so i do not expect performance degradation when data exceeds memory. this will play in favor for innodb, as it is known that tokudb performs worse in cpu-bound benchmarks.

the benchmark executes one million events, each event inserts 1,000 records in bulk. when it's finished we should have about one billion records in the table.

so let’s see how innodb (compressed 8k vs. not compressed) performs.
throughput (more is better):

response time (log 10 scale on the y axis) (less is better):

so innodb comes with the following numbers:

• innodb with no compression averages at 350 inserts per second with a response time of 80-100 ms per transaction. the final table size is 82 gb
• innodb with 8k compression has a throughput of 130 inserts per second with a response time 250 ms . the final table size is 60 gb

now, we have a pretty bad compression rate because i used uniform distribution for values of data1-data5 columns, and uniform may not be good for compression. and, actually, in the real case i expect much more repeating values, so i am going to re-test with pareto (zipfian) distribution.

for tokudb (tested tokudb_fast and tokudb_small formats):

throughput (more is better):

response time (log 10 scale on the y axis) (less is better):

tokudb observations:

• after an initial warmup, tokudb shows quite inconsistent performance with both tokudb_fast and tokudb_small formats.
• for tokudb_fast, the throughput is topping at ~ 150 inserts per second , and 95% response time at ~160 ms . however, there are periodical stalls when throughput drops almost to zero and response times jump to 10 seconds (!!!) per transaction.
• for tokudb_small, the throughput is even less stable jumping around 100 inserts per second and response times start at 300 ms per transaction, with stalls up to 30 seconds per transaction

file sizes for tokudb:

• tokudb_fast: 50 gb
  
• tokudb_small: 45 gb .
  

again, i correspond a bad compression rate to uniform distribution. if we switch to pareto, the file size for tokudb_fast is 21 gb , and tokudb_small is 13 gb

if we zoom in to a 900 second time frame we can see the periodic behavior of tokudb:

now, i consider these stalls in tokudb as severe and i do not think i can recommend using it in production under such workload conditions until the problem is fixed.

you can find the scripts for the timeseries benchmark for sysbench v0.5 here .

software versions for innodb: percona server 5.6-rc3, for tokudb: mariadb-5.5.30-tokudb-7.0.4

update (september 5, 2013) :
by many requests i'm updating the post with following information:
tokudb throughput (tokudb_small row format) with pareto distribution, for two cases:

• 1. primary key ( sensor_id , ts ) (on graph: tokudb_small )
• 2. primary key ( ts , sensor_id ), key ( sensor_id , ts ) (on graph: tokudb_small_key )

throughput in this case:

we can see that the top throughput for tokudb_small_key is less than for tokudb_small , but there is also less variance in throughput.

the my.cnf files

for innodb:

[mysqld]
# gdb
log-error=error.log
innodb_file_per_table = true
innodb_data_file_path = ibdata1:100m:autoextend
innodb_flush_method = o_direct
innodb_log_buffer_size = 256m
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 40g
innodb_buffer_pool_instances=1
innodb_file_format = barracuda
innodb_checksum_algorithm = crc32
innodb_log_file_size = 4g
innodb_log_files_in_group = 2
#innodb_log_block_size=4096
#####plugin options
innodb_read_io_threads = 16
innodb_write_io_threads = 4
innodb_io_capacity = 4000
#not innodb options (fixed)
port = 3306
back_log = 50
max_connections = 2000
max_prepared_stmt_count=500000
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16m
binlog_cache_size = 16m
max_heap_table_size = 64m
sort_buffer_size = 4m
join_buffer_size = 4m
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
thread_stack = 192k
tmp_table_size = 64m
server-id = 10
key_buffer_size = 8m
read_buffer_size = 1m
read_rnd_buffer_size = 4m
bulk_insert_buffer_size = 8m
myisam_sort_buffer_size = 8m
myisam_max_sort_file_size = 10g
myisam_repair_threads = 1
myisam_recover
socket=/var/lib/mysql/mysql.sock
user=root

for tokudb (pretty much default):

[mysqld]
gdb
skip-innodb
#not innodb options (fixed)
port = 3306
back_log = 50
max_connections = 2000
max_prepared_stmt_count=500000
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16m
binlog_cache_size = 16m
max_heap_table_size = 64m
sort_buffer_size = 4m
join_buffer_size = 4m
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
ft_min_word_len = 4
#default_table_type = innodb
thread_stack = 192k
tmp_table_size = 64m
server-id = 10
key_buffer_size = 8m
read_buffer_size = 1m
read_rnd_buffer_size = 4m
bulk_insert_buffer_size = 8m
myisam_sort_buffer_size = 8m
myisam_max_sort_file_size = 10g
#myisam_max_extra_sort_file_size = 10g
myisam_repair_threads = 1
myisam_recover
socket=/var/lib/mysql/mysql.sock
user=root