Databases Reference
In-Depth Information
potentially flawed data, and your guesses are not guaranteed to be correct. This is a
risk that you usually take when you observe systems that aren't perfectly transparent.
For example, in Percona Server 5.0, the slow query log can reveal a few of the most
important causes of poor performance, such as waiting for disk I/O or row-level locks.
If the log shows 9.6 seconds of disk I/O wait for a 10-second query, it's not important
to find out where the remaining 4% of the response time went. The disk I/O is clearly
the most important problem.
Interpreting the Profile
The profile shows you the most important tasks first, but what it doesn't show you can
be just as important. Refer to the example profile we showed earlier. Unfortunately,
there's a lot that it conceals, because all it shows is ranks, sums, and averages. Here's
what's missing:
Worthwhile queries
The profile doesn't automatically show you which queries are worth your time to
optimize. This brings us back to the meaning of optimization. If you read Cary
Millsap's book, you'll get a lot more on this topic, but we'll repeat two salient
points. First, some tasks aren't worth optimizing because they contribute such a
small portion of response time overall. Because of Amdahl's Law, a query that
consumes only 5% of total response time can contribute only 5% to overall
speedup, no matter how much faster you make it. Second, if it costs you a thousand
dollars to optimize a task and the business ends up making no additional money
as a result, you just deoptimized the business by a thousand dollars. Thus, opti-
mization should halt when the cost of improvement outweighs the benefit.
Outliers
Tasks might need to be optimized even if they don't sort to the top of the profile.
If an occasional task is very slow, it might be unacceptable to users, even though
it doesn't happen often enough to constitute a significant portion of overall re-
sponse time.
Unknown unknowns
5
A good profiling tool will show you the “lost time,” if possible. Lost time is the
amount of wall-clock time not accounted in the tasks measured. For example, if
you measure the process's overall CPU time as 10 seconds, but your profile of
subtasks adds up to 9.7 seconds, there are 300 milliseconds of lost time. This can
be an indication that you're not measuring everything, or it could just be unavoid-
able due to rounding errors and the cost of measurement itself. You should pay
attention to this, if the tool shows it. You might be missing something important.
If the profile doesn't show this, you should try to be conscious of its absence and
5. With apologies to Donald Rumsfeld. His comments were actually very insightful, even if they sounded
funny.
