Description:
performance_schema.threads doesn't report PROCESSLIST_STATE when worker is applying an event:
slave1 [localhost] {msandbox} ((none)) > SELECT * FROM performance_schema.threads WHERE NAME = 'thread/sql/slave_worker' \G SHOW PROCESSLIST;
*************************** 1. row ***************************
THREAD_ID: 27
NAME: thread/sql/slave_worker
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 485
PROCESSLIST_STATE: Waiting for an event from Coordinator
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 26
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 38880
*************************** 2. row ***************************
THREAD_ID: 28
NAME: thread/sql/slave_worker
TYPE: FOREGROUND
PROCESSLIST_ID: 4
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 485
PROCESSLIST_STATE: Waiting for an event from Coordinator
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 26
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 38881
*************************** 3. row ***************************
THREAD_ID: 29
NAME: thread/sql/slave_worker
TYPE: FOREGROUND
PROCESSLIST_ID: 5
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 485
PROCESSLIST_STATE: Waiting for an event from Coordinator
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 26
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 38882
*************************** 4. row ***************************
THREAD_ID: 30
NAME: thread/sql/slave_worker
TYPE: FOREGROUND
PROCESSLIST_ID: 6
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 485
PROCESSLIST_STATE: Waiting for an event from Coordinator
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 26
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 38883
*************************** 5. row ***************************
THREAD_ID: 31
NAME: thread/sql/slave_worker
TYPE: FOREGROUND
PROCESSLIST_ID: 7
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 37
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 26
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 38884
5 rows in set (0.01 sec)
+----+-------------+-----------+------+---------+------+---------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+---------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 485 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 0 | Reading event from the relay log | NULL |
| 3 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 37 | Executing event | NULL |
| 11 | msandbox | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+---------------------------------------+------------------+
8 rows in set (0.00 sec)
How to repeat:
1. Start slave with slave_parallel_workers > 0
2. Execute an event on master that will take long enough for you to capture it on slave
3. Compare output of SHOW PROCESSLIST and SELECT * FROM performance_schema.threads WHERE NAME = 'thread/sql/slave_worker'
Suggested fix:
Report correct PROCESSLIST_STATE on performance_schema.threads
Description: performance_schema.threads doesn't report PROCESSLIST_STATE when worker is applying an event: slave1 [localhost] {msandbox} ((none)) > SELECT * FROM performance_schema.threads WHERE NAME = 'thread/sql/slave_worker' \G SHOW PROCESSLIST; *************************** 1. row *************************** THREAD_ID: 27 NAME: thread/sql/slave_worker TYPE: FOREGROUND PROCESSLIST_ID: 3 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 485 PROCESSLIST_STATE: Waiting for an event from Coordinator PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 26 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 38880 *************************** 2. row *************************** THREAD_ID: 28 NAME: thread/sql/slave_worker TYPE: FOREGROUND PROCESSLIST_ID: 4 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 485 PROCESSLIST_STATE: Waiting for an event from Coordinator PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 26 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 38881 *************************** 3. row *************************** THREAD_ID: 29 NAME: thread/sql/slave_worker TYPE: FOREGROUND PROCESSLIST_ID: 5 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 485 PROCESSLIST_STATE: Waiting for an event from Coordinator PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 26 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 38882 *************************** 4. row *************************** THREAD_ID: 30 NAME: thread/sql/slave_worker TYPE: FOREGROUND PROCESSLIST_ID: 6 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 485 PROCESSLIST_STATE: Waiting for an event from Coordinator PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 26 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 38883 *************************** 5. row *************************** THREAD_ID: 31 NAME: thread/sql/slave_worker TYPE: FOREGROUND PROCESSLIST_ID: 7 PROCESSLIST_USER: NULL PROCESSLIST_HOST: NULL PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Connect PROCESSLIST_TIME: 37 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 26 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: NULL THREAD_OS_ID: 38884 5 rows in set (0.01 sec) +----+-------------+-----------+------+---------+------+---------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+---------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 485 | Waiting for master to send event | NULL | | 2 | system user | | NULL | Connect | 0 | Reading event from the relay log | NULL | | 3 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL | | 4 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL | | 5 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL | | 6 | system user | | NULL | Connect | 485 | Waiting for an event from Coordinator | NULL | | 7 | system user | | NULL | Connect | 37 | Executing event | NULL | | 11 | msandbox | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | +----+-------------+-----------+------+---------+------+---------------------------------------+------------------+ 8 rows in set (0.00 sec) How to repeat: 1. Start slave with slave_parallel_workers > 0 2. Execute an event on master that will take long enough for you to capture it on slave 3. Compare output of SHOW PROCESSLIST and SELECT * FROM performance_schema.threads WHERE NAME = 'thread/sql/slave_worker' Suggested fix: Report correct PROCESSLIST_STATE on performance_schema.threads