sysbench1.0でMySQLベンチマークするメモ

sysbench1.0でMySQLのベンチマークとったのでその方法のメモ。 OSはCentOS7.3

MySQLをyumでインストールする

# wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# rpm -i mysql57-community-release-el7-11.noarch.rpm
# yum install -y mysql-server mysql
# systemctl start mysqld

パスワード確認

# cat /var/log/mysqld.log | grep "temporary pass"
2017-08-01T09:23:25.727487Z 1 [Note] A temporary password is generated for root@localhost:xxxxxxxx

sysbench1.0 インストール

参考

github.com

# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench

ベンチマークテスト

準備

• データベース作成

mysql> CREATE DATABASE sysbenchdb;

• prepare
  • –mysql-dry-run=ONで実際に実行せずに確認できる

# sysbench oltp_common \
    --mysql-dry-run=off \
    --db-driver=mysql \
    --mysql-user=root \
    --mysql-password=test \
    --mysql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    prepare

実行

–threadsでスレッド数。 –timeで時間指定（秒）。

# sysbench oltp_read_write \
    --mysql-dry-run=off \
    --db-driver=mysql \
    --mysql-user=root \
    --mysql-password=test \
    --mysql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    --threads=2 \
    --time=30 \
    run

sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 2
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            430766
        write:                           122987
        other:                           61509
        total:                           615262
    transactions:                        30740  (1024.40 per sec.)
    queries:                             615262 (20503.45 per sec.)
    ignored errors:                      29     (0.97 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0055s
    total number of events:              30740

Latency (ms):
         min:                                  1.72
         avg:                                  1.95
         max:                                 10.54
         95th percentile:                      2.14
         sum:                              59936.56

Threads fairness:
    events (avg/stddev):           15370.0000/160.00
    execution time (avg/stddev):   29.9683/0.00

oltp_read_writeのような[testname]は下のlua scripts

# ls /usr/share/sysbench/
bulk_insert.lua  oltp_insert.lua        oltp_read_write.lua        oltp_write_only.lua       tests
oltp_common.lua  oltp_point_select.lua  oltp_update_index.lua      select_random_points.lua
oltp_delete.lua  oltp_read_only.lua     oltp_update_non_index.lua  select_random_ranges.lua