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.

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.

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.

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.

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.

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.

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.

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.

 


Visit our advertisers

Search

Quick Poll

Are you using Docker.io?

Visit our advertisers