TokuDB/InnoDB カジュアルベンチマーク
Fractal-Treeを採用しているTokuDBは書き込みに強く、また圧縮率が高いということなので使ってみた
unlike a B-tree, a Fractal Tree index has buffers at each node, which allow insertions, deletions and other changes to be stored in intermediate locations. The goal of the buffers is to schedule disk writes so that each write performs a large amount of useful work, thereby avoiding the worst-case performance of B-trees, in which each disk write may change a small amount of data on disk.
- TokuDBとInnoDBのsysbench(oltp)によるread/read-writeのベンチマークとテーブルサイズを確認
- TokuDBとInnoDBのmysqlslapによるread/read-writeのベンチマークを確認
Installation
- VagrantでVirtualBox上にCentOS6.5を構築してmysql-tokudbをインストール
- VMのデフォルトのディスクサイズが8GBなので、30GBくらい足しておく（結果的にほとんど使わなかった）
46 config.vm.provider "virtualbox" do |vb| 47 # # Display the VirtualBox GUI when booting the machine 48 # vb.gui = true 49 # 50 # # Customize the amount of memory on the VM: 51 vb.memory = "2048" 52 53 file_to_disk = "./tmp/disk.vdi" 54 unless File.exist?(file_to_disk) 55 vb.customize ['createhd', '--filename', file_to_disk, '--size', 30 * 1024] 56 vb.customize ['storageattach', :id, '--storagectl', 'SATA', '--port', 1, '--device', 0, '--type', 'hdd', '--medium', file_to_disk] 57 end 58 59 end
- TokuDB Documentation : Installation
- sudo service mysql startでエラーになったので
/var/lib/mysql/vagrant-centos65.vagrantup.com.errを確認 - TokuDBはHugepagesを無効にする必要があるが、
$ sudo echo never > /sys/kernel/mm/transparent_hugepage/enabledがPermission deniedになるので、"transparent_hugepage=never"をgrun.confのkernelパラメータに追加して再起動 - ディスクフォーマットはext4
- my.cnfはthread_stackとinnodb_buffer_pool_sizeを変更しただけであとはデフォルトのまま
結果
sysbench
mysqlslap
- read/read-write 3回ずつ測定
コマンドライン
sysbench - TokuDBベンチマーク
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=tokudb --mysql-engine-trx=yes --oltp-table-size=1000000 --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=tokudb --mysql-engine-trx=yes --oltp-table-size=1000000 --oltp-read-only=on --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 0
other: 20000
total: 160000
transactions: 10000 (891.58 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 140000 (12482.09 per sec.)
other operations: 20000 (1783.16 per sec.)
Test execution summary:
total time: 11.2161s
total number of events: 10000
total time taken by event execution: 11.1805
per-request statistics:
min: 0.63ms
avg: 1.12ms
max: 9.83ms
approx. 95 percentile: 1.52ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 11.1805/0.00
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=tokudb --mysql-engine-trx=yes --oltp-table-size=1000000 --oltp-read-only=off --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (245.33 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (4661.33 per sec.)
other operations: 20000 (490.67 per sec.)
Test execution summary:
total time: 40.7609s
total number of events: 10000
total time taken by event execution: 40.7048
per-request statistics:
min: 1.63ms
avg: 4.07ms
max: 113.00ms
approx. 95 percentile: 6.86ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 40.7048/0.00
[vagrant@vagrant-centos65 mysql]$ mysql -u root -p -D sbtest
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.41-tokudb-7.5.5 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> call show_table_size();
+------------+--------+----------+------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
+------------+--------+----------+------+-------+------+------+
| sbtest | TokuDB | 1000000 | 188 | 188 | 180 | 8 |
+------------+--------+----------+------+-------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
sysbench - InnoDBベンチマーク
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --oltp-read-only=on --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 0
other: 20000
total: 160000
transactions: 10000 (1083.73 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 140000 (15172.19 per sec.)
other operations: 20000 (2167.46 per sec.)
Test execution summary:
total time: 9.2274s
total number of events: 10000
total time taken by event execution: 9.1923
per-request statistics:
min: 0.39ms
avg: 0.92ms
max: 3.77ms
approx. 95 percentile: 1.34ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 9.1923/0.00
[vagrant@vagrant-centos65 mysql]$ sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --oltp-read-only=off --max-time=60 --mysql-user=root --mysql-password= --mysql-socket=/tmp/mysql.sock run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (404.58 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (7686.98 per sec.)
other operations: 20000 (809.16 per sec.)
Test execution summary:
total time: 24.7171s
total number of events: 10000
total time taken by event execution: 24.6690
per-request statistics:
min: 1.21ms
avg: 2.47ms
max: 50.98ms
approx. 95 percentile: 4.52ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 24.6690/0.00
[vagrant@vagrant-centos65 mysql]$ mysql -u root -p -D sbtest
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.41-tokudb-7.5.5 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> call show_table_size();
+------------+--------+----------+------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
+------------+--------+----------+------+-------+------+------+
| sbtest | InnoDB | 1000109 | 225 | 230 | 214 | 15 |
+------------+--------+----------+------+-------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysqlslap
[vagrant@vagrant-centos65 mysql]$ for wn in 1 5000;do for n in 1 2 3; do for db in tokudb innodb; do echo "$n $db $wn"; mysqlslap --no-defaults --concurrency=1 --iterations=1 --engine=$db --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --auto-generate-sql-write-number=$wn --number-of-queries=10000 --host=localhost --port=3306 --user=root;done ;done ;done
1 tokudb 1
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 3.721 seconds
Minimum number of seconds to run all queries: 3.721 seconds
Maximum number of seconds to run all queries: 3.721 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
1 innodb 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.699 seconds
Minimum number of seconds to run all queries: 2.699 seconds
Maximum number of seconds to run all queries: 2.699 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
2 tokudb 1
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 3.817 seconds
Minimum number of seconds to run all queries: 3.817 seconds
Maximum number of seconds to run all queries: 3.817 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
2 innodb 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.566 seconds
Minimum number of seconds to run all queries: 2.566 seconds
Maximum number of seconds to run all queries: 2.566 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
3 tokudb 1
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 3.826 seconds
Minimum number of seconds to run all queries: 3.826 seconds
Maximum number of seconds to run all queries: 3.826 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
3 innodb 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.641 seconds
Minimum number of seconds to run all queries: 2.641 seconds
Maximum number of seconds to run all queries: 2.641 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
1 tokudb 5000
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 4.540 seconds
Minimum number of seconds to run all queries: 4.540 seconds
Maximum number of seconds to run all queries: 4.540 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
1 innodb 5000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 4.216 seconds
Minimum number of seconds to run all queries: 4.216 seconds
Maximum number of seconds to run all queries: 4.216 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
2 tokudb 5000
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 7.318 seconds
Minimum number of seconds to run all queries: 7.318 seconds
Maximum number of seconds to run all queries: 7.318 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
2 innodb 5000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 3.681 seconds
Minimum number of seconds to run all queries: 3.681 seconds
Maximum number of seconds to run all queries: 3.681 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
3 tokudb 5000
Benchmark
Running for engine tokudb
Average number of seconds to run all queries: 4.678 seconds
Minimum number of seconds to run all queries: 4.678 seconds
Maximum number of seconds to run all queries: 4.678 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
3 innodb 5000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 3.977 seconds
Minimum number of seconds to run all queries: 3.977 seconds
Maximum number of seconds to run all queries: 3.977 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
- ストアドプロシージャの実体
mysql> show create procedure show_table_size\G
*************************** 1. row ***************************
Procedure: show_table_size
sql_mode: NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `show_table_size`()
begin
select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;
end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
