[Maria-developers] Proposal to split @@optimizer_switch
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
Hi, Sergey! On Jun 10, Sergey Petrunya wrote:
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:
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
Assorted thoughts: * I'd suggest to do any changes not in 5.3 but in 5.5, in the new sys_vars code * hierarchical structure of settings is good * but it has to be done the drizzle way, I mean, all options converted at once (you cannot switch the country from right-hand traffic to left-hand, by switching only few roads or few cars first :) * on the other hand, we don't want to break the backward compatibility, so there's a contradiction * and we want to be compatible with MySQL, right? * one possibile solution: introduce dots and hierarchy, but for the purpose of name matching, compare them as underscores. have an option to choouse between printing dots or underscores in show variables. * another solution, create hierarchies implicitly, splitting on underscores. Rename variables that don't fit into the correct hierarchy, keep old names as obsolete aliases, remove them later. Allow user to write SET optimizer_semijoin_*= default Frankly speaking, I'd prefer a dot as a separator, not an underscore. * you forgot saving and restoring the value of a group. Now one can save @@optimizer_switch in a variable and restore it later. How to do it with hierarchies? * named keycaches once again will be a problem, the syntax is so out of line, it never fits anywhere Regards, Sergei
On Fri, Jun 10, 2011 at 12:50:34PM +0200, Sergei Golubchik wrote:
Hi, Sergey!
On Jun 10, Sergey Petrunya wrote:
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:
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
Assorted thoughts:
* I'd suggest to do any changes not in 5.3 but in 5.5, in the new sys_vars code
* hierarchical structure of settings is good
* but it has to be done the drizzle way, I mean, all options converted at once (you cannot switch the country from right-hand traffic to left-hand, by switching only few roads or few cars first :)
I don't see how this analogy works. I understand that a switched car will collide with non-switched car. If we switch only optimizer variables, what will that collide with?
* on the other hand, we don't want to break the backward compatibility, so there's a contradiction
* and we want to be compatible with MySQL, right?
Yes.
* one possibile solution: introduce dots and hierarchy, but for the purpose of name matching, compare them as underscores. have an option to choouse between printing dots or underscores in show variables. (replied in another email)
* another solution, create hierarchies implicitly, splitting on underscores. Rename variables that don't fit into the correct hierarchy, keep old names as obsolete aliases, remove them later.
If there was a direct mapping from underscores to dots, there would have been no point in the dot notation. There is no point in changing @@time_zone to @@time.zone or @@table_definition_cache to table.definition.cache.
Allow user to write SET optimizer_semijoin_*= default Frankly speaking, I'd prefer a dot as a separator, not an underscore.
* you forgot saving and restoring the value of a group. Now one can save @@optimizer_switch in a variable and restore it later. How to do it with hierarchies?
We've rejected the SET optimizer.xxx.yyy.*=... syntax in favor of SET optimizer.prefix=...
* named keycaches once again will be a problem, the syntax is so out of line, it never fits anywhere
I think that disallowing a few names for named key caches ('optimizer', 'aria','innodb') is not a big problem? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
Hi, Sergey! On Jun 27, Sergey Petrunya wrote:
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
Assorted thoughts: ... * another solution, create hierarchies implicitly, splitting on underscores. Rename variables that don't fit into the correct hierarchy, keep old names as obsolete aliases, remove them later.
If there was a direct mapping from underscores to dots, there would have been no point in the dot notation. There is no point in changing @@time_zone to @@time.zone or @@table_definition_cache to table.definition.cache.
sure. I didn't like that solution either.
* named keycaches once again will be a problem, the syntax is so out of line, it never fits anywhere
I think that disallowing a few names for named key caches ('optimizer', 'aria','innodb') is not a big problem?
No. And I didn't mean that it is. The above was in the "assorted thoughts" list, after all :) But really, I'd prefer to introduce a new syntax for named keycaches. Something more in line with other variables and more future proof. Regards, Sergei
On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote:
Hello,
Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it.
Results of IRC discussion on June, 22 (as I interpret them): Everyone agrees that we need to keep current @@optimizer_switch for compatibility reasons (it was actually present as early as in MySQL 5.1) Monty considers the "SET optimizer.xxx.yyy.*" syntax strange, it's better to allow selecting/setting of any prefix of the variable name. There is no opposition to this. It is okay to do implementation in steps - first support the dotted syntax and SET xxx.yy.prefix=DEFAULT, and then support setting/selecting arbitrary prefixes. Sergei sees the goal as "introducing a consistent hierarchical naming system for variables". That is, - every implicit variable group, like aria_ or myisam_ should be switched to the dotted notation right away. - Old variable names should be supported, too. In order to meet the second, we could A. Have both kinds of variables (dotted and un-dotted) be settable with SET and listed in SHOW VARIABLES - Plus: straightforward, compatible. - Minus: having everything listed twice under two names is confusing. B. Introduce some scheme where we have '.'=='_' when comparing variable names. SHOW VARIABLES output will be controlled by @@SHOW_VARIABLES_USES_UNDERSCORE/ - Plus: we could use a dotted notation while some of the scripts that use "SHOW VARIABLES LIKE setting" would still work. (As long as they don't try comparing the names of the variables they got:) - Minus: The scheme is difficult to understand. Also, our intent is not always to change @@xxx_yyy_zzz to @@@xxx.yyy.zzz, e.g. we don't need @@table.definition.cache or @@system.time.zone. C. Allow SET to set both old and new names, and use some setting to control whether SHOW VARIABLES will display old, new, or both kinds of variables. The question is, what should be the default setting. Sergei thinks that "optimizer switch can survive in its current form for a while", to which SergeyP strongly disagrees. We need to discuss whether we should follow Sergei's suggestion of global system of hierarchical names or focus on @@optimizer_switch only. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
-----Original Message----- From: maria-developers- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- developers-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Sergey Petrunya Sent: Montag, 27. Juni 2011 15:28 To: maria-developers@lists.launchpad.net Subject: Re: [Maria-developers] Proposal to split @@optimizer_switch
On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote:
Hello,
Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it.
I would actually prefer more traditional underscore syntax, just for the sake of uniform parameter handling. Whether the dotted notation is better readable that underscore one, is mostly matter of individual taste. Besides, I would remove underscore from some already existing underscored keyword (this also eliminated differences between semijoin and outer_join for example). Stuff like indexconditionpushdown becomes unreadable, perhaps can be "icp", it is referred as ICP everywhere anyway, and there are other abbreviations already in place, like mrr and bka. With this proposal , keywords would look like : optimizer_indexmerge=on optimizer_indexmerge_union=on optimizer_indexmerge_sortunion=on optimizer_indexmerge_intersection=on optimizer_indexmerge_sortintersection=off optimizer_icp=on optimizer_joincache_bka=on optimizer_joincache_hashed=on optimizer_joincache_incremental=on optimizer_joincache_optimizebuffersize=on optimizer_joincache_outerjoin=off optimizer_joincache_semijoin=off optimizer_mrr=on optimizer_mrr_costbased=off optimizer_mrr_sortkeys=on optimizer_semijoin=on optimizer_semijoin_firstmatch=on optimizer_semijoin_loosescan=on optimizer_subquery_cache=on optimizer_subquery_intoexists=on optimizer_subquery_materialization=off optimizer_subquery_partialmatch_rowidmerge=on optimizer_subquery_partialmatch_tablescan=on optimizer_tableelimination=on
On Tue, Jun 28, 2011 at 01:20:07AM +0200, Vladislav Vaintroub wrote:
-----Original Message----- From: maria-developers- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- developers-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Sergey Petrunya Sent: Montag, 27. Juni 2011 15:28 To: maria-developers@lists.launchpad.net Subject: Re: [Maria-developers] Proposal to split @@optimizer_switch
On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote:
Hello,
Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it.
I would actually prefer more traditional underscore syntax, just for the sake of uniform parameter handling. Whether the dotted notation is better readable that underscore one, is mostly matter of individual taste. Besides, I would remove underscore from some already existing underscored keyword (this also eliminated differences between semijoin and outer_join for example). Stuff like indexconditionpushdown becomes unreadable, perhaps can be "icp", it is referred as ICP everywhere anyway, and there are other abbreviations already in place, like mrr and bka.
With this proposal , keywords would look like :
optimizer_indexmerge=on optimizer_indexmerge_union=on optimizer_indexmerge_sortunion=on optimizer_indexmerge_intersection=on optimizer_indexmerge_sortintersection=off optimizer_icp=on optimizer_joincache_bka=on optimizer_joincache_hashed=on optimizer_joincache_incremental=on optimizer_joincache_optimizebuffersize=on optimizer_joincache_outerjoin=off optimizer_joincache_semijoin=off optimizer_mrr=on optimizer_mrr_costbased=off optimizer_mrr_sortkeys=on optimizer_semijoin=on optimizer_semijoin_firstmatch=on optimizer_semijoin_loosescan=on optimizer_subquery_cache=on optimizer_subquery_intoexists=on optimizer_subquery_materialization=off optimizer_subquery_partialmatch_rowidmerge=on optimizer_subquery_partialmatch_tablescan=on optimizer_tableelimination=on
Ok. What syntax should be used to set all optimizer (or optimizer.join_cache) settings to their defaults? Lots of programming languages use xxx.yyy notation to denote access to member yyy of some composite entity xxx, so when one writes optimizer.join_cache.bka it hints that 'optimizer' is an entity that you could operate on as a whole. If we use optimizer_joincache_bka than personally for me it is not clear that 'optimizer' is an entity I could operate on. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
-----Original Message----- From: Sergey Petrunya [mailto:psergey@askmonty.org] Sent: Dienstag, 28. Juni 2011 08:17 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net Subject: Re: [Maria-developers] Proposal to split @@optimizer_switch
On Tue, Jun 28, 2011 at 01:20:07AM +0200, Vladislav Vaintroub wrote:
Ok. What syntax should be used to set all optimizer (or optimizer.join_cache) settings to their defaults?
set optimizer_*=default set optimiizer_joincache_*=default
Lots of programming languages use xxx.yyy notation to denote access to member yyy of some composite entity xxx, so when one writes
optimizer.join_cache.bka
it hints that 'optimizer' is an entity that you could operate on as a whole. If we use
optimizer_joincache_bka
than personally for me it is not clear that 'optimizer' is an entity I could operate on.
Well, plugins variables use the underscore notation now, and plugins are entities. I do not find the dot notation bad, it just does not look consistent with the rest of variables. Wlad
If you want to you could create a magic variable called optimizer that would set the rest of the optimizer_* variables back to their default. I don't like the idea of introducing dot notation only in the optimizer variables without also changing things like query_cache_* or ft_* It also adds a hierarchy into variables that didn't really exist before. On Tue, Jun 28, 2011 at 5:26 AM, Vladislav Vaintroub <wlad@montyprogram.com> wrote:
-----Original Message----- From: Sergey Petrunya [mailto:psergey@askmonty.org] Sent: Dienstag, 28. Juni 2011 08:17 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net Subject: Re: [Maria-developers] Proposal to split @@optimizer_switch
On Tue, Jun 28, 2011 at 01:20:07AM +0200, Vladislav Vaintroub wrote:
Ok. What syntax should be used to set all optimizer (or optimizer.join_cache) settings to their defaults?
set optimizer_*=default set optimiizer_joincache_*=default
Lots of programming languages use xxx.yyy notation to denote access to member yyy of some composite entity xxx, so when one writes
optimizer.join_cache.bka
it hints that 'optimizer' is an entity that you could operate on as a whole. If we use
optimizer_joincache_bka
than personally for me it is not clear that 'optimizer' is an entity I could operate on.
Well, plugins variables use the underscore notation now, and plugins are entities.
I do not find the dot notation bad, it just does not look consistent with the rest of variables.
Wlad
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Eric Bergen eric.bergen@gmail.com http://www.ebergen.net
Hi, Sergey! On Jun 27, Sergey Petrunya wrote:
On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote:
Sergei sees the goal as "introducing a consistent hierarchical naming system for variables". That is, - every implicit variable group, like aria_ or myisam_ should be switched to the dotted notation right away. - Old variable names should be supported, too. In order to meet the second, we could ... C. Allow SET to set both old and new names, and use some setting to control whether SHOW VARIABLES will display old, new, or both kinds of variables. The question is, what should be the default setting.
I like this variant. The default may be underscores, otherwise GUI tools could become very confused.
Sergei thinks that "optimizer switch can survive in its current form for a while", to which SergeyP strongly disagrees.
I meant "for a while" = "till 5.5", that is, the change shouldn't be done in 5.3. Regards, Sergei
participants (4)
-
Eric Bergen -
Sergei Golubchik -
Sergey Petrunya -
Vladislav Vaintroub