一. 计划：
TokuDB文档描述其具备的高insert性能、高压缩比、hot schema changes等特性较符合日志类项目的应用场景，耳听眼见终是虚，实测是必须的。
目前TokuDB仅支持MySQL5.5.x与MariaDB5.5.x，相比较之下Percona的支持要好很多，但仍然需要分开下载进行安装。此次的测试内容：MySQL5.6.22(innoDB) Vs. TokuDB for Percona5.6.22。
1. 软硬件环境信息：
| 虚拟机 |
******** |
CPU
|
CPUX5660 @ 2.80GHz |
| 逻辑CPU个数 |
4
|
| 内存 |
4G
|
| 系统 |
Red Hat Enterprise Linux Server release 6.1 (Santiago) |
| 内核 |
2.6.32-131.0.15.el6.x86_64 |
| IO调度 |
none |
| 文件系统 |
ext4 |
2. Linux下简单的物理IO测速：
[ming.liu@mydbprod52 ~]$ time dd
if=/dev/zero of=test.dbf bs=8k count=600000 oflag=direct
600000+0 records in
600000+0 records
out
4915200000 bytes
(4.9 GB) copied, 381.327 s, 12.9 MB/s
real 6m21.335s
user 0m0.292s
sys 0m12.690s
二. DB安装
1. MySQL5.6.22环境准备
1). DB Config
innodb_buffer_pool_size = 3000M
2). Installing MySQL（略）
[root@mydbprod52 ~]# /etc/init.d/mysqld start
2. Pecona5.6.22 and TokuDB环境准备
1). Prerequisites
详见手册9.2.1
安装Jemalloc：
[root@mydbprod52 ~]# wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2
[root@mydbprod52 ~]# tar xjf jemalloc-3.6.0.tar.bz2
[root@mydbprod52 ~]# cd jemalloc-3.6.0
[root@mydbprod52 ~]# ./configure
[root@mydbprod52 ~]# make && make install
[root@mydbprod52 ~]# echo ‘/usr/local/lib‘ > /etc/ld.so.conf.d/local.conf
[root@mydbprod52 ~]# ldconfig
2). DB Config
tokudb_cache_size = 3G
tokudb_directio = ON
tokudb_row_format = tokudb_fast
tokudb_write_status_frequency = 1
tokudb_read_status_frequency = 1
3). Installing Percona（安装部分略）
[root@mydbprod52 ~]# /etc/init.d/perconad start
4). Installing TokuDB Plugin
mysql> INSTALL PLUGIN TokuDB SONAME ‘ha_tokudb.so‘;
ERROR 1123 (HY000): Can‘t initialize function ‘TokuDB‘; Plugin initialization function failed.
4.1). troubleshooting
[root@mydbprod52 ~]# vi data/wifimydb01prod.err
Version: ‘5.6.22-71.0‘ socket: ‘/tmp/mysql.sock‘ port: 3306 Percona Server (GPL), Release 71.0, Revision 726
Transparent huge pages are enabled, according to /sys/kernel/mm/redhat_transparent_hugepage/enabled
2015-01-22 18:44:50 11824 [ERROR] TokuDB: Huge pages are enabled, disable them before continuing
2015-01-22 18:44:50 11824 [ERROR] ************************************************************
2015-01-22 18:44:50 11824 [ERROR]
2015-01-22 18:44:50 11824 [ERROR] @@@@@@@@@@@
2015-01-22 18:44:50 11824 [ERROR] @@‘ ‘@@
2015-01-22 18:44:50 11824 [ERROR] @@ _ _ @@
2015-01-22 18:44:50 11824 [ERROR] | (.) (.) |
2015-01-22 18:44:50 11824 [ERROR] | ` |
2015-01-22 18:44:50 11824 [ERROR] | > ‘ |
2015-01-22 18:44:50 11824 [ERROR] | .----. |
2015-01-22 18:44:50 11824 [ERROR] .. |.----.| ..
2015-01-22 18:44:50 11824 [ERROR] .. ‘ ‘ ..
2015-01-22 18:44:50 11824 [ERROR] .._______,.
2015-01-22 18:44:50 11824 [ERROR]
2015-01-22 18:44:50 11824 [ERROR] TokuDB will not run with transparent huge pages enabled.
2015-01-22 18:44:50 11824 [ERROR] Please disable them to continue.
2015-01-22 18:44:50 11824 [ERROR] (echo never > /sys/kernel/mm/transparent_hugepage/enabled)
2015-01-22 18:44:50 11824 [ERROR]
2015-01-22 18:44:50 11824 [ERROR] ************************************************************
2015-01-22 18:44:50 11824 [ERROR] Plugin ‘TokuDB‘ init function returned error.
2015-01-22 18:44:50 11824 [ERROR] Plugin ‘TokuDB‘ registration as a STORAGE ENGINE failed.
2015-01-22 18:44:50 11824 [Note] Shutting down plugin ‘TokuDB‘
......
[root@mydbprod52 ~]# vi /etc/rc.local
echo never > /sys/kernel/mm/redhat_transparent_hugepage/defrag
echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
mysql> INSTALL PLUGIN TokuDB SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.12 sec)
mysql> INSTALL PLUGIN tokudb_file_map SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN tokudb_fractal_tree_info SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN tokudb_fractal_tree_block_map SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN tokudb_trx SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN tokudb_locks SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN tokudb_lock_waits SONAME ‘ha_tokudb.so‘;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL default_storage_engine=TokuDB;
Query OK, 0 rows affected (0.00 sec)
三. 基测工具准备
3.1) Installing Sysbench
[root@mydbprod52 ~]# git clone https://github.com/zatrazz/sysbench.git
[root@mydbprod52 ~]# ./autogen.sh
[root@mydbprod52 ~]# ./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[root@mydbprod52 ~]# make && make install
[ming.liu@mydbprod52 ~]$ export LD_LIBRARY_PATH=/usr/local/mysql/lib
[ming.liu@mydbprod52 ~]$ /usr/local/sysbench/bin/sysbench --help
3.2) Installing Gnuplot
[root@mydbprod52 ~]# yum install gnuplot
四. 测试
4.1) INSERT( innodb单表)测试
a) 测试脚本
[ming.liu@mydbprod52 ~]$ /usr/local/sysbench/bin/sysbench --test=/home/ming.liu/sysbench/sysbench/tests/db/insert.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=ming --mysql-password=ming --mysql-db=sbtest4 --num-threads=1 --oltp-tables-count=1 --mysql-table-engine=innodb --oltp-table-size=100000000 prepare
[ming.liu@mydbprod52 ~]$ time for i in 1 2 4 8 16 32 64 128; do /usr/local/sysbench/bin/sysbench --test=/home/ming.liu/sysbench/sysbench/tests/db/insert.lua --mysql-table-engine=innodb --mysql-host=localhost --mysql-port=3306 --mysql-user=ming --mysql-password=ming --mysql-db=sbtest4 --oltp_tables_count=1 --oltp-table-size=100000000 --rand-init=on --num-threads=${i} --oltp-read-Only=off --rand-type=uniform --max-time=360 --max-requests=0 --percentile=99 run >> sysbench_test_insert_InnoDB.log ; done
b) 绘图
[ming.liu@mydbprod52 ~]$ cat sysbench_test_insert_innodb.log | egrep " cat|threads:|read/write requests:" | tr -d "\n" | sed ‘s/Number of threads: /\n/g‘ | sed ‘s/\[/\n/g‘ | sed ‘s/[A-Za-z\/]\{1,\}://g‘| sed ‘s/ \.//g‘ | awk ‘{print $1 $4}‘ | sed ‘s/(/\t/g‘ > sysbench_test_insert_innoDB.csv
[ming.liu@mydbprod52 ~]$ vi innodb_insert_mygraph
# output as png image
set terminal png
# save file to "benchmark.png"
set output "sysbench_test_insert_InnoDB_benchmark.png"
# graph title
set title "Benchmark for Sysbench"
# aspect ratio for image size
set size 1,1
# enable grid on y and x axis
set grid y
set grid x
# x-axis label
set xlabel "Threads"
# y-axis label
set ylabel "read/write(Insert) per sec."
# plot data from sysbench.csv
plot "sysbench_test_insert_innoDB.csv" using (log($1)):2:xtic(1) with linesp notitle
[ming.liu@mydbprod52 ~]$ gnuplot innodb_insert_mygraph
c) 图示
4.2) INSERT( tokudb单表)测试
a) 测试脚本
[ming.liu@mydbprod52 ~]$ /usr/local/sysbench/bin/sysbench --test=/home/ming.liu/sysbench/sysbench/tests/db/insert.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=ming --mysql-password=ming --mysql-db=sbtest2 --num-threads=1 --oltp-tables-count=1 --mysql-table-engine=tokudb --oltp-table-size=100000000 prepare
[ming.liu@mydbprod52 ~]$ time for i in 1 2 4 8 16 32 64 128; do /usr/local/sysbench/bin/sysbench --test=/home/ming.liu/sysbench/sysbench/tests/db/insert.lua --mysql-table-engine=tokudb --mysql-host=localhost --mysql-port=3306 --mysql-user=ming --mysql-password=ming --mysql-db=sbtest2 --oltp_tables_count=1 --oltp-table-size=100000000 --rand-init=on --num-threads=${i} --oltp-read-Only=off --rand-type=uniform --max-time=360 --max-requests=0 --percentile=99 run >> sysbench_test_insert_tokudb.log ; done
b) 绘图
[ming.liu@mydbprod52 ~]$ vi tokudb_insert_mygraph
# output as png image
set terminal png
# save file to "benchmark.png"
set output "sysbench_test_insert_tokuDB_benchmark.png"
# graph title
set title "Benchmark for Sysbench"
# aspect ratio for image size
set size 1,1
# enable grid on y and x axis
set grid y
set grid x
# x-axis label
set xlabel "Threads"
# y-axis label
set ylabel "read/write(Insert) per sec."
# plot data from sysbench.csv
plot "sysbench_test_insert_tokuDB.csv" using (log($1)):2:xtic(1) with linesp notitle
c1) 图示1（参数 tokudb_directio = ON）
c2) 图示2（参数 tokudb_directio = OFF）
5. 测试小结
1. TokuDB在写操作（Insert）的表现优于InnoDB，在并发增长的情况下表现亦优于InnoDB。
2. 在此次实际测试中，InnoDB峰值约为11000 per sec，TokuDB峰值约为15000 per sec，并发超过4以后，性能均有不同程度的下降。
3. 在数据压缩方面，同时生成2千万行数据，TokuDB表大小约为3.1G，InnoDB表大小约为4.8G。这里需说明的是测试中TokuDB所启用的压缩选项为’fast’，而InnoDB未启用压缩。InnoDB也具有压缩功能，只不过压缩行为是TokuDB的默认行为。在本文测试中并未使用InnoDB的压缩格式。
4. 对测试造成的影响的问题因素：1. 共享存储的IO控制与争用对测试过程的影响。
浅测TokuDB
京公网安备 11010802041100号