Optimizer news: @@optimizer_switch syntax changes and backport

In short, the news are:

  • @@optimizer_switch support was backported into MySQL 5.1
  • The switch syntax was changed from ‘no_optimization_name’ to ‘optimization_name=on|off|default’.
  • Added switches for index_merge, index_merge_intersection, index_merge_union, and index_merge_sort_union optimizations.

The changes will be available in next releases, that is, MySQL 5.1.34 and 6.0.11.
Now with more details:

New switch names

Until now, the syntax mimicked an enum column or the @@sql_mode variable. One could set the value of @@optimizer_switch to a set of keywords, e.g.

SET @@optimizer_switch='no_semijoin,no_materialization';

Presence of a no_xxx keyword meant that its optimization was disabled, its absence meant it was disabled.

As of the next MySQL 5.1/6.0, @@optimizer_switch value is a set of on/off flags:

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                        |
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |

the SET statement accepts a list of commands:

SET [GLOBAL|SESSION] optimizer_switch='command,command,...'

where each command is one of

default reset all optimization settings to default
optimization_name=off disable the optimization
optimization_name=on enable the optimization
optimization_name=default set the optimization to its default state

The order of the commands does not matter (’default’ will be executed first if present), setting the same flag twice within one SET command is not allowed. Flags that are not mentioned keep their current values:

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                        |
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
1 row in set (0.00 sec)

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                          |
| index_merge=on,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=on |
1 row in set (0.00 sec)

The advantages of the new way over the old one are that

  1. It is now possible to turn certain optimization on/off with a single statement (SET optimizer_switch=’malfunctioning_optimization=off’) which will not depend on what other optimizer flags exist and what their values are.
  2. One can easily see what optimizer switches are available in the current server.
  3. In contrast to the grand solution of WL#4046, I could code and push this within a reasonable amount of time.

In addition, the mysqld binary got a –optimizer-switch parameter which allows to set optimizer_switch at server startup or in my.cnf file.

The backport

This is actually the reason for making all these changes. It turns out that the optimizer can make a wrong choice when considering whether to use index_merge optimization. This can happen for both valid (unknown data correlations) and not-so-valid (mismatch between cost model and the reality) reasons. A fix for either of these problems would be too intrusive to put into the GA version (betas and major releases were invented for a reason), and we also just do not have it yet. So, we’ve decided to provide at least some resolution for those for whom index_merge made things worse and introduced the following switches

@@optimizer_switch flags in MySQL 5.1
index_merge turns on/off the all index_merge optimizations
index_merge_union turn on/off individual index_merge algorithms (names as in the documentation)

MySQL 6.0 has the above switches and also subquery optimization switches:

@@optimizer_switch flags in MySQL 6.0
semijoin turns on/off the all semi-join optimizations
materialization turns on/off materialization (including semi-join materialization)
loosescan turns on/off semi-join LooseScan strategy (not to be confused with GROUP BY’s LooseScan)
firstmatch turns on/off semi-join FirstMatch strategy

All future optimizations will be switchable as well. We’ve learned the lesson.

Posted in Uncategorized on April 14th, 2009 by spetrunia | | 2 Comments

Sun Tech Days St. Petersburg 2009

Sun Tech Days St. Petersburg was on Wednesday-Thursday the last week and we’ve had a MySQL booth there. Unlike the last year, we’re full part of Sun now so managed to get a decent-sized booth, arrange for leaflets, and Kostja gave an overall MySQL talk.

Questions at the booth (in no particular order):

  • When will Connector.NET support LINQ Entity framework? (According to Reggie Burnett: it is currently supported in Connector 6.0/Beta, which is expected to be GA soon)
  • Can Connector.Net be used with Mono? (Yes)
  • Is Workbench available for Linux (Yes)
  • When will MySQL support stored procedures? This question seems to have replaced the infamous “When will MySQL support transactions” question.
  • What is the impact of different transaction isolation levels on performance of InnoDB? (no idea. If you ran some experiments please drop a comment)
  • When will MySQL support LIMIT clause inside subqueries, in particular the
    ... WHERE IN (SELECT ... ORDER BY LIMIT n) form? (we would like to add support for this, but no plans ATM. Request taken.)
  • Does MySQL have any limitations on table/database size or number of records in the table? (I gather people are used to having limitations in free versions of SQL Server or Oracle and expect something like that in MySQL)
  • When will fulltext search support searching for different wordforms (that’s a big deal for searching in Russian texts, as the words get different suffixes depending on which grammatic case they are in)? No plans ATM. Perhaps somebody has developed a fulltext parser plugin somewhere?
  • When will InnoDB get efficient support for COUNT(*)?
  • Are there any plans to make MySQL more efficient when handling big blob columns?

We’ve got several complaints that look like bugs:

  • LEFT JOIN and multi-table DELETE fails to delete records when using foreign keys. We’ve got a test case so I’ve filed this as BUG#44207.
  • There’s something wrong with Connector/Java and timezones. I’m not sure if we’ve managed to repeat the problem on our laptops, we were promised a bug report.
  • Another person complained about a sharp slowdown in join performance when table size(s) exceed 1M rows. According to the reporter, all buffers are adequately-sized, EXPLAIN shows that the query plan is the same. No idea what this could be then, as the SQL layer doesn’t have any hard-coded buffer sizes.

Also there was this Java duke guy:

and we’ve figured that it would be nice to get a MySQL dolphin the next year. And have the developer with biggest number of bugs in his code wear it :-) .

Posted in sun tech days on April 12th, 2009 by spetrunia | | 2 Comments