List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:January 14 2011 3:53pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3253) WL#5741
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:jorgen.loland@stripped

 3253 Jorgen Loland	2011-01-14
      WL#5741: Add optimizer tracing to subqueries
      
      * Fix review comments by Guilhem
      * Update copyright notice.

    modified:
      WL4800_TODO.txt
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/optimizer_trace_no_prot.result
      mysql-test/r/optimizer_trace_ps_prot.result
      mysql-test/r/optimizer_trace_range.result
      mysql-test/r/optimizer_trace_subquery.result
      mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result
      sql/item_subselect.cc
      sql/item_subselect.h
      sql/opt_range.cc
      sql/opt_range.h
      sql/opt_trace.cc
      sql/opt_trace.h
      sql/sql_select.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt	2011-01-12 13:44:58 +0000
+++ b/WL4800_TODO.txt	2011-01-14 15:53:48 +0000
@@ -32,10 +32,6 @@ fix all crashes of --opt-trace-protocol
 
 try to avoid the call to get_current_struct in opt_range.cc
 
-solve the problems of "one trace per row scanned" ("range checked for
-each record" and subqueries); one example is at the end of
-http://lists.mysql.com/commits/124599
-
 check save_in_field_no_warnings() in opt_range.cc: do we need to
 disable tracing there? see optimizer_trace_no_prot.result. Jorgen has
 maybe looked at this in his in-review subquery tracing patch.
@@ -53,3 +49,5 @@ range access anyway.
 
 Make --opt-trace-protocol dump traces to a separate file so that mtr
 can run with it without failing all tests.
+
+Update copyright header of all changed files to reflect changed in 2011

=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result	2011-01-14 15:53:48 +0000
@@ -872,7 +872,7 @@ optimizer-prune-level 1
 optimizer-search-depth 62
 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
 optimizer-trace 
-optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on
+optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 optimizer-trace-limit 1
 optimizer-trace-max-mem-size 16384
 optimizer-trace-offset -1

=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result	2011-01-14 15:53:48 +0000
@@ -2832,7 +2832,7 @@ explain select * from t1,t2	{
 }	0	0
 select @@optimizer_trace_features;
 @@optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 set @@optimizer_trace_features="greedy_search=off";
 explain select * from t1,t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -3515,6 +3515,34 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 }
               ] /* steps */
             } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
           }
         ] /* steps */
       } /* join_execution */
@@ -4977,6 +5005,20 @@ select * from t1 where (t1.a,t1.b) not i
                 }
               ] /* steps */
             } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
           }
         ] /* steps */
       } /* join_execution */

=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result	2011-01-14 15:53:48 +0000
@@ -2816,7 +2816,7 @@ explain select * from t1,t2	{
 }	0	0
 select @@optimizer_trace_features;
 @@optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 set @@optimizer_trace_features="greedy_search=off";
 explain select * from t1,t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -3499,6 +3499,34 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 }
               ] /* steps */
             } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
           }
         ] /* steps */
       } /* join_execution */
@@ -4953,6 +4981,20 @@ select * from t1 where (t1.a,t1.b) not i
                 }
               ] /* steps */
             } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
           }
         ] /* steps */
       } /* join_execution */

=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_range.result	2011-01-14 15:53:48 +0000
@@ -3450,6 +3450,40 @@ EXPLAIN SELECT 1 FROM 
       "join_execution": {
         "select#": 2,
         "steps": [
+          {
+            "records_estimation_per_record": {
+              "database": "test",
+              "table": "t2",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 3,
+                  "cost": 4.7051
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "b",
+                    "usable": true,
+                    "key_parts": [
+                      "b"
+                    ] /* key_parts */
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "b",
+                  "cost": 1.6465,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                  {
+                    "impossible_condition": {
+                      "cause": "comparison_with_null_always_false"
+                    } /* impossible_condition */
+                  }
+                ] /* setup_range_conditions */,
+                "impossible_range": true
+              } /* range_analysis */
+            } /* records_estimation_per_record */
+          }
         ] /* steps */
       } /* join_execution */
     },
@@ -3735,6 +3769,20 @@ SELECT * from t1 where topic = all (SELE
                 }
               ] /* steps */
             } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
           }
         ] /* steps */
       } /* join_execution */

=== modified file 'mysql-test/r/optimizer_trace_subquery.result'
--- a/mysql-test/r/optimizer_trace_subquery.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/r/optimizer_trace_subquery.result	2011-01-14 15:53:48 +0000
@@ -413,6 +413,13 @@ t1.a= (SELECT a FROM t2 LIMIT 1)	{
                           }
                         ] /* steps */
                       } /* subselect_execution */
+                    },
+                    {
+                      "subselect_execution": {
+                        "select#": 2,
+                        "steps": [
+                        ] /* steps */
+                      } /* subselect_execution */
                     }
                   ] /* subselect_equality_propagation */,
                   "resulting_condition": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))"

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result	2011-01-14 13:42:35 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result	2011-01-14 15:53:48 +0000
@@ -1,25 +1,25 @@
 SET @start_global_value = @@global.optimizer_trace_features;
 SELECT @start_global_value;
 @start_global_value
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 select @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 select @@session.optimizer_trace_features;
 @@session.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 show global variables like 'optimizer_trace_features';
 Variable_name	Value
-optimizer_trace_features	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+optimizer_trace_features	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 show session variables like 'optimizer_trace_features';
 Variable_name	Value
-optimizer_trace_features	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+optimizer_trace_features	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 select * from information_schema.global_variables where variable_name='optimizer_trace_features';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+OPTIMIZER_TRACE_FEATURES	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 select * from information_schema.session_variables where variable_name='optimizer_trace_features';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+OPTIMIZER_TRACE_FEATURES	greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
 set global optimizer_trace_features=2;
 select @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
@@ -49,4 +49,4 @@ ERROR 42000: Variable 'optimizer_trace_f
 SET @@global.optimizer_trace_features = @start_global_value;
 SELECT @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=off
+greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-01-14 13:42:35 +0000
+++ b/sql/item_subselect.cc	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2011-01-14 13:42:35 +0000
+++ b/sql/item_subselect.h	2011-01-14 15:53:48 +0000
@@ -1,7 +1,7 @@
 #ifndef ITEM_SUBSELECT_INCLUDED
 #define ITEM_SUBSELECT_INCLUDED
 
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-01-14 13:42:35 +0000
+++ b/sql/opt_range.cc	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -1152,7 +1152,9 @@ SQL_SELECT *make_select(TABLE *head, tab
 }
 
 
-SQL_SELECT::SQL_SELECT() :quick(0),cond(0),icp_cond(0),free_cond(0)
+SQL_SELECT::SQL_SELECT() :
+  quick(0), cond(0), icp_cond(0),
+  free_cond(0), traced_before(false)
 {
   my_b_clear(&file);
 }
@@ -1169,6 +1171,7 @@ void SQL_SELECT::cleanup()
     cond= 0;
   }
   close_cached_file(&file);
+  traced_before= false;
 }
 
 
@@ -2588,9 +2591,6 @@ int SQL_SELECT::test_quick_select(THD *t
     if (cond)
     {
       {
-        //Guilhem: this is needed because we might evaluate a subquery
-        //inside get_mm_tree, but if you have a better suggestion I'm
-        //all ears. I don't like this tracepoint very much.
         Opt_trace_array trace_setup_cond(trace, "setup_range_conditions");
         tree= get_mm_tree(&param,cond);
       }

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2011-01-03 13:10:02 +0000
+++ b/sql/opt_range.h	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -856,6 +856,14 @@ class SQL_SELECT :public Sql_alloc {
   table_map const_tables,read_tables;
   bool	free_cond;
 
+  /** 
+      Used by optimizer tracing if this is a subquery: Whether or not
+      execution of this subselect has been traced by optimizer tracing
+      already. If optimizer trace option DYNAMIC_RANGE is disabled,
+      this is used to disable tracing after the first one.
+  */
+  bool traced_before;
+
   SQL_SELECT();
   ~SQL_SELECT();
   void cleanup();

=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc	2011-01-14 13:42:35 +0000
+++ b/sql/opt_trace.cc	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2009, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -386,9 +386,10 @@ const char *Opt_trace_context::feature_n
 };
 
 const Opt_trace_context::feature_value Opt_trace_context::FEATURES_DEFAULT=
-  Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH |
-                                   Opt_trace_context::DYNAMIC_RANGE |
-                                   Opt_trace_context::RANGE_OPTIMIZER);
+  Opt_trace_context::feature_value(Opt_trace_context::GREEDY_SEARCH   |
+                                   Opt_trace_context::RANGE_OPTIMIZER |
+                                   Opt_trace_context::DYNAMIC_RANGE   |
+                                   Opt_trace_context::REPEATED_SUBSELECT);
 
 Opt_trace_context::Opt_trace_context(void):
   oldest_stmt_to_show(NULL), newest_stmt_to_show(NULL), stmt_to_del(NULL),

=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h	2011-01-14 13:42:35 +0000
+++ b/sql/opt_trace.h	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2009, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2009, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-14 13:42:35 +0000
+++ b/sql/sql_select.cc	2011-01-14 15:53:48 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -18436,10 +18436,14 @@ join_init_quick_read_record(JOIN_TAB *ta
 
 #ifdef OPTIMIZER_TRACE  
   Opt_trace_context * const trace= tab->join->thd->opt_trace;
-  const bool trace_enabled= trace ? 
+  const bool repeated_trace_enabled= trace ? 
     trace->feature_enabled(Opt_trace_context::DYNAMIC_RANGE) :
     false;
-  Opt_trace_disable_I_S disable_trace_wrapper(trace, !trace_enabled);
+  const bool disable_trace= 
+    (tab->select->traced_before && !repeated_trace_enabled);
+  Opt_trace_disable_I_S disable_trace_wrapper(trace, disable_trace);
+
+  tab->select->traced_before= true;
 
   Opt_trace_object wrapper(trace);
   Opt_trace_object trace_table(trace, "records_estimation_per_record");


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110114155348-m2513pgvsj6d479q.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3253) WL#5741Jorgen Loland14 Jan
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3253)WL#5741Guilhem Bichot17 Jan
    • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3253)WL#5741Jorgen Loland18 Jan
      • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3253)WL#5741Guilhem Bichot18 Jan