20.9.9.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 threads\G*************************** 1. row ***************************          THREAD_ID: 1               NAME: thread/sql/main               TYPE: BACKGROUND     PROCESSLIST_ID: NULL   PROCESSLIST_USER: NULL   PROCESSLIST_HOST: NULL     PROCESSLIST_DB: NULLPROCESSLIST_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: paul   PROCESSLIST_HOST: localhost     PROCESSLIST_DB: performance_schemaPROCESSLIST_COMMAND: Query   PROCESSLIST_TIME: 0  PROCESSLIST_STATE: Sending data   PROCESSLIST_INFO: SELECT * FROM threads   PARENT_THREAD_ID: 1               ROLE: NULL       INSTRUMENTED: YES...

If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using).

The threads table has these columns:

THREAD_ID

A unique thread identifier.
NAME

The name associated with the thread instrumentation code in the server. For example, thread/sql/one_connection corresponds to the thread function in the code responsible for handling a user connection, and thread/sql/main stands for the main() function of the server.
TYPE

The thread type, either FOREGROUND or BACKGROUND. User connection threads are foreground threads. Threads associated with internal server activity are background threads. Examples are internal InnoDB threads, "binlog dump" threads sending information to slaves, and slave I/O and SQL threads.
PROCESSLIST_ID

For threads that are displayed in the INFORMATION_SCHEMA.PROCESSLIST table, this is the PROCESSLIST.ID value, which is also the value that CONNECTION_ID() would return within that thread. For background threads (threads not associated with a user connection), PROCESSLIST_ID is 0, so the values are not unique.
PROCESSLIST_USER

The user associated with a foreground thread, NULL for a background thread.
PROCESSLIST_HOST

The host name of the client associated with a foreground thread, NULL for a background thread.
PROCESSLIST_DB

The default database for the thread, or NULL if there is none.
PROCESSLIST_COMMAND

The type of command the thread is executing. For descriptions for thread commands, see Section 8.12.5, "Examining Thread Information". The value of this column corresponds to the COM_xxx commands of the client/server protocol and Com_xxx status variables. See Section 5.1.6, "Server Status Variables"
PROCESSLIST_TIME

The time in seconds that the thread has been in its current state.
PROCESSLIST_STATE

An action, event, or state that indicates what the thread is doing. For descriptions of PROCESSLIST_STATE values, see Section 8.12.5, "Examining Thread Information".

Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
PROCESSLIST_INFO

The statement the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the PROCESSLIST_INFO value shows the SELECT statement.
PARENT_THREAD_ID

If this thread is a subthread (spawned by another thread), this is the THREAD_ID value of the spawning thread.
ROLE

Unused.
INSTRUMENTED

Whether the thread is instrumented:
- For foreground threads, the initial INSTRUMENTED value is determined by whether the user account associated with the thread matches any row in the setup_actors table. Matching is based on the values of the PROCESSLIST_USER and PROCESSLIST_HOST columns.
  
  If the thread spawns a subthread, matching occurs again for the subthread.
- For background threads, INSTRUMENTED is YES by default. setup_actors is not consulted because there is no associated user for background threads.
- For any thread, its INSTRUMENTED value can be changed during the lifetime of the thread. This is the only threads table column that can be modified.
For thread monitoring to occur, these things must be true:
- The thread_instrumentation consumer in the setup_consumers table must be YES.
- The thread.INSTRUMENTED column must be YES.
- Monitoring occurs only for those thread events produced from instruments that are enabled, as specified in the setup_instruments table.

The initial contents of the threads table are based on the threads in existence when Performance Schema initialization occurs. Thereafter, a new row is added each time the server creates a 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 will be associated with a new row in the threads table (one 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.

The threads table columns with names having a prefix of PROCESS_ 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:

Access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences because they require a mutex.
threads provides additional information for each thread, such as whether it is a foreground or background thread, and the location within the server associated with the thread.
threads provides information about background threads, so it can be used to monitor activity the other thread information sources cannot.
You can turn thread monitoring on or off. To control monitoring of existing threads, set the INSTRUMENTED column of the threads table. To control the initial INSTRUMENTED value for new foreground threads, use the setup_actors table.

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

20.9.9.3. The threads Table

20.9.9.3. The `threads` Table