|
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)"
|
|