Comments on: A proposal for method of delivering optimizer bug fixes http://s.petrunia.net/blog/?p=48 Random observations made while working on MySQL / MariaDB query optimizer Thu, 09 Sep 2010 04:29:55 +0000 http://wordpress.org/?v=2.0.12-alpha by: sergey http://s.petrunia.net/blog/?p=48#comment-32610 Thu, 11 Sep 2008 09:39:26 +0000 http://s.petrunia.net/blog/?p=48#comment-32610 Mark, > When will we get optimizer tracing? That would make it much easier to diagnose these problems. ATM we don't have any plans we've committed to but better diagnostics is high in our list of desired features. Mark,

> When will we get optimizer tracing? That would make it much easier to diagnose these problems.

ATM we don’t have any plans we’ve committed to but better diagnostics is high in our list of desired features.

]]> by: sergey http://s.petrunia.net/blog/?p=48#comment-32607 Thu, 11 Sep 2008 09:15:17 +0000 http://s.petrunia.net/blog/?p=48#comment-32607 Peter, > –with-bugfix would be reasonable however I’m not sure how easy you would get it for all bug fixes - will not your turn your code to spaghetti causing development complication and potentially breaking some old code. We are not going to add this for every fix. We'll add it only 1) for query speed bugs (fixes for crashes will be on unconditionally) and 2) when that was requested by a paying customer or when the fix is assumed to be important enough for big set of people. > When fixes are behavior changes some variable configuration could be even better because it would be possible to change behavior on per connection basics. Yes.. perhaps a standard system/user variable would be better. Got to decide whether we need one variable for all or separate variables for each fix. > Another important thing I should note - when you guys add any methods to optimizer make sure it is possible to FORCE using this method when it is possible even if Optimizer does not think it is good. For example forcing to use index merge instead of single index. It would be an interesting challenge for e.g. subquery optimizations.. it's fairly straightforward to force materialization but there's no obvious way how one could specify that other strategies should [not] be used. Ok, we'll take another look at this. Peter,

> –with-bugfix would be reasonable however I’m not sure how easy you would get it for all bug fixes - will not your turn your code to spaghetti causing development complication and potentially breaking some old code.

We are not going to add this for every fix. We’ll add it only 1) for query speed bugs (fixes for crashes will be on unconditionally) and 2) when that was requested by a paying customer or when the fix is assumed to be important enough for big set of people.

> When fixes are behavior changes some variable configuration could be even better because it would be possible to change behavior on per connection basics.

Yes.. perhaps a standard system/user variable would be better. Got to decide whether we need one variable for all or separate variables for each fix.

> Another important thing I should note - when you guys add any methods to optimizer make sure it is possible to FORCE using this method when it is possible even if Optimizer does not think it is good. For example forcing to use index merge instead of single index.

It would be an interesting challenge for e.g. subquery optimizations.. it’s fairly straightforward to force materialization but there’s no obvious way how one could specify that other strategies should [not] be used. Ok, we’ll take another look at this.

]]> by: Jonas Oreland http://s.petrunia.net/blog/?p=48#comment-32548 Wed, 10 Sep 2008 07:51:16 +0000 http://s.petrunia.net/blog/?p=48#comment-32548 I think it sounds like a good/reasonable solution for the problem you describe. I think it sounds like a good/reasonable solution for the problem you describe.

]]> by: Lukas http://s.petrunia.net/blog/?p=48#comment-32520 Tue, 09 Sep 2008 21:29:16 +0000 http://s.petrunia.net/blog/?p=48#comment-32520 Sergey my point was that I think its messy and confusing and a deployment nightmare to make every little bit an option here. In the end it seems that this puts the burden on the developer to choose the right mix. I rather prefer if we optimizer would be provided in generations, where older generations are still available as one package in later versions (to be phased out eventually of course). Sergey my point was that I think its messy and confusing and a deployment nightmare to make every little bit an option here. In the end it seems that this puts the burden on the developer to choose the right mix. I rather prefer if we optimizer would be provided in generations, where older generations are still available as one package in later versions (to be phased out eventually of course).

]]> by: Vadim http://s.petrunia.net/blog/?p=48#comment-32519 Tue, 09 Sep 2008 21:14:11 +0000 http://s.petrunia.net/blog/?p=48#comment-32519 Sergey, I think --with-bugfix=12454 it may be too criptic. Imagine my.cnf with [mysqld] ... with-bugfix=13445 with-bugfix=13455 with-bugfix=13645 with-bugfix=13745 ... May be you can add descriptive name ? like with-bugfix="add index merge fix" etc ? Sergey,

I think
–with-bugfix=12454
it may be too criptic.

Imagine my.cnf with

[mysqld]
…
with-bugfix=13445
with-bugfix=13455
with-bugfix=13645
with-bugfix=13745
…

May be you can add descriptive name ?

like
with-bugfix=”add index merge fix”

etc ?

]]> by: Evert http://s.petrunia.net/blog/?p=48#comment-32509 Tue, 09 Sep 2008 17:06:41 +0000 http://s.petrunia.net/blog/?p=48#comment-32509 It would be good to see optimizer fixes released quicker.. I'm wondering if instead of --with-bugfix=1234, it might be better to use --without-bugfix=12345. I believe new fixes like this should be opt-out, instead of opt-in. It would be good to see optimizer fixes released quicker.. I’m wondering if instead of –with-bugfix=1234, it might be better to use –without-bugfix=12345.

I believe new fixes like this should be opt-out, instead of opt-in.

]]> by: Joro Kodinov http://s.petrunia.net/blog/?p=48#comment-32506 Tue, 09 Sep 2008 16:44:46 +0000 http://s.petrunia.net/blog/?p=48#comment-32506 This idea imho is will make the compatibility problems even worse. And the test suites even longer. I don't think it's wise to remedy organizational bottlenecks with technical fixes. The main problem here is the release cycle. Maybe the remedy is to "modularize" the server ? Like release certain components independently of others. Optimizer x.y.z, runtime a.b.c, etc. Note that it's not strictly speaking necessary to have the components in separate binaries. It's enough if people would know what to expect imho. This idea imho is will make the compatibility problems even worse. And the test suites even longer.
I don’t think it’s wise to remedy organizational bottlenecks with technical fixes. The main problem here is the release cycle.
Maybe the remedy is to “modularize” the server ? Like release certain components independently of others. Optimizer x.y.z, runtime a.b.c, etc.
Note that it’s not strictly speaking necessary to have the components in separate binaries. It’s enough if people would know what to expect imho.

]]> by: Mark Callaghan http://s.petrunia.net/blog/?p=48#comment-32505 Tue, 09 Sep 2008 16:04:58 +0000 http://s.petrunia.net/blog/?p=48#comment-32505 It isn't pretty but it would work for me. When will we get optimizer tracing? That would make it much easier to diagnose these problems. It isn’t pretty but it would work for me.

When will we get optimizer tracing? That would make it much easier to diagnose these problems.

]]> by: Peter Zaitsev http://s.petrunia.net/blog/?p=48#comment-32503 Tue, 09 Sep 2008 15:39:50 +0000 http://s.petrunia.net/blog/?p=48#comment-32503 Sergey, Thanks for looking into the problem. Indeed rate of optimizer bugfixes in 5.0 and 5.1 is poor and you surely have the challenge because you can't really validate your fixed on large variety of workloads until version is used by a lot of users but this is also the time when it becomes risky to change anything. --with-bugfix would be reasonable however I'm not sure how easy you would get it for all bug fixes - will not your turn your code to spaghetti causing development complication and potentially breaking some old code. When fixes are behavior changes some variable configuration could be even better because it would be possible to change behavior on per connection basics. Another important thing I should note - when you guys add any methods to optimizer make sure it is possible to FORCE using this method when it is possible even if Optimizer does not think it is good. For example forcing to use index merge instead of single index. Sergey,

Thanks for looking into the problem. Indeed rate of optimizer bugfixes in 5.0 and 5.1 is poor and you surely have the challenge because you can’t really validate your fixed on large variety of workloads until version is used by a lot of users but this is also the time when it becomes risky to change anything.

–with-bugfix would be reasonable however I’m not sure how easy you would get it for all bug fixes - will not your turn your code to spaghetti causing development complication and potentially breaking some old code.

When fixes are behavior changes some variable configuration could be even better because it would be possible to change behavior on per connection basics.

Another important thing I should note - when you guys add any methods to optimizer make sure it is possible to FORCE using this method when it is possible even if Optimizer does not think it is good. For example forcing to use index merge instead of single index.

]]> by: sergey http://s.petrunia.net/blog/?p=48#comment-32500 Tue, 09 Sep 2008 15:18:50 +0000 http://s.petrunia.net/blog/?p=48#comment-32500 Eric, > That’s an interesting idea. Is there a risk of causing further issues by having a mix of bug fixes enabled or disabled? Theoretically, yes there is, but it should be a rare case. I expect such issues to be repeatable "poor query plan"-type problems, and not e.g. random crashes. In any case, by the time you're playing with enabling fixes, you already have issues, so there's not much to lose. And if a particular combination of fixes doesn't work well, it will now be easy to move away from it. Eric,

> That’s an interesting idea. Is there a risk of causing further issues by having a mix of bug fixes enabled or disabled?

Theoretically, yes there is, but it should be a rare case. I expect such issues to be repeatable “poor query plan”-type problems, and not e.g. random crashes. In any case, by the time you’re playing with enabling fixes, you already have issues, so there’s not much to lose. And if a particular combination of fixes doesn’t work well, it will now be easy to move away from it.

]]>