Let me set the scene of how I stumbled upon the need for MySQLslap.
The database server was getting hit hard and slowing down the application, we narrowed the issue down to one potentially bad query.
We then took a copy of some production data, put it on our local machines and started hacking to see if the query could be improved.
Even though using EXPLAIN we were able to see it was a bad query, and running our new query through EXPLAIN made it look like a good query, we couldn’t actually benchmark how much better it was. You see our issue was that when ran once the query finished in a reasonable amount of time, the issue only seemed to appear under production load.
There are plenty of tools available for testing your code under load, but I didn’t want anything above these queries dirtying the results, for example I didn’t want ActiveRecord to try and do anything smart with caching, I wanted only to see what SQL statement worked best.
Turns out MySQL comes with a tool to help you do just that, MySQLslap.
MySQLslap
As the name suggests MySQLslap slaps your MySQL tables with a load of queries at a rate and concurrency that you set.
After the queries have ran it will produce a small table breaking down the times for you, which allows you to easily compare and contrast.
Here is an example of some output from MySQLslap;
Average number of seconds to run all queries: 0.014 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.017 seconds
Number of clients running queries: 50
Average number of queries per client: 2
This was generated from one command, that took a very small amount of time to set up.
MySQLslap – Setting it up
In order to get the above output I just needed to enter one line into my terminal;
mysqlslap --concurrency=50 --iterations=5 --query=/Users/me/
I will go through each flag in turn;
- mysqlslap – This is the program we are running, if you don’t have it installed make sure you are running at least MySQL 5.1.4
- –concurrency – This is how many connections you want to emulate very roughly this adds more load onto MySQL, imagine 50 people hitting the same page at the same time.
- –iterations – This is how many times you want to run the test, obviously the more you do it the more accurate your average will be.
- –query – You can type the query or queries into here, but I preferred to have them saved in a .sql file, so in my case I just reference where my file is located (note: you cannot reference the home directory by typing
~/mysql doesn’t understand it) - –create-schema – This is the most confusing flag in my opinion, this means which database you want the test to use, because it is called create I wrongly assumed at the start it would kill my current database, it does not (but any queries you run will affect it)
- -u – The username you use to connect to your database (optional depending on your setup)
- -p – The password you use to connect to your database (optional depending on your setup)
Hopefully that will help get you on your way.
MySQLslap resources
Here are a collection of useful resources (I will add to this over time);
