Movatterモバイル変換

For thread information sources other than thethreads table, information about threads for other users is shown only if the current user has thePROCESS privilege. That is not true of thethreads table; all rows are shown to any user who has theSELECT privilege for the table. Users who should not be able to see threads for other users by accessing thethreads table should not be given theSELECT privilege for it.

• 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, eitherFOREGROUND 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 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 theID column of theINFORMATION_SCHEMAPROCESSLIST 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 theHOST column of theINFORMATION_SCHEMAPROCESSLIST 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 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: 0mysql> SELECT * FROM performance_schema.socket_instances\G*************************** 1. row ***************************           EVENT_NAME: wait/io/socket/sql/client_connectionOBJECT_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, orNULL if none has been selected.

• PROCESSLIST_COMMAND

  For foreground threads, the type of command the thread is executing on behalf of the client, orSleep if the session is idle. For descriptions of thread commands, seeSection 10.14, “Examining Server Thread (Process) Information”. The value of this column corresponds to theCOM_xxx commands of the client/server protocol andCom_xxx status variables. SeeSection 7.1.10, “Server Status Variables”

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

• 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. SeeSection 19.2.3, “Replication Threads”.

• PROCESSLIST_STATE

  An action, event, or state that indicates what the thread is doing. For descriptions ofPROCESSLIST_STATE values, seeSection 10.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, orNULL 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 theTHREAD_ID value of the spawning thread.

• ROLE

  Unused.

• INSTRUMENTED

  Whether events executed by the thread are instrumented. The value isYES orNO.

  • For foreground threads, the initialINSTRUMENTED 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 thethreads 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, itsINSTRUMENTED value can be changed during the lifetime of the thread.

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

  • Thethread_instrumentation consumer in thesetup_consumers table must beYES.

  • Thethreads.INSTRUMENTED column must beYES.

  • Monitoring occurs only for those thread events produced from instruments that have theENABLED column set toYES in thesetup_instruments table.

• HISTORY

  Whether to log historical events for the thread. The value isYES orNO.

  • For foreground threads, the initialHISTORY 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 thethreads table row created for the subthread.

  • For background threads,HISTORY isYES by default.setup_actors is not consulted because there is no associated user for background threads.

  • For any thread, itsHISTORY value can be changed during the lifetime of the thread.

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

  • The appropriate history-related consumers in thesetup_consumers table must be enabled. For example, wait event logging in theevents_waits_history andevents_waits_history_long tables requires the correspondingevents_waits_history andevents_waits_history_long consumers to beYES.

  • Thethreads.HISTORY column must beYES.

  • Logging occurs only for those thread events produced from instruments that have theENABLED column set toYES in thesetup_instruments table.

• CONNECTION_TYPE

  The protocol used to establish the connection, orNULL for background threads. Permitted values areTCP/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), andShared Memory (Windows shared memory connection).

• THREAD_OS_ID

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

  • When a MySQL thread is associated with the same operating system thread for its lifetime,THREAD_OS_ID contains the operating system thread ID.

  • When a MySQL thread is not associated with the same operating system thread for its lifetime,THREAD_OS_ID containsNULL. This is typical for user sessions when the thread pool plugin is used (seeSection 7.6.3, “MySQL Enterprise Thread Pool”).

  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 thegettid() function. This value is exposed, for example, using theperf orps -L commands, or in theproc file system (/proc/[pid]/task/[tid]). For more information, see theperf-stat(1),ps(1), andproc(5) man pages.

• RESOURCE_GROUP

  The resource group label. This value isNULL if resource groups are not supported on the current platform or server configuration (seeResource Group Restrictions).

• EXECUTION_ENGINE

  The query execution engine. The value is eitherPRIMARY orSECONDARY. For use with MySQL HeatWave Service and MySQL HeatWave, where thePRIMARY engine isInnoDB and theSECONDARY engine is MySQL HeatWave (RAPID). For MySQL Community Edition Server, MySQL Enterprise Edition Server (on-premise), and MySQL HeatWave Service without MySQL HeatWave, the value is alwaysPRIMARY.

• CONTROLLED_MEMORY

  Amount of controlled memory used by the thread.

• MAX_CONTROLLED_MEMORY

  Maximum value ofCONTROLLED_MEMORY seen during the thread execution.

• TOTAL_MEMORY

  The current amount of memory, controlled or not, used by the thread.

• MAX_TOTAL_MEMORY

  The maximum value ofTOTAL_MEMORY seen during the thread execution.

• TELEMETRY_ACTIVE

  Whether the thread has an active telemetry seesion attached. The value isYES orNO.

• 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)