[Commits] Rev 3157: MWL#182: Explain running statements in file:///home/psergey/dev2/5.3-show-explain-r2/

Sergey Petrunya psergey at askmonty.org
Wed Aug 24 13:41:23 EEST 2011


At file:///home/psergey/dev2/5.3-show-explain-r2/

------------------------------------------------------------
revno: 3157
revision-id: psergey at askmonty.org-20110824104113-af61bexgg44lwkcs
parent: psergey at askmonty.org-20110823152832-3cih0u6ripz4mcwm
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-show-explain-r2
timestamp: Wed 2011-08-24 14:41:13 +0400
message:
  MWL#182: Explain running statements
  - Further progress with the code
  - Testcases.
=== added file 'mysql-test/r/show_explain.result'
--- a/mysql-test/r/show_explain.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/show_explain.result	2011-08-24 10:41:13 +0000
@@ -0,0 +1,26 @@
+drop table if exists t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
+show explain for 2*1000*1000*1000;
+ERROR HY000: Unknown thread id: 2000000000
+show explain for 3;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for 2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+select get_lock('optimizer_done', 10);
+get_lock('optimizer_done', 10)
+1
+select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1);
+select get_lock('optimizer_done', 100);
+get_lock('optimizer_done', 100)
+1
+show explain for 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
+select release_lock('optimizer_done');
+release_lock('optimizer_done')
+1
+kill query 3;
+drop table t0,t1;

=== added file 'mysql-test/t/show_explain.test'
--- a/mysql-test/t/show_explain.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/show_explain.test	2011-08-24 10:41:13 +0000
@@ -0,0 +1,46 @@
+#
+# Tests for SHOW EXPLAIN FOR functionality
+#
+--disable_warnings
+drop table if exists t0, t1;
+--enable_warnings
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
+
+# 
+# Try killing a non-existent thread
+# 
+--error ER_NO_SUCH_THREAD
+show explain for 2*1000*1000*1000;
+
+# Setup two threads and their ids
+let $thr1=`select connection_id()`;
+connect (con1, localhost, root,,);
+connection con1;
+let $thr2=`select connection_id()`;
+connection default;
+
+# SHOW EXPLAIN FOR <idle thread>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+eval show explain for $thr2;
+
+# SHOW EXPLAIN FOR <ourselves>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+eval show explain for $thr1;
+
+# SHOW EXPLAIN FOR <running thread>
+connection con1;
+select get_lock('optimizer_done', 10);
+send select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1);
+connection default;
+select get_lock('optimizer_done', 100);
+eval show explain for $thr2;
+select release_lock('optimizer_done');
+eval kill query $thr2;
+
+#insert into t1 values ('one'),('two'),('three');
+
+drop table t0,t1;

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2011-08-23 15:28:32 +0000
+++ b/sql/sql_class.cc	2011-08-24 10:41:13 +0000
@@ -2932,7 +2932,14 @@
   DBUG_VOID_RETURN;
 }
 
-// psergey
+
+/*
+  Produce EXPLAIN data.
+
+  This function is APC-scheduled to be run in the context of the thread that
+  we're producing EXPLAIN for.
+*/
+
 void Show_explain_request::get_explain_data(void *arg)
 {
   Show_explain_request *req= (Show_explain_request*)arg;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2011-08-23 15:28:32 +0000
+++ b/sql/sql_lex.cc	2011-08-24 10:41:13 +0000
@@ -3625,19 +3625,24 @@
 
 int st_select_lex::print_explain(select_result_sink *output)
 {
+  int res;
   if (join && join->optimized == 2)
   {
-    //psergey-TODO: any?
-    return join->print_explain(output, TRUE,
-                     FALSE, // need_tmp_table, 
-                     FALSE, // bool need_order,
-                     FALSE, // bool distinct,
-                     NULL); //const char *message
+    res= join->print_explain(output, TRUE,
+                             FALSE, // need_tmp_table, 
+                             FALSE, // bool need_order,
+                             FALSE, // bool distinct,
+                             NULL); //const char *message
   }
   else
   {
-    DBUG_ASSERT(0);
-    /* produce "not yet optimized" line */
+    /* Produce "not yet optimized" line */
+    const char *msg="Not yet optimized";
+    res= join->print_explain(output, TRUE,
+                             FALSE, // need_tmp_table, 
+                             FALSE, // bool need_order,
+                             FALSE, // bool distinct,
+                             msg); //const char *message
   }
   return 0;
 }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-23 15:28:32 +0000
+++ b/sql/sql_select.cc	2011-08-24 10:41:13 +0000
@@ -2019,6 +2019,17 @@
 }
 
 
+void JOIN::exec()
+{
+  /*
+    Enable SHOW EXPLAIN only if we're in the top-level query.
+  */
+  thd->apc_target.enable();
+  exec_inner();
+  thd->apc_target.disable();
+}
+
+
 /**
   Exec select.
 
@@ -2030,8 +2041,8 @@
   @todo
     When can we have here thd->net.report_error not zero?
 */
-void
-JOIN::exec()
+
+void JOIN::exec_inner()
 {
   List<Item> *columns_list= &fields_list;
   int      tmp_error;
@@ -2894,9 +2905,7 @@
   if (thd->is_error())
     goto err;
 
-  thd->apc_target.enable();
   join->exec();
-  thd->apc_target.disable();
 
   if (thd->cursor && thd->cursor->is_open())
   {

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-08-23 15:28:32 +0000
+++ b/sql/sql_select.h	2011-08-24 10:41:13 +0000
@@ -1078,6 +1078,7 @@
   int reinit();
   int init_execution();
   void exec();
+  void exec_inner();
   int destroy();
   void restore_tmp();
   bool alloc_func_list();



More information about the commits mailing list