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

Bad buffer size settings may cause BKA to be switched off

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.10
    • Fix Version/s: 10.0.12
    • Component/s: None
    • Labels:
      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.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration