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:

[code]
mysqlslap –concurrency=5 –iterations=20
–number-int-cols=2 –number-char-cols=3
–auto-generate-sql
[/code]

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

[code]
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
[/code]

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:

[code]
mysqlslap –concurrency=50 –iterations=10
–number-char-cols=4 –number-int-cols=7
–auto-generate-sql –number-of-queries=10000
[/code]

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:

[code]
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
[/code]

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