[Maria-developers] Json Explain
Is Explain output in Json format planned in MariaDB (refer http://glebshchepa.blogspot.in/2012/04/optimizer-new-explain-formatjson.html) ? If so when? and will the implementation details and output format be fully identical to Oracle/MySQL 5.6? (I did not find it mentioned here: http://monty-says.blogspot.dk/2012/09/now-is-good-time-to-be-part-of-future.... http://blog.mariadb.org/mariadb-directions/ .. sorry if I overlooked it somewhere) -- Peter -- Webyog
hi!
"Peter" == Peter Laursen <peter_laursen@webyog.com> writes:
Peter> Is Explain output in Json format planned in MariaDB (refer Peter> http://glebshchepa.blogspot.in/2012/04/optimizer-new-explain-formatjson.html) Peter> ? For the moment we don't have a plan for that. The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is. Igor, Timour or Sergey, can anyone of you comment? Peter> If so when? and will the implementation details and output format be fully Peter> identical to Oracle/MySQL 5.6? That is the question ? Peter> (I did not find it mentioned here: Peter> http://monty-says.blogspot.dk/2012/09/now-is-good-time-to-be-part-of-future.... Peter> http://blog.mariadb.org/mariadb-directions/ Peter> .. sorry if I overlooked it somewhere) No you didn't and this is the right forum to ask! Regards, Monty PS: Of course anyone can back-port the feature from MySQL 5.6 to MariaDB 10.0; We are happy to review a patch... However, lets first see what the optimizer team has to say...
-----Original Message----- From: maria-discuss- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- discuss-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Michael Widenius Sent: Freitag, 28. September 2012 16:04 To: Peter Laursen; igor@askmonty.org; timour@askmonty.org; psergey@askmonty.org Cc: Maria Discuss; Maria Developers Subject: Re: [Maria-discuss] [Maria-developers] Json Explain
For the moment we don't have a plan for that.
The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is.
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
@wlad is completely right! Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/ The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view). So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context. -- Peter On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub <wlad@montyprogram.com>wrote:
-----Original Message----- From: maria-discuss- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- discuss-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Michael Widenius Sent: Freitag, 28. September 2012 16:04 To: Peter Laursen; igor@askmonty.org; timour@askmonty.org; psergey@askmonty.org Cc: Maria Discuss; Maria Developers Subject: Re: [Maria-discuss] [Maria-developers] Json Explain
For the moment we don't have a plan for that.
The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is.
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
I do not see why this should be a concern for the optimizer team. The output from EXPLAIN (in any format) does not affect optimizer's operation but only provides information of *optimizer result*. The format chosen is (?) applied is *after* not *before* or *during* the optimization process. TRADITIONAL EXPLAIN returns the result as an **array**. JSON EXPLAIN returns the result as a single (Json-formatted) **string** (not unlike SHOW ENGINE INNODB STATUS, btw). But both should have exactly the same information - and if they don't it is a bug with JSON EXPLAIN in MySQL 5.6 IMO. But as I am not well-versed in server internals, I could have ovelooked/misunderstood something, of course. -- Peter On Fri, Sep 28, 2012 at 5:18 PM, Peter Laursen <peter_laursen@webyog.com>wrote:
@wlad is completely right!
Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view).
So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context.
-- Peter
On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub < wlad@montyprogram.com> wrote:
-----Original Message----- From: maria-discuss- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- discuss-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Michael Widenius Sent: Freitag, 28. September 2012 16:04 To: Peter Laursen; igor@askmonty.org; timour@askmonty.org; psergey@askmonty.org Cc: Maria Discuss; Maria Developers Subject: Re: [Maria-discuss] [Maria-developers] Json Explain
For the moment we don't have a plan for that.
The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is.
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
Peter, You are right that EXPLAIN in any format has nothing to do with the optimizer. Looking at the piece of the MySQL code that forms the strings for the EXPLAIN anyone could easily come up with the patch providing JSON format for the EXPLAIN (even with the extensions of MySQL 5.6) and it would take him/her 2-3 days at most (maybe even a few hours). That's why we are reluctant to pull the existing implementation of JSON format from MySQL 5.6 into MariaDB. The patch is huge and overburdened with unneeded complexities. Besides it contains a lot of "restructuring" code that has nothing to do with the feature itself. Personally I think that the content in the current EXPLAIN (including the content of the EXPLAIN EXTENDED) is so shallow that any graphical representation hardly can help it (compare it, for example, with the info provided by MS SQL server). If, nevertheless, you badly need the feature and is ready to co-sponsor the feature (together with MP AB) we could discuss the details of the possible simple implementation. And of course we will support it in MariaDB if anybody else provide us with a patch for such an implementation. Regards, Igor. On 09/30/2012 04:23 AM, Peter Laursen wrote:
I do not see why this should be a concern for the optimizer team.
The output from EXPLAIN (in any format) does not affect optimizer's operation but only provides information of *optimizer result*. The format chosen is (?) applied is *after* not *before* or *during* the optimization process.
TRADITIONAL EXPLAIN returns the result as an **array**. JSON EXPLAIN returns the result as a single (Json-formatted) **string** (not unlike SHOW ENGINE INNODB STATUS, btw). But both should have exactly the same information - and if they don't it is a bug with JSON EXPLAIN in MySQL 5.6 IMO.
But as I am not well-versed in server internals, I could have ovelooked/misunderstood something, of course.
-- Peter
On Fri, Sep 28, 2012 at 5:18 PM, Peter Laursen <peter_laursen@webyog.com <mailto:peter_laursen@webyog.com>> wrote:
@wlad is completely right!
Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view).
So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context.
-- Peter
On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub <wlad@montyprogram.com <mailto:wlad@montyprogram.com>> wrote:
> -----Original Message----- > From: maria-discuss- > bounces+wlad=montyprogram.com@lists.launchpad.net <mailto:montyprogram.com@lists.launchpad.net> [mailto:maria- <mailto:maria-> > discuss-bounces+wlad=montyprogram.com@lists.launchpad.net <mailto:montyprogram.com@lists.launchpad.net>] On Behalf > Of Michael Widenius > Sent: Freitag, 28. September 2012 16:04 > To: Peter Laursen; igor@askmonty.org <mailto:igor@askmonty.org>; timour@askmonty.org <mailto:timour@askmonty.org>; > psergey@askmonty.org <mailto:psergey@askmonty.org> > Cc: Maria Discuss; Maria Developers > Subject: Re: [Maria-discuss] [Maria-developers] Json Explain > > > For the moment we don't have a plan for that. > > The main reason is that the MariadB optimizer people don't like the > implementation and we are not sure how useful the current output > really is. >
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
I don't claim we will necessarily need it! :-) I was only asking. No more comments at the moment. I will update here if I think I have a reason to! Thanks for considering! -- Peter On Sun, Sep 30, 2012 at 7:13 PM, Igor Babaev <igor@askmonty.org> wrote:
Peter,
You are right that EXPLAIN in any format has nothing to do with the optimizer. Looking at the piece of the MySQL code that forms the strings for the EXPLAIN anyone could easily come up with the patch providing JSON format for the EXPLAIN (even with the extensions of MySQL 5.6) and it would take him/her 2-3 days at most (maybe even a few hours).
That's why we are reluctant to pull the existing implementation of JSON format from MySQL 5.6 into MariaDB. The patch is huge and overburdened with unneeded complexities. Besides it contains a lot of "restructuring" code that has nothing to do with the feature itself.
Personally I think that the content in the current EXPLAIN (including the content of the EXPLAIN EXTENDED) is so shallow that any graphical representation hardly can help it (compare it, for example, with the info provided by MS SQL server).
If, nevertheless, you badly need the feature and is ready to co-sponsor the feature (together with MP AB) we could discuss the details of the possible simple implementation. And of course we will support it in MariaDB if anybody else provide us with a patch for such an implementation.
Regards, Igor.
On 09/30/2012 04:23 AM, Peter Laursen wrote:
I do not see why this should be a concern for the optimizer team.
The output from EXPLAIN (in any format) does not affect optimizer's operation but only provides information of *optimizer result*. The format chosen is (?) applied is *after* not *before* or *during* the optimization process.
TRADITIONAL EXPLAIN returns the result as an **array**. JSON EXPLAIN returns the result as a single (Json-formatted) **string** (not unlike SHOW ENGINE INNODB STATUS, btw). But both should have exactly the same information - and if they don't it is a bug with JSON EXPLAIN in MySQL 5.6 IMO.
But as I am not well-versed in server internals, I could have ovelooked/misunderstood something, of course.
-- Peter
On Fri, Sep 28, 2012 at 5:18 PM, Peter Laursen <peter_laursen@webyog.com <mailto:peter_laursen@webyog.com>> wrote:
@wlad is completely right!
Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view).
So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context.
-- Peter
On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub <wlad@montyprogram.com <mailto:wlad@montyprogram.com>> wrote:
> -----Original Message----- > From: maria-discuss- > bounces+wlad=montyprogram.com@lists.launchpad.net <mailto:montyprogram.com@lists.launchpad.net> [mailto:maria- <mailto:maria-> > discuss-bounces+wlad=montyprogram.com@lists.launchpad.net <mailto:montyprogram.com@lists.launchpad.net>] On Behalf > Of Michael Widenius > Sent: Freitag, 28. September 2012 16:04 > To: Peter Laursen; igor@askmonty.org <mailto:igor@askmonty.org>; timour@askmonty.org <mailto:timour@askmonty.org>; > psergey@askmonty.org <mailto:psergey@askmonty.org> > Cc: Maria Discuss; Maria Developers > Subject: Re: [Maria-discuss] [Maria-developers] Json Explain > > > For the moment we don't have a plan for that. > > The main reason is that the MariadB optimizer people don't like the > implementation and we are not sure how useful the current output > really is. >
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
BTW: I reported this bug to MySQL: http://bugs.mysql.com/bug.php?id=67022 -- Peter On Fri, Sep 28, 2012 at 5:18 PM, Peter Laursen <peter_laursen@webyog.com>wrote:
@wlad is completely right!
Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view).
So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context.
-- Peter
On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub < wlad@montyprogram.com> wrote:
-----Original Message----- From: maria-discuss- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- discuss-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Michael Widenius Sent: Freitag, 28. September 2012 16:04 To: Peter Laursen; igor@askmonty.org; timour@askmonty.org; psergey@askmonty.org Cc: Maria Discuss; Maria Developers Subject: Re: [Maria-discuss] [Maria-developers] Json Explain
For the moment we don't have a plan for that.
The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is.
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
+ http://bugs.mysql.com/bug.php?id=67023 -- Peter On Sun, Sep 30, 2012 at 1:39 PM, Peter Laursen <peter_laursen@webyog.com>wrote:
BTW: I reported this bug to MySQL: http://bugs.mysql.com/bug.php?id=67022
-- Peter
On Fri, Sep 28, 2012 at 5:18 PM, Peter Laursen <peter_laursen@webyog.com>wrote:
@wlad is completely right!
Workbench introduced a Visual Explain based on JSON EXPLAIN as Blogged here: http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
The very reason why JSON EXPLAIN was introduced in MySQL 5.6 could be that Oracle develops the server and tools (WB, Enterprirse Monitor primarily) in parallel. Some recently introduced features in the server could be introduced for the primary reason that they should be 'exploited' by MEM and/or WB. JSON EXPLAIN is one. Another example is the Performance_Schema table introduced in 5.6.6 (I don't remember the table name) that records an 'aggregated summary' of queries' performance. Actually this table has about the same information as what MONyog 'Query Analyzer' module displays in its most simple display mode (but MONyog can be 'drilled down' to a more detailed view).
So Oracle *thinks* the server and their most important tools *as a whole*, I think. We/Webyog cannot ignore this, as we compete with Oracle (SQLyog <-> WB, MONyog <-> MEM). We have to consider to implement a more intuitive and advisory way of displaying EXPLAIN results (for non-expert users primarily of course). The JSON format is interesting for us in the context.
-- Peter
On Fri, Sep 28, 2012 at 4:50 PM, Vladislav Vaintroub < wlad@montyprogram.com> wrote:
-----Original Message----- From: maria-discuss- bounces+wlad=montyprogram.com@lists.launchpad.net [mailto:maria- discuss-bounces+wlad=montyprogram.com@lists.launchpad.net] On Behalf Of Michael Widenius Sent: Freitag, 28. September 2012 16:04 To: Peter Laursen; igor@askmonty.org; timour@askmonty.org; psergey@askmonty.org Cc: Maria Discuss; Maria Developers Subject: Re: [Maria-discuss] [Maria-developers] Json Explain
For the moment we don't have a plan for that.
The main reason is that the MariadB optimizer people don't like the implementation and we are not sure how useful the current output really is.
I guess json is not for normal people to read , it is for programs to analyze and ,for example, display in a visually pleasing fashion. I believe this is the reason why Peter asks, and (I guess) the reason why Workbench has visual explain feature now for 5.6+. http://www.arubin.org/blog/2012/09/26/mysql-visual-explain/
participants (4)
-
Igor Babaev -
Michael Widenius -
Peter Laursen -
Vladislav Vaintroub