WL#6369: EXPLAIN for other thread

Status: Complete   —   Priority: Medium

The goal of this WL is to extend EXPLAIN code to allow printing plan of a
query currently running in another thread. Proposed syntax is following:

EXPLAIN [FORMAT=(JSON|TRADITIONAL)] [EXTENDED] FOR CONNECTION <id>;

Where <id> is a connection id that is shown by SHOW PROCESSLIST.

This statement can be useful if a statement is running in one connection but is 
taking a long time to complete. In this case, using EXPLAIN FOR CONNECTION in 
another connection can be used to obtain the execution plan for the statement. 
This may yield information useful for optimizing the statement.
Applicability
-------------
EXPLAIN FOR CONNECTION has same applicability as regular EXPLAIN, i.e.
SELECT/INSERT/UPDATE/DELETE/REPLACE statements.

Access control
--------------
In addition to all privileges that are required by regular EXPLAIN for the
query being explained, user must have the PROCESSLIST privilege in order
to run this command, or must own the named thread.

Output
------
Empty result is returned when the process with given id isn't found.

The "EXPLAIN command is supported only for SELECT/INSERT/UPDATE/DELETE/REPLACE"
is returned if EXPLAIN FOR CONNECTION is run for any statement except
SELECT/INSERT/UPDATE/REPLACE or the thread serving connection being explained
runs any command except COM_QUERY. Same error is returned for an EXPLAIN
command, i.e. EXPLAIN of EXPLAIN isn't supported.

If EXPLAIN FOR CONNECTION is run at the moment when a plan for a [sub]query
isn't ready yet then for that particular [sub]query instead of real plan
the message "Plan isn't ready yet" in 'Extra' field is returned in traditional
format and in json format to the appropriate "query_block" the "message" tag is
added with text "Plan isn't ready yet".

Otherwise EXPLAIN FOR CONNECTION should print the plan of running query with
given id that is the same as regular EXPLAIN.

Output difference
-----------------
Estimated number of rows could be different with those reported by regular
EXPLAIN. This is caused by the implementation of regular EXPLAIN and could be
fixed in 5.7 within scope of another WL.
EXPLAIN FOR CONNECTION won't print attached conditions, in such case
"using_where: true" is printed instead, in JSON mode.
EXPLAIN FORMAT=TRADITIONAL EXTENDED FOR CONNECTION, unlike regular EXPLAIN,
won't show warning with resolved query text printout.
For 'Range checked for each record' regular EXPLAIN will print type 'ALL' while
EXPLAIN FOR CONNECTION likely will print 'range' as the actual access method is
chosen during execution.
For a subselect that was optimized away, EXPLAIN FOR CONNECTION could print
"Plan isn't ready yet"
EXPLAIN for connection could yield "Impossible WHERE" instead of "No tables 
used"
EXPLAIN FOR CONNECTION will not print "Using join buffer" for
multi-(delete|update) as it's actually isn't used (regular EXPLAIN lies about 
it).

Examples
--------

Examples are show in following format (for sake of readability):

EXPLAIN FORMAT=(TRADITIONAL|JSON) FOR CONNECTION '<query>'
<explain output>

Where <query> is run in one thread and EXPLAIN FOR CONNECTION in another.
<explain output> is the output of EXPLAIN FOR CONNECTION for <query>.

Explain queries with prepared plan 
(<query> is stopped before execution of the first join):

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT f1 FROM t1'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT * FROM (SELECT * FROM t1) tt'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
NULL
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	NULL

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT * FROM t1 WHERE f1 IN (SELECT
* FROM t1)'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using 
where; FirstMatch(t1); Using join
buffer (Block Nested Loop)

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT * FROM t1 UNION ALL SELECT *
FROM t1 ORDER BY 1'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
NULL	UNION RESULT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	
Plan isn't ready yet

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT f1 FROM t1'
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows": 2,
      "filtered": 100
    }
  }
}

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT * FROM (SELECT * FROM t1) tt'
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "rows": 2,
      "filtered": 100,
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 2,
            "filtered": 100
          }
        }
      }
    }
  }
}

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT * FROM t1 WHERE f1 IN (SELECT * FROM 
t1)'
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 2,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows": 2,
          "filtered": 100,
          "first_match": "t1",
          "using_join_buffer": "Block Nested Loop",
          "attached_condition": "(`test`.`t1`.`f1` = `test`.`t1`.`f1`)"
        }
      }
    ]
  }
}

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT * FROM t1 UNION ALL SELECT * FROM t1
ORDER BY 1'
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 2,
              "filtered": 100
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 2,
              "filtered": 100
            }
          }
        }
      ]
    }
  }
}

Explain partially prepared queries
(<query> is stopped after finishing optimization of a first join)

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT * FROM (SELECT * FROM t1 GROUP
BY 1) tt'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Plan 
isn't ready yet
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using 
temporary; Using filesort

EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 'SELECT * FROM t1 UNION ALL SELECT *
FROM t1 ORDER BY
(SELECT * FROM t1 LIMIT 1)'
id	select_type	table	type	possible_keys	key	key_len	ref	
rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Plan 
isn't ready yet
3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	
Plan isn't ready yet
NULL	UNION RESULT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	
Plan isn't ready yet

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT * FROM (SELECT * FROM t1 GROUP BY 1) 
tt'
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "planned": false,
    "table": {
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 2,
              "filtered": 100
            }
          }
        }
      }
    }
  }
}

EXPLAIN FORMAT=JSON FOR CONNECTION 'SELECT * FROM t1 UNION ALL SELECT * FROM t1
ORDER BY
(SELECT * FROM t1 LIMIT 1)'
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "planned": false,
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 2,
              "filtered": 100
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "planned": false,
            "order_by_subqueries": [
              {
                "dependent": false,
                "cacheable": true,
                "query_block": {
                  "select_id": 3,
                  "planned": false
                }
              }
            ]
          }
        }
      ]
    }
  }
}



Overall design
=============

The EXPLAIN FOR CONNECTION is implemented as a new command. It has a dedicated
command id - SQLCOM_EXPLAIN_OTHER. For that id the mysql_explain_other function
is called. It does all necessary checks and either runs already existing explain
code using specified thread data as the source of the query plan, or throws an
error if thread was found non-applicable. The thread being explained isn't
stopped and it continues its work while being explained, it's only prevented
from changing query plan(s) and from quitting due to any reason. The doesn't
modify the data it explains in any way, neither directly, nor by calling
a function from objects being explained.

Details of the implementation
=============================

Applicability
-------------
The EXPLAIN FOR CONNECTION is applicable to SELECT/INSERT/UPDATE/DELETE/REPLACE 
statements, both single and multi-table variants. EXPLAIN FOR CONNECTION isn't 
applicable to another EXPLAIN FOR CONNECTION and appropriate error is thrown in 
this case.

For SELECT the already present tree of SELECT_LEX_UNIT/SELECT_LEX/JOIN objects
is used as the plan source. For INSERT/UPDATE/DELETE/REPLACE the newly
introduced Modification_plan structure is used (see *New data structures).

Changes to parser
------
Changes to parser are small and simple - it's just extended to allow proposed
syntax without introducing new keywords or conflicts.

Handling two threads
--------------------
The explain code is changed to take into account two thread handlers - 
the explaining one and the one being explained. The explain code saves and
passes through arguments the explaining one - usually called ethd.
The thread being explained isn't stored and obtained from unit->thd or
join->thd on rare occasions when needed. ethd is used for all allocations
during explain process - for allocating nodes within explain format plugins,
for allocation of select_result, etc. Explain code detects that it is run from
another thread by comparing thd and unit->thd. In this case explain doesn't
run any preparation or optimization on the code, going straight to explaining
plan. Beside that, the explain code is changed to refer to the plan owning
thread in the least possible places to avoid misusing it.
This way these threads don't interfere.

Main function
-------------
The entry point for EXPLAIN FOR CONNECTION is the new function called
mysql_explain_other. It looks for the thread with specified id in the list,
checks if it's applicable (throws an error if not), locks required data
and runs explain functions that are appropriate to the command the specified
thread runs - mysql_explain_unit for SELECTs, or
mysql_explain_single_table_modification for INSERT/UPDATE/DELETE/REPLACE.
When these functions returns, locks are freed.

Locking
-------
The implementation uses 3 mutexes in total: 2 already present and 1 newly
introduced by this WL.
Two already present mutexes are LOCK_thread_count and LOCK_thd_data.
The first one is needed to lock threads list while looking for the specified
one. The second one locks the thread from being killed during explaining
and from changing thread data.
The new mutex is called LOCK_query_plan and used only for explaining purposes.
It guards tree variables - JOIN *SELECT_LEX::join, JOIN_TAB *JOIN::plan,
THD::modification_plan. All subqueries also have to take this mutex before 
changing named variables.

The mysql_explain_other function locks LOCK_thd_data and LOCK_query_plan
mutexes after finding specified thread, holds them during EXPLAIN and
unlocks after EXPLAIN is finished.

Plan locking schema for optimizer is following:
 1) Prepare plan
 2) Lock pointer to the plan that would be used by executor as the source
    plan
 3) Set pointer to the correct plan
 4) Unlock the pointer

Subqueries' plans are locked separately, right after subquery's plan is
ready, but using the same mutex in THD and using the same schema as for the
topmost select.

Comment from Guilhem, needs to be implemented:
In mysql_execute_command(), before the big switch(), we are in a place
where thd->lex is the real LEX (not some tmp LEX used for this or that
purpose); thd->lex->sql_command is the real command. Chain of units is
created and won't change. So at this place (we're in 'othd'), do:
if (explainable command && !in_sub_stmt)
{
  // save the real values in an object part of thd:
  lock LOCK_query_plan;
  thd->explain_info_for_others.lex= thd->lex;
  thd->explain_info_for_others.sql_command= thd->lex->sql_command;
  unlock LOCK_query_plan;
}
At end of mysql_execute_command(), reset the object above to
{lex=NULL,sql_command=SQLCOM_END}, under mutex. The temporary changes
of thd->lex (for managing user vars etc) during execution are NOT
replicated into thd->explain_info_for_others.
Btw put THD::plan inside thd->explain_info_for_others, for
'tidiness'.
That was for the to-be-explained connection (othd). For the explainer,
in mysql_explain_other():
- locate othd and hold its mutexes as you already do
- check othd->explain_info_for_others.sql_command instead of
othd->lex->sql_command.
- the same way, in all EXPLAIN code, check
othd->explain_info_for_others members.
This way, the explainer is only reading the real values of othd and
not tmp ones. And it's not explaining if parsing has not finished.
And not testing THD::m_command==COM_QUERY.

In this idea, othd locks/unlocks LOCK_query_plan 4 times:
1- at start of stmt, to set explain_info_for_others.lex/sql_command
2- at end of JOIN::optimize(), to set JOIN::plan
3- in JOIN::cleanup(true), to set JOIN::plan to NULL
4- at end of stmt, to reset explain_info_for_others.lex/sql_command
Actually step 4 should be done in step 3 because:
 * it saves a lock/unlock (locking a not-wanted-by-others mutex is in
 theory as cheap as an atomic operation, but it's even cheaper to not
 lock)
 * if the unit chain is already cleaned up (i.e. we are after 3), we
 mustn't try an explain-other (must not walk the units chain), which
 can be avoided by doing 4 in 3.
All in all, we lock/unlock 3 times. I suspect the benchmarks won't
notice a difference between stock 5.6 and this proposal.


Changes to EXPLAIN code
-----------------------
Unlike regular explain, EXPLAIN FOR CONNECTION not necessarily will find
prepared plan - either join object could be missing or plan could be not ready
yet and pointer to it is set to NULL. This cause major change in design of
explain code. Now instead of JOIN it is designed around SELECT_LEX and uses
JOIN only when available. This caused following changes:
  .) Explain class doesn't store and use pointer to JOIN anymore, it uses
     appropriate select_lex instead 
  .) Same is Explain_join, but it expects that select_lex->join is not null
  .) JOIN::explain is removed. The code it consisted of is merged with
     explain_query_specification. This is done to consolidate all plan
     explaining function calls (e.g. explain_no_plan, explain_no_tables,
     Explain_join(...).send(), etc) in a single place.

Changes to cleanup code
-----------------------
Plans of all subqueries of all kinds are kept for the lifetime of the top
query and freed only at the end of execution of the top query in order to
provide info for EXPLAIN.

Cleanup code that is used to free intermediate results (i.e filesort
buffers, join buffers, etc) doesn't free joins and plans anymore.
This effectively converts all code like

  if (!lex->describe)
   cleanup();

to

  cleanup(false);

which means "always do 'not full' cleanup".

Full cleanup, which frees all plans including subqueries' ones, is done only 
after the top query is complete. Plans are locked before the purge and
unlocked only after all plans were cleaned.

In order to make distinction between full and not-full cleanup, the
SELECT_LEX_UNIT::cleaned variable is changed to enum and now can take 3
values
0 - UC_DIRTY      - not cleaned,
1 - UC_PART_CLEAN - non-full cleanup were done
2 - UC_CLEAN      - full cleanup were done

Changes in the SELECT handling
------------------------------
For better code reuse SELECT handling functions were re-grouped. Prior this
WL mysql_select was calling mysql_prepare_select which was preparing JOIN,
after that and locking tables, the mysql_execute was called which optimized
and executed JOIN. This is inconvenient for EXPLAIN FOR CONNECTION as it have
to skip JOIN preparation and optimization.
Now mysql_select calls mysql_prepare_and_optimize_select which prepares JOIN,
locks tables and optimizes JOIN. Explain code calls it instead of doing the
same work on its own.

Changes in UPDATE/DELETE/INSERT/REPLACE handling
------------------------------------------------
Currently when an e.g impossible condition is found during statement execution
it immediately exits. In this case no plan is generated and EXPLAIN FOR
CONNECTION shows "Plan isn't ready yet" which is wrong. To solve this
in cases of quitting execution early a message describing quit cause will be
created and used as the plan.
Another change is in regular EXPLAIN output. From now on EXPLAIN will print
the kind of operation in select_type field in traditional format for affected
tables. I.e. For query UPDATE t1 SET f1=1, EXPLAIN will print 'UPDATE' instead
of 'SIMPLE', 'DELETE' for DELETE statements, etc.

New variables
-------------
JOIN::plan - pointer to the final plan for the JOIN object. It is set to point
  to JOIN::join_tab at the end of JOIN::make_tmp_tables_info.
  Set to NULL at JOIN object creation and also by JOIN::cleanup.
THD::modification_plan  - pointer to the Modification_plan for
INSERT/UPDATE/DELETE/REPLACE statements. See new data structures.

New data structures
-------------------
A new structure called Modification_plan is introduced. It is used as the plan
for INSERT/UPDATE/DELETE/REPLACE commands, currently only for explain purposes.
The plan is prepared by appropriate functions:
  INSERT,REPLACE  - mysql_insert,
  UPDATE          - mysql_update,
  DELETE          - mysql_delete
In each function the plan is created on the stack. Modification_plan's
constructor and destructor register and de-register, appropriately, plan in
THD. As we don't explain statements in triggers/functions the plan aren't
created for such statements.


DEBUG_SYNC
----------
Few new debug sync points were added for testing purposes:
  before_join_exec - at the beginning of JOIN::exec
  after_join_optimize - after the call to JOIN::make_tmp_tables_info
  after_materialize_derived - at the end of join_materialize_table
  before_explain_other, after_explain_other - at the beginning and the end of
    mysql_explain_other