WL#6369: EXPLAIN for other thread
Status: Complete
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; Where 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 '' Where is run in one thread and EXPLAIN FOR CONNECTION in another. is the output of EXPLAIN FOR CONNECTION for . Explain queries with prepared plan ( 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 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 ( 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
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.