====== 50 MySQLベンチマーク(sysbench) ======

[[01_linux:11_データベース:01_mysql:51_mysqlslap|mysqlslap]]はMySQL 5.1.4以降からなので、それ以前のバージョンではsysbenchでベンチマークを行う。

こちらが新しいバージョン->[[01_linux:11_データベース:01_mysql:52_sysbench_1.0]]

===== インストール =====
<code>
# yum -y install sysbench 
</code>

==== ベンチ用DB、ユーザ作成 ====
<code>
mysql> create database sbtest charset=utf8;
Query OK, 1 row affected (0.03 sec)
mysql> GRANT ALL ON sbtest.* TO 'sbtest'@'localhost' IDENTIFIED BY '[PASSWORD]';
Query OK, 0 rows affected (0.02 sec)
</code>

==== 初期設定 ====

<code>
sysbench \
--test=oltp \
--db-driver=mysql \
--oltp-table-size=10000 \
--mysql-password=kumolabo \
prepare 
</code>

==== ベンチマーク実行 ====

<code>
sysbench \
--test=oltp \
--db-driver=mysql \
--oltp-table-size=10000 \
--mysql-password=kumolabo \
--num-threads=1 \
--max-requests=0 \
--max-time=60 \
--oltp-read-only=off \
run 
</code>

==== 結果 ====

下記のように色々出力されるが、下記部分を参考にすれば良いと思う。
    transactions:                        11273  (187.88 per sec.)
※187.88/sec トランザクションを実行できた。

<code>
sysbench 0.4.10:  multi-threaded system evaluation benchmark

WARNING: Preparing of "BEGIN" is unsupported, using emulation
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
Threads started!
Time limit exceeded, exiting...
Done.

OLTP test statistics:
    queries performed:
        read:                            157822
        write:                           56365
        other:                           22546
        total:                           236733
    transactions:                        11273  (187.88 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 214187 (3569.71 per sec.)
    other operations:                    22546  (375.76 per sec.)

Test execution summary:
    total time:                          60.0013s
    total number of events:              11273
    total time taken by event execution: 59.8877
    per-request statistics:
         min:                                  2.91ms
         avg:                                  5.31ms
         max:                               1028.61ms
         approx.  95 percentile:               6.24ms

Threads fairness:
    events (avg/stddev):           11273.0000/0.00
    execution time (avg/stddev):   59.8877/0.00
</code>


==== sysbench Option ====
<code>
# sysbench --test=oltp help
sysbench 0.4.10:  multi-threaded system evaluation benchmark

oltp options:
  --oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]
  --oltp-sp-name=STRING           name of store procedure to call in SP test mode []
  --oltp-read-only=[on|off]       generate only 'read' queries (do not modify database) [off]
  --oltp-skip-trx=[on|off]        skip BEGIN/COMMIT statements [off]
  --oltp-range-size=N             range size for range queries [100]
  --oltp-point-selects=N          number of point selects [10]
  --oltp-simple-ranges=N          number of simple ranges [1]
  --oltp-sum-ranges=N             number of sum ranges [1]
  --oltp-order-ranges=N           number of ordered ranges [1]
  --oltp-distinct-ranges=N        number of distinct ranges [1]
  --oltp-index-updates=N          number of index update [1]
  --oltp-non-index-updates=N      number of non-index updates [1]
  --oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  --oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay=N          time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0]
  --oltp-table-name=STRING        name of test table [sbtest]
  --oltp-table-size=N             number of records in test table [10000]
  --oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special} [special]
  --oltp-dist-iter=N              number of iterations used for numbers generation [12]
  --oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res=N               percentage of 'special' values to use (for special distribution) [75]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]


Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:
  --mysql-host=[LIST,...]       MySQL server host [localhost]
  --mysql-port=N                MySQL server port [3306]
  --mysql-socket=STRING         MySQL socket
  --mysql-user=STRING           MySQL user [sbtest]
  --mysql-password=STRING       MySQL password []
  --mysql-db=STRING             MySQL database name [sbtest]
  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]
  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]
  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options=STRING additional options passed to CREATE TABLE []

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
</code>
[[https://blog.apar.jp/linux/3350/|参考サイト]]