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.
