27.12.21.6 The threads Table

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 replicas, and replication I/O and SQL threads.

PROCESSLIST_ID

For a foreground thread (associated with a user connection), this is the connection identifier. This is the same value displayed in the ID column of the INFORMATION_SCHEMA PROCESSLIST table, displayed in the Id column of SHOW PROCESSLIST output, and returned by the CONNECTION_ID() function within the thread.

For a background thread (not associated with a user connection), PROCESSLIST_ID is NULL, 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.

Unlike the HOST column of the INFORMATION_SCHEMA PROCESSLIST table or the Host column of SHOW PROCESSLIST output, the PROCESSLIST_HOST column does not include the port number for TCP/IP connections. To obtain this information from the Performance Schema, enable the socket instrumentation (which is not enabled by default) and examine the socket_instances table:

mysql> SELECT NAME, ENABLED, TIMED
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'wait/io/socket%';
+----------------------------------------+---------+-------+
| NAME                                   | ENABLED | TIMED |
+----------------------------------------+---------+-------+
| wait/io/socket/sql/server_tcpip_socket | NO      | NO    |
| wait/io/socket/sql/server_unix_socket  | NO      | NO    |
| wait/io/socket/sql/client_connection   | NO      | NO    |
+----------------------------------------+---------+-------+
3 rows in set (0.01 sec)
 
mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED='YES'
       WHERE NAME LIKE 'wait/io/socket%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> SELECT * FROM performance_schema.socket_instances\G
*************************** 1. row ***************************
           EVENT_NAME: wait/io/socket/sql/client_connection
OBJECT_INSTANCE_BEGIN: 140612577298432
            THREAD_ID: 31
            SOCKET_ID: 53
                   IP: ::ffff:127.0.0.1
                 PORT: 55642
                STATE: ACTIVE
...

PROCESSLIST_DB

The default database for the thread, or NULL if none has been selected.

PROCESSLIST_COMMAND

For foreground threads, the type of command the thread is executing on behalf of the client, or Sleep if the session is idle. For descriptions of thread commands, see Section 8.14, “Examining Server Thread (Process) 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.10, “Server Status Variables”

Background threads do not execute commands on behalf of clients, so this column may be NULL.

PROCESSLIST_TIME

The time in seconds that the thread has been in its current state. For a replica SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the replica host. See Section 17.2.3, “Replication Threads”.

PROCESSLIST_STATE

An action, event, or state that indicates what the thread is doing. For descriptions of PROCESSLIST_STATE values, see Section 8.14, “Examining Server Thread (Process) Information”. If the value if NULL, the thread may correspond to an idle client session or the work it is doing is not instrumented with stages.

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

PROCESSLIST_INFO

The statement the thread is executing, or NULL if it is executing no 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 events executed by the thread are instrumented. The value is YES or NO.

For monitoring of events executed by the thread to occur, these things must be true:

HISTORY

Whether to log historical events for the thread. The value is YES or NO.

For historical event logging for the thread to occur, these things must be true:

CONNECTION_TYPE

The protocol used to establish the connection, or NULL for background threads. Permitted values are TCP/IP (TCP/IP connection established without encryption), SSL/TLS (TCP/IP connection established with encryption), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), and Shared Memory (Windows shared memory connection).

THREAD_OS_ID

The thread or task identifier as defined by the underlying operating system, if there is one:

For Windows, THREAD_OS_ID corresponds to the thread ID visible in Process Explorer (https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx).

For Linux, THREAD_OS_ID corresponds to the value of the gettid() function. This value is exposed, for example, using the perf or ps -L commands, or in the proc file system (/proc/[pid]/task/[tid]). For more information, see the perf-stat(1), ps(1), and proc(5) man pages.

RESOURCE_GROUP

The resource group label. This value is NULL if resource groups are not supported on the current platform or server configuration (see Resource Group Restrictions).

Primary key on (THREAD_ID)

Index on (NAME)

Index on (PROCESSLIST_ID)

Index on (PROCESSLIST_USER, PROCESSLIST_HOST)

Index on (PROCESSLIST_HOST)

Index on (THREAD_OS_ID)

Index on (RESOURCE_GROUP)