TokuBD 7.5.6 Insert Performance

MySQL & MariaDB Other MySQL® Questions
1

Hi

  1. Is this TokuDB insert performance normal or slow?
  2. It it possible to increase TokuDB insert performance?

Thank you



num-threads=4, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1
* MyISAM 16684 per sec
* InnoDB 3379 per sec
* TokuDB 1763 per sec


num-threads=4, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2
* MyISAM 15485 per sec
* InnoDB 22085 per sec
* TokuDB 14956 per sec

---
num-threads=2, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1
* MyISAM 11848 per sec
* InnoDB 2695 per sec
* TokuDB 1251 per sec


num-threads=2, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2
* MyISAM 14377 per sec
* InnoDB 12283 per sec
* TokuDB 9079 per sec

---
num-threads=1, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1
* MyISAM 6181 per sec
* InnoDB 1618 per sec
* TokuDB 922 per sec


num-threads=1, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2
* MyISAM 6507 per sec
* InnoDB 6518 per sec
* TokuDB 5598 per sec


-----------------------------------------------------------------
CPU: AMD A8-5600K (4 core)
RAM: 8 Gb
HDD: SATA 7200 rpm

CentOS 7 x86_64
Percona 5.6.23-rel72.1.el7.x86_64 (tokudb 7.5.6)
Sysbench 0.5 (snapshot 20150126)


-----------------------------------------------------------------
my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


key_buffer_size = 2048M

tokudb_cache_size = 2048M
#tokudb_commit_sync = OFF

innodb_buffer_pool_size = 2048M
#innodb_flush_log_at_trx_commit = 2


[mysqld_safe]
thp-setting=never
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


-----------------------------------------------------------------
sysbench.sh:

#!/bin/bash

function bench {
engine=$1
shift

sysbench \
--test=/usr/share/doc/sysbench/tests/db/insert.lua \
\
--db-driver=mysql \
--mysql-password= \
--mysql-host=localhost \
--mysql-table-engine=$engine \
--mysql-user=root \
\
--num-threads=4 \
--oltp-table-size=1000000 \
$*
}


function run {
echo "======================================================="
echo " $1"
echo "======================================================="

mysql -u root --password= -e "drop database sbtest" 2>/dev/null
mysql -u root --password= -e "create database sbtest" 2>/dev/null
bench $1 prepare > /dev/null

bench $1 --max-time=60 --max-requests=0 run | grep 'requests:'
}


run 'MyISAM'
run 'InnoDB'
run 'TokuDB'
2

I think TokuDB shines when your dataset doesn’t fit in RAM. InnoDB will easily outperform it otherwise.

Also, TokuDB can do upserts: [url]http://www.tokutek.com/2013/02/fast-updates-with-tokudb/[/url]