[MDEV-7811] EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache Created: 2015-03-21  Updated: 2015-07-01  Resolved: 2015-07-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.6

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: analyze-stmt

Issue Links:
Relates
relates to MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt Open
Sprint: 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)

 Comments   
Comment by Oleksandr Byelkin [ 2015-03-22 ]

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

Comment by Oleksandr Byelkin [ 2015-03-26 ]

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

Comment by Sergei Petrunia [ 2015-04-03 ]

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.

Comment by Sergei Petrunia [ 2015-04-03 ] 

+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.

Comment by Sergei Petrunia [ 2015-04-03 ]

Review feedback provided on skype/irc.

Comment by Oleksandr Byelkin [ 2015-04-07 ]

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

Comment by Sergei Petrunia [ 2015-04-09 ]

Patch pushed into 10.1-MDEV7811 patch, taking over

Generated at Thu Feb 08 07:22:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.