検証ツール（sysbench） - のんびりSE備忘録 @ ウィキ

• パッケージの入手

http://sourceforge.net/projects/sysbench/files/
よりsysbench-0.4.10.tar.gzを入手

• sysbechのインストール

# tar xvfz sysbench-0.4.10.tar.gz
# cd sysbench-0.4.10
# ./configure
# make
# make install

• RPMの準備

以下のRPMをインストールする

# rpm -qaa | grep MySQL
MySQL-client-advanced-gpl-5.1.43-1.rhel4.x86_64
MySQL-server-advanced-gpl-5.1.43-1.rhel4.x86_64
MySQL-devel-advanced-gpl-5.1.43-1.rhel4.x86_64

• データベース作成

# mysql -uroot -ppassword -e"create database sbtest;"

• テーブル作成

# /usr/local/bin/sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=password prepare
sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...

• 負荷掛け

# /usr/local/bin/sysbench --test=oltp --oltp-table-size=1000000 --num-threads=4 --max-requests=0 --max-time=180 --mysql-engine-trx=yes --oltp-test-mode=complex --mysql-user=root --mysql-password=password run
sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 3 times)
Running the test with following options:
Number of threads: 4

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
Threads started!
Time limit exceeded, exiting...
(last message repeated 3 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            236936
        write:                           84620
        other:                           33848
        total:                           355404
    transactions:                        16924  (93.99 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 321556 (1785.87 per sec.)
    other operations:                    33848  (187.99 per sec.)

Test execution summary:
    total time:                          180.0555s
    total number of events:              16924
    total time taken by event execution: 719.9606
    per-request statistics:
         min:                                  5.76ms
         avg:                                 42.54ms
         max:                                456.40ms
         approx.  95 percentile:              77.24ms

Threads fairness:
    events (avg/stddev):           4231.0000/9.03
    execution time (avg/stddev):   179.9901/0.02

• テーブル削除

# /usr/local/bin/sysbench --test=oltp --mysql-user=root --mysql-password=password cleanup
sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Dropping table 'sbtest'...
Done.

その他負荷掛け

• 特定SQLのみ負荷掛け（例：insertのみ）

/usr/local/bin/sysbench --test=oltp --oltp-table-size=1000000 --num-threads=4 --max-requests=0 --max-time=180 --mysql-engine-trx=yes --oltp-test-mode=nontrx --oltp-nontrx-mode=insert --mysql-user=root --mysql-password=password run

オプション --oltp-nontrx-mode= で{select, update_key, update_nokey, insert, delete}に5種類のSQLが対応可能

• マニュアル

http://sysbench.sourceforge.net/docs/#database_mode