MySQL :: MySQL 8.0 Reference Manual :: 29.12.21.8 The threads Table (original) (raw)

29.12.21.8 The threads Table

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring and historical event logging are 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: mysql
  PROCESSLIST_COMMAND: NULL
     PROCESSLIST_TIME: 418094
    PROCESSLIST_STATE: NULL
     PROCESSLIST_INFO: NULL
     PARENT_THREAD_ID: NULL
                 ROLE: NULL
         INSTRUMENTED: YES
              HISTORY: YES
      CONNECTION_TYPE: NULL
         THREAD_OS_ID: 5856
       RESOURCE_GROUP: SYS_default
     EXECUTION_ENGINE: PRIMARY
    CONTROLLED_MEMORY: 1456
MAX_CONTROLLED_MEMORY: 67480
         TOTAL_MEMORY: 1270430
     MAX_TOTAL_MEMORY: 1307317
     TELEMETRY_ACTIVE: NO
...

When the Performance Schema initializes, it populates thethreads table based on the threads in existence then. Thereafter, a new row is added each time the server creates a thread.

The INSTRUMENTED andHISTORY column values for new threads are determined by the contents of thesetup_actors table. For information about how to use thesetup_actors table to control these columns, seeSection 29.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 andHISTORY values for the new thread may be different from those of the original thread: Thesetup_actors table may have changed in the meantime, and if theINSTRUMENTED or HISTORY value for the original thread was changed after the row was initialized, the 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) and historical event logging. To control the initialINSTRUMENTED and HISTORY values for new foreground threads, use thesetup_actors table. To control these aspects of existing threads, set theINSTRUMENTED and HISTORY columns of threads table rows. (For more information about the conditions under which thread monitoring and historical event logging occur, see the descriptions of the INSTRUMENTED andHISTORY columns.)

For a comparison of the threads table columns with names having a prefix ofPROCESSLIST_ to other process information sources, see Sources of Process Information.

Important

For thread information sources other than thethreads table, information about threads for other users is shown only if the current user has the PROCESS 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 the threads table should not be given theSELECT privilege for it.

The threads table has these columns:

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: 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 threads table has these indexes:

TRUNCATE TABLE is not permitted for the threads table.