maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hello,

Please find below descriptions of problems with current @@optimizer_switch
variable, as well as a proposal on how to fix it.

<contents>
Problems with @@optimizer_switch
Proposed solution
  Details about hooking this into parser
Alternate approach1: grouping
</contents>

Problems with @@optimizer_switch
--------------------------------

@@optimizer_switch variable use has outgrown its syntax. There are
too many settings to fit on the single line:


MariaDB [(none)]> select @@optimizer_switch;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The line is now 510 characters long. 

It's hard to find the flag you're looking for, and one needs to write 
regular expressions if they want to check the value of a particular flag in
some program.

There is no easy way to get settings for a subset of flags (which means we've 
banned "select @@optimizer_switch" from almost all .test files, because one
had to change .result file all the time)

The extra-long line also gets into "SHOW VARIABLES" output, and makes it
hard-to-read, too.

The situation is going to get worse, as we've already committed to adding even
to add even more switches (Igor's patch adds some, then we've decided that two semi-join optimizations
that do not have their switches, should have them)

The benefits of having all settings grouped together are:

- It was easy to implement 

- One can easily see all optimizer switches without having to fish them out of
  the list of all server variables.

- One can reset all optimizer settings with a single statement:

    SET @@optimizer_switch=default

Proposed solution
-----------------

Break optimizer_switch into multiple variables, with names having the dots
in them. That is, current @@optimizer_switch flags will change into this set
of variables:

  optimizer.index_merge=on
  optimizer.index_merge.union=on
  optimizer.index_merge.sort_union=on
  optimizer.index_merge.intersection=on
  optimizer.index_merge.sort_intersection=off
  optimizer.index_condition_pushdown=on
  optimizer.join_cache.bka=on
  optimizer.join_cache.hashed=on
  optimizer.join_cache.incremental=on
  optimizer.join_cache.optimize_buffer_size=on
  optimizer.join_cache.outer_join=off
  optimizer.join_cache.semijoin=off
  optimizer.mrr=on
  optimizer.mrr.cost_based=off
  optimizer.mrr.sort_keys=on
  optimizer.semijoin=on
  optimizer.semijoin.firstmatch=on
  optimizer.semijoin.loosescan=on
  optimizer.subquery.cache=on
  optimizer.subquery.in_to_exists=on
  optimizer.subquery.materialization=off
  optimizer.subquery.partial_match_rowid_merge=on
  optimizer.subquery.partial_match_table_scan=on
  optimizer.table_elimination=on


The dots are there so that 
- the structuring is more apparent
- it is possible to reset groups of variables to their defaults. Current idea
  is to use syntax like this:

  SET optimizer.semijoin.*= default;

- in the future we may also allow to select and set groups of settings.


Details about hooking this into parser
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Currently, dotted syntax is allowed to some extent: we allow

- GLOBAL/SESSION/DEFAULT prefix for any variable 
- key_cache_name prefix for multiple named key caches. There is one key cache
  named default, it is not possible to make a key cache named "session".

To make variable lookup rules easy, we will diallow creation/use of key caches
named 'optimizer'.

Overall, system variables can be encountered 
 - as part of @@variable syntax when their value is selected
 - as part of SET statement.

in both cases, bison can easily see it's parsing a system variable name, so I
was able to add support for dotted variable syntax into the parser without
introducing any extra conflicts.

Alternate approach1: grouping
-----------------------------

(Suggested by Igor (or was it Timour?))

Replace one @@optimizer_switch variable with multiple:

  @@optimizer_switch_semijoin
  @@optimizer_switch_mrr 

.. and so forth. 

The advantage of this approach is that it's easy and we don't need to change
the parser.

The disadavantage is that it may turn out that groups of flags may be still 
too big, and so either look ugly or require further splits.

BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog