Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.2, 5.5.31
-
None
-
None
-
10.1.30
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-