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.
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
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
PROCESSLIST_ID value. The
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
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
For thread information sources other than the
threads table, information
about threads for other users is shown only if the current
user has the
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
table should not be given the
SELECT privilege for it.
threads table has these
A unique thread identifier.
The name associated with the thread instrumentation code in the server. For example,
thread/sql/one_connectioncorresponds to the thread function in the code responsible for handling a user connection, and
thread/sql/mainstands for the
main()function of the server.
The thread type, either
BACKGROUND. User connection threads are foreground threads. Threads associated with internal server activity are background threads. Examples are internal
InnoDBthreads, “binlog dump” threads sending information to replicas, and replica I/O and SQL threads.
For a foreground thread (associated with a user connection), this is the connection identifier. This is the same value displayed in the
IDcolumn of the
PROCESSLISTtable, displayed in the
SHOW PROCESSLISToutput, and returned by the
CONNECTION_ID()function within the thread.
For a background thread (not associated with a user connection),
NULL, so the values are not unique.
The user associated with a foreground thread,
NULLfor a background thread.
The host name of the client associated with a foreground thread,
NULLfor a background thread.
HOSTcolumn of the
PROCESSLISTtable or the
SHOW PROCESSLISToutput, the
PROCESSLIST_HOSTcolumn 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
mysql> SELECT * 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 ...
The default database for the thread, or
NULLif none has been selected.
For foreground threads, the type of command the thread is executing on behalf of the client, or
Sleepif 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_commands of the client/server protocol and
Com_status variables. See Section 5.1.9, “Server Status Variables”
Background threads do not execute commands on behalf of clients, so this column may be
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.1, “Replication Threads”.
An action, event, or state that indicates what the thread is doing. For descriptions of
PROCESSLIST_STATEvalues, 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.
The statement the thread is executing, or
NULLif 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
CALLstatement executes a stored procedure that is executing a
PROCESSLIST_INFOvalue shows the
If this thread is a subthread (spawned by another thread), this is the
THREAD_IDvalue of the spawning thread. Thread spawning occurs, for example, to handle insertion of rows from
Whether events executed by the thread are instrumented. The value is
For foreground threads, the initial
INSTRUMENTEDvalue is determined by whether the user account associated with the thread matches any row in the
setup_actorstable. Matching is based on the values of the
If the thread spawns a subthread, matching occurs again for the
threadstable row created for the subthread.
For background threads,
setup_actorsis not consulted because there is no associated user for background threads.
For any thread, its
INSTRUMENTEDvalue can be changed during the lifetime of the thread. This is the only
threadstable column that can be modified.
For monitoring of events executed by the thread to occur, these things must be true: