How to run a MySQL benchmarking using sysbench

How to run a MySQL benchmarking using sysbench

Installing sysbench

#yum install sysbench

The next step is to use the prepare statement with sysbench to generate a table in the specified database which will be used when performing tests.

root@# sysbench –test=oltp –oltp-table-size=250000 –mysql-db=database name –mysql-user=db user –mysql-password=db password –mysql-socket=/tmp/mysql.sock –db-driver=mysql –oltp-auto-inc=off prepare

sysbench 0.4.12: multi-threaded system evaluation benchmark

Creating table ‘sbtest’…
Creating 250000 records in table ‘sbtest’…

The above command has created a table called sbtest with 250000 rows of data which will be used for testing. The below commands show the the created table.

root@# sysbench –test=oltp –oltp-table-size=10000 –oltp-test-mode=complex –oltp-read-only=on –num-threads=1000 –max-time=300 –max-requests=5000 –mysql-db=database name –mysql-user=db user –mysql-password=db password –mysql-socket=/tmp/mysql.sock –db-driver=mysql run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1000

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 5000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 70056
write: 0
other: 10008
total: 80064
transactions: 5004 (1128.50 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 70056 (15799.05 per sec.)
other operations: 10008 (2257.01 per sec.)

Test execution summary:
total time: 4.4342s
total number of events: 5004
total time taken by event execution: 4294.8422
per-request statistics:
min: 34.05ms
avg: 858.28ms
max: 2907.31ms
approx. 95 percentile: 2281.78ms

Threads fairness:
events (avg/stddev): 5.0040/0.73
execution time (avg/stddev): 4.2948/0.07
=-=-=-=

Finally, you can drop the database that have created while doing test.