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

Slow performance with outer joins and join buffer.

        Details

          Description

          A query over empty tables is extremely slow when outer join is used together with join buffering (which is ON by default).

          Take this file

          drop table if exists t2,t1;

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` smallint(3) NOT NULL DEFAULT '0',
  `col2` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

select now();
SELECT t.* 
FROM 
  t1 t 
  LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
  LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
  LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
  LEFT JOIN t2 c28 ON c28.parent_id = t.id AND c28.col2 = "val"
  LEFT JOIN t2 c29 ON c29.parent_id = t.id AND c29.col2 = "val"
  LEFT JOIN t2 c30 ON c30.parent_id = t.id AND c30.col2 = "val"
  LEFT JOIN t2 c31 ON c31.parent_id = t.id AND c31.col2 = "val"
  LEFT JOIN t2 c32 ON c32.parent_id = t.id AND c32.col2 = "val"
  LEFT JOIN t2 c33 ON c33.parent_id = t.id AND c33.col2 = "val"
ORDER BY 
  col1;

select now();

          and then run it.
          First, run it with join_cache_level=0 - it will finish instantly
          Then, run it with join_cache_level=2 (the default). The query will take more than 10 minutes (more in debug builds).

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link MySQL Bug #72854 Extremely slow performance with outer joins and join buffer

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  The problem is repeatable on MySQL 5.6.17 (the latest):

                  Putting the example into .test file and running , I get:

                  ...
Test case timeout after 900 seconds
...
Thread 2 (Thread 0x7f8296b3d700 (LWP 21683)):
#0  0x00007f82a3231307 in __errno_location () at ../csu/errno-loc.c:27
#1  0x0000000000e53163 in _db_return_ (_line_=1801, _stack_frame_=0x7f8296b39190) at /home/psergey/dev2/mysql-5.6/dbug/dbug.c:1300
#2  0x00000000009b2c54 in JOIN_CACHE::join_records (this=0x7f827c1885a8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1801
#3  0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c188410, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#4  0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c188278, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#5  0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1880e0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#6  0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187f48, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#7  0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187db0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#8  0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187c18, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#9  0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187a80, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#10 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c1878e8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#11 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187750, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#12 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1875b8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#13 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187420, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#14 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187288, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#15 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1870f0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#16 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186f58, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#17 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186dc0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#18 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186c28, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#19 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186a90, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#20 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c1868f8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#21 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186760, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#22 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1865c8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#23 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186430, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#24 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186298, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#25 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186100, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#26 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185f68, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#27 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185dd0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#28 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185c38, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#29 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185aa0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#30 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185908, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#31 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185770, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#32 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1855d8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#33 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185440, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755
#34 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1851d0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722
#35 0x00000000009b5c2d in JOIN_CACHE::end_send (this=0x7f827c1851d0) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.h:457
#36 0x00000000007b36f0 in sub_select_op (join=0x7f827c024468, join_tab=0x7f827c129af8, end_of_records=true) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:1053
#37 0x00000000007b381d in sub_select (join=0x7f827c024468, join_tab=0x7f827c1297f8, end_of_records=true) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:1209
#38 0x00000000007b339f in do_select (join=0x7f827c024468) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:935
#39 0x00000000007b128f in JOIN::exec (this=0x7f827c024468) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:194
#40 0x0000000000812e92 in mysql_execute_select (thd=0x29a74a0, select_lex=0x29a9de0, free_join=true) at /home/psergey/dev2/mysql-5.6/sql/sql_select.cc:1100
                  Show
                  psergey Sergei Petrunia added a comment - The problem is repeatable on MySQL 5.6.17 (the latest): Putting the example into .test file and running , I get: ... Test case timeout after 900 seconds ... Thread 2 (Thread 0x7f8296b3d700 (LWP 21683)): #0 0x00007f82a3231307 in __errno_location () at ../csu/errno-loc.c:27 #1 0x0000000000e53163 in _db_return_ (_line_=1801, _stack_frame_=0x7f8296b39190) at /home/psergey/dev2/mysql-5.6/dbug/dbug.c:1300 #2 0x00000000009b2c54 in JOIN_CACHE::join_records (this=0x7f827c1885a8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1801 #3 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c188410, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #4 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c188278, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #5 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1880e0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #6 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187f48, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #7 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187db0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #8 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187c18, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #9 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187a80, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #10 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c1878e8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #11 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187750, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #12 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1875b8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #13 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c187420, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #14 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c187288, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #15 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1870f0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #16 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186f58, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #17 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186dc0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #18 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186c28, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #19 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186a90, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #20 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c1868f8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #21 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186760, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #22 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1865c8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #23 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c186430, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #24 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186298, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #25 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c186100, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #26 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185f68, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #27 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185dd0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #28 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185c38, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #29 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c185aa0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #30 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185908, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #31 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185770, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #32 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1855d8, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #33 0x00000000009b2a96 in JOIN_CACHE::join_records (this=0x7f827c185440, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1755 #34 0x00000000009b294d in JOIN_CACHE::join_records (this=0x7f827c1851d0, skip_last=false) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.cc:1722 #35 0x00000000009b5c2d in JOIN_CACHE::end_send (this=0x7f827c1851d0) at /home/psergey/dev2/mysql-5.6/sql/sql_join_buffer.h:457 #36 0x00000000007b36f0 in sub_select_op (join=0x7f827c024468, join_tab=0x7f827c129af8, end_of_records=true) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:1053 #37 0x00000000007b381d in sub_select (join=0x7f827c024468, join_tab=0x7f827c1297f8, end_of_records=true) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:1209 #38 0x00000000007b339f in do_select (join=0x7f827c024468) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:935 #39 0x00000000007b128f in JOIN::exec (this=0x7f827c024468) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:194 #40 0x0000000000812e92 in mysql_execute_select (thd=0x29a74a0, select_lex=0x29a9de0, free_join=true) at /home/psergey/dev2/mysql-5.6/sql/sql_select.cc:1100
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  Percona Server 5.6.17-65.0-debug fails in the same way - the test times out after 15 min, deep insde JOIN_CACHE::join_records() calls.

                  Show
                  psergey Sergei Petrunia added a comment - Percona Server 5.6.17-65.0-debug fails in the same way - the test times out after 15 min, deep insde JOIN_CACHE::join_records() calls.
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  Filed http://bugs.mysql.com/bug.php?id=72854 against upstream

                  Show
                  psergey Sergei Petrunia added a comment - Filed http://bugs.mysql.com/bug.php?id=72854 against upstream
                  Hide
                  Permalink
                  igor Igor Babaev added a comment -

                  A fix for this bug was pushed into the 5.3 tree.

                  Show
                  igor Igor Babaev added a comment - A fix for this bug was pushed into the 5.3 tree.

                    People

                    • Assignee:
                      igor Igor Babaev
                      Reporter:
                      psergey Sergei Petrunia
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: