Databases Reference
In-Depth Information
We've shown only the first few lines in the profile, ranked in order of total response
time consumption, with the minimal set of columns that a profile ought to have. Each
row shows the response time as a total and as a percent of the overall total, the number
of times the query executed, the average response time per query, and an abstraction
of the query. This profile makes it clear how expensive each of these types of queries
is, relative to each other as well as to the whole. In this case, tasks are queries, which
is probably the most common way that you'll profile MySQL.
We will actually discuss two kinds of profiling:
execution-time profiling
and
wait
analysis
. Execution-time profiling shows which tasks consume the most time, whereas
wait analysis shows where tasks get stuck or blocked the most.
When tasks are slow because they're consuming too many resources and are spending
most of their time executing, they won't spend much time waiting, and wait analysis
will not be useful. The reverse is true, too: when tasks are waiting all the time and not
consuming any resources, measuring where they spend time executing won't be very
helpful. If you're not sure which kind of time consumption is the problem, you might
need to do both. We'll show some examples of that later.
In practice, when execution-time profiling shows that a task is responsible for a lot of
elapsed time, you might be able to drill into it and find that some of the “execution
time” is spent waiting, at some lower level. For example, our simplified profile above
shows that a lot of time is consumed by a
SELECT
against the
InvitesNew
table, but at a
lower level, that time might be spent waiting for I/O to complete.
Before you can profile a system, you need to be able to measure it, and that often
requires
instrumentation
. An instrumented system has measurement points where data
is captured, and some way to make the data available for collection. Systems that are
well-instrumented are rather uncommon. Most systems are not built with a lot of in-
strumentation points, and those that are often provide only counts of activities, and no
way to measure how much time those activities took. MySQL is an example of this, at
least until version 5.5 when the first version of the Performance Schema introduced a
few time-based measurement points.
4
Versions 5.1 and earlier of MySQL had practi-
cally no time-based measurement points; most of the data you could get about the
server's operation was in the form of
SHOW STATUS
counters, which simply count how
many times activities occur. That's the main reason we ended up creating Percona
Server, which has offered detailed query-level instrumentation since version 5.0.
Fortunately, even though our ideal performance optimization technique works best
with great instrumentation, you can still make progress even with imperfectly instru-
mented systems. It's often possible to measure the systems externally, or, failing that,
to make educated guesses based on knowledge of the system and the best information
available to you. However, when you do so, just be conscious that you're operating on
4. The Performance Schema in MySQL 5.5 doesn't provide query-level details; that is added in MySQL 5.6.
