List:Commits« Previous MessageNext Message »
From:Jan Wedvik Date:January 4 2011 2:51pm
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(jan.wedvik:3402)
View as plain text  
#At file:///net/atum17/export/home/tmp/jw159207/mysql/repo/push-scan-scan/ based on revid:jan.wedvik@stripped

 3402 Jan Wedvik	2011-01-04
      This commit changes mysqld (and the ndb handler) such that it no longer 
      disables join buffering (aka join cache) in order to be able to push more
      queries to the ndb. This is done to prevent performance regressions for queries that 
      would otherwise benefit from join buffering.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/abstract_query_plan.cc
      sql/abstract_query_plan.h
      sql/ha_ndbcluster.cc
      sql/handler.h
      sql/sql_select.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-12-07 13:58:30 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2011-01-04 14:50:54 +0000
@@ -45,6 +45,86 @@ a	b	c	d	a	b	c	d
 4	1	4	5	1	4	2	3
 4	3	1	2	3	1	1	2
 4	4	4	4	4	4	4	4
+explain extended 
+select count(*) 
+from t1 as x1 
+join t1 as x2 on x1.d > x2.a + 1000 
+join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	16	100.00	
+1	SIMPLE	x2	ALL	NULL	NULL	NULL	NULL	16	100.00	Using where; Using join buffer
+1	SIMPLE	x3	eq_ref	PRIMARY	PRIMARY	8	test.x1.c,test.x1.d	1	100.00	
+Warnings:
+Note	1644	Can't push table 'x2' as child, 'type' must be a 'ref' access
+Note	1644	Cannot push table 'x3' as child of table 'x1'. Doing so would prevent using join buffer for table 'x2'.
+Note	1644	Cannot push table 'x3' as child of 'x2', since it referes to column 'x1.c' which will be stored in a join buffer.
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` where ((`test`.`x3`.`b` = `test`.`x1`.`d`) and (`test`.`x3`.`a` = `test`.`x1`.`c`) and (`test`.`x1`.`d` > (`test`.`x2`.`a` + 1000)))
+select count(*) 
+from t1 as x1 
+join t1 as x2 on x1.d > x2.a + 1000 
+join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
+count(*)
+0
+explain extended select * 
+from t1 as x1 
+join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b 
+join t1 as x3 
+join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	x1	ref	PRIMARY	PRIMARY	4	const	10	100.00	Parent of 2 pushed join@1
+1	SIMPLE	x2	eq_ref	PRIMARY	PRIMARY	8	test.x1.c,test.x1.d	1	100.00	Child of pushed join@1
+1	SIMPLE	x3	ALL	NULL	NULL	NULL	NULL	16	100.00	Using join buffer
+1	SIMPLE	x4	eq_ref	PRIMARY	PRIMARY	8	test.x3.c,test.x1.d	1	100.00	
+Warnings:
+Note	1644	Can't push table 'x3' as child, 'type' must be a 'ref' access
+Note	1644	Cannot push table 'x4' as child of table 'x1'. Doing so would prevent using join buffer for table 'x3'.
+Note	1644	Cannot push table 'x4' as child of 'x3', since it referes to column 'x1.d' which will be stored in a join buffer.
+Note	1003	select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x1`.`d` AS `d`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c`,`test`.`x2`.`d` AS `d`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b`,`test`.`x3`.`c` AS `c`,`test`.`x3`.`d` AS `d`,`test`.`x4`.`a` AS `a`,`test`.`x4`.`b` AS `b`,`test`.`x4`.`c` AS `c`,`test`.`x4`.`d` AS `d` from `test`.`t1` `x1` join `test`.`t1` `x2` join `test`.`t1` `x3` join `test`.`t1` `x4` where ((`test`.`x2`.`a` = `test`.`x1`.`c`) and (`test`.`x1`.`a` = 1) and (`test`.`x2`.`b` = `test`.`x1`.`d`) and (`test`.`x4`.`b` = `test`.`x1`.`d`) and (`test`.`x4`.`a` = `test`.`x3`.`c`))
+select * 
+from t1 as x1 
+join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b 
+join t1 as x3 
+join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
+a	b	c	d	a	b	c	d	a	b	c	d	a	b	c	d
+1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
+1	1	1	1	1	1	1	1	1	3	1	2	1	1	1	1
+1	1	1	1	1	1	1	1	1	4	2	3	2	1	3	4
+1	1	1	1	1	1	1	1	2	1	3	4	3	1	1	2
+1	1	1	1	1	1	1	1	2	2	2	2	2	1	3	4
+1	1	1	1	1	1	1	1	2	3	4	5	4	1	4	5
+1	1	1	1	1	1	1	1	3	1	1	2	1	1	1	1
+1	1	1	1	1	1	1	1	3	2	2	3	2	1	3	4
+1	1	1	1	1	1	1	1	3	3	3	3	3	1	1	2
+1	1	1	1	1	1	1	1	3	4	3	4	3	1	1	2
+1	1	1	1	1	1	1	1	4	1	4	5	4	1	4	5
+1	1	1	1	1	1	1	1	4	3	1	2	1	1	1	1
+1	1	1	1	1	1	1	1	4	4	4	4	4	1	4	5
+1	3	1	2	1	2	5	1	1	1	1	1	1	2	5	1
+1	3	1	2	1	2	5	1	1	3	1	2	1	2	5	1
+1	3	1	2	1	2	5	1	1	4	2	3	2	2	2	2
+1	3	1	2	1	2	5	1	2	1	3	4	3	2	2	3
+1	3	1	2	1	2	5	1	2	2	2	2	2	2	2	2
+1	3	1	2	1	2	5	1	2	3	4	5	4	2	5	1
+1	3	1	2	1	2	5	1	3	1	1	2	1	2	5	1
+1	3	1	2	1	2	5	1	3	2	2	3	2	2	2	2
+1	3	1	2	1	2	5	1	3	3	3	3	3	2	2	3
+1	3	1	2	1	2	5	1	3	4	3	4	3	2	2	3
+1	3	1	2	1	2	5	1	4	1	4	5	4	2	5	1
+1	3	1	2	1	2	5	1	4	3	1	2	1	2	5	1
+1	3	1	2	1	2	5	1	4	4	4	4	4	2	5	1
+1	4	2	3	2	3	4	5	1	1	1	1	1	3	1	2
+1	4	2	3	2	3	4	5	1	3	1	2	1	3	1	2
+1	4	2	3	2	3	4	5	1	4	2	3	2	3	4	5
+1	4	2	3	2	3	4	5	2	1	3	4	3	3	3	3
+1	4	2	3	2	3	4	5	2	2	2	2	2	3	4	5
+1	4	2	3	2	3	4	5	2	3	4	5	4	3	1	2
+1	4	2	3	2	3	4	5	3	1	1	2	1	3	1	2
+1	4	2	3	2	3	4	5	3	2	2	3	2	3	4	5
+1	4	2	3	2	3	4	5	3	3	3	3	3	3	3	3
+1	4	2	3	2	3	4	5	3	4	3	4	3	3	3	3
+1	4	2	3	2	3	4	5	4	1	4	5	4	3	1	2
+1	4	2	3	2	3	4	5	4	3	1	2	1	3	1	2
+1	4	2	3	2	3	4	5	4	4	4	4	4	3	1	2
 explain extended
 select *
 from t1
@@ -397,11 +477,12 @@ from (t1 as x cross join t1 as y)
 join t1 as z on z.a=x.a and z.b=y.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	x	ALL	PRIMARY	NULL	NULL	NULL	16	100.00	
-1	SIMPLE	y	ALL	NULL	NULL	NULL	NULL	16	100.00	Parent of 2 pushed join@1
-1	SIMPLE	z	eq_ref	PRIMARY	PRIMARY	8	test.x.a,test.y.b	1	100.00	Child of pushed join@1
+1	SIMPLE	y	ALL	NULL	NULL	NULL	NULL	16	100.00	Using join buffer
+1	SIMPLE	z	eq_ref	PRIMARY	PRIMARY	8	test.x.a,test.y.b	1	100.00	
 Warnings:
 Note	1644	Can't push table 'y' as child, 'type' must be a 'ref' access
-Note	1644	Can't push table 'z' as child of 'x', column 'y.b' is outside scope of pushable join
+Note	1644	Cannot push table 'z' as child of table 'x'. Doing so would prevent using join buffer for table 'y'.
+Note	1644	Cannot push table 'z' as child of 'y', since it referes to column 'x.a' which will be stored in a join buffer.
 Note	1003	select straight_join `test`.`x`.`a` AS `a`,`test`.`x`.`b` AS `b`,`test`.`x`.`c` AS `c`,`test`.`x`.`d` AS `d`,`test`.`y`.`a` AS `a`,`test`.`y`.`b` AS `b`,`test`.`y`.`c` AS `c`,`test`.`y`.`d` AS `d`,`test`.`z`.`a` AS `a`,`test`.`z`.`b` AS `b`,`test`.`z`.`c` AS `c`,`test`.`z`.`d` AS `d` from `test`.`t1` `x` join `test`.`t1` `y` join `test`.`t1` `z` where ((`test`.`z`.`b` = `test`.`y`.`b`) and (`test`.`z`.`a` = `test`.`x`.`a`))
 select straight_join *
 from (t1 as x cross join t1 as y)
@@ -4443,13 +4524,15 @@ on table2.pk =  table3.pk )
 on table1.a =  table4.pk
 where  table2.pk != 6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	table1	ALL	NULL	NULL	NULL	NULL	6	33.33	Parent of 2 pushed join@1; Using temporary
-1	SIMPLE	table2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Parent of 2 pushed join@2; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct
-1	SIMPLE	table3	eq_ref	PRIMARY	PRIMARY	4	test.table2.pk	1	100.00	Child of pushed join@2; Distinct
-1	SIMPLE	table4	eq_ref	PRIMARY	PRIMARY	4	test.table1.a	1	100.00	Child of pushed join@1; Using where; Distinct
+1	SIMPLE	table1	ALL	NULL	NULL	NULL	NULL	6	33.33	Using temporary
+1	SIMPLE	table2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`table2`.`pk` <> 6); Distinct; Using join buffer
+1	SIMPLE	table3	eq_ref	PRIMARY	PRIMARY	4	test.table2.pk	1	100.00	Child of pushed join@1; Distinct
+1	SIMPLE	table4	eq_ref	PRIMARY	PRIMARY	4	test.table1.a	1	100.00	Using where; Distinct
 Warnings:
 Note	1644	Can't push table 'table2' as child, 'type' must be a 'ref' access
-Note	1644	Can't push table 'table3' as child of 'table1', column 'table2.pk' is outside scope of pushable join
+Note	1644	Cannot push table 'table3' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
+Note	1644	Cannot push table 'table4' as child of table 'table1'. Doing so would prevent using join buffer for table 'table2'.
+Note	1644	Cannot push table 'table4' as child of 'table2', since it referes to column 'table1.a' which will be stored in a join buffer.
 Note	1003	select straight_join distinct `test`.`table1`.`pk` AS `pk` from `test`.`t` `table1` join `test`.`t` `table2` join `test`.`t` `table3` join `test`.`t` `table4` where ((`test`.`table3`.`pk` = `test`.`table2`.`pk`) and (`test`.`table4`.`a` = `test`.`table2`.`pk`) and (`test`.`table4`.`pk` = `test`.`table1`.`a`) and (`test`.`table2`.`pk` <> 6))
 select distinct straight_join table1.pk FROM 
 t as table1  join
@@ -4708,32 +4791,32 @@ and spj_counts_at_end.counter_name <> 'L
        and spj_counts_at_end.counter_name <> 'SCAN_BATCHES_RETURNED';
 counter_name	spj_counts_at_end.val - spj_counts_at_startup.val
 CONST_PRUNED_RANGE_SCANS_RECEIVED	6
-LOCAL_TABLE_SCANS_SENT	260
+LOCAL_TABLE_SCANS_SENT	226
 PRUNED_RANGE_SCANS_RECEIVED	17
-RANGE_SCANS_RECEIVED	722
-READS_NOT_FOUND	405
+RANGE_SCANS_RECEIVED	718
+READS_NOT_FOUND	404
 READS_RECEIVED	61
-SCAN_ROWS_RETURNED	70269
-TABLE_SCANS_RECEIVED	260
+SCAN_ROWS_RETURNED	69724
+TABLE_SCANS_RECEIVED	226
 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
        from spj_counts_at_end, spj_counts_at_startup 
 where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
 and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT'
        or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
 LOCAL+REMOTE READS_SENT
-29150
+28877
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2425
+2414
 pruned_scan_count
 8
 sorted_scan_count
 9
 pushed_queries_defined
-352
+350
 pushed_queries_dropped
 11
 pushed_queries_executed
-556
+537
 set ndb_join_pushdown = @save_ndb_join_pushdown;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-12-07 13:58:30 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2011-01-04 14:50:54 +0000
@@ -56,6 +56,32 @@ select *
 from t1
 join t1 as t2 on t2.a = t1.b and t2.b = t1.c;
 
+# Check that we do not push an operation if this prevents us from using
+# 'join buffer'.
+explain extended 
+select count(*) 
+from t1 as x1 
+join t1 as x2 on x1.d > x2.a + 1000 
+join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
+select count(*) 
+from t1 as x1 
+join t1 as x2 on x1.d > x2.a + 1000 
+join t1 as x3 on x1.c=x3.a and x1.d=x3.b;
+
+# Check that we do not push an operation if this prevents us from using
+# 'join buffer'.
+explain extended select * 
+from t1 as x1 
+join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b 
+join t1 as x3 
+join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
+--sorted_result
+select * 
+from t1 as x1 
+join t1 as x2 on x1.a=1 and x1.c=x2.a and x1.d=x2.b 
+join t1 as x3 
+join t1 as x4 where x4.a=x3.c and x4.b=x1.d;
+
 explain extended
 select *
 from t1

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-12-07 13:58:30 +0000
+++ b/sql/abstract_query_plan.cc	2011-01-04 14:50:54 +0000
@@ -534,6 +534,16 @@ namespace AQP
   }
 
   /**
+    Check if the results from this operation will joined with results 
+    from the next operation using a join buffer (instead of plain nested loop).
+    @return True if using a join buffer. 
+  */
+  bool Table_access::uses_join_cache() const
+  {
+    return get_join_tab()->next_select == sub_select_cache;
+  }
+
+  /**
     @param plan Iterate over fields within this plan.
     @param field_item Iterate over Item_fields equal to this.
   */

=== modified file 'sql/abstract_query_plan.h'
--- a/sql/abstract_query_plan.h	2010-11-16 13:26:19 +0000
+++ b/sql/abstract_query_plan.h	2011-01-04 14:50:54 +0000
@@ -213,6 +213,8 @@ namespace AQP
 
     bool is_fixed_ordered_index() const;
 
+    bool uses_join_cache() const;
+
   private:
 
     /** Backref. to the Join_plan which this Table_access is part of */

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-12-07 17:27:02 +0000
+++ b/sql/ha_ndbcluster.cc	2011-01-04 14:50:54 +0000
@@ -957,7 +957,7 @@ ndb_pushed_builder_ctx::is_pushable_as_c
                            const AQP::Table_access*& parent)
 {
   DBUG_ENTER("is_pushable_as_child");
-  uint tab_no = table->get_access_no();
+  const uint tab_no = table->get_access_no();
   parent= NULL;
 
   DBUG_ASSERT (join_root() < table);
@@ -1007,6 +1007,20 @@ ndb_pushed_builder_ctx::is_pushable_as_c
     DBUG_RETURN(false);
   }
 
+  for (uint i = tab_no - 1; i >= join_root()->get_access_no() && i < ~uint(0); 
+       i--)
+  {
+    if (m_plan.get_table_access(i)->uses_join_cache())
+    {
+      EXPLAIN_NO_PUSH("Cannot push table '%s' as child of table '%s'. Doing so "
+                      "would prevent using join buffer for table '%s'.",
+                      table->get_table()->alias,
+                      join_root()->get_table()->alias,
+                      m_plan.get_table_access(i+1)->get_table()->alias);
+      DBUG_RETURN(false);
+    }
+  }
+
   DBUG_PRINT("info", ("Table:%d, Checking %d REF keys", tab_no, 
                       table->get_no_of_key_fields()));
 
@@ -1171,6 +1185,33 @@ ndb_pushed_builder_ctx::is_pushable_as_c
       // This key item is const. and did not cause the set of possible parents
       // to be recalculated. Reuse what we had before this key item.
       DBUG_ASSERT(parents.is_clear_all());
+      /** 
+       * Scan queries cannot be pushed if the pushed query may refer column 
+       * values (paramValues) from rows stored in a join cache.  
+       */
+      if (!is_lookup_operation(root_type))
+      {
+        const st_table* const referred_tab = key_item_field->field->table;
+        uint access_no = tab_no;
+        do
+        {
+          DBUG_ASSERT(access_no > 0);
+          access_no--;
+          if (m_plan.get_table_access(access_no)->uses_join_cache())
+          {
+            EXPLAIN_NO_PUSH("Cannot push table '%s' as child of '%s', since "
+                            "it referes to column '%s.%s' which will be stored "
+                            "in a join buffer.",
+                            table->get_table()->alias, 
+                            join_root()->get_table()->alias,
+                            get_referred_table_access_name(key_item_field),
+                            get_referred_field_name(key_item_field));
+            DBUG_RETURN(false);
+          }
+        } while (m_plan.get_table_access(access_no)->get_table() 
+                 != referred_tab);
+
+      } // if (!is_lookup_operation(root_type)
       parents= old_parents;
     }
     else
@@ -1221,11 +1262,12 @@ ndb_pushed_builder_ctx::is_pushable_as_c
   }
   else // scan operation
   {
-    for (parent_no= tab_no-1;
-         parent_no >= join_root()->get_access_no() && !parents.contain_table(m_plan.get_table_access(parent_no));
-         parent_no--)
-    {}
-
+    parent_no= tab_no-1;
+    while (!parents.contain_table(m_plan.get_table_access(parent_no)))
+    {
+      DBUG_ASSERT(parent_no > join_root()->get_access_no());
+      parent_no--;
+    }
     /**
      * If parent already has a scan descendant:
      *   appending 'table' will make this a 'bushy scan' which we don't yet nativily support as a pushed operation.
@@ -2136,20 +2178,6 @@ ha_ndbcluster::test_push_flag(enum ha_pu
 
     DBUG_RETURN(true);
   }
-  case HA_PUSH_BLOCK_JOINCACHE:
-    /**
-     * Join cache is blocked for pushed join root if the 
-     * pushed join may refer column values (paramValues)
-     * from rows stored in the join cache.
-     */
-    if (m_pushed_join &&
-         (m_pushed_join->get_field_referrences_count() > 0 || // Childs has field refs
-         !m_pushed_join->get_query_def().isScanQuery()))      // Roots lookup keys may refer joincache
-    {
-      DBUG_RETURN(true);
-    }
-    DBUG_RETURN(false);
-
   case HA_PUSH_MULTIPLE_DEPENDENCY:
     /**
      * If any child operation within this pushed join refer 

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2010-11-03 14:11:46 +0000
+++ b/sql/handler.h	2011-01-04 14:50:54 +0000
@@ -429,11 +429,6 @@ enum ha_push_flag {
   /* Handler want to block const table optimization */
   HA_PUSH_BLOCK_CONST_TABLE
 
-  /* Handler want to block 'Using join buffer' on the pushed join
-     having this handler instance as *root*.
-   */
-  ,HA_PUSH_BLOCK_JOINCACHE
-
   /* Handler reports a pushed join as having multiple dependencies 
      if its results does not only depend on the root operation:
      ie. results from some child operations does not only depend

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-21 10:38:10 +0000
+++ b/sql/sql_select.cc	2011-01-04 14:50:54 +0000
@@ -1687,19 +1687,6 @@ make_pushed_join(THD *thd, JOIN *join)
       }
       active_pushed_joins--;
     }
-
-    // Disable 'Using join buffer' if there are active pushed join sequence
-    // across the scope of the join buffer.
-    //  -- OR --
-    // the handler for the next JOIN_TAB (having cur. as source) has requested
-    // join caching to be disabled.
-    //
-    if (tab->next_select == sub_select_cache && 
-        (active_pushed_joins > 0 ||
-         (i+1 < join->tables && tab[1].table->file->test_push_flag(HA_PUSH_BLOCK_JOINCACHE))))
-    {
-      tab->next_select=sub_select;
-    }
   }
 
   /* If we just pushed a join containing an ORDER BY and/or a GROUP BY clause,


Attachment: [text/bzr-bundle] bzr/jan.wedvik@sun.com-20110104145054-1owur5hct65frcgs.bundle
Thread
bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(jan.wedvik:3402) Jan Wedvik4 Jan