Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
-
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
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-
Activity
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.
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
ghmmmm.... It looks like feature request....