Benchmark MySQL server Performance with Sysbench

Benchmark MySQL server Performance with Sysbench

Get Social!

mysql-logoYou 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

 


4 Comments

tom

4-Aug-2014 at 4:05 am

thanks, this is a very interesting posting.

could you please, pleaes write a littel bit about your hardware and mybe even publish your my.cnf?
I have the same settings used as you my results are so much worse:-(
i only get about 150 transacctions/second (on a intel i7-920 with 4 cores, 3,4 GHZ, , 32 GB Ram…)

thanks very much!!

    james.coyle

    4-Aug-2014 at 2:28 pm

    Hi Tom,

    It looks like I’m using very similar hardware to you – I’m also running a raid array of SSDs.

Mark

8-Feb-2015 at 1:37 am

What a fantastic article! I was wondering forever how to benchmark my system… here are my results of the server I have under my television (I use it for collecting public databases and running my TV entertainment system (KODI).

OLTP test statistics:
queries performed:
read: 1467424
write: 524080
other: 209632
total: 2201136
transactions: 104816 (1746.35 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1991504 (33180.68 per sec.)
other operations: 209632 (3492.70 per sec.)

Test execution summary:
total time: 60.0200s
total number of events: 104816
total time taken by event execution: 958.5816
per-request statistics:
min: 2.09ms
avg: 9.15ms
max: 1072.01ms
approx. 95 percentile: 25.18ms

Threads fairness:
events (avg/stddev): 6551.0000/45.02
execution time (avg/stddev): 59.9114/0.01

I’m using an Asus KGP-16 with dual G34 processors, 44 gigs of ram, a hardware RAID card running two 110 gb SSD drives in RAID 1. It is on Ubuntu 14.04 and currently serving 16 web sites… one web forum getting about 50,000 hits per day. I don’t know how much overhead that takes… but anyways, when running HTOP and doing the test, I didn’t see any of my cores exceeding 60%. My ram is only topping out at 12 gigs, despite one of my databases having 14.2 million rows. 2 gigs are used for indexes for those databases.

Again, thank you for the wonderful article!

Olayemi Olatunji

9-Apr-2015 at 5:00 pm

Hi,

My name is Yemi.

New to Linux.

Could you please help me with a detailed analysis of what the output of the OLTP benchmarking test mean or could you recommend a website that could help me with the analysis the results.

Thanks for helping me guys.

Leave a Reply

Visit our advertisers

Quick Poll

What type of VPN protocol do you use?

Visit our advertisers