Benchmarking Tools: sql-bench and Super Smack - MySQL

Let’s kick-start the presentation of benchmarking utilities with the official MySQL Benchmarking Suite called “sql-bench” that comes with MySQL distributions. This bundled benchmarking suite provides a general overview of the tested SQL server as a whole. This means that it measures the overall performance of the server and can be used as a basis for comparison with others, not necessarily testing new tweaks.

We will do our best to point out the advantages and drawbacks of each utility. It is important to know when to use which, how, and why exactly. SQL-bench is serialized; it is single-threaded and unable to take advantage of multi-threading (multi-CPU or dual/quad-core setups). SQL-bench brings absolutely no SMP capabilities. However, it has been said that the MySQL team plans to add multi-threading support in the future.

You can execute this benchmarking utility by launching the run-all-tests script residing in the /sql-bench directory. The execution time-frame of the entire suite takes a while -- depending on your hardware specifications, a few minutes to a few hours.

This article gives you a general guideline of each utility that’s presented within. But it does not replace the original documentation and/or manual that comes with the benchmarking tools. Be sure to read those before trying to run the scripts and command-line described here. Each utility has various parameters and each one of them is documented in-depth in the official manuals.

The aforementioned sql-bench ought to be launched with the following syntax:

./run-all-tests --server=mysql --user=root --log --fast

In the above syntax, using the parameter --log results in verbose extensive real-time logging in a subdirectory called output. During the benchmarking process you can check out these logs. This benchmark measures the wallclock seconds required to execute the queries. After this value there are various other values in parentheses. You need to subtract from the wallclock seconds that second smaller value.

The aforementioned subtraction is important if you want to find out exactly the time-frame it takes for the SQL server to respond. The total count of wallclock seconds contains the script execution and other external factors as well. You shouldn’t count those. This benchmark is still used frequently because it comes bundled; it’s scripted in Perl, doesn’t really reflect real-life performance, and it is just single-threaded.

Going forward, there’s the highly popular stress-tester called Super Smack. The beauty of this tool lies in its multi-threading capabilities and highly customizable architecture. The user is able to specify the count of concurrent connections and also the number of iterations each client will execute. Additionally, you can specify the tests that should be performed on your test data along with tables. It’s very customizable.

As a result, creating your own set of Super Smack tests takes a bit of research and time. Super Smack is amazingly well-documented; however, you need to actually read those manuals. You need to create your own test tables, fill them up either randomly on-the-fly or from an input file, and explicitly specify the set of queries which are going to be performed during the benchmarking session.

However, you can run Super Smack without preparing a customized set of tests yourself. In that case, the table http_auth is automatically generated (if not found) and populated from the smack data file words.dat bundled with Super Smack. However, you still need to specify the count of concurrent connections and the number of iterations when running Super Smack in the command line. Here are a few examples:

super-smack update-select.smack 50 20000

// 50 concurrent users and each executing 20,000 iterations of the test queries

super-smack -d mysql select-key-smack 75 1000

// 75 concurrent users and each executing 1,000 iterations

Summing these up, Super Smack is probably one of the best benchmarking utilities due to its incredible flexibility, configurability, and functions that just won’t get old. No matter what kind of unique requirements you do have, if you really take the time to take control of Super Smack, you can be absolutely positive that the custom set of tests you will create will be of great benefit to you. Due to this, it’s quite advanced.