Xaprb

I caught most of the second day of MySQL Camp 2007. It was fun and educational as before. The format was a little different than the last Camp; everything was in one room. Google and Proven Scaling provided food.

Sessions were loosely organized, to say the least, but that’s what an un-conference is all about. When I got there, Ronald Bradford was presenting on MySQL Proxy. Bob Stein, creator of Visibone charts and cheat-sheets, followed with a session seeking feedback to improve the charts. By the way, the way he produces those charts is totally off the wall. Jay Pipes gave an extended tutorial on ways to make MySQL perform really badly. After lunch, I gave sort of a stand-up talk on MySQL Toolkit, which I typed up while listening to the other talks. I tried to give an overview of what the tools in the toolkit are, how they work, and what to use them for. A couple other people showed some of their own tools after that too.

Then I went to supper in Manhattan with a bunch of old and new friends from the MySQL community, played some card games, and that’s it. Worth every minute!

I’m going to try to help Jay organize the next camp in central Virginia at the University of Virginia in Charlottesville. I think the tentative plan is early next May or something like that. I’m sure that is all subject to change.

Download MySQL Toolkit

I’ve just released changes to all tools in MySQL Toolkit. The biggest changes are in MySQL Table Sync, which I’m beginning to give sane defaults and options to. Some of the changes are incompatible (but that’s what you get with MySQL Table Sync, which is still very rough). I also found and fixed some bugs with MySQL Visual Explain. Thanks to everyone who submitted bug reports.

Note, the formatting overflow in MySQL Query Profiler was not a security vulnerability. It was simply an issue with a Perl formatting code that displayed numbers as hash marks when they got big enough.

Here’s the whole changelog:

Changelog for mysql-archiver:

2007-08-23: version 1.0.1

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-deadlock-logger:

2007-08-23: version 1.0.3

   * MySQL socket connection option didn't work.
   * Added --askpass option.
   * Truncated output could crash on an undefined regex result.
   * Made --source and --dest accept bareword hostnames.
   * Made DBI errors only print once.

Changelog for mysql-duplicate-key-checker:

2007-08-23: version 1.0.5

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-find:

2007-08-23: version 0.9.4

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-query-profiler:

2007-08-23: version 1.1.3

   * MySQL socket connection option didn't work.
   * Large queries overflowed the formatting room available.

Changelog for mysql-show-grants:

2007-08-23: version 1.0.3

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-slave-delay:

2007-08-23: version 1.0.0

   * MySQL socket connection option didn't work.
   * Added a check that the server is a slave.

Changelog for mysql-slave-restart:

2007-08-23: version 1.0.0

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-table-checksum:

2007-08-23: version 1.1.13

   * MySQL socket connection option didn't work.
   * Added --askpass option.

Changelog for mysql-table-sync:

2007-08-23: version 0.9.6

   * Added --askpass option.
   * Changed --replicate option to --synctomaster.
   * Fixed the MySQL socket option.
   * Made --synctomaster able to connect to the master from SHOW SLAVE STATUS.
   * MySQL socket connection option didn't work.
   * Suppress duplicated error messages from MySQL.
   * Changed DSN from URL-ish format to key=value format.
   * Generated WHERE clauses weren't properly isolated in parentheses.
   * Changed exit status to 0 when --help is given.
   * Made --replicate imply --wait 60.

Changelog for mysql-visual-explain:

2007-08-23: version 1.0.1

   * MySQL socket connection option didn't work.
   * Added --askpass option.
   * UNIONs inside a SUBQUERY weren't correctly nested.
   * Some types of impossible queries weren't handled.

Download MySQL Visual Explain

If you’ve ever wished you could see MySQL’s EXPLAIN output formatted as a tree, now you can. MySQL Visual Explain transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand.

What it does

MySQL Visual Explain is a command-line tool, not a Graphical User Interface (GUI). You can use it two ways:

• Give it a query and some connection options, and it will connect and EXPLAIN the query, then show you the result as a tree.
• Give it the output of EXPLAIN in any of several formats, and it will parse it and turn it into a tree.

Here’s a simple example. Given the following query,

select actor_id,
   (select count(film_id) from sakila.film join sakila.film_actor using(film_id))
from sakila.actor;

You get this EXPLAIN output:

+----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+
| id | select_type | table      | type  | possible_keys  | key                | key_len | ref                 | rows | Extra       |
+----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+
|  1 | PRIMARY     | actor      | index | NULL           | PRIMARY            | 2       | NULL                |  200 | Using index | 
|  2 | SUBQUERY    | film       | index | PRIMARY        | idx_fk_language_id | 1       | NULL                |  951 | Using index | 
|  2 | SUBQUERY    | film_actor | ref   | idx_fk_film_id | idx_fk_film_id     | 2       | sakila.film.film_id |    2 | Using index | 
+----+-------------+------------+-------+----------------+--------------------+---------+---------------------+------+-------------+

MySQL Visual Explain turns this into the following query execution plan:

SUBQUERY
+- JOIN
|  +- Index lookup
|  |  key            film_actor->idx_fk_film_id
|  |  possible_keys  idx_fk_film_id
|  |  key_len        2
|  |  ref            sakila.film.film_id
|  |  rows           2
|  +- Index scan
|     key            film->idx_fk_language_id
|     possible_keys  PRIMARY
|     key_len        1
|     rows           951
+- Index scan
   key            actor->PRIMARY
   key_len        2
   rows           200

You should read this as a depth-first tree traversal. In other words, the root of the tree is the output node — the last thing that happens in query execution.

As I said, this is a simple example. When your queries have many subqueries and/or UNIONs, you quickly get much more complicated EXPLAIN output, which is very hard to understand. It’s much easier to read the tree representation for complex queries. Here are thumbnails of the query execution plans of some real queries from a project I’ve worked on:

The corresponding EXPLAIN output is very hard to understand, even though I’ve become an expert on EXPLAIN. I can understand the tree view without trouble. I don’t think it matters how much of an expert I am, a tree view is always going to be easier to understand.

How it works

MySQL Visual Explain tries to reverse-engineer EXPLAIN by re-ordering and dividing the input into parent/child relationships. How exactly it happens is complex, though it can be expressed in code fairly succinctly. I wanted to write this tool nearly a year ago, but after studying EXPLAIN for a while, I found I just didn’t understand it well enough. Then this spring at the MySQL Conference and Expo 2007, I saw a talk on the Query Optimizer by Timour Katchaounov, who works on the optimizer team. Timour helped me grasp how MySQL executes queries. Most importantly, he showed a diagram of the execution plan as a left-deep tree. I always assumed the query execution plan was a bushy tree, but once I saw the left-deep tree I understood more about how MySQL works (for example, I now understand why MySQL doesn’t support FULL OUTER JOIN).

The crucial bridge between that and understanding EXPLAIN was a slide that showed the nodes of the tree mapped to rows in EXPLAIN. This made me see how to approach the problem. After spending hours reading the MySQL manual and source code, and studying many examples, I slowly understood how to go backwards from EXPLAIN to a tree.

It is not as simple as it sounds! There are many details, such as how to decide which nodes should be children of which other nodes (when there are no subqueries or UNIONs, of course it’s trivial). EXPLAIN’s output can be quite complex, and a moderately complicated query takes me a few minutes to reverse-engineer by hand — and most of the time I get it wrong. Maybe I can write the details in another blog post, or contribute them to MySQL’s documentation or the MySQL Forge wiki.

Download it

You can get the goodies from the Sourceforge MySQL Toolkit project page and read the documentation online at the MySQL Toolkit homepage.

If you find bugs

I couldn’t have written this tool without unit tests, especially since I had to start over twice when I found I was misunderstanding something major (that’s a huge plug for test-driven development). If you find a query it transforms wrong, please report it via the project’s Sourceforge bug tracking system. Please give the query and EXPLAIN output, so I can add it to the test suite.

Future plans

MySQL themselves, and many community members, have sometimes discussed the need for a tree view of EXPLAIN. I sincerely hope they implement that feature and make this little tool obsolete in future versions of MySQL. I also understand MySQL is trying to add more information to EXPLAIN. Currently it’s not possible to get a complete query execution plan from EXPLAIN, because it doesn’t show you everything the server does while executing the query. If MySQL adds information, I’ll update this tool. I have a feature request pending to show when a GROUP BY happens, for example.

I also specifically wrote this tool to be useful as a module, not just a command-line utility. This makes it possible for you to use the module in your own programs. I have a few ideas for this myself, though I may not get time to implement them.

Your support appreciated

Though the end result makes it look easy, this was a seriously hard project that took many evenings and weekends of research, testing, and coding, with many false starts. If you feel inclined, there’s a “support” link in the navigation bar at the top of this page! (For those of you who tried to send me something and it got returned, I’ve fixed that issue).