Database Diagnosis and Profiling with mysqlslap

If you’re chasing bottlenecks in your application or you just feel like you could squeeze a bit of performance out of your database, you’ll probably be measuring query times at one point or another. If you’re using MySQL, you’re in luck: Starting with version 5.1.4, MySQL comes with a command line tool called mysqlslap. There are many profiling solutions for MySQL available, but since this program comes bundled with MySQL we’ll just give it a try.

mysqlslap is a diagnosis tool for the MySQL RDBMS, but you don’t even need a database to use it. mysqlslap can create one for you and perform queries on it. But that’s not what we’re interested in here. Let’s say we have a couple of slow queries and want to fix them. mysqlslap can help us with that!

Basic Usage of mysqlslap

To use mysqlslap with a specific database, we’ve got to pass it the oddly-named create-schema parameter, along with our database credentials, and, of course, a query to run:

mysqlslap --create-schema=mydatabase \
  --user=myuser \
  --password \
  --query="SELECT * FROM users"

This will print a benchmark to the console:

Benchmark
Average number of seconds to run all queries: 0.003 seconds
Minimum number of seconds to run all queries: 0.003 seconds
Maximum number of seconds to run all queries: 0.003 seconds
Number of clients running queries: 1
Average number of queries per client: 1

So far, so good. But we’ve got far more complex queries to worry about. We could type them in on the console, but that would be tiresome. Luckily, we can pass a file to the program via the query parameter, like so:

mysqlslap --create-schema=mydatabase \
  --user=myuser \
  --password \
  --query=/home/user/queries.sql

Now, as we tweak our queries or make changes to the database (like adding indexes or fixing data types), we can invoke mysqlslap again to see the effects.

Adding Load

To find out how our queries perform under load, mysqlslap lets us define a number of concurrent connections as well as how often the queries will be run by each connection:

mysqlslap --create-schema=mydatabase \
  --user=myuser \
  --password \
  --query=/home/user/queries.sql \
  --concurrency=500 \
  --iterations=5

Benchmark
Average number of seconds to run all queries: 0.935 seconds
Minimum number of seconds to run all queries: 0.907 seconds
Maximum number of seconds to run all queries: 0.972 seconds
Number of clients running queries: 500
Average number of queries per client: 1

Conclusion

mysqlslap provides a quick and easy way to find bottlenecks in a database. Run single queries to inspect and adapt, or stress test the database by emulating concurrent connections over several iterations. Hopefully, this post contained enough information to get you started with mysqlslap. If you want to get down and dirty, the program has many command line parameters explained in the MySQL reference manual.

2 thoughts on “Database Diagnosis and Profiling with mysqlslap

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.