threads table contains a row
for each server thread. Each row contains information about a
thread and indicates whether monitoring is enabled for it:
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: 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: paul PROCESSLIST_HOST: localhost PROCESSLIST_DB: performance_schema PROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 0 PROCESSLIST_STATE: Sending data PROCESSLIST_INFO: SELECT * FROM threads PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES ...
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
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 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.
threads table columns with
names having a prefix of
provide information similar to that available from the
table or the
statement. Thus, all three sources provide thread-monitoring
information. Use of
differs from use of the other two sources in these ways:
threads does not
require a mutex and has minimal impact on server
SHOW PROCESSLIST have
negative performance consequences because they require a
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 monitoring of existing threads, set the
INSTRUMENTED column of the
threads table. To control the
INSTRUMENTED value for new
foreground threads, use the
setup_actors table. (For more
information about the conditions under which thread
monitoring occurs, see the description of the
information about threads for other users is shown only if
the current user has the
PROCESS privilege. That is
not true of the
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.
threads table has these
A unique thread identifier.
The name associated with the thread instrumentation code
in the server. For example,
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.
The thread type, either
BACKGROUND. User connection threads are
foreground threads. Threads associated with internal
server activity are background threads. Examples are
InnoDB threads, “binlog
dump” threads sending information to slaves, and
slave I/O and SQL threads.
For threads that are displayed in the
table, this is the
value, which is also the value that
return within that thread. For background threads (threads
not associated with a user connection),
NULL, so the values are not unique.
(Before MySQL 5.6.9, the value is 0 for background
The user associated with a foreground thread,
NULL for a background thread.
The host name of the client associated with a foreground
NULL for a background thread.
The default database for the thread, or
NULL if there is none.
The type of command the thread is executing. For
descriptions of thread commands, see
Section 8.12.5, “Examining Thread Information”. The value of this
column corresponds to the
commands of the client/server protocol and
status variables. See
Section 5.1.6, “Server Status Variables”
The time in seconds that the thread has been in its current 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 bears investigation.
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
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 from
Whether the thread is instrumented. This does not affect
threads table row for the
thread, it affects whether events executed by the thread
For foreground threads, the initial
INSTRUMENTED value is determined by
whether the user account associated with the thread
matches any row in the
Matching is based on the values of the
If the thread spawns a subthread, matching occurs again for the subthread.
For background threads,
YES by default.
setup_actors is not
consulted because there is no associated user for
For any thread, its
value can be changed during the lifetime of the
thread. This is the only
threads table column that
can be modified.
For monitoring of events executed by the thread to occur, these things must be true: