This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 Explain running statements
Title
Task ID182
Queue
Version N/A
Status
PriorityN/A
Copies toSergei

Created byMdcallag13 Mar 2011Done
Supervisor N/A  
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level Description
I want to know the query plan for running statements -- something like "SHOW PLAN 
FOR <connection_id>".  This requires running statements to generate a string that 
describes their plan in case another thread needs to examine it. For me it would 
be good enough to know the join order and indexes.
 Task Dependencies
Others waiting for Task 182Task 182 is waiting forGraph
218 Better EXPLAIN
 
 High-Level Specification
<contents>
1. General idea
1.1 Details about A3
2. Performance/overhead considerations
3. Possible problems with getting query plan midway the query execution
3.1 UNION construct
3.2 IN/EXISTS/scalar-context subqueries
3.3 FROM subqueries
3.4 ORDER/GROUP BY optimizations
3.5 Other differences due to all these if (select_options & SELECT_DESCRIBE)

</contents>

1. General idea
---------------
The server already has a similar functionality: each thread saves the query
string somewhere, and then SHOW PROCESSLIST displays query text for all active
connections.

The difference is that query text lives as long as prepared statement lives,
while query plan's lifetime is limited to one prepared statement's execution.

With regards to saving query plan information, there are two possible
approaches:

A1: Let each query save its query plan in some compact format somewhere in THD
structure; then let SHOW PLAN access that information.

A2: Let SHOW PLAN walk each statement's execution structures and produce
EXPLAIN output dynamically.

A1 seems to be easier to implement locking-wise.

A2 requires more work with locking to synchronize between the statement running 
the query while SHOW PLAN is walking its execution structures, but will allow
to extract more information (e.g. extract info about where we are in a
particular table scan, etc).

Sergei Golubchik has suggested the following:
A3: To avoid both [expensive and most-likely redundant] pre-processing step
of A1 and locking complications of A2, use a "cooperative" approach: 
- The SHOW PLAN thread should post a kind of request to query thread to 
  get its query plan.
- The query thread should periodically check whether it has a request, and 
  if yes, execute it. The check is already in the code - all threads check
  if they've been killed by KILL statement. What needs to be done is to
  amend the check to check for posted "give me your EXPLAIN info" requests.
The mechanism could be extended to allow posting of general callback calls to
other threads.

1.1 Details about A3
~~~~~~~~~~~~~~~~~~~~
There may be a situation where we want to post a "give me your EXPLAIN info"
request to a thread that already has such pending request.  We don't want the
complication of request queues, so the solution will be to wait until the 
first request is satisfied, and then post ours.

We'll need to handle the situation where two threads are running SHOW PLAN 
and attempt to request EXPLAIN info from one another. 

According to SergeiG, lock waits inside InnoDB do not prevent KILL statement
from working, which means that SHOW PLAN will not freeze when requesting EXPLAIN
info from a thread that's waiting for some InnoDB lock.

A possible generic approach would be:

- instead of checking
    if (thd->killed == something)
  make thd->killed a function pointer, and check as:
   if (thd->killed && thd->killed() == something).
  Detail 1: add thd->killed_arg, and call thd->killed(thd->killed_arg)
  Defail 2: rename "killed".
- this will introduce cooperative data/code exchange between threads, and
re-implement thd->killed using the new approach.
- the best part: it'll be completely transparent and backward compatible for
plugins, they call thd_killed() function that returns the value of thd->killed.
The semantics of this function won't change, so plugins won't be affected.
- but server code uses thd->killed directly. it needs to be changed to call
thd::killed() inline method, to encapsulate killed, and avoid future massive
changes.

(When discussed on optimizer call, Monty also voiced preference for A3)

2. Performance/overhead considerations
--------------------------------------
If we pick approach A1, saving query plan will add extra overhead. In order not
to have this overhead when it is not necessary, we'll need to introduce a
server variable which will control whether a statement should save its query
plan.

A3 has no additional overhead as compared to the old code (the tests for
thd->killed already exist) when the feature is not used.

3. Possible problems with getting query plan midway the query execution
-----------------------------------------------------------------------

DBMS with a more textbook-like design break down the process of query execution
into these steps:
  
  statement= parse_query();
  query_plan= statement->optimize();
  // (*)
  query_plan->execute();

There is a clear boundary between optimization and execution phases. One can 
put a call to save_plan() function at line (*), which will guarantee that 
whenever the query is running, we have its full query plan saved somewhere.

MySQL/MariaDB is different: optimization and execution phases are somewhat
interleaved, that is, parts of execution start before the optimization is 
finished.

3.1 UNION construct
~~~~~~~~~~~~~~~~~~~
One example is UNION queries. If one traces this query:

  SELECT * FROM t1 UNION SELECT * FROM t2

they will observe the calls:

  JOIN::optimize()   # first select
  JOIN::exec()       # first select
  JOIN::optimize()   # second select
  JOIN::exec()       # second select
  JOIN::optimize()   # result-of-union select
  JOIN::exec()       # result-of-union select

i.e. first part of the query will be executed before the second one is
optimized.

EXPLAIN .. UNION .. has a different execution flow where it will optimize 
all its selects without executing them (see mysql_explain_union()).

Altering UNION execution so that it optimizes everything first and then starts
execution is a too big change for this WL entry.

Proposed solution: SHOW PLAN should detect this case (should be a matter of 
checking a flag), and if it encounters a not-yet-optimized select, it should
display for it one EXPLAIN line with all columns being NULL except Extra that
should have 'Not yet optimized'.


3.2 IN/EXISTS/scalar-context subqueries
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When a SELECT statement is run, IN/EXIST/Scalar-context subqueries are optimized 
"lazily", i.e. right before they are executed for the first time. (This
property should be gone in MariaDB 5.3)

The solution here can be the same as with UNIONs.


3.3 FROM subqueries
~~~~~~~~~~~~~~~~~~~
FROM subquery will be optimized and executed (and its result stored in a
temptable) before the upper select is optimized and executed.

The solution here can be the same as with UNIONs.


3.4 ORDER/GROUP BY optimizations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Query plans with "Using filesort" or "Using filesort; Using temporary" are
executed as follows:

  access the first table in the join order, storing the obtained records 
    in the tempfile;
  run filesort() on tempfile contents;
  change join->join_tab[0] to access the data produced by filesort();  (*)
  ...

The step (*) will change join->join_tab[0].  Since a join can be part of the
subquery, it has support for multiple executions, which means that contents of 
join->join_tab[0] are not irrevocably lost. However, special arrangements may
be needed to get them.

(grep in sql_select.cc for calls to save_join_tab() and to make_simple_join()
to find the problematic areas.)

3.5 Other differences due to all these if (select_options & SELECT_DESCRIBE)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The optimizer source code has lots of statements like this:

  if(select_options & SELECT_DESCRIBE) 
    do this;
  else
    do that;


Nearly all of them are actually harmless (e.g. we will allocate actual buffers
or increment counters for SELECTs but not for EXPLAINs).

The only difference with possible impact that I could find is that 
create_ref_for_key() does something differently for  
  
   key_part=const_expresssion

depending on whether SELECT_DESCRIBE is set.

4. Questions
------------
- What's the exact syntax for the new statement:
  SHOW PLAN FOR?
  EXPLAIN [EXTENDED|PARTITIONS] FOR?
  A: SHOW PLAN FOR <id>

- What should be produced if the thread of interest is not running an
  EXPLAIN'able command? Empty set? Error?
  A: An error saying that plan information is not available for that thread

- How does one get *both* the EXPLAIN and the query it is for?
  First, I need to do SHOW PROCESSLIST to obtain a connection_id. Then I do 
  SHOW PLAN FOR $connection_id, and get a plan, but how do I know that the plan
  I've got is for the query I've seen? I could run SHOW PROCESSLIST again, and
  check that $connection_id is still executing the same query (by checking
  the query execution time).

  Would it help if SHOW PLAN returned multi-resultset, the first resultset
  being the query (or a line from SHOW PROCESSLIST), and the second being the
  EXPLAIN output?

  Or it can return the original query in a warning.
  A: Current solution is to produce query text in a warning.

- Most(All?) of SHOW commands have an equivalent in INFORMATION_SCHEMA. Do we
  need to expose the SHOW PLAN information through INFORMATION_SCHEMA, too?
  A: not in the scope of this WL entry.

- What permissions should one have to be able to do SHOW PLAN on some
  connection?
  A: the same as required for SHOW FULL PROCESSLIST.
 Low-Level Design
Code-wise, we'll need: 

Item                                                                    HOURS
-----------------------------------------------------------------------+-------
1. Hook 'SHOW PLAN FOR' into the parser                                |  4

2. Add "plumbing" code to handle the SQLCOM_SHOW_PLAN command: 
   make send_explain_fields() call, etc.                               |  4 

3. [Assuming option A3] 
   Add capability to produce [partial] query plan at any point in
   time during select execution. This includes 
   - Fixing all problems described in section "3. Possible problems
     with getting query plan midway the query execution"
     = 3.1 UNIONs                                                      |  4
     = 3.2 IN/EXISTS/scalar-context subqueries                         |  4
     = 3.3 FROM subqueries                                             |  4
     = 3.4 ORDER/GROUP BY optimizations                                |  8
     = 3.5 Other cases of "if (select_options & SELECT_DESCRIBE) {...}"|  8
   - making select_describe() not to modify any execution data 
     structures (currently, it may change values of join->error, 
     offset_limit_cnt, 
      server_status, join->join_tab[i].type)                           |  3
   
   - Implementing query tree traversal (so that we get parts of EXPLAIN
     for subqueries, union members, etc). We can re-use
     mysql_explain_union()'s loop but need to figure out how to make it
     not to modify any of query tree's data structures.                |  8

4. [Assuming option A3] 
   Implement the "plan retrieval engine": add functions to:
   - Locate the thread we're looking to get EXPLAIN for                |  4

   - Post it a request to produce its EXPLAIN, wait until              |  8
     the request have been executed.

   - Have all the code that checks for thd->killed also check if 
     there were any EXPLAIN requests posted, and execute them.         |  24
  
   - Arrange so that running select_describe() in the context of
     query thread delivers the result to the SHOW PLAN FOR thread.     |   4
     (According to Monty: it should be sufficient to change THD and
      some of its members, and then we will make it so that
       - temporary items are allocated on SHOW PLAN's mem_root
       - results are sent to SHOW PLAN's connection)

5. Testing                                                             |  16
6. Code review                                                         |  16
-----------------------------------------------------------------------+------
TOTAL:                                                                 | 120
 File Attachments
 NameTypeSizeByDate
 User Comments
(Mdcallag - 2011-04-08 00:33:11
    InnoDB behavior on lock wait timeout is described at http://bugs.mysql.com/bug.php?id=51920. It used to not check. Now it does.

(Mdcallag - 2011-04-13 15:39:50
    The "show plans" command must not block when clients don't respond quickly to a request for a plan. It should timeout after waiting X seconds.
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
Total000
 
 Funding and Votes
Votes: 1: 100%
 Change vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Psergey - Tue, 01 Nov 2011, 20:54
    
Encountered a problem: JOIN::exec() may call JOIN::join_free() somewhere
in the middle of execution, and that destroys join->join_tab[i].select.

Code that produces EXPLAIN output checks join->join_tab[i].select->cond when
deciding if it should print "Using where". So, EXPLAINs are printed without
"Using where" if SHOW EXPLAIN is called late enough.

(Monty - Thu, 30 Jun 2011, 16:58
    
Dependency created: WL#218 now depends on WL#182

(Psergey - Thu, 07 Apr 2011, 17:50
    
Low Level Design modified.
--- /tmp/wklog.182.old.14362	2011-04-07 17:50:10.000000000 +0000
+++ /tmp/wklog.182.new.14362	2011-04-07 17:50:10.000000000 +0000
@@ -43,7 +43,10 @@
       some of its members, and then we will make it so that
        - temporary items are allocated on SHOW PLAN's mem_root
        - results are sent to SHOW PLAN's connection)
+
+5. Testing                                                             |  16
+6. Code review                                                         |  16
 -----------------------------------------------------------------------+------
-TOTAL:                                                                 |  90
+TOTAL:                                                                 | 120
 
 

(Psergey - Thu, 07 Apr 2011, 15:17
    
Low Level Design modified.
--- /tmp/wklog.182.old.9899	2011-04-07 15:17:38.000000000 +0000
+++ /tmp/wklog.182.new.9899	2011-04-07 15:17:38.000000000 +0000
@@ -2,50 +2,48 @@
 
 Item                                                                    HOURS
 -----------------------------------------------------------------------+-------
-1. Hook 'SHOW PLAN FOR' into the parser
+1. Hook 'SHOW PLAN FOR' into the parser                                |  4
 
 2. Add "plumbing" code to handle the SQLCOM_SHOW_PLAN command: 
-   make send_explain_fields() call, etc.
+   make send_explain_fields() call, etc.                               |  4 
 
 3. [Assuming option A3] 
    Add capability to produce [partial] query plan at any point in
    time during select execution. This includes 
-   - Fixing all problems described in section "3. Possible problems with
-     getting query plan midway the query execution"
-     = 3.1
-     = 3.2
-     = 3.3
-     = 3.4
-     = 3.5
-   - making select_describe() not to modify any exectution data 
+   - Fixing all problems described in section "3. Possible problems
+     with getting query plan midway the query execution"
+     = 3.1 UNIONs                                                      |  4
+     = 3.2 IN/EXISTS/scalar-context subqueries                         |  4
+     = 3.3 FROM subqueries                                             |  4
+     = 3.4 ORDER/GROUP BY optimizations                                |  8
+     = 3.5 Other cases of "if (select_options & SELECT_DESCRIBE) {...}"|  8
+   - making select_describe() not to modify any execution data 
      structures (currently, it may change values of join->error, 
      offset_limit_cnt, 
-      server_status, join->join_tab[i].type)
+      server_status, join->join_tab[i].type)                           |  3
    
    - Implementing query tree traversal (so that we get parts of EXPLAIN
      for subqueries, union members, etc). We can re-use 
      mysql_explain_union()'s loop but need to figure out how to make it
-     not to modify any of query tree's data structures.
-
-Q: Does slow query log have something similar? If yes, where does it 
-get its source data from?
+     not to modify any of query tree's data structures.                |  8
 
 4. [Assuming option A3] 
    Implement the "plan retrieval engine": add functions to:
-   - Locate the thread we're looking to get EXPLAIN for;
+   - Locate the thread we're looking to get EXPLAIN for                |  4
 
-   - Post it a request to produce its EXPLAIN, wait until 
+   - Post it a request to produce its EXPLAIN, wait until              |  8
      the request have been executed.
 
    - Have all the code that checks for thd->killed also check if 
-     there were any EXPLAIN requests posted, and execute them.
+     there were any EXPLAIN requests posted, and execute them.         |  24
   
    - Arrange so that running select_describe() in the context of
-     query thread delivers the result to the SHOW PLAN FOR thread.
+     query thread delivers the result to the SHOW PLAN FOR thread.     |   4
      (According to Monty: it should be sufficient to change THD and
       some of its members, and then we will make it so that
        - temporary items are allocated on SHOW PLAN's mem_root
        - results are sent to SHOW PLAN's connection)
-
+-----------------------------------------------------------------------+------
+TOTAL:                                                                 |  90
 
 

(Psergey - Thu, 07 Apr 2011, 15:16
    
High-Level Specification modified.
--- /tmp/wklog.182.old.9886	2011-04-07 15:16:44.000000000 +0000
+++ /tmp/wklog.182.new.9886	2011-04-07 15:16:44.000000000 +0000
@@ -1,4 +1,3 @@
- 
 <contents>
 1. General idea
 1.1 Details about A3
@@ -202,9 +201,11 @@
 - What's the exact syntax for the new statement:
   SHOW PLAN FOR?
   EXPLAIN [EXTENDED|PARTITIONS] FOR?
+  A: SHOW PLAN FOR <id>
 
 - What should be produced if the thread of interest is not running an
   EXPLAIN'able command? Empty set? Error?
+  A: An error saying that plan information is not available for that thread
 
 - How does one get *both* the EXPLAIN and the query it is for?
   First, I need to do SHOW PROCESSLIST to obtain a connection_id. Then I do 
@@ -218,11 +219,14 @@
   EXPLAIN output?
 
   Or it can return the original query in a warning.
+  A: Current solution is to produce query text in a warning.
 
 - Most(All?) of SHOW commands have an equivalent in INFORMATION_SCHEMA. Do we
   need to expose the SHOW PLAN information through INFORMATION_SCHEMA, too?
+  A: not in the scope of this WL entry.
 
 - What permissions should one have to be able to do SHOW PLAN on some
   connection?
+  A: the same as required for SHOW FULL PROCESSLIST.
 
 

(Psergey - Wed, 30 Mar 2011, 09:38
    
Low Level Design modified.
--- /tmp/wklog.182.old.460	2011-03-30 09:38:32.000000000 +0000
+++ /tmp/wklog.182.new.460	2011-03-30 09:38:32.000000000 +0000
@@ -1,2 +1,51 @@
+Code-wise, we'll need: 
+
+Item                                                                    HOURS
+-----------------------------------------------------------------------+-------
+1. Hook 'SHOW PLAN FOR' into the parser
+
+2. Add "plumbing" code to handle the SQLCOM_SHOW_PLAN command: 
+   make send_explain_fields() call, etc.
+
+3. [Assuming option A3] 
+   Add capability to produce [partial] query plan at any point in
+   time during select execution. This includes 
+   - Fixing all problems described in section "3. Possible problems with
+     getting query plan midway the query execution"
+     = 3.1
+     = 3.2
+     = 3.3
+     = 3.4
+     = 3.5
+   - making select_describe() not to modify any exectution data 
+     structures (currently, it may change values of join->error, 
+     offset_limit_cnt, 
+      server_status, join->join_tab[i].type)
+   
+   - Implementing query tree traversal (so that we get parts of EXPLAIN
+     for subqueries, union members, etc). We can re-use 
+     mysql_explain_union()'s loop but need to figure out how to make it
+     not to modify any of query tree's data structures.
+
+Q: Does slow query log have something similar? If yes, where does it 
+get its source data from?
+
+4. [Assuming option A3] 
+   Implement the "plan retrieval engine": add functions to:
+   - Locate the thread we're looking to get EXPLAIN for;
+
+   - Post it a request to produce its EXPLAIN, wait until 
+     the request have been executed.
+
+   - Have all the code that checks for thd->killed also check if 
+     there were any EXPLAIN requests posted, and execute them.
+  
+   - Arrange so that running select_describe() in the context of
+     query thread delivers the result to the SHOW PLAN FOR thread.
+     (According to Monty: it should be sufficient to change THD and
+      some of its members, and then we will make it so that
+       - temporary items are allocated on SHOW PLAN's mem_root
+       - results are sent to SHOW PLAN's connection)
+
 
 

(Psergey - Wed, 30 Mar 2011, 09:38
    
High-Level Specification modified.
--- /tmp/wklog.182.old.446	2011-03-30 09:38:14.000000000 +0000
+++ /tmp/wklog.182.new.446	2011-03-30 09:38:14.000000000 +0000
@@ -1,10 +1,15 @@
-(the below is a draft. it will be updated)
  
 <contents>
-General idea
-Performance/overhead considerations
-Possible problems with saving query plan
-  Proposed solution
+1. General idea
+1.1 Details about A3
+2. Performance/overhead considerations
+3. Possible problems with getting query plan midway the query execution
+3.1 UNION construct
+3.2 IN/EXISTS/scalar-context subqueries
+3.3 FROM subqueries
+3.4 ORDER/GROUP BY optimizations
+3.5 Other differences due to all these if (select_options & SELECT_DESCRIBE)
+
 </contents>
 
 1. General idea
@@ -44,8 +49,8 @@
 The mechanism could be extended to allow posting of general callback calls to
 other threads.
 
-Details about A3
-~~~~~~~~~~~~~~~~
+1.1 Details about A3
+~~~~~~~~~~~~~~~~~~~~
 There may be a situation where we want to post a "give me your EXPLAIN info"
 request to a thread that already has such pending request.  We don't want the
 complication of request queues, so the solution will be to wait until the 
@@ -75,6 +80,8 @@
 thd::killed() inline method, to encapsulate killed, and avoid future massive
 changes.
 
+(When discussed on optimizer call, Monty also voiced preference for A3)
+
 2. Performance/overhead considerations
 --------------------------------------
 If we pick approach A1, saving query plan will add extra overhead. In order not
@@ -85,8 +92,8 @@
 A3 has no additional overhead as compared to the old code (the tests for
 thd->killed already exist) when the feature is not used.
 
-3. Possible problems with saving query plan
--------------------------------------------
+3. Possible problems with getting query plan midway the query execution
+-----------------------------------------------------------------------
 
 DBMS with a more textbook-like design break down the process of query execution
 into these steps:
@@ -104,6 +111,8 @@
 interleaved, that is, parts of execution start before the optimization is 
 finished.
 
+3.1 UNION construct
+~~~~~~~~~~~~~~~~~~~
 One example is UNION queries. If one traces this query:
 
   SELECT * FROM t1 UNION SELECT * FROM t2
@@ -120,36 +129,83 @@
 i.e. first part of the query will be executed before the second one is
 optimized.
 
-Another points which may cause this effect are:
-- subqueries (ought to be fixed in MariaDB 5.3)
-- ORDER/GROUP BY optimizations.
+EXPLAIN .. UNION .. has a different execution flow where it will optimize 
+all its selects without executing them (see mysql_explain_union()).
+
+Altering UNION execution so that it optimizes everything first and then starts
+execution is a too big change for this WL entry.
+
+Proposed solution: SHOW PLAN should detect this case (should be a matter of 
+checking a flag), and if it encounters a not-yet-optimized select, it should
+display for it one EXPLAIN line with all columns being NULL except Extra that
+should have 'Not yet optimized'.
+
+
+3.2 IN/EXISTS/scalar-context subqueries
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+When a SELECT statement is run, IN/EXIST/Scalar-context subqueries are optimized 
+"lazily", i.e. right before they are executed for the first time. (This
+property should be gone in MariaDB 5.3)
+
+The solution here can be the same as with UNIONs.
 
-3.1 Proposed solution
-~~~~~~~~~~~~~~~~~~~~~
-Save/display a partial query plan.
 
+3.3 FROM subqueries
+~~~~~~~~~~~~~~~~~~~
+FROM subquery will be optimized and executed (and its result stored in a
+temptable) before the upper select is optimized and executed.
 
-LLD
----
-Code-wise, we'll need: 
+The solution here can be the same as with UNIONs.
 
-1. Hook 'SHOW PLAN FOR' into the parser
-2. Implement the SHOW PLAN command (send_explain_fields, fill with data, etc)
-3. Modify select_describe() so it can store data into a data structure (or, 
-    create a select_result sink that will capture and save EXPLAIN output)
-4. Union/subquery handling.
 
+3.4 ORDER/GROUP BY optimizations
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+Query plans with "Using filesort" or "Using filesort; Using temporary" are
+executed as follows:
 
-Q: Does slow query log have something similar? If yes, where does it get source
-data from?
+  access the first table in the join order, storing the obtained records 
+    in the tempfile;
+  run filesort() on tempfile contents;
+  change join->join_tab[0] to access the data produced by filesort();  (*)
+  ...
 
+The step (*) will change join->join_tab[0].  Since a join can be part of the
+subquery, it has support for multiple executions, which means that contents of 
+join->join_tab[0] are not irrevocably lost. However, special arrangements may
+be needed to get them.
 
-Questions
----------
-- How do we cann the new statement?
+(grep in sql_select.cc for calls to save_join_tab() and to make_simple_join()
+to find the problematic areas.)
+
+3.5 Other differences due to all these if (select_options & SELECT_DESCRIBE)
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+The optimizer source code has lots of statements like this:
+
+  if(select_options & SELECT_DESCRIBE) 
+    do this;
+  else
+    do that;
+
+
+Nearly all of them are actually harmless (e.g. we will allocate actual buffers
+or increment counters for SELECTs but not for EXPLAINs).
+
+The only difference with possible impact that I could find is that 
+create_ref_for_key() does something differently for  
+  
+   key_part=const_expresssion
+
+depending on whether SELECT_DESCRIBE is set.
+
+4. Questions
+------------
+- What's the exact syntax for the new statement:
   SHOW PLAN FOR?
   EXPLAIN [EXTENDED|PARTITIONS] FOR?
 
+- What should be produced if the thread of interest is not running an
+  EXPLAIN'able command? Empty set? Error?
+
 - How does one get *both* the EXPLAIN and the query it is for?
   First, I need to do SHOW PROCESSLIST to obtain a connection_id. Then I do 
   SHOW PLAN FOR $connection_id, and get a plan, but how do I know that the plan

(Sergei - Thu, 24 Mar 2011, 17:29
    
High-Level Specification modified.
--- /tmp/wklog.182.old.30435	2011-03-24 17:29:25.000000000 +0000
+++ /tmp/wklog.182.new.30435	2011-03-24 17:29:25.000000000 +0000
@@ -146,6 +146,10 @@
 
 Questions
 ---------
+- How do we cann the new statement?
+  SHOW PLAN FOR?
+  EXPLAIN [EXTENDED|PARTITIONS] FOR?
+
 - How does one get *both* the EXPLAIN and the query it is for?
   First, I need to do SHOW PROCESSLIST to obtain a connection_id. Then I do 
   SHOW PLAN FOR $connection_id, and get a plan, but how do I know that the plan
@@ -157,6 +161,8 @@
   being the query (or a line from SHOW PROCESSLIST), and the second being the
   EXPLAIN output?
 
+  Or it can return the original query in a warning.
+
 - Most(All?) of SHOW commands have an equivalent in INFORMATION_SCHEMA. Do we
   need to expose the SHOW PLAN information through INFORMATION_SCHEMA, too?
 

(Sergei - Thu, 24 Mar 2011, 17:26
    
High-Level Specification modified.
--- /tmp/wklog.182.old.30360	2011-03-24 17:26:48.000000000 +0000
+++ /tmp/wklog.182.new.30360	2011-03-24 17:26:48.000000000 +0000
@@ -58,6 +58,22 @@
 from working, which means that SHOW PLAN will not freeze when requesting EXPLAIN
 info from a thread that's waiting for some InnoDB lock.
 
+A possible generic approach would be:
+
+- instead of checking
+    if (thd->killed == something)
+  make thd->killed a function pointer, and check as:
+   if (thd->killed && thd->killed() == something).
+  Detail 1: add thd->killed_arg, and call thd->killed(thd->killed_arg)
+  Defail 2: rename "killed".
+- this will introduce cooperative data/code exchange between threads, and
+re-implement thd->killed using the new approach.
+- the best part: it'll be completely transparent and backward compatible for
+plugins, they call thd_killed() function that returns the value of thd->killed.
+The semantics of this function won't change, so plugins won't be affected.
+- but server code uses thd->killed directly. it needs to be changed to call
+thd::killed() inline method, to encapsulate killed, and avoid future massive
+changes.
 
 2. Performance/overhead considerations
 --------------------------------------
@@ -66,6 +82,9 @@
 server variable which will control whether a statement should save its query
 plan.
 
+A3 has no additional overhead as compared to the old code (the tests for
+thd->killed already exist) when the feature is not used.
+
 3. Possible problems with saving query plan
 -------------------------------------------
 

(Psergey - Thu, 24 Mar 2011, 17:01
    
High-Level Specification modified.
--- /tmp/wklog.182.old.29624	2011-03-24 17:01:37.000000000 +0000
+++ /tmp/wklog.182.new.29624	2011-03-24 17:01:37.000000000 +0000
@@ -32,6 +32,32 @@
 to extract more information (e.g. extract info about where we are in a
 particular table scan, etc).
 
+Sergei Golubchik has suggested the following:
+A3: To avoid both [expensive and most-likely redundant] pre-processing step
+of A1 and locking complications of A2, use a "cooperative" approach: 
+- The SHOW PLAN thread should post a kind of request to query thread to 
+  get its query plan.
+- The query thread should periodically check whether it has a request, and 
+  if yes, execute it. The check is already in the code - all threads check
+  if they've been killed by KILL statement. What needs to be done is to
+  amend the check to check for posted "give me your EXPLAIN info" requests.
+The mechanism could be extended to allow posting of general callback calls to
+other threads.
+
+Details about A3
+~~~~~~~~~~~~~~~~
+There may be a situation where we want to post a "give me your EXPLAIN info"
+request to a thread that already has such pending request.  We don't want the
+complication of request queues, so the solution will be to wait until the 
+first request is satisfied, and then post ours.
+
+We'll need to handle the situation where two threads are running SHOW PLAN 
+and attempt to request EXPLAIN info from one another. 
+
+According to SergeiG, lock waits inside InnoDB do not prevent KILL statement
+from working, which means that SHOW PLAN will not freeze when requesting EXPLAIN
+info from a thread that's waiting for some InnoDB lock.
+
 
 2. Performance/overhead considerations
 --------------------------------------
-- View All Progress Notes (17 total) --


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB