[MDEV-4626] Setting join_buffer_size causes join buffer not to be used - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.2, 5.5.31
Fix Version/s: None
Component/s: None
Labels:
None

Description

I was experimenting with join buffering.

I made a big table:

create table ten (a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k (a int);
insert into one_k select A.a + 1000 *B.a from ten A, ten B;

create table t1 (a int);
insert into t1 select A.a*1000 + B.a  from one_k A, one_k B;
alter table t1 add b int, add c int, add d int;

And ran a query that uses join buffer:

MariaDB [test]> explain select count(*) from t1 A, t1 B where (A.b + B.a) mod 5 =0;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
|    1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL | 1000533 |                                                 |
|    1 | SIMPLE      | B     | ALL  | NULL          | NULL | NULL    | NULL | 1000533 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
2 rows in set (0.01 sec)

Then I run the corresponding SELECT. In debugger, I saw execution reach JOIN_CACHE::alloc_buffer(), where it allocated a buffer of ~130Kbytes in size. Everything is ok so far.

Then, I decided to try with a bigger join buffer. I did

MariaDB [test]> set join_buffer_size=10*1000*1000;
Query OK, 0 rows affected (0.00 sec)

and run the query again. EXPLAIN remained the same. As for SELECT, I notice that JOIN_CACHE::alloc_buffer() doesn't call my_malloc() anymore.
It reaches this line:

  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;

and it takes the "if" and executes "goto fail".

Whatever it is, it must not work like this. Increasing @@join_buffer_size should not switch one from using join buffering to not using it. It is a very nasty performance surprise (especially since EXPLAIN gives one no clue that join buffer won't be used anymore).

I was using all default settings.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2013-06-08 23:51 - edited

Relevant variables:

+-------------------------+----------------------+
| Variable_name           | Value                |
+-------------------------+----------------------+
| join_buffer_size        | 10000000             |
| join_buffer_space_limit | 2097152              |
| join_cache_level        | 2                    |

@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Show

Sergei Petrunia added a comment - 2013-06-08 23:51 - edited Relevant variables: +-------------------------+----------------------+ | Variable_name | Value | +-------------------------+----------------------+ | join_buffer_size | 10000000 | | join_buffer_space_limit | 2097152 | | join_cache_level | 2 | @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Hide

Permalink

Sergei Petrunia added a comment - 2013-06-08 23:54 - edited

If I set @@join_buffer_space_limit to be 11M, then I see JOIN_CACHE::alloc_buffer() to allocate 10M.

I'm wondering what should happen when @@join_buffer_space_limit < @@join_buffer_size.

I think,

not using join buffer at all in this case is wrong.
the server should either disallow situation where holds, or maybe print warnings when it holds, etc.

Show

Sergei Petrunia added a comment - 2013-06-08 23:54 - edited If I set @@join_buffer_space_limit to be 11M, then I see JOIN_CACHE::alloc_buffer() to allocate 10M. I'm wondering what should happen when @@join_buffer_space_limit < @@join_buffer_size. I think, not using join buffer at all in this case is wrong. the server should either disallow situation where holds, or maybe print warnings when it holds, etc.

Hide

Permalink

Elena Stepanova added a comment - 2014-10-13 18:21

I see the following relatively acceptable solutions:

do not allow join_buffer_space_limit to be set smaller than join_buffer_size, and join_buffer_size to be set greater than join_buffer_space_limit – OK, although probably too restrictive in case when optimizer_switch=optimize_join_buffer_size=on;

throw a warning and automatically adjust (set to the same value) the other variable if one is changed – rather scary if, for example, a bad value was chosen my mistake;

just throw a warning – doesn't really solve anything except for doing some conscience clearing;

do one of the above, but also take into account optimizer_switch.optimize_join_buffer_size – seems too complicated to me.

Sergei Petrunia ,

What of this should be done, or who should make this decision, who should actually do it, and in which version?

Show

Elena Stepanova added a comment - 2014-10-13 18:21 I see the following relatively acceptable solutions: do not allow join_buffer_space_limit to be set smaller than join_buffer_size , and join_buffer_size to be set greater than join_buffer_space_limit – OK, although probably too restrictive in case when optimizer_switch=optimize_join_buffer_size=on ; throw a warning and automatically adjust (set to the same value) the other variable if one is changed – rather scary if, for example, a bad value was chosen my mistake ; just throw a warning – doesn't really solve anything except for doing some conscience clearing ; do one of the above, but also take into account optimizer_switch.optimize_join_buffer_size – seems too complicated to me . Sergei Petrunia , What of this should be done, or who should make this decision, who should actually do it, and in which version?

Setting join_buffer_size causes join buffer not to be used

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile