Databases Reference
In-Depth Information
the system from the user to the servers and back. It's often true that the database server
is to blame for performance problems, but it's the application's fault at least as often.
Bottlenecks can also be caused by any of the following:
• External resources, such as calls to web services or search engines
• Operations that require processing large amounts of data in the application, such
as parsing big XML files
• Expensive operations in tight loops, such as abusing regular expressions
• Badly optimized algorithms, such as naïve search algorithms to find items in lists
Fortunately, it's easy to figure out whether MySQL is the problem. You just need an
application profiling tool. (As a bonus, once you have it in place, it can help developers
write efficient code from the start.)
We recommend that you include profiling code in
every
new project you start. It might
be hard to inject profiling code into an existing application, but it's easy to include it
in new applications.
Will Profiling Slow Your Servers?
Yes, it will make your application slower. No, it will make your application much faster.
Wait, we can explain.
Profiling and routine monitoring add overhead. The important questions are how much
overhead they add and whether the extra work is worth the benefit.
Many people who design and build high-performance applications believe that you
should measure everything you can and just accept the cost of measurement as a part
of your application's work. Oracle performance guru Tom Kyte was famously asked
how costly Oracle's instrumentation is, and he replied that the instrumentation makes
it possible to improve performance by at least 10%. We agree with this philosophy,
and for most applications that wouldn't otherwise receive detailed performance eval-
uations every day, we think the improvement is likely to be much more than 10%. Even
if you don't agree, it's a great idea to build in at least some lightweight profiling that
you can enable permanently. It's no fun to hit a performance bottleneck you never saw
coming, just because you didn't build your systems to capture day-to-day changes in
their performance. Likewise, when you find a problem, historical data is invaluable.
You can also use the profiling data to help you plan hardware purchases, allocate re-
sources, and predict load for peak times or seasons.
What do we mean by “lightweight” profiling? Timing all SQL queries, plus the total
script execution time, is certainly cheap. And you don't have to do it for every page
view. If you have a decent amount of traffic, you can just profile a random sample by
enabling profiling in your application's setup file:
