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

Sergey Petrunya psergey at askmonty.org
Thu Apr 26 04:10:40 EEST 2012


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

------------------------------------------------------------
revno: 3502
revision-id: psergey at askmonty.org-20120426011036-eaosozjasypr49o1
parent: psergey at askmonty.org-20120413210115-0xirn62a14901bi0
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-show-explain-r20
timestamp: Thu 2012-04-26 06:40:36 +0530
message:
  MWL#182: Explain running statements
  - Code cleanup
=== modified file 'mysql-test/r/show_explain.result'
--- a/mysql-test/r/show_explain.result	2012-04-13 21:01:15 +0000
+++ b/mysql-test/r/show_explain.result	2012-04-26 01:10:36 +0000
@@ -1,4 +1,4 @@
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int);
@@ -125,4 +125,60 @@
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
+# Try to do SHOW EXPLAIN for a query that runs a  SET command:
+#   I've found experimentally that select_id==2 here...
+# 
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+set @foo= (select max(a) from t0 where sin(a) >0);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+#
+# Attempt SHOW EXPLAIN for an UPDATE
+#
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Attempt SHOW EXPLAIN for a DELETE
+# 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Multiple SHOW EXPLAIN calls for one select 
+# 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
 drop table t0,t1;

=== modified file 'mysql-test/t/show_explain.test'
--- a/mysql-test/t/show_explain.test	2011-10-27 22:30:02 +0000
+++ b/mysql-test/t/show_explain.test	2012-04-26 01:10:36 +0000
@@ -4,7 +4,7 @@
 --source include/have_debug.inc
 
 --disable_warnings
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 --enable_warnings
 
 # 
@@ -186,12 +186,75 @@
 
 # TODO: explain in the parent subuqery when the un-correlated child has been
 # run (and have done irreversible cleanups)
-
-# TODO: hit JOIN::optimize for non-select commands: UPDATE/DELETE, SET.
-
-
-## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select
-## 
+#  ^^ Is this at all possible after 5.3? 
+#     Maybe, for 5.3 try this: 
+#       - run before/after the parent has invoked child's optimization
+#       - run after materialization 
+
+--echo # Try to do SHOW EXPLAIN for a query that runs a  SET command:
+--echo #   I've found experimentally that select_id==2 here...
+--echo # 
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send set @foo= (select max(a) from t0 where sin(a) >0);
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for an UPDATE
+--echo #
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for a DELETE
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+
+--echo #
+--echo # Multiple SHOW EXPLAIN calls for one select 
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 

=== modified file 'sql/my_apc.cc'
--- a/sql/my_apc.cc	2011-09-24 17:56:42 +0000
+++ b/sql/my_apc.cc	2012-04-26 01:10:36 +0000
@@ -24,6 +24,14 @@
 */
 
 
+/* 
+  Initialize the target. 
+   
+  @note 
+  Initialization must be done prior to enabling/disabling the target, or making
+  any call requests to it.
+  Initial state after initialization is 'disabled'.
+*/
 void Apc_target::init()
 {
   // todo: should use my_pthread_... functions instead?
@@ -36,6 +44,9 @@
 }
 
 
+/* 
+  Destroy the target. The target must be disabled when this call is made.
+*/
 void Apc_target::destroy()
 {
   DBUG_ASSERT(!enabled);
@@ -43,6 +54,9 @@
 }
 
 
+/* 
+  Enter ther state where the target is available for serving APC requests
+*/
 void Apc_target::enable()
 {
   pthread_mutex_lock(&LOCK_apc_queue);
@@ -51,6 +65,13 @@
 }
 
 
+/* 
+  Make the target unavailable for serving APC requests. 
+  
+  @note
+    This call will serve all requests that were already enqueued
+*/
+
 void Apc_target::disable()
 {
   bool process= FALSE;
@@ -62,6 +83,9 @@
     process_apc_requests();
 }
 
+
+/* (internal) Put request into the request list */
+
 void Apc_target::enqueue_request(Call_request *qe)
 {
   //call_queue_size++;
@@ -81,6 +105,13 @@
   }
 }
 
+
+/* 
+  (internal) Remove given request from the request queue. 
+  
+  The request is not necessarily first in the queue.
+*/
+
 void Apc_target::dequeue_request(Call_request *qe)
 {
   //call_queue_size--;
@@ -99,8 +130,10 @@
 
 
 /*
-  Make an apc call in another thread. The caller is responsible so 
-  that we're not calling to ourselves.
+  Make an APC (Async Procedure Call) in another thread. 
+
+  The caller is responsible for making sure he's not calling an Apc_target 
+  that is serviced by the same thread it is called from.
   
   psergey-todo: Should waits here be KILLable? (it seems one needs 
   to use thd->enter_cond() calls to be killable)
@@ -119,7 +152,7 @@
     Call_request apc_request;
     apc_request.func= func;
     apc_request.func_arg= func_arg;
-    apc_request.done= FALSE;
+    apc_request.processed= FALSE;
     (void)pthread_cond_init(&apc_request.COND_request, NULL);
     (void)pthread_mutex_init(&apc_request.LOCK_request, MY_MUTEX_INIT_SLOW);
     pthread_mutex_lock(&apc_request.LOCK_request);
@@ -133,19 +166,19 @@
     
     int wait_res= 0;
     /* todo: how about processing other errors here? */
-    while (!apc_request.done && (wait_res != ETIMEDOUT))
+    while (!apc_request.processed && (wait_res != ETIMEDOUT))
     {
       wait_res= pthread_cond_timedwait(&apc_request.COND_request,
                                        &apc_request.LOCK_request, &abstime);
     }
 
-    if (!apc_request.done)
+    if (!apc_request.processed)
     {
-      /* We timed out */
-      apc_request.done= TRUE;
+      /* The wait has timed out. Remove the request from the queue */
+      apc_request.processed= TRUE;
       *timed_out= TRUE;
       pthread_mutex_unlock(&apc_request.LOCK_request);
-
+      //psergey-todo: "Whoa rare event" refers to this part, right? put a comment.
       pthread_mutex_lock(&LOCK_apc_queue);
       dequeue_request(&apc_request);
       pthread_mutex_unlock(&LOCK_apc_queue);
@@ -171,7 +204,8 @@
 
 
 /*
-  Process all APC requests
+  Process all APC requests.
+  This should be called periodically by the APC target thread.
 */
 
 void Apc_target::process_apc_requests()
@@ -190,7 +224,7 @@
     request->what="seen by process_apc_requests";
     pthread_mutex_lock(&request->LOCK_request);
 
-    if (request->done)
+    if (request->processed)
     {
       /*
         We can get here when
@@ -214,7 +248,7 @@
     */
     request->what="dequeued by process_apc_requests";
     dequeue_request(request);
-    request->done= TRUE;
+    request->processed= TRUE;
 
     pthread_mutex_unlock(&LOCK_apc_queue);
 

=== modified file 'sql/my_apc.h'
--- a/sql/my_apc.h	2011-09-24 17:56:42 +0000
+++ b/sql/my_apc.h	2012-04-26 01:10:36 +0000
@@ -3,9 +3,18 @@
 */
 
 /*
-  Design
-  - Mutex-guarded request queue (it belongs to the target), which can be enabled/
-    disabled (when empty).
+  Interface
+  ~~~~~~~~~
+   (
+    - This is an APC request queue
+    - We assume there is a particular owner thread which periodically calls
+      process_apc_requests() to serve the call requests.
+    - Other threads can post call requests, and block until they are exectued.
+  )
+
+  Implementation
+  ~~~~~~~~~~~~~~
+  - The target has a mutex-guarded request queue.
 
   - After the request has been put into queue, the requestor waits for request
     to be satisfied. The worker satisifes the request and signals the
@@ -21,31 +30,11 @@
   Apc_target() : enabled(0), apc_calls(NULL) /*, call_queue_size(0)*/ {} 
   ~Apc_target() { DBUG_ASSERT(!enabled && !apc_calls);}
 
-  /* 
-    Initialize the target. This must be called before anything else. Right
-    after initialization, the target is disabled.
-  */
   void init();
-
-  /* 
-    Destroy the target. The target must be disabled when this call is made.
-  */
   void destroy();
-  
-  /* 
-    Enter into state where this target will be serving APC requests
-  */
   void enable();
-
-  /* 
-    Leave the state where we could serve APC requests (will serve all already 
-    enqueued requests)
-  */
   void disable();
   
-  /*
-    This should be called periodically to serve observation requests.
-  */
   void process_apc_requests();
 
   typedef void (*apc_func_t)(void *arg);
@@ -68,18 +57,32 @@
 #endif
 private:
   class Call_request;
+
+  /* 
+    Non-zero value means we're enabled. It's an int, not bool, because one can
+    call enable() N times (and then needs to call disable() N times before the 
+    target is really disabled)
+  */
   int enabled;
 
+  /* 
+    Circular, double-linked list of all enqueued call requests. 
+    We use this structure, because we 
+     - process requests sequentially 
+     - a thread that has posted a request may time out (or be KILLed) and 
+       cancel the request, which means we'll need to remove its request at 
+       arbitrary point in time.
+  */
   Call_request *apc_calls;
+
   pthread_mutex_t LOCK_apc_queue;
 
-
   class Call_request
   {
   public:
-    apc_func_t func;
-    void *func_arg;
-    bool done;
+    apc_func_t func; /* Function to call */
+    void *func_arg;  /* Argument to pass it */
+    bool processed;
 
     pthread_mutex_t LOCK_request;
     pthread_cond_t COND_request;
@@ -87,13 +90,15 @@
     Call_request *next;
     Call_request *prev;
     
-    const char *what;
+    const char *what; /* State of the request */
   };
 
   void enqueue_request(Call_request *qe);
   void dequeue_request(Call_request *qe);
+
+  /* return the first call request in queue, or NULL if there are none enqueued */
   Call_request *get_first_in_queue()
-  { 
+  {
     return apc_calls;
   }
 };

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2012-04-13 21:01:15 +0000
+++ b/sql/sql_class.cc	2012-04-26 01:10:36 +0000
@@ -3033,7 +3033,8 @@
   req->target_thd->set_n_backup_active_arena((Query_arena*)req->request_thd,
                                              &backup_arena);
 
-  req->target_thd->lex->unit.print_explain(req->explain_buf);
+  if (req->target_thd->lex->unit.print_explain(req->explain_buf))
+    req->failed_to_produce= TRUE;
 
   req->target_thd->restore_active_arena((Query_arena*)req->request_thd, 
                                         &backup_arena);

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-04-13 21:01:15 +0000
+++ b/sql/sql_class.h	2012-04-26 01:10:36 +0000
@@ -1467,6 +1467,8 @@
   THD *target_thd;
   THD *request_thd;
   
+  bool failed_to_produce;
+  
   select_result_explain_buffer *explain_buf;
 
   static void get_explain_data(void *arg);

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-04-13 21:01:15 +0000
+++ b/sql/sql_lex.cc	2012-04-26 01:10:36 +0000
@@ -3792,6 +3792,15 @@
 {
   int res= 0;
   SELECT_LEX *first= first_select();
+  
+  if (first && !first->next_select() && !first->join)
+  {
+    /*
+      If there is only one child, 'first', and it has join==NULL, emit "not in
+      EXPLAIN state" error.
+    */
+    return 1;
+  }
 
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
   {

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2012-04-13 21:01:15 +0000
+++ b/sql/sql_show.cc	2012-04-26 01:10:36 +0000
@@ -2112,16 +2112,19 @@
     explain_req.explain_buf= explain_buf;
     explain_req.target_thd= tmp;
     explain_req.request_thd= thd;
+    explain_req.failed_to_produce= FALSE;
 
     bres= tmp->apc_target.make_apc_call(Show_explain_request::get_explain_data,
                                         (void*)&explain_req,
                                         timeout_sec, &timed_out);
-    if (bres)
+
+    if (bres || explain_req.failed_to_produce)
     {
       /* TODO not enabled or time out */
       my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0), 
                "SHOW EXPLAIN",
                "Target is not running EXPLAINable command");
+      bres= TRUE;
     }
     pthread_mutex_unlock(&tmp->LOCK_thd_data);
     if (!bres)



More information about the commits mailing list