一. 计划：
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 ddif=/dev/zero of=test.dbf bs=8k count=600000 oflag=direct600000+0 records in600000+0 recordsout4915200000 bytes(4.9 GB) copied, 381.327 s, 12.9 MB/s real 6m21.335suser 0m0.292ssys 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 726Transparent huge pages are enabled, according to /sys/kernel/mm/redhat_transparent_hugepage/enabled2015-01-22 18:44:50 11824 [ERROR] TokuDB: Huge pages are enabled, disable them before continuing2015-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.localecho never > /sys/kernel/mm/redhat_transparent_hugepage/defragecho never > /sys/kernel/mm/redhat_transparent_hugepage/enabledmysql> 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 imageset terminal png # save file to "benchmark.png"set output "sysbench_test_insert_InnoDB_benchmark.png" # graph titleset title "Benchmark for Sysbench" # aspect ratio for image sizeset size 1,1 # enable grid on y and x axisset grid yset grid x # x-axis labelset xlabel "Threads" # y-axis labelset ylabel "read/write(Insert) per sec." # plot data from sysbench.csvplot "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 imageset terminal png # save file to "benchmark.png"set output "sysbench_test_insert_tokuDB_benchmark.png" # graph titleset title "Benchmark for Sysbench" # aspect ratio for image sizeset size 1,1 # enable grid on y and x axisset grid yset grid x # x-axis labelset xlabel "Threads" # y-axis labelset ylabel "read/write(Insert) per sec." # plot data from sysbench.csvplot "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控制与争用对测试过程的影响。