MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

25.12.16.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 and historical event logging are 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
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 489803
...
*************************** 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
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 755399
...

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 and HISTORY column values for new threads are determined by the contents of the setup_actors table. For information about how to use the setup_actors table to control these columns, see Section 25.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 and HISTORY values for the new thread may be different from those of the original thread: The setup_actors table may have changed in the meantime, and if the INSTRUMENTED or HISTORY value for the original thread was changed after the row was initialized, the change does not carry over to the new thread.

The threads table columns with names having a prefix of PROCESSLIST_ provide information similar to that available from the INFORMATION_SCHEMA.PROCESSLIST table or the SHOW PROCESSLIST statement. Thus, all three sources provide thread-monitoring information. Use of threads differs from use of the other two sources in these ways:

For these reasons, DBAs who perform server monitoring using INFORMATION_SCHEMA.PROCESSLIST or SHOW PROCESSLIST may wish to monitor using the threads table instead.

Note

For INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST, 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 should not be given that privilege.

The threads table has these columns:

TRUNCATE TABLE is not permitted for the threads table.