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