MySQL Benchmarking Tools and Utilities

You are reading the second segment of the four-part article series covering MySQL benchmarking concepts and strategies, benchmarking tools and utilities, and the most common query optimizations, design of optimal schemas, and server tuning tips. In this part we will present some of the popular benchmarking and monitoring tools and utilities. By the end of this article, you’ll know how to use and work with each of them.

Before we begin, it would be helpful to refresh your memory by skimming through the first part of this series, titled “MySQL Benchmarking Concepts and Strategies,” published also here on DevShed in the MySQL section. In that article we laid out the necessary benchmarking concepts, strategies, and the important trio of performance factors. If you missed the article, please read it by all means.

There are numerous utilities and applications that sport various benchmarking and monitoring features and functions. Many of them are commercial. For the purpose of this article, we are going to rely only on freeware, almost always open-source tools and scripts that accomplish the very same tasks. Freeware, especially the sort we’re going to explore here, is also highly customizable in order to meet your unique needs, setup, and database requirements.

As a taste of what’s to come, let’s enumerate some of the benchmarking utilities that we’re going to present in this article. First of all, we won’t neglect the MySQL Benchmark Suite (“sql-bench”) that comes with the original source distribution of MySQL. We’ll also look at the highly popular Super Smack stress-tester, SysBench (modular, cross-platform, multi-threaded), and the mybench Perl script written by Jeremy Zawodny, MySQL Slap (“mysqlslap”).

Moreover, we will extend this list with other functional behavior and performance monitoring tools targeting entire server suites, not just MySQL. Here the Apache JMeter stands out, as well as TPC-C, which measures on-line transactions. It is important to realize that the more you benchmark using a variety of tools, the more you can understand and “imagine” the real-world performance of your server.

As a result, we will also describe few really simplistic benchmarking utilities, some of which may be outdated. You might be surprised to learn that many of the tools I’ve already mentioned are three to four years old, if not more. During this article, you will also find other tools that are quite useful but nothing outstanding. Still, be creative and try to get the most of each tool and utility. Try various options and multiple runs, for example.

You see, benchmarking remains the same; databases at their core and by their very nature are still similar. The same concerns revolving around performance, reliability/stability, and scalability apply as much today as today as they did back then. Benchmarking is a creative task that requires lots of patience and a healthy dose of curiosity—after all, much of what you’re doing involves executing the same runs after each and every change multiple times to see their real effect.

{mospagebreak title=Benchmarking Tools: sql-bench and Super Smack}

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.

{mospagebreak title=Benchmarking Tools: SysBench and the Rest}

By now you surely realize that we cannot discuss in great detail the features and functions of each presented utility. We do our best to present their cons and pros, and give you a brief guide to using them. However, since you will be aware of their existence and most of their functions, you’ll know when to use which and, ultimately, where to get the documentation and research.

The third utility is SysBench. This benchmarking utility measures the OS-relevant parameters during intensive database load. It is cross-platform, multi-threaded, and modular. It was designed with MySQL in mind. Current functions include: evaluating file I/O performance, scheduler performance, memory allocation and transfer speed, POSIX threads implementation performance, and database server performance (OLTP).

Once again, this utility is documented really well. For further information read this. Its usage is quite simple. It has a variety of test modes: cpu, threads, mutex, memory, fileio, and oltp; each can be performed by command line parameters. Here’s an example of an OLTP test run on a database of the InnoDB type. The advantage of SysBench lies in its simplicity. It does the work quite well after all.

sysbench –test=oltp –mysql-table-type=innodb –oltp-table-size=1000000 –mysql-socket=/tmp/mysql.sock prepare

sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-socket=/tmp/mysql.sock –oltp-read-only run

Then there is Jeremy Zawodny’s mybench little Perl script. He is one of the authors of the “High Performance MySQL book. In Chapter 3 of the book you can find extensive documentation of this script. It’s built with a Perl module and a script. The script is called bench_example and should be customized by the user. Once again, the main advantage of this script is its simplicity. It’s not hard to use at all.

You just need to specify the number of user clients (-n) the benchmark should simulate and the count of iterations (-h) each client should perform. The author himself explains that the down side of this script is that it’s a bit heavy on the client side. In order to alleviate the possible bottlenecks, you shouldn’t run from the same SQL server.

It is worthwhile to mention the in-built benchmark() function in MySQL. This function measures the elapsed time on the client end. Its major drawback is that only scalar expressions can be used; as a result, it’s unable to return more than one row or column. It is something fun to play around with but doesn’t match other tools.

SELECT BENCHMARK(50000, SHA1(MD5(rand())));

Up to this point we have presented MySQL database-specific benchmarking tools. Now we’re going to introduce a few really complex and advanced applications. Learning each takes quite a bit of research and practice, but they are very powerful. Apache JMeter truly stands out as a performance measurements utility since it can generate high loads to simulate different behaviors from various servers.

JMeter’s documentation is among the best because the application is very extensible. It supports multi-threading, is 100% cross-platform (Java-based), and is designed to add load to and test a variety of servers: Web, SOAP, Database (via JDBC), LDAP, JMS, and Mail (via POP3). Be sure to read its Manual, Wiki, and FAQ page. It also supports a variety of results exportations (graphs, tables, etc.).

JMeter is definitely advanced. Technically it has the ability to load and measure the performance of all kinds of servers. But because of this, you need to create and tailor your own test sets and plans. It is trickier and more difficult to use than just running command line tools with few parameters or modifying pre-built scripts. Once you grasp the concepts of JMeter, however, you will appreciate its flexibility. It also performs exceptionally well!

Another application that deserves to be named in an article like this is TPC-C. It is an on-line transaction processing (OLTP) benchmark. If you are interested in delving into this, then by all means you should read the official documentation here. This tool is much more complex than anything you could imagine. There are very few professionals that are actually familiar with it. But at least we know it exists and if need be, we can invest the necessary time to research and understand its usage. It is really powerful.

Finally, I’d also like to mention SQLBuster. This tool isn’t popular at all. Try it out!

{mospagebreak title=Taking a Break}

Throughout this article we presented a variety of benchmarking utilities and monitoring tools, many but not all of which are MySQL-specific. The main guideline that you should follow each time you plan to benchmark is stated below.

First of all, create the test schema. Write the data generator and code the required scripts to accomplish this task. You may use already-existing entries to simulate a real-world scenario, or use a percentage of your entire database, or simply use randomly generated data as well. It’s up to you.

After the benchmarking source data is done, you need to grab the tools you are going to use. Research their usage, command line arguments and parameters, read their manuals and documentation, and learn how to use them. After this step, you should be able to execute the benchmarking tool(s) on the test data you have created.

Benchmarking is a critical part of optimizing and tuning, because it shows you a numerical representation of performance. But don’t forget the influencing factors we’ve covered in the first segment; always restart the server to eliminate caching artifacts, do more than a few iterative tests (5+), and then calculate the average. Usually, it is helpful to benchmark using an idle system rather than your active server. 

In the upcoming two parts we are going to cover query optimizations and other server tuning tips and strategies. By the end of this series, preparation (benchmarking) will meet opportunity (after each tweak applied, variable changed, etc.). That’s when you’ll get the most out of the things we have learned in these two articles.

As a closure, I’d like to invite you to join our experienced community of technology professionals on all areas of IT&C starting from software and hardware up to consumer electronics at Dev Hardware Forums. As well as, be sure to check out the community of our sister site at Dev Shed Forums. We are friendly and we’ll do our best to help you.

[gp-comments width="770" linklove="off" ]

chat