top of page

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.


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.

36 views0 comments

Recent Posts

See All

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa

bottom of page