Databases Reference
In-Depth Information
We've observed that many people, when trying to optimize something, spend the bulk
of their time changing things and very little time measuring. In contrast, we aim to
spend most of our time—perhaps upwards of 90%—measuring where the response
time is spent. If we don't find the answer, we might not have measured correctly or
completely. When we gather complete and properly scoped measurements about server
activity, performance problems usually can't hide, and the solution often becomes
trivially obvious. Measuring can be a challenge, however, and it can also be hard to
know what to do with the results once you have them—measuring where the time is
spent is not the same thing as understanding why the time is spent.
We mentioned proper scoping, but what does that mean? A properly scoped measure-
ment is one that measures only the activity you want to optimize. There are two com-
mon ways that you can capture something irrelevant:
• You can begin and end your measurements at the wrong time.
• You can measure things in aggregate instead of specifically targeting the activity
itself.
For example, a common mistake is to observe a slow query, and then look at the whole
server's behavior to try to find what's wrong. If the query is slow, then it's best to
measure the query, not the whole server. And it's best to measure from the beginning
of the query to the end, not before or after.
The time required to execute a task is spent either executing, or waiting. The best way
to reduce the time required to execute is to identify and measure the subtasks, and then
do one or more of the following: eliminate subtasks completely, make them happen
less often, or make them happen more efficiently. Reducing waiting is a more complex
exercise, because waiting can be caused by “collateral damage” from other activities
on the system, and thus there can be interaction between the task and other tasks that
might be contending for access to resources such as the disk or CPU. And you might
need to use different techniques or tools, depending on whether the time is spent ex-
ecuting or waiting.
In the preceding paragraph we said that you need to identify and optimize subtasks.
But that's an oversimplification. Infrequent or short subtasks might contribute so little
to overall response time that it's not worth your time to optimize them. How do you
determine which tasks to target for optimization? This is why profiling was invented.
