Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27021

Extend SHOW EXPLAIN to support SHOW ANALYZE [FORMAT=JSON]

Details

    Description

      SHOW EXPLAIN allows one to view EXPLAIN of a currently executing query.

      However, it doesn't handle this use case:

      A really huge query runs for a long time and never finishes. Since it never finishes, one cannot view ANALYZE output. Adding "LIMIT N" to the top query is of limited help.
      One can view the EXPLAIN, but the query has a lot of subqueries that have many-table joins. One can see multiple sub-optimal access methods, and it is not clear which of them are the ones that are causing the performance problem.

      Suggested solution: extend SHOW EXPLAIN to report SHOW ANALYZE FORMAT=JSON data of the query that's currently running.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28124 Server crashes in Explain_aggr_filesort::print_json_members upon SHOW ANALYZE/EXPLAIN FORMAT=JSON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28125 Server crashes in Explain_table_access::tag_to_json upon SHOW EXPLAIN FORMAT=JSON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28201 Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-30156 SHOW ANALYZE is missing a KB page

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31774 mariadb command line client missing some commands in HELP SHOW

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33293 Race condition in test main.show_analyze

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-28112 prepare 10.9.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-10000 Add EXPLAIN FOR CONNECTION syntax support to SHOW EXPLAIN

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-25956 SHOW EXPLAIN FOR should support FORMAT=JSON

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28318 SHOW ANALYZE|EXPLAIN: Lock wait timeout with long GIS computations

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            View 8 older comments
            psergei Sergei Petrunia added a comment -

            2. How to name this field and where to place it? Is it OK to place it in the top of the output like this:
            SHOW ANALYZE: {
            "r_query_running_time_ms": 5432
            "query_block": {

            As agreed on Slack: Let it be r_time_in_progress_ms at the top level. For non-ANALYZE execution, there's a question of where to get the query start time. Please check where INFORMATION_SCHEMA.PROCESSLIST gets it from.

            psergei Sergei Petrunia added a comment - 2. How to name this field and where to place it? Is it OK to place it in the top of the output like this: SHOW ANALYZE: { "r_query_running_time_ms": 5432 "query_block": { As agreed on Slack: Let it be r_time_in_progress_ms at the top level. For non-ANALYZE execution, there's a question of where to get the query start time. Please check where INFORMATION_SCHEMA.PROCESSLIST gets it from.
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed a new commit addressing the issues discussed.

            {{commit a335245bbf9c49327d8b34ac30e3ad49822a5aa3 (HEAD > bb-10.8MDEV-10000, origin/bb-10.8-MDEV-10000)
            Author: Oleg Smirnov <olernov@gmail.com>
            Date: Wed Feb 16 13:03:46 2022 +0700

            MDEV-27021 Add explicit indication of SHOW EXPLAIN/ANALYZE.

            1. Add explicit indication that the output is produced by
            SHOW EXPLAIN/ANALYZE FORMAT=JSON command.
            2. Remove useless "r_total_time_ms" field from SHOW ANALYZE FORMAT=JSON
            output when there is no timed statistics gathered.
            3. Add "r_query_time_in_progress_ms" to the output of SHOW ANALYZE FORMAT=JSON.
            }}

            psergei, please review.

            oleg.smirnov Oleg Smirnov added a comment - Pushed a new commit addressing the issues discussed. {{commit a335245bbf9c49327d8b34ac30e3ad49822a5aa3 (HEAD > bb-10.8 MDEV-10000 , origin/bb-10.8- MDEV-10000 ) Author: Oleg Smirnov <olernov@gmail.com> Date: Wed Feb 16 13:03:46 2022 +0700 MDEV-27021 Add explicit indication of SHOW EXPLAIN/ANALYZE. 1. Add explicit indication that the output is produced by SHOW EXPLAIN/ANALYZE FORMAT=JSON command. 2. Remove useless "r_total_time_ms" field from SHOW ANALYZE FORMAT=JSON output when there is no timed statistics gathered. 3. Add "r_query_time_in_progress_ms" to the output of SHOW ANALYZE FORMAT=JSON. }} psergei , please review.
            oleg.smirnov Oleg Smirnov added a comment -

            By the way, I haven't fixed test cases yet, so some tests will fail. Once we're happy with the results I'll fix the test cases accordingly.

            oleg.smirnov Oleg Smirnov added a comment - By the way, I haven't fixed test cases yet, so some tests will fail. Once we're happy with the results I'll fix the test cases accordingly.
            psergei Sergei Petrunia added a comment -

            The last patch is ok. I've requested small fix in comments. After that, we can pass it to testing.

            psergei Sergei Petrunia added a comment - The last patch is ok. I've requested small fix in comments. After that, we can pass it to testing.
            nunop Nuno added a comment - - edited

            Hey
            Nice to see this being implemented!

            Here's a related post:

            "extend explain output to include "attached_condition""
            https://jira.mariadb.org/browse/MDEV-27146

            Thanks!

            nunop Nuno added a comment - - edited Hey Nice to see this being implemented! Here's a related post: "extend explain output to include "attached_condition"" https://jira.mariadb.org/browse/MDEV-27146 Thanks!

            People

              alice Alice Sherepa
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.