Databases Reference
In-Depth Information
It looks like the query did create three temp tables after all, which was somewhat hidden
from view in
SHOW PROFILE
(perhaps due to a subtlety in the way the server executed
the query). Two of the temp tables were on disk. And we're shortening the output here
for readability, but toward the end, the
SHOW PROFILE
data for this query is actually
written to the log, so you can even log that level of detail in Percona Server.
As you can see, this highly verbose slow query log entry contains just about everything
you can see in
SHOW PROFILE
and
SHOW STATUS
, and then some. This makes the log a very
useful place to look for more detail when you find a “bad” query with
pt-query-digest
.
When you're looking at a report from
pt-query-digest
, you'll see a header line such as
the following:
# Query 1: 0 QPS, 0x concurrency, ID 0xEE758C5E0D7EADEE at byte 3214 _____
You can use the byte offset from the header to zoom right into that section of the log,
like this:
tail -c +3214 /path/to/query.log | head -n100
And presto, you can look at all the details. By the way,
pt-query-digest
understands all
the added name-value pairs in the Percona Server slow query log format, and auto-
matically prints out a much more detailed report as a result.
Using the Performance Schema
At the time of writing, the Performance Schema tables introduced in MySQL 5.5 don't
support query-level profiling. The Performance Schema is rather new and in rapid de-
velopment, with much more functionality in the works for future releases. However,
even MySQL 5.5's initial functionality can reveal interesting information. For example,
here's a query that shows the top causes of waiting in the system:
mysql>
SELECT event_name, count_star, sum_timer_wait
->
FROM events_waits_summary_global_by_event_name
->
ORDER BY sum_timer_wait DESC LIMIT 5;
+----------------------------------------+------------+------------------+
| event_name | count_star | sum_timer_wait |
+----------------------------------------+------------+------------------+
| innodb_log_file | 205438 | 2552133070220355 |
| Query_cache::COND_cache_status_changed | 8405302 | 2259497326493034 |
| Query_cache::structure_guard_mutex | 55769435 | 361568224932147 |
| innodb_data_file | 62423 | 347302500600411 |
| dict_table_stats | 15330162 | 53005067680923 |
+----------------------------------------+------------+------------------+
There are a few of things that limit the Performance Schema's use as a general-purpose
profiling tool at present. First, it doesn't yet provide the level of detail on query exe-
cution stages and timing that we've been showing with existing tools. Second, it hasn't
been “in the wild” for all that long, and the implementation has more overhead at
present than many conservative users are comfortable with. (There is reason to believe
this will be fixed soon.)
