From: Jorgen Loland Date: January 14 2011 3:54pm Subject: bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3252 to 3253) WL#5741 List-Archive: http://lists.mysql.com/commits/128821 Message-Id: <20110114155426.39F0A7A3@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 3252 Jorgen Loland 2011-01-14 WL#5741: Add optimizer tracing to subqueries Fix tracepoints so that all mtr test cases can be traced without JSON syntax errors. Added DYNAMIC_RANGE and REPEATED_SUBSELECT tracing features to avoid repeated tracing of range analysis and execution, respectively, of subselects. @ sql/item_subselect.cc Added Item_subselect::executed_before used to avoid tracing of subquery execution when this happens repeatedly for each record in outer select. @ sql/item_subselect.h Added Item_subselect::executed_before used to avoid tracing of subquery execution when this happens repeatedly for each record in outer select. @ sql/opt_range.cc Add tracepoints to range optimizer @ sql/sql_select.cc Add tracepoints to range optimizer added: mysql-test/r/optimizer_trace_subquery.result mysql-test/t/optimizer_trace_subquery.test modified: mysql-test/include/optimizer_trace.inc mysql-test/r/mysqld--help-notwin.result mysql-test/r/optimizer_trace2.result mysql-test/r/optimizer_trace_charset.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/suite/sys_vars/r/optimizer_trace_features_basic.result mysql-test/t/optimizer_trace_range.test sql/item_subselect.cc sql/item_subselect.h sql/opt_range.cc sql/opt_trace.cc sql/opt_trace.h sql/sql_select.cc sql/sys_vars.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(¶m,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"); No bundle (reason: useless for push emails).