A proposal for method of delivering optimizer bug fixes

Working on query optimizer bugs can be a rather frustrating experience. First, as soon as some query doesn’t run as fast it theoretically could people will consider it a bug. On one hand that’s great, you get a constant stream of user input, but on the other hand you end up with a whole pile of “bugs” which you can’t hope to finish.

What’s more frustrating is that even if you manage to create a fix for an optimizer bug, there are chances it won’t be allowed into next GA (currently 5.0.70) or approaching-GA (currently 5.1.30) release (GA is our term for “stable” or “release”).

The reason behind this is that most optimizer bugfixes cause the optimizer to pick different query plans, and there’s no way to guarantee that the fix will be a change for the better for absolutely everyone. Experience shows that it is possible to have a query that hits two optimizer bugs/deficiencies at once in such a way that they cancel each other out, and get problems when one of the bugs is fixed. A more common scenario is when the optimizer makes the best choice but it just doesn’t have all the info. The top five unknowns are

  • data distributions
  • correlations between data columns
  • correlations between data value and physical record order
  • highly selective conditions on non-indexed columns
  • hot/cold caches

Neither of those can be easily checked, so we’re very conservative and have the “no query plan changes in GA versions” rule.

The problem is that it turns out our GA releases aren’t very frequent and one may have to wait a looong time before the fix makes it into official GA release. Bazaar and its ease of creation of publicly-accessible branches have rectified the situation a bit but most users want a binary and also we don’t want to end up with the need to maintain 2^N branches after N optimizer bugs.

The proposal

This aims at query optimizer (”here’s a query which uses a non-optimal plan”-type) bugfixes that affect a small amount of code in small number of places.

  • We’ll put the fix into both GA and next-after-GA versions.
  • For next-after-GA version, just put the fix in, do not support the old behavior. That’s the only feasible long-term option, we can’t afford to support all behavior we’ve had at some point in the past.
  • For the GA version, make it possible to switch new behavior on and off. The default should be the old behavior (so we only put one “if” into the old execution path. Can one hope that *that* won’t break anything?).

The mechanism to turn on the new behavior will be server command line option, something like --with-bugfix=NNNN. It’s possible to ask to turn on multiple bugfixes by using the option several times:

mysqld --with-bugfix=37642  --with-bugfix=13356

or, in my.cnf syntax:

[mysqld]
...
with-bugfix=13356
with-bugfix=27432
...

The code of GA versions doesn’t change much, so it should be tolerable to have, say, twenty “if (bugfix_nnn) {…} else {…}” branches. mysqld binary should only know numbers of bugs which it has switchable fixes for. If it is invoked with –with-bugfix=N where N is not a bug number it knows, it should print issue a warning, something like this:

[Warning] This version doesn't have ability to switch fix BUG#NNNN , see
[Warning]   http://bugs.mysql.com/check-version.php?binary_version=X.Y.Z&bug=NNNN.

Visiting the printed URL gets you to the bugs database which has information about which fixes appeared in which versions, so it can tell you whether your binary already has the fix for BUG#NNNN integrated into the code or you need to upgrade, in which case it can tell you what is the first version that has the needed bugfix.

-end of proposal-

Any comments or feedback on this scheme are welcome. Their impact will be greater if they arrive till September, 17. We’re having a developer meeting on Sept, 17-24 and I’ll try to get this discussed and some decision made about this.

Posted in ideas, bugfixes on September 9th, 2008 by spetrunia | | 17 Comments

EXPLAIN CONDITIONS patch available

I’ve made a patch that makes EXPLAIN show conditions that are attached to various points of the query plan. If you run EXPLAIN CONDITIONS (or EXPLAIN CONDS) statement, the output besides the usual EXPLAIN resultset will have a second resultset that will show

  • Conditions attached to individual tables
  • Conditions that are applied before/after join buffering
  • Table and index conditions that were pushed down into the storage engine
  • … and so forth (I believe it prints out all possible conditions that are there)

It looks like this:

mysql> explain conds select * from City, Country where City.Country=Country.Code and City.Name like 'C%' and Country.Continent='Asia' and Country.Population>5000000;
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
| id | select_type | table   | type | possible_keys     | key       | key_len | ref             | rows | Extra                              |
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
|  1 | SIMPLE      | Country | ref  | PRIMARY,Continent | Continent | 21      | const           |    1 | Using index condition; Using where |
|  1 | SIMPLE      | City    | ref  | Country           | Country   | 3       | db.Country.CODE |   18 | Using where                        |
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
2 rows in set (0.01 sec)

+—-+———+—————–+——————————–+
| id | table   | cond_type       | cond                           |
+—-+———+—————–+——————————–+
|  1 | Country | pushed_idx_cond | (Country.Continent = ‘Asia’)   |
|  1 | Country | where           | (Country.Population > 5000000) |
|  1 | City    | where           | (City.`Name` like ‘C%’)        |
+—-+———+—————–+——————————–+ 
3 rows in set (0.01 sec)

Unlike EXPLAIN EXTENDED, EXPLAIN CONDS doesn’t use excessive quoting or database prefixes before all columns. Excessive parentheses are still there, I have intent to remove them.

How you can get it:

Both the branch and the patch are made against mysql-6.0 tree. The code has some intersection with new 6.0 features, eg. it prints pushed index conditions, which are in 6.0 only, so the patch can’t be automatically applied to MySQL-5.x. The conflicts should be trivial though, the downport should be a question of removing all parts of the patch that break the compilation. If you need EXPLAIN CONDS in 5.x but can’t manage the downport, please let me know, perhaps I’ll be able to lend a hand.

Posted in Uncategorized on September 2nd, 2008 by spetrunia | | 3 Comments