Bug #84655 replication worker thread status not showing on performance_schema.threads
Submitted: 24 Jan 2017 22:59 Modified: 25 Jan 2017 7:52
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[24 Jan 2017 22:59] Marcelo Altmann
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
[25 Jan 2017 7:52] Umesh Shastry
Hello Marcelo,

Thank you for the report.

Thanks,
Umesh