[Maria-discuss] MySQL 5.6 optimizer trace functionality in MariaDB 10?
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
that's the "same" feature of dbug in mariadb 2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>:
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Can you point to the docs on the mariadb implementation? Writing tools to support competing implementations of the same feature is not fun. Sent from my iPhone
On Feb 14, 2014, at 2:12 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>:
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@lists.launchpad.net 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
here: https://lists.launchpad.net/maria-developers/msg05719.html 2014-02-15 1:21 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Can you point to the docs on the mariadb implementation? Writing tools to support competing implementations of the same feature is not fun.
Sent from my iPhone
On Feb 14, 2014, at 2:12 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>:
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@lists.launchpad.net 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
but it's not doc, just a comment about mariadb/mysql 2014-02-15 1:35 GMT-02:00 Roberto Spadim <roberto@spadim.com.br>:
here: https://lists.launchpad.net/maria-developers/msg05719.html
2014-02-15 1:21 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Can you point to the docs on the mariadb implementation? Writing tools to
support competing implementations of the same feature is not fun.
Sent from my iPhone
On Feb 14, 2014, at 2:12 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>:
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@lists.launchpad.net 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
On 15 Feb 2014, at 11:35, Roberto Spadim <roberto@spadim.com.br> 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@gmail.com>: 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@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>: 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@lists.launchpad.net 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@lists.launchpad.net 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@lists.launchpad.net 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
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. The optimizer trace information is available in a special information schema table. On Fri, Feb 14, 2014 at 8:23 PM, Colin Charles <colin@mariadb.org> wrote:
On 15 Feb 2014, at 11:35, Roberto Spadim <roberto@spadim.com.br> 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@gmail.com>: 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@spadim.com.br>
wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>: 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@lists.launchpad.net 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@lists.launchpad.net 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@lists.launchpad.net 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
On 15 Feb 2014, at 13:01, Justin Swanhart <greenlion@gmail.com> 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@mariadb.org> wrote:
On 15 Feb 2014, at 11:35, Roberto Spadim <roberto@spadim.com.br> 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@gmail.com>: 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@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>: 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@lists.launchpad.net 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@lists.launchpad.net 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@lists.launchpad.net 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
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@mariadb.org> wrote:
On 15 Feb 2014, at 13:01, Justin Swanhart <greenlion@gmail.com> 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@mariadb.org> wrote:
On 15 Feb 2014, at 11:35, Roberto Spadim <roberto@spadim.com.br> 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@gmail.com>: 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@spadim.com.br> wrote:
that's the "same" feature of dbug in mariadb
2014-02-14 19:01 GMT-02:00 Tim Callaghan <tmcallaghan@gmail.com>: 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@lists.launchpad.net 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@lists.launchpad.net 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@lists.launchpad.net 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
Hi, Tim! On Feb 14, Tim Callaghan wrote:
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
No, it is not in MariaDB 10.0. We've considered it, but other features, most requested by our users, got higher priority. If you think that optimizer trace is generaly useful outside of the small group of optimizer developers, we can consider it for 10.1. Regards, Sergei
participants (5)
-
Colin Charles -
Justin Swanhart -
Roberto Spadim -
Sergei Golubchik -
Tim Callaghan