MySQL -> what to do with NESTING_EVENT_ID
March 24th, 2022
How does one use NESTING_EVENT_ID?
Below I’ve selected from events_waits_history_long for a single thread, but none of the NESTING_EVENT_ID values show up in EVENT_ID or END_EVENT_ID.
To simplify things , I’m just showing the data for one THREAD_ID listed in order of TIMER_START.
mysql> select
THREAD_ID,
EVENT_ID,
END_EVENT_ID,
NESTING_EVENT_ID,
NESTING_EVENT_TYPE,
EVENT_NAME,
SOURCE,
TIMER_WAIT,
SPINS,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
OBJECT_TYPE,
OBJECT_TYPE,
OBJECT_INSTANCE_BEGIN,
OPERATION,
NUMBER_OF_BYTES,
FLAGS
from
performance_schema.events_waits_history_long
where
THREAD_ID in ( select max(thread_id) from performance_schema.events_waits_history_long where NESTING_EVENT_ID is not NULL )
order by
THREAD_ID, TIMER_START
Limit 100;
+-----------+----------+--------------+------------------+--------------------+-------------------------------------------+---------------------------------+---------------+-------+---------------+----------------------------+------------+-------------+-------------+-----------------------+---------------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | NESTING_EVENT_ID | NESTING_EVENT_TYPE | EVENT_NAME | SOURCE | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | OBJECT_TYPE | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+------------------+--------------------+-------------------------------------------+---------------------------------+---------------+-------+---------------+----------------------------+------------+-------------+-------------+-----------------------+---------------+-----------------+-------+
| 518033674 | 92174 | 92174 | 92171 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:145 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 4 | NULL |
| 518033674 | 92232 | 92232 | 92231 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:145 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 0 | NULL |
| 518033674 | 92210 | 92210 | 92207 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:145 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 4 | NULL |
| 518033674 | 92209 | 92209 | 92207 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:813 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | select | NULL | NULL |
| 518033674 | 92208 | 92208 | 92207 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:145 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 0 | NULL |
| 518033674 | 92173 | 92173 | 92171 | WAIT | wait/io/socket/sql/client_connection | viosocket.cc:813 | NULL | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | select | NULL | NULL |
| 518033674 | 92176 | 92176 | 92175 | STATEMENT | wait/io/socket/sql/client_connection | viosocket.cc:145 | 1612800 | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 97 | NULL |
| 518033674 | 92177 | 92177 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.h:3758 | 149600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541392 | lock | NULL | NULL |
| 518033674 | 92178 | 92178 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 512800 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92179 | 92179 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.cc:2152 | 232000 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92180 | 92180 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 69600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92181 | 92181 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 73600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92182 | 92182 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 190400 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92183 | 92183 | 92175 | STATEMENT | wait/synch/mutex/sql/LOCK_table_cache | table_cache.h:140 | 328000 | NULL | NULL | NULL | NULL | NULL | NULL | 94768184 | lock | NULL | NULL |
| 518033674 | 92184 | 92184 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.h:3769 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541392 | lock | NULL | NULL |
| 518033674 | 92185 | 92185 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1355 | 214400 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92186 | 92186 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1383 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92187 | 92191 | 92175 | STATEMENT | wait/lock/table/sql/handler | handler.cc:7789 | 2965600 | NULL | dbm | info | PRIMARY | TABLE | TABLE | 47817989761064 | read external | NULL | NULL |
| 518033674 | 92188 | 92188 | 92187 | WAIT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1355 | 33600 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92189 | 92189 | 92187 | WAIT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1383 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92191 | 92191 | 92187 | WAIT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1355 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92192 | 92192 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 114400 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92193 | 92195 | 92175 | STATEMENT | wait/io/table/sql/handler | handler.cc:3280 | 18676800 | NULL | dbm | info | PRIMARY | TABLE | TABLE | 47817989761064 | fetch | 1 | NULL |
| 518033674 | 92194 | 92194 | 92193 | WAIT | wait/synch/mutex/innodb/trx_sys_mutex | read0read.cc:544 | 123200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811968171800 | lock | NULL | NULL |
| 518033674 | 92195 | 92195 | 92193 | WAIT | wait/synch/sxlock/innodb/btr_search_latch | btr0sea.ic:122 | 158400 | NULL | NULL | NULL | NULL | NULL | NULL | 47811987295224 | shared_lock | NULL | NULL |
| 518033674 | 92196 | 92196 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1383 | 45600 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92197 | 92197 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 52000 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92198 | 92198 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 66400 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92199 | 92199 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1355 | 45600 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92200 | 92200 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1383 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92201 | 92201 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_base.cc:1727 | 45600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541392 | lock | NULL | NULL |
| 518033674 | 92202 | 92202 | 92175 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | ha_innodb.cc:17396 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92203 | 92203 | 92175 | STATEMENT | wait/synch/mutex/sql/LOCK_table_cache | table_cache.h:140 | 45600 | NULL | NULL | NULL | NULL | NULL | NULL | 94768184 | lock | NULL | NULL |
| 518033674 | 92204 | 92204 | 92175 | STATEMENT | wait/io/socket/sql/client_connection | viosocket.cc:220 | 20792800 | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | send | 77 | NULL |
| 518033674 | 92205 | 92205 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.cc:2152 | 75200 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92206 | 92206 | 92175 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 34400 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92212 | 92212 | 92211 | STATEMENT | wait/io/socket/sql/client_connection | viosocket.cc:145 | 454400 | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | recv | 9 | NULL |
| 518033674 | 92213 | 92213 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.h:3758 | 37600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541392 | lock | NULL | NULL |
| 518033674 | 92214 | 92214 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 32800 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92215 | 92215 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.cc:2152 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92216 | 92216 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 52800 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92217 | 92217 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 47200 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92218 | 92218 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 44800 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92219 | 92219 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1355 | 44800 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92220 | 92220 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | lock0lock.cc:6386 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92221 | 92221 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1331 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92222 | 92222 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.cc:1771 | 28800 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92223 | 92223 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | lock0lock.cc:6406 | 121600 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92224 | 92224 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | lock0lock.cc:6458 | 27200 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92225 | 92225 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.cc:1957 | 44000 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92226 | 92226 | 92211 | STATEMENT | wait/synch/mutex/innodb/trx_mutex | trx0trx.h:1383 | 28000 | NULL | NULL | NULL | NULL | NULL | NULL | 47811978633272 | lock | NULL | NULL |
| 518033674 | 92227 | 92227 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_query_plan | sql_class.h:1044 | 45600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541584 | lock | NULL | NULL |
| 518033674 | 92228 | 92228 | 92211 | STATEMENT | wait/io/socket/sql/client_connection | viosocket.cc:220 | 4366400 | NULL | NULL | ::ffff:10.135.133.25:38102 | NULL | SOCKET | SOCKET | 47811962370304 | send | 11 | NULL |
| 518033674 | 92229 | 92229 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.cc:2152 | 30400 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92230 | 92230 | 92211 | STATEMENT | wait/synch/mutex/sql/THD::LOCK_thd_query | sql_class.h:2970 | 37600 | NULL | NULL | NULL | NULL | NULL | NULL | 47817989541440 | lock | NULL | NULL |
| 518033674 | 92171 | 92174 | NULL | NULL | idle | init_net_server_extension.cc:66 | 4024163774000 | NULL | NULL | NULL | NULL | NULL | NULL | 0 | idle | NULL | NULL |
| 518033674 | 92207 | 92210 | NULL | NULL | idle | init_net_server_extension.cc:66 | 761912000 | NULL | NULL | NULL | NULL | NULL | NULL | 0 | idle | NULL | NULL |
+-----------+----------+--------------+------------------+--------------------+-------------------------------------------+---------------------------------+---------------+-------+---------------+----------------------------+------------+-------------+-------------+-----------------------+---------------+-----------------+-------+
mysql> select * from performance_schema.events_waits_history_long
where EVENT_ID in (
select NESTING_EVENT_ID from performance_schema.events_waits_history_long
where
THREAD_ID in ( select max(thread_id) from performance_schema.events_waits_history_long where NESTING_EVENT_ID is not NULL )
);
Empty set (0.17 sec)
mysql> select * from performance_schema.events_waits_history_long
where END_EVENT_ID in (
select NESTING_EVENT_ID from performance_schema.events_waits_history_long
where
THREAD_ID in ( select max(thread_id) from performance_schema.events_waits_history_long where NESTING_EVENT_ID is not NULL )
);
Empty set (0.14 sec)
Sometimes when selecting from performance_schema.events_waits_current I get two lines for the same thread:
select
t.thread_id,
t.PROCESSLIST_COMMAND cmd,
ww.EVENT_ID,
ww.END_EVENT_ID,
ww.NESTING_EVENT_TYPE ,
ww.NESTING_EVENT_ID,
ww.EVENT_NAME wait_event,
REPLACE(REPLACE(substr(t.PROCESSLIST_info,1,80),'\r',''),'\n','') sql_text
from
performance_schema.threads t
left join performance_schema.events_waits_current ww on ww.thread_id = t.thread_id
where
t.PROCESSLIST_STATE IS NOT NULL
and ( t.PROCESSLIST_COMMAND != 'Sleep' and t.PROCESSLIST_COMMAND != 'Daemon')
and t.PROCESSLIST_ID != CONNECTION_ID();
+-----------+-------+----------+--------------+--------------------+------------------+-------------------------------------------+----------------------------------------------------------------------------------+
| thread_id | cmd | EVENT_ID | END_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_ID | wait_event | sql_text |
+-----------+-------+----------+--------------+--------------------+------------------+-------------------------------------------+----------------------------------------------------------------------------------+
| 518032847 | Query | 76405 | NULL | STATEMENT | 76347 | wait/io/table/sql/handler | SELECT customer.id_ AS customer_id_, customer.name AS customer_name, customer.de |
| 518032847 | Query | 91369 | NULL | WAIT | 76405 | wait/io/file/sql/hash_join | SELECT customer.id_ AS customer_id_, customer.name AS customer_name, customer.de |
I see that NESTING_EVENT_ID 76405 for second row where NESTING_EVENT_TYPE = WAIT is the same as the EVENT_ID for the first line where NESTING_EVENT_TYPE = STATEMENT, but what information does this communicate?
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-events-statements-current-table.html
-
NESTING_EVENT_ID,NESTING_EVENT_TYPE,NESTING_EVENT_LEVELThese three columns are used with other columns to provide information as follows for top-level (unnested) statements and nested statements (executed within a stored program).
For top level statements:
OBJECT_TYPE = NULL OBJECT_SCHEMA = NULL OBJECT_NAME = NULL NESTING_EVENT_ID = NULL NESTING_EVENT_TYPE = NULL NESTING_LEVEL = 0For nested statements:
OBJECT_TYPE = the parent statement object type OBJECT_SCHEMA = the parent statement object schema OBJECT_NAME = the parent statement object name NESTING_EVENT_ID = the parent statement EVENT_ID NESTING_EVENT_TYPE = 'STATEMENT' NESTING_LEVEL = the parent statement NESTING_LEVEL plus one
Get the last statements for thread with blocking transactions
select
t.thread_id,
s.EVENT_ID,
REPLACE(REPLACE(substr(s.SQL_TEXT,1,40),'\r',''),'\n','') thread_proc_info_short,
s.TIMER_WAIT/100000000000 time
from
performance_schema.events_transactions_current t
join performance_schema.events_statements_history s on s.NESTING_EVENT_ID = t.EVENT_ID
where t.thread_id in ( select BLOCKING_THREAD_ID from performance_schema.data_lock_waits )
and s.NESTING_EVENT_TYPE='TRANSACTION'
order by t.thread_id, s.EVENT_ID;
+-----------+----------+------------------------------------------+----------+
| thread_id | EVENT_ID | thread_proc_info_short | time |
+-----------+----------+------------------------------------------+----------+
| 941942 | 1157846 | update toto set id=4 | 0.0044 |
| 941942 | 1157944 | select t.thread_id, s.EVENT_ID, | 2.1082 |
| 941942 | 1158151 | update toto set id=9 | 0.2056 |
| 941942 | 1158259 | select t.thread_id, s.EVENT_ID, | 1.1989 |
| 941942 | 1158433 | select BLOCKING_THREAD_ID from performan | 0.2601 |
| 942146 | 6245 | update titi set id=2 | 0.0089 |
| 942146 | 6354 | update toto set id=999 | 508.0915 |
| 942146 | 6455 | SET SESSION innodb_lock_wait_timeout =10 | 0.0032 |
| 949290 | 5682 | update tutu set id=3 | 0.1975 |
+-----------+----------+------------------------------------------+----------+
NOt sure why this doesn’t work – would think INNODB_TRX has all the transactions but apparently not ?
select
t.thread_id,
s.EVENT_ID,
REPLACE(REPLACE(substr(s.SQL_TEXT,1,40),'\r',''),'\n','') thread_proc_info_short,
s.TIMER_WAIT/100000000000 time
from
performance_schema.events_transactions_current t
join performance_schema.events_statements_history_long s on s.NESTING_EVENT_ID = t.EVENT_ID
where t.thread_id in ( select trx_mysql_thread_id from information_schema.INNODB_TRX )
and s.NESTING_EVENT_TYPE='TRANSACTION'
order by t.thread_id, s.EVENT_ID;
Empty set (2.00 sec)
This works
select
t.thread_id,
s.EVENT_ID,
REPLACE(REPLACE(substr(s.SQL_TEXT,1,40),'\r',''),'\n','') thread_proc_info_short,
s.TIMER_WAIT/100000000000 time
from
performance_schema.events_transactions_current t
join performance_schema.events_statements_history s on s.NESTING_EVENT_ID = t.EVENT_ID
where t.thread_id in ( select distinct THREAD_ID from performance_schema.data_locks)
and s.NESTING_EVENT_TYPE='TRANSACTION'
order by t.thread_id, s.EVENT_ID;
+-----------+----------+------------------------------------------+----------+
| thread_id | EVENT_ID | thread_proc_info_short | time |
+-----------+----------+------------------------------------------+----------+
| 941942 | 1157846 | update toto set id=4 | 0.0044 |
| 941942 | 1157944 | select t.thread_id, s.EVENT_ID, | 2.1082 |
| 941942 | 1158151 | update toto set id=9 | 0.2056 |
| 941942 | 1158259 | select t.thread_id, s.EVENT_ID, | 1.1989 |
| 941942 | 1158433 | select BLOCKING_THREAD_ID from performan | 0.2601 |
| 941942 | 1158599 | select t.thread_id, s.EVE | 57.5793 |
| 941942 | 2602390 | select t.thread_id, s.EVE | 18.5984 |
| 942146 | 6245 | update titi set id=2 | 0.0089 |
| 942146 | 6354 | update toto set id=999 | 508.0915 |
| 942146 | 6455 | SET SESSION innodb_lock_wait_timeout =10 | 0.0032 |
| 949290 | 5682 | update tutu set id=3 | 0.1975 |
+-----------+----------+------------------------------------------+----------+
11 rows in set (3.85 sec)
Could select by thread_id but that gives all the SQL and not just SQL in transaction
select
s.thread_id,
s.EVENT_ID,
REPLACE(REPLACE(substr(s.SQL_TEXT,1,40),'\r',''),'\n','') thread_proc_info_short,
s.TIMER_WAIT/100000000000 time
from
performance_schema.events_statements_history s where THREAD_ID=942146;
-----------+----------+------------------------------------------+---------+
| thread_id | EVENT_ID | thread_proc_info_short | time |
+-----------+----------+------------------------------------------+---------+
| 941942 | 1158259 | select t.thread_id, s.EVENT_ID, | 1.1989 |
| 941942 | 1158433 | select BLOCKING_THREAD_ID from performan | 0.2601 |
| 941942 | 1158599 | select t.thread_id, s.EVE | 57.5793 |
| 941942 | 2602390 | select t.thread_id, s.EVE | 18.5984 |
| 941942 | 2604424 | select t.thread_id, s.EVE | 36.9560 |
| 941942 | 1157736 | SET SESSION innodb_lock_wait_timeout =10 | 0.0152 |
| 941942 | 1157755 | start transaction | 0.1609 |
| 941942 | 1157846 | update toto set id=4 | 0.0044 |
| 941942 | 1157944 | select t.thread_id, s.EVENT_ID, | 2.1082 |
| 941942 | 1158151 | update toto set id=9 | 0.2056 |
+-----------+----------+------------------------------------------+---------+
reference:
https://www.bobbydurrettdba.com/2019/02/12/one-query-in-mysql-performance-schema/
Trackbacks
Comments
From Øystein Grøvlen @ogrovlen on Twitter:
If the NESTING_EVENT_TYPE is WAIT, you will actually find the NESTING_EVENT_ID in the events_waits_history table. This is how there can be multiple waits for a single thread. For example, if the handler call (wait/io/table/sql/handler ) causes a file I/O wait.
You will not necessarily find the NESTING_EVENT_ID in the events_waits_current table, but in the table given by the NESTING_EVENT_TYPE.
For example, if NESTING_EVENT_TYPE = STATEMENT, you will find the NESTING_EVENT_ID as an EVENT_ID in the events_statements_current table.