MySQL Performance Testing with MySQLSLAP and Apache Bench

When we create a database it is always a difficult task to determine the performance of the database system under heavy data sets. In the real time, there are situations in which more than one user tries to access your application, web site, etc.. concurrently. In such situations the performance degradation might have a great affect with your database. Therefore it is always necessary to put your database under stress test before you put it to work in the real time.
In this article I am trying to give a brief explanation about how to use some effective tools to test your database system under heavy loads.


This tool always come with your MySQL installation and can be found PATH_TO_MYSQL/mysql/bin . Inside this directory you can find a script called mysqlslap.exe (In Windows). After locating the relevant script direct to the location and then you can invoke the mysqlslap as mysqlslap [options].

Above figure shows most widely used set of options to test a certain query with mysqlslap. With this query under the options parameter --query we include the location of relevant sql file in which you have mentioned your query. Under the parameter --create-schema you include the name of your database. Under the parameter --concurrency mention the number of concurrent accesses which tries to access the query concurrently. Then you can mention under the parameter -- iterations, the number of time the query runs. If you wish to include more than one query in the sql you need to mention the query delimiter to separate one query from the other and use --delimiter to specify the relevant delimiter.
According to the above figure the mentioned query runs 5 time under one concurrent access. As a total the same query runs 250 times for the configurations.
After the benchmarking you can see the results and analyze them depending on your requirements. Rather than the above mentioned options there are other options which you can find in mysqlslap official documentation.

Apache Bench

With mysqlslap you can mention the set of or individual queries to be tested under stress testing. With Apache bench it is easy to test your web application when a certain page loads. With this tool you do not need to mention all the queries and this allows to measure the performance of your application when executing a set of queries when the loads.
After installing Apache Bench in your system you  need to direct to the location where you have installed it.
Now you can simply issue the command ab –n 500 –c 100 <Enter Url here> to test the page which is mentioned by the url. With the options -n you can ensure the number of requests to perform and the parameter -c determines the number of concurrent requests.
You can find the other available set of options in Apache Bench Options.


Popular posts from this blog

Enabling Custom SSL Profiles in WSO2 ESB 4.8.1

Using WSO2 ESB HTTP Endpoints to define Restful Endpoints

Integrating WSO2 ESB Connectors in real world integration Scenarios