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_IDNESTING_EVENT_TYPENESTING_EVENT_LEVEL

    These 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 = 0

    For 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/


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. khailey
    | #1

    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.


seven + = 15