Details
Description
After MDEV-7835, we can know how the sizes of buffers that were allocated for "Using join buffer".
It would be also nice to know how much space in the buffer was actually used.
This can be done as follows:
analyze format=json select * from one_k t1, t2 where t1.a+t2.a<1000;
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 5818.7,
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 1000,
"r_rows": 1000,
"r_total_time_ms": 21.695,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 1000,
"r_rows": 1000,
"r_total_time_ms": 18.274,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "flat",
"buffer_size": "128Kb",
"join_type": "BNL",
"attached_condition": "((t1.a + t2.a) < 1000)",
"r_filtered": 50.05
}
}
}
Here we see that t2 has r_loops=1, which means t2 was accessed one time.
Is this sufficient to indicate that the buffer was filled only once?
My suggestion for the first approach: let ANALYZE show another field:
r_buffer_used. It should print
r_buffer_used: size
when the buffer was used one time, and
r_buffer_used: "multiple times"
when the buffer was used multiple times.
I have a similar solution for ANALYZE+filesort(). If filesort was invoked once,
it prints:
r_sort_passes: nnnn,
...
and if this is a subquery and filesort was run many times, it prints
r_sort_passes: "(varied across executions)"
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7835 ANALYZE FORMAT=JSON should show buffer sizes
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions