Databases Reference
In-Depth Information
CHAPTER 3
Profiling Server Performance
The three most common performance-related requests we receive in our consulting
practice are to find out whether a server is doing all of its work optimally, to find out
why a specific query is not executing quickly enough, and to troubleshoot mysterious
intermittent incidents, which users commonly call “stalls,” “pileups,” or “freezes.” This
chapter is a direct response to those three types of requests. We'll show you tools and
techniques to help you speed up a server's overall workload, speed up a single query,
or troubleshoot and solve a problem when it's hard to observe, and you don't know
what causes it or even how it manifests.
This might seem like a tall order, but it turns out that a simple method can show you
the signal within the noise. That method is to focus on measuring what the server spends
its time doing, and the technique that supports this is called
profiling
. In this chapter,
we'll show you how to measure systems and generate profiles, and we'll show you how
to profile your whole stack, from the application to the database server to individual
queries.
But you must empty your cup before you can fill it, so let's dispel a few common mis-
conceptions about performance first. This gets a bit dense, so stay with us and we'll
explain it all with examples later.
Introduction to Performance Optimization
Ask 10 people to define performance and you'll probably get 10 different answers,
filled with terms such as “queries per second,” “CPU utilization,” “scalability,” and so
on. This is fine for most purposes, because people understand performance differently
in different contexts, but we will use a formal definition in this chapter. Our definition
is that performance is measured by the time required to complete a task. In other words,
performance is response time
. This is a very important principle. We measure
performance by tasks and time, not by resources. A database server's purpose is to
execute SQL statements, so the tasks we care about are queries or statements—the
