Xaprb

Much has been made of certain limitations of MySQL’s query optimizer (“planner”). Subqueries, lack of sort-merge joins, and so on. This is not MySQL-bashing and no one should be offended. Some people have worked to make things better, and that code is in branches that were intended for future releases. But if that work were available right now, would you trust it?

This question is important because the optimizer is complex and full of compromises and black magic. Even minor changes occasionally have weird edge cases that cause a regression in some workload. Are major changes trustworthy?

I’ll give a specific example. In version 5.0, MySQL gained the ability to use more than one index for a query. This is called index_merge in EXPLAIN, and sometimes people think it’s the best thing ever. In practice, I can say two general things about queries that use an index_merge plan:

1. If the optimizer chooses an index_merge, it is a fair guess that there are no good indexes for the query, and it’s making the best of a bad situation.
2. The optimizer has no way to model the cost of an index_merge operation, and sometimes underestimates the cost so badly that even a full table scan can be faster. Such queries are often much faster when rewritten, for example, as a UNION. (This was the workaround in pre-5.0 anyway.)

As a result, queries that use index_merge can usually be flagged as “bad queries” without much further thought. Queries that really benefit from index_merge are relatively rare edge cases. A “more advanced” optimizer tactic turns out, by and large, to be a heuristic for when you need to rewrite your queries to a simpler form that MySQL 4.1 could optimize well.

The video of my 5-minute Ignite talk on XtraDB is up on YouTube. Ignite talks have exactly 20 slides on auto-advance every 15 seconds. “Enlighten us, and make it fast!” It’s better than a lightning talk. I explained the difference between Percona XtraDB and other technologies such as standard MySQL, OurDelta, and MariaDB.

Some of the utilities we were adding to Maatkit really did not belong there. Yes, this included some of the functionality in the now-retired mk-audit tool. We really learned a lesson about what it’s possible to support, design, spec, code, and test in a single tool.

I’ve moved those tools to a new project, Aspersa. Some folks are revolting and calling it Asparagus, because apparently that’s easier to say. Aspersa is the name of the common garden snail, which turns out to be a fascinating creature. It is also slow. Draw your own conclusions.

This project is more of a home for simple scripts and snippets — a simple place I can grab all the little utilities I use to make my life easy. There is a “summary” tool that largely replaces mk-audit’s functionality outside the database, and I plan to add a “mysql-summary” tool to summarize the database.

I don’t plan to make “releases.” You get the tools with wget directly from SVN trunk. There is no separate website, and little to no documentation, but there is a mailing list, and you’re invited to join and contribute.