Details
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)
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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.
+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.
Review feedback provided on skype/irc.
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
Patch pushed into 10.1-MDEV7811 patch, taking over
ghmmmm.... It looks like feature request....