maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

I am working in a tool for people to run to autotune a query, and would like to use the info.  It could also be of use to the optimizer in shard query to make adaptive choices about query execution.

Sent from my iPhone

> On Feb 14, 2014, at 9:15 PM, Colin Charles <colin@xxxxxxxxxxx> wrote:
> 
> 
>> On 15 Feb 2014, at 13:01, Justin Swanhart <greenlion@xxxxxxxxx> wrote:
>> 
>> Workbench uses EXPLAIN FORMAT=JSON which is different from optimizer trace.  Optimizer trace allows us to very accurately determine what indexes were used, why ICP was picked, etc.  It allows us to see when things like the exists strategy is used for a subquery instead of materializing it.  Lots of very great stuff from it that is not available from EXPLAIN FORMAT=JSON.
> 
> i was under the impression that besides just EXPLAIN FORMAT=JSON, workbench could also display the optimizer trace info (from a presentation i saw on this @ mysql connect 2013). that said, i've never tried said feature with workbench (i don't generally use workbench)
> 
>> The optimizer trace information is available in a special information schema table.
> 
> yes, this i know. it is something we clearly are missing (besides, EXPLAIN FORMAT=JSON)
> 
> do you know any tools that make use of it though? depend on it? i mean usage within mysql 5.6 is something along the lines of:
> set optimizer_trace="enabled=on";
> select * from foo;
> select * from information_schema.optimizer_trace;
> 
> i'm just curious if we're accidentally breaking anything/functionality for something else?
> (you mentioned this too: "Writing tools to support competing implementations of the same feature is not fun.")
> 
> cheers,
> -colin
> 
>> 
>> On Fri, Feb 14, 2014 at 8:23 PM, Colin Charles <colin@xxxxxxxxxxx> wrote:
>> 
>> On 15 Feb 2014, at 11:35, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
>> 
>>> here:
>>> https://lists.launchpad.net/maria-developers/msg05719.html
>> 
>> isn't this just the standard DBUG_ENTER(), DBUG_PRINT() of the SE API?
>> 
>> extracts from https://mariadb.com/kb/en/plans-for-10x/ :
>>        • QA request: better EXPLAIN (HIGH priority; MP; Spetrunia)
>>                • required in order to debug performance issues in queries without knowing the query or the data;
>>                • the customer will only provide EXPLAIN and SHOW output, we need to debug based on that; (need examples)
>>                • Perhaps optimizer trace is what we need
>> 
>>        • U/C at Oracle: OPTIMIZER tracing spetrunia: report actual estimates, and all decisions of the optimizer, including why an index was *not* picked, etc.
>>                • want to change for 5.7
>> 
>> 
>>> 2014-02-15 1:21 GMT-02:00 Justin Swanhart <greenlion@xxxxxxxxx>:
>>> Can you point to the docs on the mariadb implementation?  Writing tools to support competing implementations of the same feature is not fun.
>> 
>> i don't think they are the same feature. optimizer tracing, and its accompanying json output is something that mariadb 10.0 will not have, but something we probably need in future versions (10.1) to remain "backwards compatible".
>> 
>> i see mysql workbench as a front-end tool supporting optimizer trace -- do you know of more justin?
>> 
>> cheers,
>> -colin
>> 
>> 
>>> 
>>> 
>>> Sent from my iPhone
>>> 
>>>> On Feb 14, 2014, at 2:12 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
>>>> 
>>>> that's the "same" feature of dbug in mariadb
>>>> 
>>>> 
>>>> 2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@xxxxxxxxx>:
>>>> Is this feature or something similar already in MariaDB 10?  If not, is it on the roadmap?
>>>> 
>>>> http://dev.mysql.com/doc/internals/en/optimizer-tracing.html
>>>> 
>>>> -Tim Callaghan
>>>> 
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> More help   : https://help.launchpad.net/ListHelp
>>>> 
>>>> 
>>>> 
>>>> 
>>>> --
>>>> Roberto Spadim
>>>> SPAEmpresarial
>>>> Eng. Automação e Controle
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> More help   : https://help.launchpad.net/ListHelp
>>> 
>>> 
>>> 
>>> --
>>> Roberto Spadim
>>> SPAEmpresarial
>>> Eng. Automação e Controle
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~maria-discuss
>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>> More help   : https://help.launchpad.net/ListHelp
>> 
>> --
>> Colin Charles, Chief Evangelist
>> MariaDB | t: +6-012-204-3201 | Skype: colincharles
> 
> --
> Colin Charles, Chief Evangelist
> MariaDB | t: +6-012-204-3201 | Skype: colincharles
>