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

optimizer trace

Details

    Description

      In MySQL, Optimizer trace is a JSON object recording the execution path through the optimizer, decisions that were made and the reasons for them. See https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

      Users were asking for MariaDB to have a similar feature.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-7239 explain index choice

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

          Task - A task that needs to be done. MDEV-17929 Optimizer trace support for different parts of the server

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17930 Optimizer trace: what's on the execution path when trace=off?

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18117 Crash with Explain extended when using limit rows examined

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-18172 Benchmarking 10.4 for optimizer trace

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18306 Optimizer_trace has invalid JSON near rows_estimation

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-18489 Limit the memory used by the optimizer trace

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-12980 Index hints for loose scan

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18670 Document optimizer trace

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-20440 Optimizer trace: print more details about semi-join optimization

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18527 Optimizer trace for DELETE query shows table:null

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18528 Optimizer trace support for multi-table UPDATE and DELETE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18741 Optimizer trace: multi-part key ranges are printed incorrectly.

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-18822 investigate performance regressions in 10.4

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19160 JSON_DETAILED output unnecessarily verbose (Part Time project)

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-20444 More information regarding access of a table to be printed inside the optimizer_trace

          • Major - Major loss of function.
          • Closed

          Activity

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

            ... However, MariaDB's JSON function do not allow to compute JSON paths? I tried JSON_EXTRACT and others, none could achieve what I wanted - extract all nodes in the document with the name range_analysis.

            psergei Sergei Petrunia added a comment - ... However, MariaDB's JSON function do not allow to compute JSON paths? I tried JSON_EXTRACT and others, none could achieve what I wanted - extract all nodes in the document with the name range_analysis .
            psergei Sergei Petrunia added a comment -

            As for pretty-printing, there is JSON_DETAILED function but its output is not as beautiful as the one that EXPLAIN FORMAT=JSON pretty-printer produces.

            Example:

             
             
            		 
                                                        "key_parts": 
                                                        [
            		 
                                                            "a"
            		 
                                                        ]
            		 
                                                    }
            		 
                                                ],
            		 
                                                "setup_range_conditions": 
                                                [
            		 
                                                ],

            psergei Sergei Petrunia added a comment - As for pretty-printing, there is JSON_DETAILED function but its output is not as beautiful as the one that EXPLAIN FORMAT=JSON pretty-printer produces. Example:   "key_parts": [ "a" ] } ], "setup_range_conditions": [ ],
            psergei Sergei Petrunia added a comment -

            WIth holyfoot's and Serg's input:

            mysql> select  JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from t20\G
            		 
            *************************** 1. row ***************************
            		 
            JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')): [
            		 
                
                {
            		 
                    "table_scan": 
                    {
            		 
                        "rows": 1000,
            		 
                        "cost": 103.1
            		 
                    },
            		 
                    "potential_range_indexes": 
                    [
            		 
                        
                        {
            		 
                            "index": "a",
            		 
                            "usable": true,
            		 
                            "key_parts": 
                            [
            		 
                                "a"
            		 
                            ]
            		 
                        }
            		 
                    ],
            		 
                    "setup_range_conditions": 
                    [
            		 
                    ],

            so, it is possible to extract parts of trace using JSON function. You'll need to re-format them, and formatter is not perfect, but still.

            psergei Sergei Petrunia added a comment - WIth holyfoot 's and Serg's input: mysql> select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from t20\G *************************** 1. row *************************** JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')): [ { "table_scan": { "rows": 1000, "cost": 103.1 }, "potential_range_indexes": [ { "index": "a", "usable": true, "key_parts": [ "a" ] } ], "setup_range_conditions": [ ], so, it is possible to extract parts of trace using JSON function. You'll need to re-format them, and formatter is not perfect, but still.
            psergei Sergei Petrunia added a comment -

            Pushed this:

            https://github.com/MariaDB/server/commit/2dbe472ed011a951b28434ae8e67945e964d2030

            Optimizer trace: print cost and #rows of the join prefix
            		 
             
            		 
            The names rows_for_plan and cost_for_plan follow MySQL
            		 
            Also added post-join-operation selectivity cost

            psergei Sergei Petrunia added a comment - Pushed this: https://github.com/MariaDB/server/commit/2dbe472ed011a951b28434ae8e67945e964d2030 Optimizer trace: print cost and #rows of the join prefix   The names rows_for_plan and cost_for_plan follow MySQL Also added post-join-operation selectivity cost
            psergei Sergei Petrunia added a comment - - edited

            Pushed this:

            http://lists.askmonty.org/pipermail/commits/2019-August/013945.html

            http://lists.askmonty.org/pipermail/commits/2019-August/013945.html
            		 
            commit 4a490d1a993959222deb8bae822f40575586a148 (HEAD -> 10.4, origin/HEAD, origin/10.4, tmp10)
            		 
            Author: Sergei Petrunia <psergey@askmonty.org>
            		 
            Date:   Sun Aug 25 11:03:19 2019 +0300
            		 
             
            		 
                MDEV-6111: Optimizer Trace: add tracing for semi-join optimizations
            		 
                
                Added:
            		 
                - "semijoin_strategy_choice" element (actions in advance_sj_state(), name
            		 
                  matches the name in MySQL)
            		 
                
                - semijoin_table_pullout element.

            UPDATE: filed a MDEV to track the above patch: MDEV-20440.

            psergei Sergei Petrunia added a comment - - edited Pushed this: http://lists.askmonty.org/pipermail/commits/2019-August/013945.html http://lists.askmonty.org/pipermail/commits/2019-August/013945.html commit 4a490d1a993959222deb8bae822f40575586a148 (HEAD -> 10.4, origin/HEAD, origin/10.4, tmp10) Author: Sergei Petrunia <psergey@askmonty.org> Date: Sun Aug 25 11:03:19 2019 +0300   MDEV-6111: Optimizer Trace: add tracing for semi-join optimizations Added: - "semijoin_strategy_choice" element (actions in advance_sj_state(), name matches the name in MySQL) - semijoin_table_pullout element. UPDATE : filed a MDEV to track the above patch: MDEV-20440 .

            People

              varun Varun Gupta (Inactive)
              serg Sergei Golubchik
              Votes:
              10 Vote for this issue
              Watchers:
              17 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.