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 5.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.
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:
Access to
threads
does not require a mutex and has minimal impact on server performance.INFORMATION_SCHEMA.PROCESSLIST
andSHOW 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 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 thesetup_actors
table. To control monitoring of existing threads, set theINSTRUMENTED
column ofthreads
table rows. (For more information about the conditions under which thread monitoring occurs, see the description of theINSTRUMENTED
column.)
For these reasons, DBAs who perform server monitoring using
INFORMATION_SCHEMA.PROCESSLIST
or
SHOW PROCESSLIST
may wish to
monitor using the threads
table
instead.
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:
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 slaves, and slave I/O and SQL threads.PROCESSLIST_ID
For threads that are displayed in the
INFORMATION_SCHEMA.PROCESSLIST
table, this is the same value displayed in theID
column of that table. It is also the value displayed in theId
column ofSHOW PROCESSLIST
output, and the value thatCONNECTION_ID()
would return within that thread.For background threads (threads 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 there is none.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 Examining Thread Information. The value of this column corresponds to theCOM_
commands of the client/server protocol andxxx
Com_
status variables. See Server Status Variablesxxx
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.
PROCESSLIST_STATE
An action, event, or state that indicates what the thread is doing. For descriptions of
PROCESSLIST_STATE
values, see Examining Thread 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 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 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.