MySQL performance schema threads

A trap for those new to the MySQL Performance Schema is the expectation that thread_id in tables such as events_statements_current matches the id you find in the MySQL processlist. This is NOT TRUE.

If we look at the INFORMATION_SCHEMA.PROCESSLIST table we will find information like:

mysql> select id,db,command,state from information_schema.processlist order by id;
-----------+--------------------+---------+------------------------------------------------------------------+
| id        | db                 | command | state                                                            |
+-----------+--------------------+---------+------------------------------------------------------------------+
|         1 | NULL               | Connect | Slave has read all relay log; waiting for the slave I/O thread t |
|         2 | NULL               | Connect | Waiting for master to send event                                 |
|         5 | NULL               | Sleep   |                                                                  |
|  34404870 | NULL               | Sleep   |                                                                  |
| 106759077 | performance_schema | Sleep   |                                                                  |
| 106904549 | performance_schema | Query   | executing                                                        |
| 107004213 | abc                | Sleep   |                                                                  |
| 107004600 | abc                | Sleep   |                                                                  |
| 107004877 | abc                | Sleep   |                                                                  |
| 107004937 | abc                | Sleep   |                                                                  |
| 107004989 | abc                | Sleep   |                                                                  |
| 107005164 | abc                | Sleep   |                                                                  |
| 107005305 | abc                | Sleep   |                                                                  |
| 107005310 | abc                | Sleep   |                                                                  |
| 107005314 | abc                | Sleep   |                                                                  |
| 107005316 | abc                | Sleep   |                                                                  |
| 107005317 | abc                | Sleep   |                                                                  |
| 107005321 | abc                | Sleep   |                                                                  |
+-----------+--------------------+---------+------------------------------------------------------------------+

However, if I wanted to look at the two slave threads in performance_schema.events_statements_current I will find no matching data.

mysql> select * from performance_schema.events_statements_current where thread_id=1;
Empty set (0.00 sec)
mysql> select * from performance_schema.events_statements_current where thread_id=2;
Empty set (0.00 sec)

In the performance schema, you need to use the threads table in order to determine a match between the processlist and the threads. If we look at the table, you will find information about all the BACKGROUND threads in MySQL.

mysql> select thread_id,name,type,processlist_id from threads;
+-----------+----------------------------------------+------------+----------------+
| thread_id | name                                   | type       | processlist_id |
+-----------+----------------------------------------+------------+----------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL |
|         2 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         3 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         4 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         5 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         6 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         7 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         8 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|         9 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        10 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        11 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        12 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        13 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        14 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        15 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL |
|        17 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL |
|        18 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL |
|        19 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL |
|        20 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL |
|        21 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL |
|        22 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL |
|        23 | thread/sql/signal_handler              | BACKGROUND |           NULL |
|        24 | thread/sql/slave_io                    | BACKGROUND |           NULL |
|        25 | thread/sql/slave_sql                   | BACKGROUND |           NULL |
|        28 | thread/sql/one_connection              | FOREGROUND |              5 |
| 107013952 | thread/sql/one_connection              | FOREGROUND |      107013929 |
| 107013989 | thread/sql/one_connection              | FOREGROUND |      107013966 |
| 106759100 | thread/sql/one_connection              | FOREGROUND |      106759077 |
| 107014180 | thread/sql/one_connection              | FOREGROUND |      107014157 |
| 107014291 | thread/sql/one_connection              | FOREGROUND |      107014268 |
| 106904572 | thread/sql/one_connection              | FOREGROUND |      106904549 |
| 107014443 | thread/sql/one_connection              | FOREGROUND |      107014420 |
| 107014490 | thread/sql/one_connection              | FOREGROUND |      107014467 |
| 107014491 | thread/sql/one_connection              | FOREGROUND |      107014468 |
| 107014493 | thread/sql/one_connection              | FOREGROUND |      107014470 |
| 107014497 | thread/sql/one_connection              | FOREGROUND |      107014474 |
| 107014498 | thread/sql/one_connection              | FOREGROUND |      107014475 |
|  34404893 | thread/sql/one_connection              | FOREGROUND |       34404870 |
| 107013233 | thread/sql/one_connection              | FOREGROUND |      107013210 |
+-----------+----------------------------------------+------------+----------------+
39 rows in set (0.00 sec)

While it’s great the performance schema can track the statements executed in the Slave SQL thread (I’ll discuss that later), it’s disappointing that the processlist_id, the one thing that can join our two sources of data other, has not value for the slave threads.

mysql> select * from threads where thread_id=25\G
*************************** 1. row ***************************
          THREAD_ID: 25
               NAME: thread/sql/slave_sql
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Slave has read all relay log; waiting for the slave I/O thread t
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
1 row in set (0.00 sec)

For normal queries we can get a relationship between the processlist and the performance schema with:

select p.id,p.db,p.command,p.state,s.*
from information_schema.processlist p
inner join performance_schema.threads t on p.id = t.processlist_id
inner join performance_schema. events_statements_current s using (thread_id)
where p.command='Query'\G
...
*************************** 2. row ***************************
                     id: 106904549
                     db: performance_schema
                command: Query
                  state: executing
              THREAD_ID: 106904572
               EVENT_ID: 78
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: mysqld.cc:931
            TIMER_START: 268504759200898000
              TIMER_END: NULL
             TIMER_WAIT: NULL
              LOCK_TIME: 580000000
               SQL_TEXT: select p.id,p.db,p.command,p.state,s.* from information_schema.processlist p inner join performance_schema.threads t on p.id = t.processlist_id inner join performance_schema. events_statements_current s using (thread_id) where p.command='Query'
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: performance_schema
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 1
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 2
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL

2 Responses to “MySQL performance schema threads”

  1. Mark Leith says:

    There is actually no need to JOIN to the I_S.PROCESSLIST table with P_S and 5.6, as all the columns in the PROCESSLIST table are in the P_S.threads table (those prefixed with PROCESSLIST_*)..

    In fact I would specifically recommend against it, as the I_S table has mutex implications, where P_S does not, see:

    http://www.markleith.co.uk/2012/07/13/monitoring-processes-with-performance-schema-in-mysql-5-6/

  2. ronald says:

    Thanks Mark, that’s great feedback!