Load Testing Mysql with mysqlslap

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:

• --auto-generate-sql-load-type=type
• --auto-generate-sql-write-number=#
• --auto-generate-sql-unique-query-number=#

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