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.
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:
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, andthread/sql/main
stands for themain()
function of the server.TYPE
The thread type, either
FOREGROUND
orBACKGROUND
. User connection threads are foreground threads. Threads associated with internal server activity are background threads. Examples are internalInnoDB
threads, “binlog dump” threads sending information to replicas, and replica 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 theINFORMATION_SCHEMA
PROCESSLIST
table, displayed in theId
column ofSHOW PROCESSLIST
output, and returned by theCONNECTION_ID()
function within the thread.For a background thread (not associated with a user connection),
PROCESSLIST_ID
isNULL
, 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 theINFORMATION_SCHEMA
PROCESSLIST
table or theHost
column ofSHOW PROCESSLIST
output, thePROCESSLIST_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 thesocket_instances
table: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 ...
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 theCOM_
commands of the client/server protocol andxxx
Com_
status variables. See Section 5.1.9, “Server Status Variables”xxx
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.1, “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 ifNULL
, 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 aCALL
statement executes a stored procedure that is executing aSELECT
statement, thePROCESSLIST_INFO
value shows theSELECT
statement.PARENT_THREAD_ID
If this thread is a subthread (spawned by another thread), this is the
THREAD_ID
value of the spawning thread. Thread spawning occurs, for example, to handle insertion of rows fromINSERT DELAYED
statements.ROLE
Unused.
INSTRUMENTED
Whether events executed by the thread are instrumented. The value is
YES
orNO
.For foreground threads, the initial
INSTRUMENTED
value is determined by whether the user account associated with the thread matches any row in thesetup_actors
table. Matching is based on the values of thePROCESSLIST_USER
andPROCESSLIST_HOST
columns.If the thread spawns a subthread, matching occurs again for the
threads
table row created for the subthread.For background threads,
INSTRUMENTED
isYES
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 onlythreads
table column that can be modified.
For monitoring of events executed by the thread to occur, these things must be true:
The
thread_instrumentation
consumer in thesetup_consumers
table must beYES
.The
threads.INSTRUMENTED
column must beYES
.Monitoring occurs only for those thread events produced from instruments that have the
ENABLED
column set toYES
in thesetup_instruments
table.
TRUNCATE TABLE
is not permitted
for the threads
table.