[MySQL]: how to do MySQL Sysbench testing on Linux
SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load. The idea of this benchmark suite is to quickly get an impression about system performance without setting up complex database benchmarks or even without installing a database at all.
Current features allow to test the following system parameters:
* file I/O performance
* scheduler performance
* memory allocation and transfer speed
* POSIX threads implementation performance
* database server performance (OLTP benchmark)
(Primarily written for MySQL server benchmarking, SysBench will be further extended to support multiple database backends, distributed benchmarks and third-party plug-in modules)
Download Sysbench (current version is 0.4.12)
wget http://garr.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.12.tar.gz
Then unpack it and install with
# tar -xvzf sysbench-0.4.12.7tar.gz
# cd sysbench-0.4.12.7
# ./configure
# make
# make install
For MySQL test, you’ll need to prepare database for testing with
[root@dbversity.com ~]# cd $SYSBENCH_HOME
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]# pwd
/home/ec2-user/sysbench-0.4.12.7/sysbench
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]# mysql -h MyDatabaeHostName.com -P 3306 -u MyUserName -p”MyPassw0rd”
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2867
Server version: 5.6.10 MySQL Community Server (GPL)
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>
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| aurora |
| mysql |
| performance_schema |
+——————–+
5 rows in set (0.00 sec)
mysql>
mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)
mysql> Bye
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]# ll -lhtr /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Apr 6 19:03 /var/lib/mysql/mysql.sock
[root@dbversity.com sysbench]#
(replace myaswuser with valid username and MyPassw0rd with valid password)
This command will create sample table inside sbtest and it will have 500000 rows (InnoDB engine).
[root@dbversity.com sysbench]# ./sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=500000 –mysql-user=MyUserName –mysql-password=MyPassw0rd –mysql-socket=/var/lib/mysql/mysql.sock –mysql-host=MyDatabaeHostName.com prepare
sysbench 0.4.13: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table ‘sbtest’…
Creating 500000 records in table ‘sbtest’…
[root@dbversity.com sysbench]#
[root@dbversity.com sysbench]# mysql -h MyDatabaeHostName.com -P 3306 -u MyUserName -p”MyPassw0rd” -D sbtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3001
Server version: 5.6.10 MySQL Community Server (GPL)
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>
mysql> show tables;
+——————+
| Tables_in_sbtest |
+——————+
| sbtest |
+——————+
1 row in set (0.00 sec)
mysql> desc sbtest;
+——-+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+——————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+——-+——————+——+—–+———+—————-+
4 rows in set (0.00 sec)
mysql> select count(id) from sbtest;
+———–+
| count(id) |
+———–+
| 500000 |
+———–+
1 row in set (0.09 sec)
mysql> select * from sbtest limit 5;
+—-+—+—+—————————————————-+
| id | k | c | pad |
+—-+—+—+—————————————————-+
| 1 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 2 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 3 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 4 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 5 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+—-+—+—+—————————————————-+
5 rows in set (0.00 sec)
mysql>
You can start read test with below command
[root@dbversity.com sysbench]# ./sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=500000 –mysql-socket=/var/lib/mysql/mysql.sock –oltp-read-only –mysql-user=MyUserName –mysql-password=MyPassw0rd –mysql-host=MyDatabaeHostName.com run
sysbench 0.4.13: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
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 100000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400000
write: 0
other: 200000
total: 1600000
transactions: 100000 (807.46 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1400000 (11304.48 per sec.)
other operations: 200000 (1614.93 per sec.)
Test execution summary:
total time: 123.8447s
total number of events: 100000
total time taken by event execution: 1980.9840
per-request statistics:
min: 17.16ms
avg: 19.81ms
max: 55.22ms
approx. 95 percentile: 20.56ms
Threads fairness:
events (avg/stddev): 6250.0000/5.67
execution time (avg/stddev): 123.8115/0.01
[root@dbversity.com sysbench]#
For read-write test you can try
[root@dbversity.com sysbench]# ./sysbench –num-threads=16 –max-requests=10000 –test=oltp –oltp-table-size=500000 –mysql-socket=/var/lib/mysql/mysql.sock –oltp-test-mode=complex –mysql-user=MyUserName –mysql-password=MyPassw0rd –mysql-host=MyDatabaeHostName.com run
sysbench 0.4.13: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored
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
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (395.16 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (7508.02 per sec.)
other operations: 20000 (790.32 per sec.)
Test execution summary:
total time: 25.3063s
total number of events: 10000
total time taken by event execution: 404.8364
per-request statistics:
min: 32.77ms
avg: 40.48ms
max: 87.65ms
approx. 95 percentile: 51.62ms
Threads fairness:
events (avg/stddev): 625.0000/0.61
execution time (avg/stddev): 25.3023/0.00
[root@dbversity.com sysbench]#