Load Testing Mysql with mysqlslap Load Testing Mysql with mysqlslap

I have a few thoughts for some testing/comparison of mysql tables that I have been curious about.  Before starting though, I thought it was prudent that I should give it some thought about the approach.  The first thing that came to mind was altering the A Simple But Effective Speed Comparison code to execute a bunch of SQL queries against my local installation.

I really didn't like this approach because of the potential inaccuracy due to the overhead of the language and connecting to mysql before executing the queries.  The whole purpose of these potential upcoming articles is to compare mysql, not a specific language integrating with mysql.

Luckily for us, as of Mysql 5.1.4, a tool called mysqlslap is shipped with the server installation.  This tool allows us to client emulation against our mysql server!


Using the tool couldn't be easier:


mysqlslap --concurrency=5 --iterations=20
--number-int-cols=2 --number-char-cols=3
--auto-generate-sql

After running this, here is the nice output from the tool:


Average number of seconds to run all queries: 0.021 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 0.032 seconds
Number of clients running queries: 5
Average number of queries per client: 0

If you wish to see exactly what queries are being executed, you can add the --verbose flag as well.

As you can see from the above example, there are a plethora of options that we can define to perform a testing.  In the above example we are defining things like the number of clients, integer columns, character columns, as well as the number of iterations.  Finally an SQL file can also be specified.  In this example, I am letting mysqlslap auto generate the SQL that will be running.

As this tool has evolved, several excellent auto generated SQL can be running against your database.  A highlight of my favorite are:

The first example didn't provide too much useful information as the test was quite basic.  Let's attempt a little bit more thorough of test and compare the results:


mysqlslap --concurrency=50 --iterations=10
--number-char-cols=4 --number-int-cols=7
--auto-generate-sql --number-of-queries=10000

This one definitely takes a few minutes to run as I am testing 50 concurrent connections with 10 iterations of 10,000 queries!  Once this has final finished here are the results I receive:


Average number of seconds to run all queries: 42.814 seconds
Minimum number of seconds to run all queries: 40.421 seconds
Maximum number of seconds to run all queries: 46.010 seconds
Number of clients running queries: 50
Average number of queries per client: 200

After first glance your eyes might pop out of their sockets – 42 seconds!  Don't forget that is to perform all 10,000 queries – meaning 0.0042 seconds on average.

For more information, you can view the full details on mysqlslap @ mysql.

Photo courtesy of Paul Mutant

Published on Sep 17, 2012

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Optimization | load testing

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.