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

EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

Details

    • 10.1.6-1

    Description

      EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
      An example query:

      MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
      		 
      *************************** 1. row ***************************
      		 
      EXPLAIN: {
      		 
        "query_block": {
      		 
          "select_id": 1,
      		 
          "r_loops": 1,
      		 
          "r_time_ms": 2497.7,
      		 
          "table": {
      		 
            "table_name": "customer",
      		 
            "access_type": "index",
      		 
            "key": "i_c_nationkey",
      		 
            "key_length": "5",
      		 
            "used_key_parts": ["c_nationkey"],
      		 
            "r_loops": 1,
      		 
            "rows": 150747,
      		 
            "r_rows": 150000,
      		 
            "r_time_ms": 34.957,
      		 
            "filtered": 100,
      		 
            "r_filtered": 60.691,
      		 
            "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
      		 
            "using_index": true
      		 
          },
      		 
          "subqueries": [
      		 
            {
      		 
              "query_block": {
      		 
                "select_id": 2,
      		 
                "r_loops": 150000,
      		 
                "r_time_ms": 2395.5,
      		 
                "table": {
      		 
                  "table_name": "orders",
      		 
                  "access_type": "ref",
      		 
                  "possible_keys": ["i_o_custkey"],
      		 
                  "key": "i_o_custkey",
      		 
                  "key_length": "5",
      		 
                  "used_key_parts": ["o_custkey"],
      		 
                  "ref": ["dbt3sf1.customer.c_custkey"],
      		 
                  "r_loops": 150000,
      		 
                  "rows": 7,
      		 
                  "r_rows": 10,
      		 
                  "r_time_ms": 2120.7,
      		 
                  "filtered": 100,
      		 
                  "r_filtered": 100
      		 
                }
      		 
              }
      		 
            }
      		 
          ]
      		 
        }
      		 
      }

      EXPLAIN EXTENDED+subquery cache shows the query uses subquery cache.

      • EXPLAIN FORMAT=JSON should show it: subquery's query_block should be wrapped inside subquery_cache element.
      • ANALYZE FORMAT=JSON should also show r_hits (how many times cache was invoked) and r_hit_ratio (0...100% - fraction of times there was a cache hit)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            Description EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
            ```
            MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
            *************************** 1. row ***************************
            EXPLAIN: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_time_ms": 2497.7,
                "table": {
                  "table_name": "customer",
                  "access_type": "index",
                  "key": "i_c_nationkey",
                  "key_length": "5",
                  "used_key_parts": ["c_nationkey"],
                  "r_loops": 1,
                  "rows": 150747,
                  "r_rows": 150000,
                  "r_time_ms": 34.957,
                  "filtered": 100,
                  "r_filtered": 60.691,
                  "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
                  "using_index": true
                },
                "subqueries": [
                  {
                    "query_block": {
                      "select_id": 2,
                      "r_loops": 150000,
                      "r_time_ms": 2395.5,
                      "table": {
                        "table_name": "orders",
                        "access_type": "ref",
                        "possible_keys": ["i_o_custkey"],
                        "key": "i_o_custkey",
                        "key_length": "5",
                        "used_key_parts": ["o_custkey"],
                        "ref": ["dbt3sf1.customer.c_custkey"],
                        "r_loops": 150000,
                        "rows": 7,
                        "r_rows": 10,
                        "r_time_ms": 2120.7,
                        "filtered": 100,
                        "r_filtered": 100
                      }
                    }
                  }
                ]
              }
            }
            1 row in set (2.50 sec)
            ```             		EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
            An example query:

            {noformat}
            MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
            *************************** 1. row ***************************
            EXPLAIN: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_time_ms": 2497.7,
                "table": {
                  "table_name": "customer",
                  "access_type": "index",
                  "key": "i_c_nationkey",
                  "key_length": "5",
                  "used_key_parts": ["c_nationkey"],
                  "r_loops": 1,
                  "rows": 150747,
                  "r_rows": 150000,
                  "r_time_ms": 34.957,
                  "filtered": 100,
                  "r_filtered": 60.691,
                  "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
                  "using_index": true
                },
                "subqueries": [
                  {
                    "query_block": {
                      "select_id": 2,
                      "r_loops": 150000,
                      "r_time_ms": 2395.5,
                      "table": {
                        "table_name": "orders",
                        "access_type": "ref",
                        "possible_keys": ["i_o_custkey"],
                        "key": "i_o_custkey",
                        "key_length": "5",
                        "used_key_parts": ["o_custkey"],
                        "ref": ["dbt3sf1.customer.c_custkey"],
                        "r_loops": 150000,
                        "rows": 7,
                        "r_rows": 10,
                        "r_time_ms": 2120.7,
                        "filtered": 100,
                        "r_filtered": 100
                      }
                    }
                  }
                ]
              }
            }
            1 row in set (2.50 sec)
            {noformat}
            psergei Sergei Petrunia made changes -
            Description EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
            An example query:

            {noformat}
            MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
            *************************** 1. row ***************************
            EXPLAIN: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_time_ms": 2497.7,
                "table": {
                  "table_name": "customer",
                  "access_type": "index",
                  "key": "i_c_nationkey",
                  "key_length": "5",
                  "used_key_parts": ["c_nationkey"],
                  "r_loops": 1,
                  "rows": 150747,
                  "r_rows": 150000,
                  "r_time_ms": 34.957,
                  "filtered": 100,
                  "r_filtered": 60.691,
                  "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
                  "using_index": true
                },
                "subqueries": [
                  {
                    "query_block": {
                      "select_id": 2,
                      "r_loops": 150000,
                      "r_time_ms": 2395.5,
                      "table": {
                        "table_name": "orders",
                        "access_type": "ref",
                        "possible_keys": ["i_o_custkey"],
                        "key": "i_o_custkey",
                        "key_length": "5",
                        "used_key_parts": ["o_custkey"],
                        "ref": ["dbt3sf1.customer.c_custkey"],
                        "r_loops": 150000,
                        "rows": 7,
                        "r_rows": 10,
                        "r_time_ms": 2120.7,
                        "filtered": 100,
                        "r_filtered": 100
                      }
                    }
                  }
                ]
              }
            }
            1 row in set (2.50 sec)
            {noformat}
            		 EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON should show subquery cache use. ANALYZE must include #hits and hit ratio.
            An example query:

            {noformat}
            MariaDB [dbt3sf1]> analyze FORMAT=JSON select count(*) from customer where (select sum(o_totalprice) from orders where o_custkey=c_custkey) > 1000*1000\G
            *************************** 1. row ***************************
            EXPLAIN: {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_time_ms": 2497.7,
                "table": {
                  "table_name": "customer",
                  "access_type": "index",
                  "key": "i_c_nationkey",
                  "key_length": "5",
                  "used_key_parts": ["c_nationkey"],
                  "r_loops": 1,
                  "rows": 150747,
                  "r_rows": 150000,
                  "r_time_ms": 34.957,
                  "filtered": 100,
                  "r_filtered": 60.691,
                  "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))",
                  "using_index": true
                },
                "subqueries": [
                  {
                    "query_block": {
                      "select_id": 2,
                      "r_loops": 150000,
                      "r_time_ms": 2395.5,
                      "table": {
                        "table_name": "orders",
                        "access_type": "ref",
                        "possible_keys": ["i_o_custkey"],
                        "key": "i_o_custkey",
                        "key_length": "5",
                        "used_key_parts": ["o_custkey"],
                        "ref": ["dbt3sf1.customer.c_custkey"],
                        "r_loops": 150000,
                        "rows": 7,
                        "r_rows": 10,
                        "r_time_ms": 2120.7,
                        "filtered": 100,
                        "r_filtered": 100
                      }
                    }
                  }
                ]
              }
            }
            {noformat}

            EXPLAIN EXTENDED+subquery cache shows the query uses subquery cache.
            * EXPLAIN FORMAT=JSON should show it: subquery's {{query_block}} should be wrapped inside {{subquery_cache}} element.
            * ANALYZE FORMAT=JSON should also show {{r_hits}} (how many times cache was invoked) and {{r_hit_ratio}} (0...100% - fraction of times there was a cache hit)
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Assignee Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin added a comment -

            ghmmmm.... It looks like feature request....

            sanja Oleksandr Byelkin added a comment - ghmmmm.... It looks like feature request....
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            Labels analyze-stmt
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin added a comment -

            revision-id: ec398a2ee84be0af3970d6247296fe1077b2f449
            parent(s): 3578419b778876fc1e7ec2a6994b9273b5d2b166
            committer: Oleksandr Byelkin
            branch nick: server
            timestamp: 2015-03-25 18:27:10 +0100
            message:

            MDEV-7811: EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

            sanja Oleksandr Byelkin added a comment - revision-id: ec398a2ee84be0af3970d6247296fe1077b2f449 parent(s): 3578419b778876fc1e7ec2a6994b9273b5d2b166 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-03-25 18:27:10 +0100 message: MDEV-7811 : EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache
            psergei Sergei Petrunia added a comment -

            So, the current patch prints: 

            +          "expression_cache": {
            		 
            +            "state": "ENABLED",
            		 
            +            "hit": 6,
            		 
            +            "miss": 4
            		 
            +          },

            One problem with this is that it breaks the rule: ANALYZE members start with r_. All other members start with r: r_rows, r_filtered, r_keys.

            Another problem is printing hit/miss. When I have a cache I want to know:

            • 1. What is the cache's hit ratio (the percentage of accesses that resulted in cache hits). This show if/how much the cache helps.
            • 2. How many accesses there were.
            • 3. How much the cache costed us (in CPU/mem usage/etc) (let this be outside the scope of this task)

            it is inconvenient when I have to calculate hit/(hit + miss) manually.
            Don't fix yet, there will be more comments.

            psergei Sergei Petrunia added a comment - So, the current patch prints: + "expression_cache": { + "state": "ENABLED", + "hit": 6, + "miss": 4 + }, One problem with this is that it breaks the rule: ANALYZE members start with r_ . All other members start with r: r_rows, r_filtered, r_keys. Another problem is printing hit/miss. When I have a cache I want to know: 1. What is the cache's hit ratio (the percentage of accesses that resulted in cache hits). This show if/how much the cache helps. 2. How many accesses there were. 3. How much the cache costed us (in CPU/mem usage/etc) (let this be outside the scope of this task) it is inconvenient when I have to calculate hit/(hit + miss) manually. Don't fix yet, there will be more comments.
            psergei Sergei Petrunia added a comment - 

            +const char *Expression_cache_stat::state_str[3]=
            		 
            +{"UNINITIZED", "STOPPED", "ENABLED"};

            UNINITIZED has a typo, and it is not clear what it should mean.

            I can understand two states:

            • enabled (the initial state)
            • auto-disabled (this happens when the cache disables itself)

            it's not clear what the third state should be.

            psergei Sergei Petrunia added a comment - +const char *Expression_cache_stat::state_str[3]= +{"UNINITIZED", "STOPPED", "ENABLED"}; UNINITIZED has a typo, and it is not clear what it should mean. I can understand two states: enabled (the initial state) auto-disabled (this happens when the cache disables itself) it's not clear what the third state should be.
            psergei Sergei Petrunia added a comment -

            Review feedback provided on skype/irc.

            psergei Sergei Petrunia added a comment - Review feedback provided on skype/irc.
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin added a comment -

            revision-id: 3027c25d522b637d796776396f8a940ff098c233
            parent(s): 3578419b778876fc1e7ec2a6994b9273b5d2b166
            committer: Oleksandr Byelkin
            branch nick: server
            timestamp: 2015-04-07 12:53:16 +0200
            message:

            MDEV-7811: EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache

            sanja Oleksandr Byelkin added a comment - revision-id: 3027c25d522b637d796776396f8a940ff098c233 parent(s): 3578419b778876fc1e7ec2a6994b9273b5d2b166 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-04-07 12:53:16 +0200 message: MDEV-7811 : EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

            Patch pushed into 10.1-MDEV7811 patch, taking over

            psergei Sergei Petrunia added a comment - Patch pushed into 10.1-MDEV7811 patch, taking over
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 60171 ] MariaDB v3 [ 66190 ]
            psergei Sergei Petrunia made changes -
            Sprint 10.1.6-1 [ 6 ]
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.1.6 [ 19401 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 66190 ] MariaDB v4 [ 148918 ]

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.