Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.10
-
None
-
None
Description
Discovered this while working on MDEV-6015. I had these settings
set join_cache_level=6;
|
set join_buffer_size=90*1024*1024;
|
set optimizer_switch='mrr=on,mrr_sort_keys=on';
|
set optimizer_switch='exists_to_in=on';
|
and ran Q4 on DBT-3 scale=1.
Note that I didn't have any setting for join_buffer_space_limit. EXPLAIN was showing the query will be using BKA. Tracing IO looked like BKA wasn't being used. Debugging confirmed that BKA indeed wasn't being used.
The decision not to use it was made here:
#0 JOIN_CACHE::alloc_buffer (this=0x7ffe2d52e8e0) at /home/psergey/dev2/10.0/sql/sql_join_cache.cc:926
|
#1 0x00000000007ada13 in JOIN_CACHE::init (this=0x7ffe2d52e8e0) at /home/psergey/dev2/10.0/sql/sql_join_cache.cc:1066
|
#2 0x00000000007b29f9 in JOIN_CACHE_BKA::init (this=0x7ffe2d52e8e0) at /home/psergey/dev2/10.0/sql/sql_join_cache.cc:4212
|
#3 0x00000000006a50d2 in check_join_cache_usage (tab=0x7ffe2d456730, options=0, no_jbuf_after=2, table_index=1, prev_tab=0x7ffe2d456408) at /home/psergey/dev2/10.0/sql/sql_select.cc:10518
|
#4 0x00000000006a53ab in check_join_cache_usage_for_tables (join=0x7ffe2d5324e0, options=0, no_jbuf_after=2) at /home/psergey/dev2/10.0/sql/sql_select.cc:10624
|
#5 0x00000000006a5b11 in make_join_readinfo (join=0x7ffe2d5324e0, options=0, no_jbuf_after=2) at /home/psergey/dev2/10.0/sql/sql_select.cc:10791
|
#6 0x000000000068dda0 in JOIN::optimize_inner (this=0x7ffe2d5324e0) at /home/psergey/dev2/10.0/sql/sql_select.cc:1764
|
#7 0x000000000068b372 in JOIN::optimize (this=0x7ffe2d5324e0) at /home/psergey/dev2/10.0/sql/sql_select.cc:1013
|
#8 0x00000000006931c7 in mysql_select (thd=0x7ffe3f7e8070, rref_pointer_array=0x7ffe3f7ec6d8, tables=0x7ffe2d4226a0, wild_num=0, fields=..., conds=0x7ffe2d423480, og_num=2, order=0x7ffe2d5323d0, group=0x7ffe2d532270, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ffe2d5324c0, unit=0x7ffe3f7ebd78, select_lex=0x7ffe3f7ec460) at /home/psergey/dev2/10.0/sql/sql_select.cc:3279
|
Here, I had
(gdb) print curr_min_buff_space_sz
|
$102 = 83
|
(gdb) print join_buff_space_limit
|
$103 = 2097152
|
(gdb) print curr_buff_space_sz
|
$104 = 94371840
|
(gdb) print join_buff_space_limit
|
$105 = 2097152
|
(gdb) print optimize_buff_size
|
$106 = false
|
and running this code
if (curr_min_buff_space_sz > join_buff_space_limit ||
|
(curr_buff_space_sz > join_buff_space_limit &&
|
(!optimize_buff_size ||
|
join->shrink_join_buffers(join_tab, curr_buff_space_sz,
|
join_buff_space_limit))))
|
goto fail;
|
resulted in "goto fail". I think it's a big gotcha that @join_buffer_size must be lower than @@join_buffer_space_limit when optimizer_space_limit optimization is off.