Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4626

Setting join_buffer_size causes join buffer not to be used



    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.0.2, 5.5.31
    • 10.1
    • None
    • None
    • 10.1.30


      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,
          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.


        Issue Links



              igor Igor Babaev
              psergei Sergei Petrunia
              4 Vote for this issue
              8 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.