Benchmark MySQL server Performance with Sysbench

You can spend hours tweaking the settings of a MySQL server instance to get the best possible performance for your hardware and environment. The hardest part is to ensure that the changes made are reflected with increased performance.

To ensure each change results in better performance of the MySQL server we need to measure the performance of the MySQL server before and after the change.

There are a verity of tools to automate MySQL benchmarking, one of which is Sysbench. I will be demonstrating the tests on a Debian 7 system however Sysbench will work on most common Linux distributions. Sysbench can be used to test both InnoDB or MyISAM database types in either a single server environment or a clustered environment with a single instance.

Installing Sysbench will differ on each Linux distribution; it can be downloaded and built from source from Sourceforge or installed with apt-get on Ubuntu or Debian.

apt-get install sysbench

Login to MySQL using the CLI or your favorite GUI tool and create a new database which will be used for the test. If you already have a database you can use for the test then you can skip this step. This example will use a database called dbtest for the tests.

create database dbtest;

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.

From the command line, run the below command changing [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] prepare

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...

This has created a table called sbtest with 1000000 rows of data which will be used for testing. The below commands show the the created table and do not need to be executed.

mysql> use dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| sbtest           |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sbtest;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)

The next step is to being the performance tests. There are multiple parameters which can be changed to alter the test performed but we will do a simple read write test. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --oltp-read-only=off --num-threads=6 --max-time=60 --max-requests=0 --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] run

To perform a read only test, change the above parameter oltp-read-only=off to oltp-read-only=on.

The results will look similar to the below output. The main statistic to look for is transactions which shows the number of transactions the test managed to complete, and how many per second.

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 6

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...
(last message repeated 5 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            456680
        write:                           163100
        other:                           65240
        total:                           685020
    transactions:                        32620  (543.63 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 619780 (10329.05 per sec.)
    other operations:                    65240  (1087.27 per sec.)

Test execution summary:
    total time:                          60.0036s
    total number of events:              32620
    total time taken by event execution: 359.8823
    per-request statistics:
         min:                                  1.66ms
         avg:                                 11.03ms
         max:                                981.94ms
         approx.  95 percentile:              15.13ms

Threads fairness:
    events (avg/stddev):           5436.6667/31.44
    execution time (avg/stddev):   59.9804/0.00

Finally, you need to clean up your test area. If you can drop the entire database which was used for testing then login to MySQL and run the below command.

drop database dbtest;

If you are unable to drop the whole database then Sysbench comes with a cleanup command. Again you will need to change [USER] and [PASSWORD] to your MySQL access credentials.

sysbench --test=oltp --mysql-db=dbtest --mysql-user=[USER] --mysql-password=[PASSWORD] cleanup