MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

22.12.10.3 The threads Table

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it: 

mysql> SELECT * FROM performance_schema.threads\G
*************************** 1. row ***************************
          THREAD_ID: 1
               NAME: thread/sql/main
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 80284
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
...
*************************** 4. row ***************************
          THREAD_ID: 51
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 34
   PROCESSLIST_USER: isabella
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: performance_schema
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: SELECT * FROM performance_schema.threads
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
...

When the Performance Schema initializes, it populates the threads table based on the threads in existence then. Thereafter, a new row is added each time the server creates a thread.

The INSTRUMENTED column value for new threads is determined by the contents of the setup_actors table. For information about how to use the setup_actors table to control this column, see Section 22.4.6, “Pre-Filtering by Thread”.

Removal of rows from the threads table occurs when threads end. For a thread associated with a client session, removal occurs when the session ends. If a client has auto-reconnect enabled and the session reconnects after a disconnect, the session becomes associated with a new row in the threads table that has a different PROCESSLIST_ID value. The initial INSTRUMENTED value for the new thread may be different from that of the original thread: The setup_actors table may have changed in the meantime, and if the INSTRUMENTED value for the original thread was changed after it was initialized, that change does not carry over to the new thread.

You can enable or disable thread monitoring (that is, whether events executed by the thread are instrumented). To control the initial INSTRUMENTED value for new foreground threads, use the setup_actors table. To control monitoring of existing threads, set the INSTRUMENTED column of threads table rows. (For more information about the conditions under which thread monitoring occurs, see the description of the INSTRUMENTED column.)

For a comparison of the threads table columns with names having a prefix of PROCESSLIST_ to other process information sources, see Sources of Process Information.

Important

For thread information sources other than the threads table, information about threads for other users is shown only if the current user has the PROCESS privilege. That is not true of the threads table; all rows are shown to any user who has the SELECT privilege for the table. Users who should not be able to see threads for other users by accessing the threads table should not be given the SELECT privilege for it.

The threads table has these columns:

TRUNCATE TABLE is not permitted for the threads table.

MySQL Documentation Library