Benchmarking Tools: SysBench and the Rest - MySQL

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!