Basics of Benchmarking - MySQL

Before we get into the details of benchmarking we need to understand its basics. In order to explain as clearly as possible the concepts of "benchmarking" and "stress-testing" when it comes to the world of database servers, we'll approach the situation with familiar analogies from generic computer benchmarking and stress-testing.

Since the graphics card industry started booming back in the early 2000s, newer and newer video card benchmarking utilities have come out, such as the popular 3DMark series. In a nutshell, these tools actually play snippets of bleeding-edge video games, which in fact test the overall video processing performance of the system during those scenes (calculations are based on frame rates per second).

In the end, the user is given a score. That score could be used for comparison measures with other users having either similar or identical hardware, or even totally different system setups. As a result, users could get a "sense" of the capabilities of their system, how it performs in comparison to others, and in the end, whether or not it could support the latest video game releases on the market.

Benchmarking becomes truly crucial the moment users try to tweak the settings of their components and even attempt overclocking. After each and every change, running the benchmarking utility could show whether the tweak was useful or not, whether it improves or degrades performance, and so forth. The same applies for benchmarking in the world of database servers.

Benchmarking, by definition, is a set of standards by which the performance of components can be measured. These standards are created from scenarios from the real-world. In video card benchmarking they are snippets from various new video games. In database benchmarking, as you will learn, they are based on day-to-day situations and the likelihood that these will appear during everyday activities is acceptable.

This is the most important and key distinction between benchmarking and stress-testing. The former gives us results in the shape of numbers; the ones we care about mostly are the differences between those numbers while we are applying tweaks, changing the server/system settings, and so forth. On the other hand, stress testing is nothing but testing on the extreme edge, finding the server's/system's limits, and so forth.

The purpose of this article series is to show you how to get the most out of your MySQL server by applying tweaks, optimizing your database, and tuning the performance of your server. Therefore, we won't cover extreme stress-testing. It's good to keep in mind, however, that stress testing isn't useless, because finding the limits of your infrastructure, MySQL database, etc. is good to know. Covering this area, though, would be beyond the scope of this multi-part series.

Needless to say, by the end of this series you will know the factors professionals usually consider critical for benchmarking purposes, and in the next segment you will learn about a variety of tools that benchmark those criteria. With a little imagination you will realize that using those exact same tools and feeding them  "extreme" data is akin to stress-testing. It all depends on the source data.

It's obvious that using an unrealistic amount of data, such as billions of rows in a table, while running the exact same benchmarking utilities would generate potentially bizarre results, but such results are important when stress-testing. As soon as your server crashes when running 1,500 simultaneous connections, you will know why it cannot accomplish the requested task. That's how you find its limits.

Now that we know what benchmarking is about, we need to define the sub-concepts of MySQL server performance. In general, we consider throughput, latency times, and scalability the three most important performance factors. These factors, when combined, represent the overall performance of an SQL server. There are individual benchmark utilities that test each of these, but we'll discuss those on the next page. Let's move on.