Databases Reference
In-Depth Information
Finally, it's sometimes too complex and low-level to be accessible to most users in its
raw form. The features implemented so far are mostly targeted toward the things we
need to measure when modifying MySQL source code to improve the server's perfor-
mance. This includes things like waits and mutexes. Some of the features in MySQL
5.5 are valuable to power users as opposed to server developers, but those still need
some frontend tool development to make it convenient to use them and interpret the
results. Right now the state of the art is writing complex queries against a large variety
of metadata tables with lots and lots of columns. It's a pretty intimidating amount of
instrumentation to navigate and understand.
When the Performance Schema gets more functionality in MySQL 5.6 and beyond, and
there are nice tools to use it, it's going to be awesome. And it's really nice that Oracle
is implementing it as tables accessible through SQL so that users can consume the data
in whatever manner is most useful to them. For the time being, though, it's not quite
a workable replacement for the slow query log or other tools that can help us imme-
diately see how to improve server and query performance.
Using the Profile for Optimization
So you've got a profile of your server or your query—what do you do with it? A good
profile usually makes the problem obvious, but the
solution
might not be (although it
often is). At this point, especially when optimizing queries, you need to rely on a lot of
knowledge about the server and how it executes queries. The profile, or as much of one
as you can gather, points you in the right direction and gives you a basis for using further
tools, such as
EXPLAIN
, to apply your knowledge and measure the results. That's a topic
for future chapters, but at least you have the right starting point.
In general, although a profile with complete measurements ought to make determining
the problem trivial, we can't always measure perfectly because the systems we're trying
to measure don't support it. In the example we've been looking at, we suspect that
temporary tables and unindexed reads are contributing most of the response time to
the query, but we can't prove it. Sometimes problems are hard to solve because you
might not have measured everything you need, or your measurements might be badly
scoped. You might be measuring server-wide activity instead of looking specifically at
what you're trying to optimize, for example, or you might be looking at measurements
that count from a point in time before your query started to execute, rather than the
instant the query began.
There's another possibility. Suppose you analyze your slow query log and find a simple
query that took an unreasonably long time to execute a handful of times, although it
ran quickly thousands of other times. You run the query again, and it is lightning fast,
as it should be. You use
EXPLAIN
, and it is using an index correctly. You even try similar
queries with different values in the
WHERE
clause to ensure you aren't just seeing cache
hits, and they run quickly. Nothing seems to be wrong with this query. What gives?
