High Performance MySQL, 2nd Edition

Chapter 10. Application-Level Optimization

This book would not be complete without a chapter on optimizing the applications that connect to MySQL, because they are frequently to blame for many performance problems that seem to be caused by MySQL. We focus mostly on optimizing MySQL in this book, but we don’t want you to miss the bigger picture. There is no way to optimize MySQL enough to compensate for poor application design. In fact, sometimes the answer is to take operations entirely out of MySQL and do them in the application or use other tools, which may offer much better performance.

This chapter isn’t a reference for how to build a high-performance application, but we hope it will help you avoid some common mistakes that can hurt MySQL’s performance. We focus on web applications, because MySQL is so often used for them.

Application Performance Overview

The search for faster performance begins very simply: the application is taking too long to respond to requests, and you need to do something about it. But what exactly is the problem? The common bottlenecks are slow queries, locks, CPU saturation, network latency, and file I/O. Any of these can become a problem if the application is misconfigured or uses resources inappropriately.

Find the Source of the Problem

The first task is to find the culprit. This will be much easier if you’ve added profiling capabilities to your application. If you’ve done this but you can’t see what’s causing the slow performance, you may need to add more ...