mysqlslapはMySQL 5.1.4以降からなので、それ以前のバージョンではsysbenchでベンチマークを行う。
こちらが新しいバージョン→52 MySQLQ sysbench 1.0
# yum -y install sysbench
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)
sysbench \ --test=oltp \ --db-driver=mysql \ --oltp-table-size=10000 \ --mysql-password=kumolabo \ prepare
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
下記のように色々出力されるが、下記部分を参考にすれば良いと思う。
transactions: 11273 (187.88 per sec.)
※187.88/sec トランザクションを実行できた。
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
# 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]