Shallow measurement tokudb

A. Plan:

The TOKUDB document describes its high insert performance, high compression ratio, hot schema changes and other features that match the application scenario of the Log class project, the hearing is the end of the false, the measurement is necessary.

Currently TOKUDB only supports mysql5.5.x and mariadb5.5.x, which is much better than percona support, but it still needs to be downloaded separately for installation. This test content: MySQL5.6.22 (InnoDB) Vs. Tokudb for Percona5.6.22.

1. Hardware and Software Environment information:

Virtual machines	********
Cpu	CPUX5660 @ 2.80GHz
Number of logical CPUs	4
Memory	4G
System	Red Hat Enterprise Linux Server release 6.1 (Santiago)
Kernel	2.6.32-131.0.15.el6.x86_64
IO scheduling	None
File system	Ext4

2. Simple physical IO velocimetry under Linux:

[Email protected] ~]$ time Ddif=/dev/zero of=test.dbf bs=8k count=600000 oflag=direct600000+0 Records in600000+0 Records out4915200000 bytes (4.9 GB) copied, 381.327 s, 12.9 mb/s real 6m21.335suser 0m0.292ssys 0m12.690s

Two. DB installation

1. MySQL5.6.22 Environment Preparation

1). DB Config

Innodb_buffer_pool_size = 3000M

2). Installing MySQL (slightly)

[[email protected] ~]#/etc/init.d/mysqld start

2. Pecona5.6.22 and TOKUDB Environment preparation

1). Prerequisites

See Manual 9.2.1

Install Jemalloc:

[Email protected] ~]# wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2[[email protected] ~]# Tar xjf jemalloc-3.6.0.tar.bz2 [[email protected] ~]# CD Jemalloc-3.6.0[[email protected] ~]#./configure[[email protecte D] ~]# make && make Install[[email protected] ~]# echo '/usr/local/lib ' >/etc/ld.so.conf.d/local.conf[[email P Rotected] ~]# Ldconfig

2). DB Config

Tokudb_cache_size = 3gtokudb_directio = Ontokudb_row_format = Tokudb_fasttokudb_write_status_frequency = 1tokudb_read_ Status_frequency = 1

3). Installing Percona (Installation part slightly)

[[email protected] ~]#/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

[[email protected] ~]# 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]                       |    &nbsp.----.     | 2015-01-22 18:44:50 11824 [error]                      &NBSP, ....    |. ----.|  &NBSP, .... 2015-01-22 18:44:50 11824 [error]                       &NBSP, ....    '        '   2015-01-22 18:44:50 11824 [error]                        &NBSP, .... _______,.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 ' ... [[email protected] ~]# 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)

Three. Preparation of the Base test tool

3.1) Installing Sysbench

[[email protected] ~]# git clone https://github.com/zatrazz/sysbench.git[[email protected] ~]#./autogen.sh[[email Protected] ~]#/configure--prefix=/usr/local/sysbench--with-mysql-includes=/usr/local/mysql/include-- With-mysql-libs=/usr/local/mysql/lib[[email protected] ~]# make && make Install[[email protected] ~]$ export Ld_ Library_path=/usr/local/mysql/lib[[email protected] ~]$/usr/local/sysbench/bin/sysbench--help

3.2) Installing Gnuplot

[email protected] ~]# Yum install gnuplot

Four. Testing

4.1) INSERT ( innodb single table) test

A) test script

[[email protected] ~]$ /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[[email protected] ~]$  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) Drawing

[[email protected] ~]$ 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[[email protected] ~]$ 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): 2:xtic (1)  with linesp notitle[[email protected] ~]$ gnuplot  innodb_insert_mygraph

c) Illustration

4.2) INSERT ( tokudb single table) test

A) test script

[[email protected] ~]$ /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[[email protected] ~]$  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)   drawing

[[email protected] ~]$ 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): 2:xtic (1)  with linesp notitle 

C1) Figure 1 (parameter tokudb_directio =on)

C2) Figure 2 (parameter tokudb_directio = OFF)

5. Test Summary

1. The performance of the tokudb in the write operation (Insert) is better than the InnoDB, and the performance is better than InnoDB in the case of concurrency growth.

2. in this practical test, the peak value of InnoDB is about 11000 per Sec,tokudb peak of about 15000 per sec, and after more than 4, performance has varying degrees of decline.

3. in data compression, generate 20 million rows of data at the same time, the Tokudb table size of about 3.1G,INNODB table size is about 4.8G. What you need to explain here is that the compression option enabled by TOKUDB in the test is ' fast ', and InnoDB does not have compression enabled. InnoDB also has a compression function, except that the compression behavior is the default behavior of Tokudb. The INNODB compression format is not used in this test.

4. The impact of the test on the problem factor: 1. The impact of the IO control and contention of shared storage on the test process.

Shallow measurement tokudb