Prerequisites
- installed sysbench package
- installed MySQL server
- root access to MySQL server (for creating and dropping database)
Create test database
root@server:~# mysql -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2544 Server version: 5.6.28-1 (Debian) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> \q Bye
Prepare database for test
root@server:~# sysbench –test=oltp prepare –oltp-table-size=1000000 –mysql-db=test –mysql-user=root –mysql-password=mysqlrootpassword
sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Creating table 'sbtest'... Creating 1000000 records in table 'sbtest'...
Running test
root@server:~# sysbench –test=oltp –oltp-table-size=1000000 –mysql-db=test –mysql-user=root –mysql-password=mysqlrootpassword –max-time=60 –oltp-read-only=on –max-requests=0 –num-threads=8 run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8
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
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.
OLTP test statistics:
queries performed:
read: 2110318
write: 0
other: 301474
total: 2411792
transactions: 150737 (2512.19 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 2110318 (35170.64 per sec.)
other operations: 301474 (5024.38 per sec.)
Test execution summary:
total time: 60.0023s
total number of events: 150737
total time taken by event execution: 479.3135
per-request statistics:
min: 1.18ms
avg: 3.18ms
max: 30.91ms
approx. 95 percentile: 4.76ms
Threads fairness:
events (avg/stddev): 18842.1250/91.20
execution time (avg/stddev): 59.9142/0.00
The most important number is the transactions per second value. In current example: 2512.19 per second.
Cleanup
root@server:~$ mysql -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3798 Server version: 5.6.28-1 (Debian) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop database test; Query OK, 1 row affected (0.18 sec) mysql> \q Bye
For more info at HowToForge