MySQL : events_waits_current – who is waiting and who is on CPU

March 25th, 2022

Anyone familiar with Oracle performance tuning knows about ASH , aka active session history. ASH saves a history of every session that is active every collection, which by default is once per second. Once per second is fast enough to give a clear view of database load.

The ASH data comes from v$session which is a structure that contains information on every connection and shows which connections are active, what SQL they are running (SQL_ID) and if they are runnable on CPU or waiting on a wait_event for some resource like an I/O to complete or a lock or latch. It’s a pretty simple select query on v$session to select who is active,  their wait_event and SQL_ID.

On Oracle that gives us a query like:

select sid||':'||serial# session_id,
       username,
       s.sql_id||':'||sql_child_number sqlid,
       decode(state, 'WAITING', wait_class||':'||event, 'CPU') event
from v$session s
where   (( s.wait_time != 0  /* on CPU  */ and s.status='ACTIVE'  /*ACTIVE */)
            or s.wait_class  != 'Idle' )

On MySQL it’s not so straight forward.  The view that shows who is connect to the instances is performance_schema.threads, but this view doesn’t have the wait event.

Example content of threads:

*************************** 1. row ***************************
          THREAD_ID: 469
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 357
   PROCESSLIST_USER: shopper_22
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: dbmorders_6
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: updating
   PROCESSLIST_INFO: UPDATE order_item SET sku=trim(sku) WHERE id < 553
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 246
     RESOURCE_GROUP: USR_default

Selecting out Active Session Data

SELECT
        threads.THREAD_ID,
    --  threads.PROCESSLIST_info sql_text,
        REPLACE(REPLACE(substr(threads.PROCESSLIST_info,1,40),'\r',''),'\n','') sql_text_short,
        threads.PROCESSLIST_USER,
        threads.PROCESSLIST_HOST,
        threads.PROCESSLIST_COMMAND,
        threads.PROCESSLIST_STATE
FROM
        performance_schema.threads AS threads 
 Where 
     threads.PROCESSLIST_STATE IS NOT NULL
 and threads.PROCESSLIST_COMMAND != 'Sleep'
 and threads.PROCESSLIST_COMMAND != 'Daemon'
 and threads.PROCESSLIST_ID != CONNECTION_ID()
;

-----------+------------------------------------------+------------------+------------------+---------------------+-------------------+
| THREAD_ID | sql_text_short                           | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_STATE |
+-----------+------------------------------------------+------------------+------------------+---------------------+-------------------+
|       492 | SELECT * FROM order_item where sku LIKE  | shopper_11       | localhost        | Query               | executing         |
|       503 | SELECT * FROM order_item where sku LIKE  | shopper_1        | localhost        | Query               | executing         |
|       596 | SELECT * FROM order_item where sku LIKE  | shopper_7        | localhost        | Query               | executing         |
|       621 | INSERT INTO order_status_change (dbm_ord | shopper_6        | localhost        | Query               | executing         |
|       868 | INSERT INTO order_status_change (dbm_ord | shopper_2        | localhost        | Query               | executing         |
|       926 | INSERT INTO order_status_change (dbm_ord | shopper_13       | localhost        | Query               | executing         |
|     51380 | SELECT * FROM discount where description | shopper_7        | localhost        | Query               | executing         |
|    165954 | INSERT INTO order_status_change (dbm_ord | shopper_20       | localhost        | Query               | executing         |
|     98532 | SELECT * FROM discount where description | shopper_19       | localhost        | Query               | executing         |
|    843087 | select sleep(20)                         | dbmapp           | localhost        | Query               | User sleep        |
|    800275 | select sleep(20)                         | dbmapp           | localhost        | Query               | User sleep        |
|    932043 | select sleep(20)                         | dbmapp           | localhost        | Query               | User sleep        |
+-----------+------------------------------------------+------------------+------------------+---------------------+-------------------+

A few things:

  1. We get the SQL text directly from threads. Seems like this could be a bit inefficient if we collect and store this data frequently. Compare this to Oracle where we can just collect the SQL_ID each time and look up the SQL text just once per SQL_ID.  On MySQL we could has the text every collection and just store the text once per hash. TBD
  2. There is no WAIT_EVENT and no indication of who is waiting or who is runnable on CPU

 events_waits_current

To get wait events we should be able to look at performance_schema.events_waits_current and join threads using (THREAD_ID) .  Both views have the field THREAD_ID.

It sounds pretty simple as the documentation says there is one row per thread

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-events-waits-current-table.html

The table stores one row per thread showing the current status of the thread’s most recent monitored wait event, so there is no system variable for configuring the table size.

Two problems:

  1. by default performance_schema.events_waits_current is empty
  2. there are cases where there are multiple lines in performance_schema.events_waits_current for the same thread_id

For the first issue where performance_schema.events_waits_current is empty, three things are required

  1. performance_schema enabled
  2. setup_consumers enabled
  3.  setup_instruments enabled

1. performance_schema enabled

Make sure

performance_schema  is set to ON:
SHOW VARIABLES LIKE 'performance_schema%';
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
| performance_schema_accounts_size                         | -1    |
| performance_schema_digests_size                          | 10000 |
| performance_schema_error_size                            | 5018  |
| performance_schema_events_stages_history_long_size       | 10000 |
| performance_schema_events_stages_history_size            | 10    |
| performance_schema_events_statements_history_long_size   | 10000 |
| performance_schema_events_statements_history_size        | 10    |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size      | 10    |
| performance_schema_events_waits_history_long_size        | 10000 |
| performance_schema_events_waits_history_size             | 10    |
...

 

2. setup_consumers

https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.6/en/performance-schema-wait-tables.html

The wait consumers are disabled by default:

mysql> SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE 'events_waits%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_waits_current      | NO      |
| events_waits_history      | NO      |
| events_waits_history_long | NO      

To enable all the wait consumers :

      update performance_schema.setup_consumers set enabled="YES" where name like 'events_waits%';

3.setup_instruments

https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.6/en/performance-schema-wait-tables.html

Some wait instruments are enabled by default; others are disabled. For example:

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'wait/io/file/innodb%';
+--------------------------------------+---------+-------+
| NAME                                 | ENABLED | TIMED |
+--------------------------------------+---------+-------+
| wait/io/file/innodb/innodb_data_file | YES     | YES   |
| wait/io/file/innodb/innodb_log_file  | YES     | YES   |
| wait/io/file/innodb/innodb_temp_file | YES     | YES   |
+--------------------------------------+---------+-------+

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'wait/io/socket/%';
+----------------------------------------+---------+-------+
| NAME                                   | ENABLED | TIMED |
+----------------------------------------+---------+-------+
| wait/io/socket/sql/server_tcpip_socket | NO      | NO    |
| wait/io/socket/sql/server_unix_socket  | NO      | NO    |
| wait/io/socket/sql/client_connection   | NO      | NO    |
+----------------------------------------+---------+-------+

There should be zero rows returned for

      SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait%' and ENABLED='NO';

To enable all the instruments :

      update performance_schema.setup_instruments set enabled='YES' , 
      timed='YES'where NAME LIKE 'wait/%';

Now we are ready to query events_waits_current to get wait_events for active thread_id values from performance_schema.threads

 select
     t.thread_id,
     t.PROCESSLIST_STATE,
     w.EVENT_ID,
     w.END_EVENT_ID,
     w.NESTING_EVENT_TYPE ,
     w.NESTING_EVENT_ID,
     w.SOURCE,
     COALESCE(
    IF ( t.PROCESSLIST_STATE ='User sleep','User sleep',  
        IF(w.EVENT_ID=w.END_EVENT_ID,'CPU',EVENT_NAME)  )
    ,'CPUbis')
       AS wait_event,
     REPLACE(REPLACE(substr(t.PROCESSLIST_info,1,40),'\r',''),'\n','') sql_text_short
 from
            performance_schema.threads t
  left join performance_schema.events_waits_current w on w.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()
   order by t.thread_id
 ;


+-----------+-------------------+-----------+--------------+--------------------+------------------+---------------------------------------+------------------------------------------+
| thread_id | PROCESSLIST_STATE | EVENT_ID  | END_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_ID | wait_event                            | sql_text                                 |
+-----------+-------------------+-----------+--------------+--------------------+------------------+---------------------------------------+------------------------------------------+
|       374 | Sending data      |  47464227 |         NULL | NULL               |             NULL | idle                                  | SELECT * FROM discount where description |
|       651 | Sending data      |  48306893 |         NULL | STATEMENT          |         48299933 | wait/io/table/sql/handler             | INSERT INTO order_status_change (dbm_ord |
|       651 | Sending data      |  48306901 |         NULL | WAIT               |         48306898 | wait/synch/mutex/innodb/lock_mutex    | INSERT INTO order_status_change (dbm_ord |
|       701 | Sending data      |  47680423 |         NULL | STATEMENT          |         47309200 | wait/io/table/sql/handler             | INSERT INTO order_status_change (dbm_ord |
|       701 | Sending data      |  47680425 |         NULL | WAIT               |         47680423 | wait/synch/mutex/innodb/trx_sys_mutex | INSERT INTO order_status_change (dbm_ord |
|       712 | Sending data      |  73823545 |         NULL | NULL               |             NULL | idle                                  | SELECT * FROM order_item where sku LIKE  |

Thread_id 651 and 701 both have two rows despite what the manual says about one row per thread!

Which row do we use? Or is there some bug?

Thanks to Mark Leith @MarkLeith who pointed out to me that the one to use is the one with the highest EVENT_ID

 select
     t.thread_id,
     t.PROCESSLIST_STATE,
     w.EVENT_ID,
     w.END_EVENT_ID,
     w.NESTING_EVENT_TYPE ,
     w.NESTING_EVENT_ID,
     w.SOURCE,
     COALESCE(
    IF ( t.PROCESSLIST_STATE ='User sleep','User sleep',  
        IF(w.EVENT_ID=w.END_EVENT_ID,'CPU',EVENT_NAME)  )
    ,'CPU')
       AS wait_event,
     REPLACE(REPLACE(substr(t.PROCESSLIST_info,1,40),'\r',''),'\n','') sql_text 
 from
            performance_schema.threads t
  left join performance_schema.events_waits_current w on w.thread_id = t.thread_id              
   and w.EVENT_ID in (
                           select max(w1.EVENT_ID)
                            from
                                performance_schema.threads t1
                     left join  performance_schema.events_waits_current w1
                     on w1.thread_id = t1.thread_id
                    where 
                            t1.PROCESSLIST_STATE IS NOT NULL
                      and   t1.PROCESSLIST_COMMAND != 'Sleep' 
                      and   t1.PROCESSLIST_COMMAND != 'Daemon'
                      and   t1.PROCESSLIST_ID != CONNECTION_ID()
                    group by t1.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()
   order by t.thread_id
 ;

+-----------+-------------------+-----------+--------------+--------------------+------------------+-------------------------------------------+------------------------------------------+
| thread_id | PROCESSLIST_STATE | EVENT_ID  | END_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_ID | EVENT_NAME                                | sql_text                                 |
+-----------+-------------------+-----------+--------------+--------------------+------------------+-------------------------------------------+------------------------------------------+
|       672 | Sending data      |  47224916 |         NULL | STATEMENT          |         47224892 | wait/io/table/sql/handler                 | SELECT * FROM order_item where sku LIKE  |
|      1864 | User sleep        |  97625244 |         NULL | STATEMENT          |         97625228 | wait/synch/cond/sql/Item_func_sleep::cond | select sleep(20)                         |
|      1911 | updating          | 108506184 |         NULL | STATEMENT          |        108506129 | wait/io/table/sql/handler                 | update customer            set name=nam  |
|      2059 | User sleep        |  89344512 |         NULL | STATEMENT          |         89344496 | wait/synch/cond/sql/Item_func_sleep::cond | select sleep(20)                         |
+-----------+-------------------+-----------+--------------+--------------------+------------------+-------------------------------------------+------------------------------------------+

Now there are still a few problems. First how do we know who is on CPU and who is waiting?
If END_EVENT_ID has a value then the wait has finished and we should be on CPU.
Also what happens if there is not a corresponding row in events_waits_current for a thread_id that is active in performance_schema.threads? I imagine that means the thread is runnable on CPU as well. Putting this together we get

select
    t.thread_id,
    t.PROCESSLIST_COMMAND cmd,
    t.PROCESSLIST_STATE,
    w.EVENT_ID,
    w.END_EVENT_ID,
    w.NESTING_EVENT_TYPE ,
    w.NESTING_EVENT_ID,
    COALESCE( IF(w.EVENT_ID=w.END_EVENT_ID,'CPU',EVENT_NAME) ,'CPU') AS wait_event,
    REPLACE(REPLACE(substr(t.PROCESSLIST_info,1,40),'\r',''),'\n','') sql_text 
from
           performance_schema.threads t
 left join performance_schema.events_waits_current w on w.thread_id = t.thread_id  
  and w.EVENT_ID in (
                           select max(w1.EVENT_ID)
                            from
                                performance_schema.threads t1
                     left join  performance_schema.events_waits_current w1
                     on w1.thread_id = t1.thread_id
                    where 
                            t1.PROCESSLIST_STATE IS NOT NULL
                      and   t1.PROCESSLIST_COMMAND != 'Sleep' 
                      and   t1.PROCESSLIST_COMMAND != 'Daemon'
                      and   t1.PROCESSLIST_ID != CONNECTION_ID()
                    group by t1.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 | PROCESSLIST_STATE | EVENT_ID | END_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_ID | wait_event                                | sql_text                                 |
+-----------+-------------------+----------+--------------+--------------------+------------------+-------------------------------------------+------------------------------------------+
|      1000 | Sending data      |  2101672 |         NULL | NULL               |             NULL | idle                                      | SELECT * FROM order_item where sku LIKE  |
|      1461 | User sleep        |   273962 |         NULL | NULL               |             NULL | wait/synch/cond/sql/Item_func_sleep::cond | select sleep(20)                         |
|      1466 | Sending data      |  1576483 |         NULL | NULL               |             NULL | idle                                      | SELECT * FROM discount where description |
|      1682 | Sending data      |  1037890 |         NULL | NULL               |             NULL | idle                                      | SELECT * FROM discount where description |
|      1888 | freeing items     |  1489102 |      1489102 | NULL               |             NULL | CPU                                       | COMMIT                                   |
+-----------+-------+-------------------+----------+--------------+--------------------+------------------+-------------------------------------------+------------------------------------------+

Now there are still strange things such as the “idle” wait_events? Why does performance_schema.threads say we are active but the corresponding row in events_waits_current say we are idle?
By the way, I only see these “idle” events on 5.6 and 5.7. On 8.0 they seem to be gone.
One nice thing is that this query runs on all three versions which is a bit surprising considering all the performance schema changes over those three versions.
Adding in performance_schema.events_statements_current & performance_schema.socket_instances

SELECT
    threads.thread_id, 
    threads.PROCESSLIST_ID,
    threads.PROCESSLIST_USER,
    threads.PROCESSLIST_HOST,
    threads.PROCESSLIST_DB,
    threads.PROCESSLIST_COMMAND,
    threads.PROCESSLIST_STATE ,
--  statement.TIMER_START AS event_timer_start,
--  statement.TIMER_END AS event_timer_end,
-- statement.LOCK_TIME,
    statement.CURRENT_SCHEMA,
--  threads.PROCESSLIST_info as SQL_TEXT,
    REPLACE(REPLACE(substr(threads.PROCESSLIST_info,1,40),'\r',''),'\n','') sql_text,
    COALESCE(
       IF ( threads.PROCESSLIST_STATE ='User sleep','User sleep',  
             IF(waits.EVENT_ID=waits.END_EVENT_ID,'CPU',waits.EVENT_NAME)  )
       ,'CPUbis') AS wait_event,
--  waits.TIMER_START AS wait_timer_start,
--  waits.TIMER_END AS wait_timer_end,
    waits.OBJECT_SCHEMA,
    waits.OBJECT_NAME,
    waits.INDEX_NAME,
    waits.OBJECT_TYPE,
    waits.SOURCE,
    socket.IP,
    socket.PORT
FROM
           performance_schema.threads                   AS threads
 LEFT JOIN performance_schema.events_waits_current      AS waits on waits.thread_id = threads.thread_id 
   and waits.EVENT_ID in (  select max(w1.EVENT_ID)
                           from   performance_schema.threads t1
                       left join  performance_schema.events_waits_current w1
                         on w1.thread_id = t1.thread_id
                   where 
                           t1.PROCESSLIST_STATE IS NOT NULL
                     and   t1.PROCESSLIST_COMMAND != 'Sleep' 
                     and   t1.PROCESSLIST_COMMAND != 'Daemon'
                     and   t1.PROCESSLIST_ID != CONNECTION_ID()
                   group by t1.thread_id)
 LEFT JOIN performance_schema.events_statements_current AS statement ON statement.thread_id = threads.thread_id
 LEFT JOIN performance_schema.socket_instances          AS socket  ON socket.thread_id = threads.thread_id
where 
        threads.PROCESSLIST_STATE IS NOT NULL
  and   threads.PROCESSLIST_COMMAND != 'Sleep' 
  and   threads.PROCESSLIST_COMMAND != 'Daemon'
  and   threads.PROCESSLIST_ID != CONNECTION_ID()
  order by threads.thread_id
;  
  

One thing that doesn’t make sense at all to me is that with the last line the SQL, the order by, the output includes lines with PROCESSLIST_COMMAND = Sleep even though there is an explicit threads.PROCESSLIST_COMMAND != ‘Sleep’. Anyone have an idea of what is happening? Is this a bug or is there an explanation?

-----------+----------------+------------------+------------------+----------------+---------------------+----------------------------+----------------+------------------------------------------+---------------------------+---------------+-------------+------------+-------------+--------------------+-----------+-------+
| thread_id | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_STATE          | CURRENT_SCHEMA | sql_text                                 | wait_event                | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | OBJECT_TYPE | SOURCE             | IP        | PORT  |
+-----------+----------------+------------------+------------------+----------------+---------------------+----------------------------+----------------+------------------------------------------+---------------------------+---------------+-------------+------------+-------------+--------------------+-----------+-------+
|       557 |            445 | shopper_28       | localhost        | dbmorders_4    | Query               | executing                  | dbmorders_4    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL               | 127.0.0.1 | 42832 |
|       582 |            470 | shopper_12       | localhost        | dbmorders_5    | Query               | executing                  | dbmorders_5    | SELECT * FROM order_item where sku LIKE  | wait/io/table/sql/handler | dbmorders_5   | order_item  | NULL       | TABLE       | handler.cc:2991    | 127.0.0.1 | 42884 |
|       608 |            496 | shopper_11       | localhost        | dbmorders_1    | Query               | executing                  | dbmorders_1    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL               | 127.0.0.1 | 42940 |
|       750 |            638 | shopper_14       | localhost        | dbmorders_9    | Query               | waiting for handler commit | dbmorders_9    | INSERT INTO order_status_change (dbm_ord | CPU                       | NULL          | NULL        | NULL       | NULL        | sql_plugin.cc:2632 | 127.0.0.1 | 43240 |
|       789 |            677 | shopper_3        | localhost        | dbmorders_4    | Query               | executing                  | dbmorders_4    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL               | 127.0.0.1 | 43320 |
|       811 |            699 | shopper_7        | localhost        | dbmorders      | Sleep               | NULL                       | dbmorders      | NULL                                     | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL               | 127.0.0.1 | 43366 |
|       866 |            754 | shopper_4        | localhost        | dbmorders_8    | Sleep               | NULL                       | dbmorders_8    | NULL                                     | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL               | 127.0.0.1 | 43482 |
|       921 |            809 | dbmapp           | localhost        | dbm            | Query               | updating                   | dbm            | update customer            set name=nam  | wait/io/table/sql/handler | dbm           | customer    | PRIMARY    | TABLE       | handler.cc:3262    | 127.0.0.1 | 43598 |
|       938 |            826 | dbmapp           | localhost        | dbm            | Query               | User sleep                 | dbm            | select sleep(20)                         | User sleep                | NULL          | NULL        | NULL       | NULL        | item_func.cc:5301  | 127.0.0.1 | 43636 |


where as without the order by there are no threads.PROCESSLIST_COMMAND = ‘Sleep’

+-----------+----------------+------------------+------------------+----------------+---------------------+----------------------------+----------------+------------------------------------------+---------------------------+---------------+-------------+------------+-------------+-------------------+-----------+-------+
| thread_id | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_STATE          | CURRENT_SCHEMA | sql_text                                 | wait_event                | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | OBJECT_TYPE | SOURCE            | IP        | PORT  |
+-----------+----------------+------------------+------------------+----------------+---------------------+----------------------------+----------------+------------------------------------------+---------------------------+---------------+-------------+------------+-------------+-------------------+-----------+-------+
|       657 |            545 | shopper_9        | localhost        | dbmorders_7    | Query               | executing                  | dbmorders_7    | SELECT * FROM dbm_user where username LI | wait/io/table/sql/handler | dbmorders_7   | dbm_user    | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 43046 |
|       485 |            373 | shopper_8        | localhost        | dbmorders_4    | Query               | executing                  | dbmorders_4    | SELECT * FROM dbm_user where username LI | wait/io/table/sql/handler | dbmorders_4   | dbm_user    | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 42666 |
|       561 |            448 | shopper_23       | localhost        | dbmorders_5    | Query               | executing                  | dbmorders_5    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 42840 |
|       778 |            666 | shopper_5        | localhost        | dbmorders      | Query               | executing                  | dbmorders      | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 43298 |
|     26965 |           1061 | shopper_17       | localhost        | dbmorders_2    | Query               | executing                  | dbmorders_2    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 44132 |
|       720 |            608 | shopper_22       | localhost        | dbmorders_1    | Query               | executing                  | dbmorders_1    | SELECT * FROM discount where description | wait/io/table/sql/handler | dbmorders_1   | discount    | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 43176 |
|       726 |            614 | shopper_22       | localhost        | dbmorders_5    | Query               | executing                  | dbmorders_5    | SELECT * FROM discount where description | wait/io/table/sql/handler | dbmorders_5   | discount    | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 43188 |
|     10663 |            995 | shopper_25       | localhost        | dbmorders_2    | Query               | executing                  | dbmorders_2    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 43988 |
|      8619 |            987 | shopper_24       | localhost        | dbmorders_9    | Query               | executing                  | dbmorders_9    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 43970 |
|      1350 |            862 | shopper_27       | localhost        | dbmorders_5    | Query               | executing                  | dbmorders_5    | SELECT * FROM dbm_user where username LI | wait/io/table/sql/handler | dbmorders_5   | dbm_user    | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 43712 |
|    222821 |           1341 | dbmapp           | localhost        | dbm            | Query               | updating                   | dbm            | update customer            set name=nam  | wait/io/table/sql/handler | dbm           | customer    | PRIMARY    | TABLE       | handler.cc:3262   | 127.0.0.1 | 44734 |
|    108033 |           1233 | dbmapp           | localhost        | dbm            | Query               | User sleep                 | dbm            | select sleep(20)                         | User sleep                | NULL          | NULL        | NULL       | NULL        | item_func.cc:5301 | 127.0.0.1 | 44502 |
|     31247 |           1075 | dbmapp           | localhost        | dbm            | Query               | User sleep                 | dbm            | select sleep(20)                         | User sleep                | NULL          | NULL        | NULL       | NULL        | item_func.cc:5301 | 127.0.0.1 | 44164 |
|     58167 |           1143 | shopper_26       | localhost        | dbmorders_1    | Query               | executing                  | dbmorders_1    | INSERT INTO order_status_change (dbm_ord | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 44306 |
|    550359 |           1871 | root             | localhost        | kyle           | Query               | updating                   | kyle           | update toto set id=4                     | wait/io/table/sql/handler | kyle          | toto        | NULL       | TABLE       | handler.cc:2991   | 127.0.0.1 | 45860 |
|    550417 |           1929 | dbm              | localhost        | sbtest         | Query               | waiting for handler commit | sbtest         | COMMIT                                   | CPUbis                    | NULL          | NULL        | NULL       | NULL        | NULL              | 127.0.0.1 | 45984 |
+-----------+----------------+------------------+------------------+----------------+---------------------+----------------------------+----------------+------------------------------------------+---------------------------+---------------+-------------+------------+-------------+-------------------+-----------+-------+

From Twitter, Øystein Grøvlen @ogrovlen, suggests
·
Regarding your last question, there is no concurrency control for performance schema tables, and the optimizer may choose to fetch column values after sorting. I suggest to try, ORDER BY threads.thread_id, threads.PROCESSLIST_COMMAND
 order by “threads.thread_id, threads.PROCESSLIST_COMMAND” didn’t work for me. Still getting sleep in the output but it gave me a idea  about adding a column from events_waits_current to the order by. That did work and now the sleeps are correctly filtered out
“order by t.thread_id , w.EVENT_NAME”


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. | #1

    I tried your query for waits
    but it looks like I am getting only the latest wait (max) from events_waits_current table,

    should add a join to events_waits_current using this data:
    event_name, sum(timer_wait) group by event_name from events_waits_history
    there thread_id=xx and event_id=yy?

  3. khailey
    | #2

    @Yossi Nixon
    what does the output of your query look like?
    Which waits are you looking for? The query should give the current wait. Are you looking for next waits?


+ eight = 9