Performance Measurement of MySQL using MySQLslap with Custom Queries
MySQL comes with a small analytic tool called mysqlslap that's been about since version 5.1.4. It's a benchmarking tool that can benefit DBAs load test their database servers.
mysqlslap can compete with many client connections hammering the database server at the same time. The load testing constraints are fully configurable and the results from changed test runs can be used to fine-tune database plan or hardware resources.
In this blog we will study how to use mysqlslap to load test a MySQL database with some custom queries and see how benchmarking can help us fine-tune individuals’ queries. After some straightforward demos, we will run over a realistic test scenario where we create a copy of an existing database for testing, gather queries from a log, and run the test from a script.
Installing a Sample Database
Next, we need to put in a sample database for testing. This database is called employees and it's freely available from the MySQL web site. The database can also be transferred from Launchpad. The employees.
We are picking the employees database because it structures a large data set. The database structure is simple enough: it's got only 6 tables; but the data it covers has more than 3,000,000 employee records. This will help us compete with a more genuine production workload.
Download the data:
Unzip the archive:
Connect to MySQL and execute the employees.sql script, which will generate the database and load the data:
Output will be like this:
Using mysqlslap
Before we get into definite commands for trying, you may want to take a expression at this list of the most beneficial mysqlslap options.
Benchmarking with Custom Queries
Auto-generated SQL is decent if you are measuring the server's physical properties. It's helpful when you want to find the neck and neck of load a certain system can yield.
In the following code extract, we are running a modest query in contrast to the dept_emp table.
sudo mysqlslap --user=sysadmin --password --host=localhost --concurrency=10 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose
This will take a sometime to run. You should have a performance benchmark like this after a minute or two:
Now, we will use several SQL queries in the --query constraint. In the subsequent example we are terminating each request with a semicolon. mysqlslap knows we are using several distinct SQL commands because we have stated the --delimiter option:
It's informal to add a complex SQL queries to a script than to type it out for tests. So, we can teach mysqlslap to read the request from a script file.
Conclusion
mysqlslap is a modest, light-weight tool that's informal to use and that mixes natively with the MySQL database engine. It's offered for all versions of MySQL from version 5.1.4 onwards.