読者です 読者をやめる 読者になる 読者になる

TokuDB/InnoDB カジュアルベンチマーク

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.

Installation

  • VagrantVirtualBox上に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を変更しただけであとはデフォルトのまま
    • thread_stack = 256K
    • innodb_flush_log_at_trx_commit = 1
    • innodb_buffer_pool_size = 5242880
    • read_buffer_size = 262144
    • tokudb_read_buf_size = 131072

結果

sysbench

f:id:nishidy:20150531214019p:plain f:id:nishidy:20150531214024p:plain f:id:nishidy:20150531222500p:plain

mysqlslap

  • read/read-write 3回ずつ測定 f:id:nishidy:20150602213038p:plain

コマンドライン

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)