MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0 The setup_instruments Table

The setup_instruments table lists classes of instrumented objects for which events can be collected:

mysql> SELECT * FROM performance_schema.setup_instruments\G
*************************** 1. row ***************************
         NAME: wait/synch/mutex/pfs/LOCK_pfs_share_list
        TIMED: NO
   PROPERTIES: singleton
        FLAGS: NULL
DOCUMENTATION: Components can provide their own performance_schema tables. 
This lock protects the list of such tables definitions.
*************************** 410. row ***************************
         NAME: stage/sql/executing
        TIMED: NO
        FLAGS: NULL
*************************** 733. row ***************************
         NAME: statement/abstract/Query
        TIMED: YES
   PROPERTIES: mutable
        FLAGS: NULL
DOCUMENTATION: SQL query just received from the network. 
At this point, the real statement type is unknown, the type 
will be refined after SQL parsing.
*************************** 737. row ***************************
         NAME: memory/performance_schema/mutex_instances
        TIMED: NULL
   PROPERTIES: global_statistics
DOCUMENTATION: Memory used for table performance_schema.mutex_instances
*************************** 823. row ***************************
         NAME: memory/sql/Prepared_statement::infrastructure
        TIMED: NULL
   PROPERTIES: controlled_by_default
        FLAGS: controlled
DOCUMENTATION: Map infrastructure for prepared statements per session.

Each instrument added to the source code provides a row for the setup_instruments table, even when the instrumented code is not executed. When an instrument is enabled and executed, instrumented instances are created, which are visible in the xxx_instances tables, such as file_instances or rwlock_instances.

Modifications to most setup_instruments rows affect monitoring immediately. For some instruments, modifications are effective only at server startup; changing them at runtime has no effect. This affects primarily mutexes, conditions, and rwlocks in the server, although there may be other instruments for which this is true.

For more information about the role of the setup_instruments table in event filtering, see Section 27.4.3, “Event Pre-Filtering”.

The setup_instruments table has these columns:

The setup_instruments table has these indexes:

TRUNCATE TABLE is not permitted for the setup_instruments table.

As of MySQL 8.0.27, to assist monitoring and troubleshooting, the Performance Schema instrumentation is used to export names of instrumented threads to the operating system. This enables utilities that display thread names, such as debuggers and the Unix ps command, to display distinct mysqld thread names rather than mysqld. This feature is supported only on Linux, macOS, and Windows.

Suppose that mysqld is running on a system that has a version of ps that supports this invocation syntax:

ps -C mysqld H -o "pid tid cmd comm"

Without export of thread names to the operating system, the command displays output like this, where most COMMAND values are mysqld:

  PID   TID CMD                         COMMAND
 1377  1377 /usr/sbin/mysqld            mysqld
 1377  1528 /usr/sbin/mysqld            mysqld
 1377  1529 /usr/sbin/mysqld            mysqld
 1377  1530 /usr/sbin/mysqld            mysqld
 1377  1531 /usr/sbin/mysqld            mysqld
 1377  1534 /usr/sbin/mysqld            mysqld
 1377  1535 /usr/sbin/mysqld            mysqld
 1377  1588 /usr/sbin/mysqld            xpl_worker1
 1377  1589 /usr/sbin/mysqld            xpl_worker0
 1377  1590 /usr/sbin/mysqld            mysqld
 1377  1594 /usr/sbin/mysqld            mysqld
 1377  1595 /usr/sbin/mysqld            mysqld

With export of thread names to the operating system, the output looks like this, with threads having a name similar to their instrument name:

  PID   TID CMD                         COMMAND
27668 27668 /usr/sbin/mysqld            mysqld
27668 27671 /usr/sbin/mysqld            ib_io_ibuf
27668 27672 /usr/sbin/mysqld            ib_io_log
27668 27673 /usr/sbin/mysqld            ib_io_rd-1
27668 27674 /usr/sbin/mysqld            ib_io_rd-2
27668 27677 /usr/sbin/mysqld            ib_io_wr-1
27668 27678 /usr/sbin/mysqld            ib_io_wr-2
27668 27699 /usr/sbin/mysqld            xpl_worker-2
27668 27700 /usr/sbin/mysqld            xpl_accept-1
27668 27710 /usr/sbin/mysqld            evt_sched
27668 27711 /usr/sbin/mysqld            sig_handler
27668 27933 /usr/sbin/mysqld            connection

Different thread instances within the same class are numbered to provide distinct names where that is feasible. Due to constraints on name lengths with respect to potentially large numbers of connections, connections are named simply connection.