[Maria-developers] 5.3: optimizer status update and test requests
Hi Elena, A quick update on what has been happening in 5.3 optimizer recently. The text is FYI, requests for QA are marked with 'REQUEST': == Semi-joins: no non-PS crashes, optimizations refactoring == When I was fixing BUG#887468, I have made some refactorings to semi-join optimization code and pushed them into 5.3-main (revno: 3316, revision-id: psergey@askmonty.org-20111126082752-u8s9tv928lc67c8o) This change has grown to be more than just refactoring: - EXPLAIN now shows "Start temporary" at different position (documentation pending) - The optimizer may pick certain plans that were not picked before. REQUEST#1: please re-run semi-join optimization tests for the new server. == Discovery of effects of @@optimizer_prune_level == I have (yesterday) found https://bugs.launchpad.net/maria/+bug/898747. In a nutshell, default value of @@optimizer_prune_level=1 causes the optimizer to prune away some semi-join query plans. There can be different views about how serious BUG#898747 is, but its effect is that certain range of query plans were not tested before. REQUEST#2: re-run semi-join optimization tests with @@optimizer_prune_level=0 This can be joined with REQUEST#1 (however, if you find bugs, we're interested to know whether they have been present before the revno:3316 patch). To be continued... BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
On Thu, Dec 01, 2011 at 10:29:44PM +0400, Sergei Petrunia wrote:
To be continued... Continued:
== Optimizer debugging == We have discussed something like this in Athens and even before it. I've just implemented it, please take a look at http://kb.askmonty.org/en/mariadb-53-optimizer-debugging REQUEST: Can we get semi-join testing done with these settings: debug_optimizer_dupsweedout_penalized=ON optimizer_prune_level=0 REQUEST: I'm not exactly sure what to do with @@debug_optimizer_prefer_join_prefix, we both should think about how to use it (take a query, and try forcing all possible join orders?). At least, it should be easier to repeat the bugs now. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
REQUEST: I'm not exactly sure what to do with @@debug_optimizer_prefer_join_prefix, we both should think about how to use it (take a query, and try forcing all possible join orders?). At least, it should be easier to repeat the bugs now.
Elena, The reason I asked for this feature is because the RQG detected some transient semijoin issues that were not repeatable outside of the RQG. In particular, the RQG reported seeing EXPLAIN plans where "End Temporary" was before "Start Temporary", which should never happen. Sergey P reported that it is difficult to build an assertion for this situation that will make the server crash, so the only option is to find a repeatable MTR test case. However, STRAIGHT JOIN and FORCE INDEX are not enough to force a particular ordering of tables where semijoin is involved, as semijoin may place the tables from the subquery anywhere in the join order of the outer query. Hence the idea to force an order using a separate variable. The way to use this feature is to create a new Validator for the RQG that will 1. Run explain to obtain the names of participating tables 2. Use Bernt's code from combinations.pl --run-all-combinations-once to generate each possible join order, or at least move around just the semijoined tables; 3. Let the RQG execute the modified query and compare the result to the original join order This can be accomplished with a short Perl module, which would be a nice exercise in RQG internals. Let me know if you want to do it and I will help sort out the technicalities. Philip Stoev
BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
_______________________________________________ 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
participants (2)
-
Philip Stoev -
Sergei Petrunia