Databases Reference
In-Depth Information
CHAPTER 14
Application-Level Optimization
If you spend a lot of time improving MySQL's performance, it's easy to get tunnel vision
and forget to focus on the user's experience. You may step back for a bit and realize
that MySQL is so highly optimized that it's contributing only a tiny fraction of the
response time the user sees, and it's time to focus elsewhere. This is a great insight
(especially for a DBA), and it's exactly the right thing to do. But what is causing prob-
lems, if not MySQL? The answer can be found most reliably and quickly by measuring,
using the techniques we showed in
Chapter 3
. If your profiling is thorough and you
follow a logical process, it should not be hard to find the source of your problem.
Sometimes, though, even when the problem is MySQL, it might be easiest to solve it
in another part of the system!
No matter where the problem lies, there's sure to be at least one great tool available to
help you measure it, often for free. For example, if you have issues with JavaScript or
page rendering, you can use the profiler included with the Firebug extension for the
Firefox web browser, or you can use the YSlow tool from Yahoo!. We mentioned several
application-level tools in
Chapter 3
. Some tools even profile the whole stack; New Relic
is an example of a tool that profiles the frontend, application, and backend of web
applications.
Common Problems
We see the same problems over and over again in applications, often because people
have used poorly designed off-the-shelf systems or popular frameworks that simplify
development. Although it's sometimes easier and faster to use something you didn't
build yourself, it also adds risk if you don't really know what it's doing under the hood.
Here's a laundry list of things that we often find to be problems, to stimulate your
creative thought processes:
• What's using the CPU, disk, network, and memory resources on each of the ma-
chines involved? Do the numbers look reasonable to you? If not, check the basics
for the applications that are hogging resources. Configuration is sometimes the
