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:
- 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
- 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:
- by default performance_schema.events_waits_current is empty
- 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
- performance_schema enabled
- setup_consumers enabled
- 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”
“order by t.thread_id , w.EVENT_NAME”
Trackbacks
Comments
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?
@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?