Load Testing MySQL database with mysqlslap
To Nha Notes | May 20, 2021, 10:49 a.m.
I'd like to introduce mysqlslap as a tool to perform load test for MySQL server.
It can emulate a large number of client connections hitting the database server at the same time. The load testing parameters are fully configurable and the results from different test runs can be used to fine-tune database design or hardware resources.
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/</wbr>Desktop/test1.sql --create-schema=my_database -umy_username -pmy_password
Or
mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200
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.
For more detail, take a look at its document here https://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html