High Performance MySQL, 2nd Edition

Chapter 2. Finding Bottlenecks: Benchmarking and Profiling

At some point, you’re bound to need more performance from MySQL. But what should you try to improve? A particular query? Your schema? Your hardware? The only way to know is to measure what your system is doing, and test its performance under various conditions. That’s why we put this chapter early in the book.

The best strategy is to find and strengthen the weakest link in your application’s chain of components. This is especially useful if you don’t know what prevents better performance—or what will prevent better performance in the future.

Benchmarking and profiling are two essential practices for finding bottlenecks. They are related, but they’re not the same. A benchmark measures your system’s performance. This can help determine a system’s capacity, show you which changes matter and which don’t, or show how your application performs with different data.

In contrast, profiling helps you find where your application spends the most time or consumes the most resources. In other words, benchmarking answers the question “How well does this perform?” and profiling answers the question “Why does it perform the way it does?”

We’ve arranged this chapter in two parts, the first about benchmarking and the second about profiling. We begin with a discussion of reasons and strategies for benchmarking, then move on to specific benchmarking tactics. We show you how to plan and design benchmarks, design for accurate results, run benchmarks, ...