Databases Reference
In-Depth Information
How Do You Know If Measurements Are Right?
If measurements are so important, then what if the measurements are wrong? In fact,
measurements are always wrong
. The measurement of a quantity is not the same as the
quantity itself. The measurements might not be wrong enough to make a big difference,
but they're wrong. So the question really should be, “How uncertain is the measure-
ment?” This is a topic that's addressed in great detail in other books, so we won't tackle
it here. Just be conscious that you're working with measurements, not the actual
quantities they represent. As usual, the measurements can be presented in confusing
or ambiguous ways, which can lead to wrong conclusions, too.
Optimization Through Profiling
Once you have learned and practiced the response time-oriented method of perfor-
mance optimization, you'll find yourself profiling systems over and over.
Profiling is the primary means of measuring and analyzing where time is consumed.
Profiling entails two steps: measuring tasks and the time elapsed, and aggregating and
sorting the results so that the important tasks bubble to the top.
Profiling tools all work in pretty much the same way. When a task begins, they start a
timer, and when it ends, they stop the timer and subtract the start time from the end
time to derive the response time. Most tools also record the task's parent. The resulting
data can be used to construct call graphs, but more importantly for our purpose, similar
tasks can be grouped together and summed up. It can be helpful to do sophisticated
statistical analysis on the tasks that were grouped into one, but at a minimum, you need
to know how many tasks were grouped together, and the sum of their response times.
The
profile report
accomplishes this. A profile report consists of a table of tasks, one
line per task. Each line shows a name, the number of times the task executed, the total
time consumed, the average time per execution, and what portion of the whole this
task consumed. The profile report should be sorted in order of total time consumed,
descending.
To make this clearer, let's look at a real profile of an entire server's workload, which
shows the types of queries that the server spends its time executing. This is a top-level
view of where the response time goes; we'll show others later. The following is from
Percona Toolkit's
pt-query-digest
tool, which is the successor to Maatkit's
mk-query-
digest
. We've simplified it slightly and included only the first few types of queries, to
remove distractions:
Rank Response time Calls R/Call Item
==== ================ ===== ====== =======
1 11256.3618 68.1% 78069 0.1442 SELECT InvitesNew
2 2029.4730 12.3% 14415 0.1408 SELECT StatusUpdate
3 1345.3445 8.1% 3520 0.3822 SHOW STATUS
