Master MySQL Performance Testing with mysqlslap: A Step‑by‑Step Guide

mysqlslap is MySQL's built‑in stress‑testing utility that simulates many concurrent clients to evaluate database performance.

Why Use mysqlslap?

It helps establish baseline performance metrics for a server, assess the impact of OS kernel tweaks, MySQL configuration changes, and hardware upgrades, and provides reference points for monitoring during production.

Basic Usage

Run a simple automatic test that generates SQL statements on the fly: mysqlslap --user=root --password=111111 --auto-generate-sql The --auto-generate-sql flag creates test queries automatically.

Adding Concurrency

Specify the number of simultaneous client connections and total queries:

mysqlslap --user=root --password=111111 --concurrency=100 --number-of-queries=1000 --auto-generate-sql

--concurrency=100

means 100 clients run at the same time, and --number-of-queries=1000 sets the total query count (clients × queries per client).

Generating Complex Tables

Customize the generated table structure by defining column counts:

mysqlslap --user=root --password=111111 --concurrency=50 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql

--number-int-cols=5

creates five INT columns, and --number-char-cols=20 creates twenty CHAR columns.

Using Your Own Schema and Queries

For realistic testing, point mysqlslap at an existing database and supply custom SQL statements:

mysqlslap --user=root --password=111111 --concurrency=50 --create-schema=employees --query="SELECT * FROM dept_emp;"

--create-schema

selects the test database, and --query provides the SQL to execute.

When testing multiple statements, place them in a file and reference it:

echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql

mysqlslap --user=root --password=111111 --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="select_query.sql" --delimiter=";"

--delimiter=";"

tells mysqlslap what separates statements in the file.

Interpreting Results

The output includes metrics such as average, minimum, and maximum execution time, number of clients, and average queries per client, which help identify performance bottlenecks and verify the effect of optimizations.

References

MySQL Documentation: http://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html

DigitalOcean Tutorial: https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap