The setup_instruments table lists the available instruments:

mysql> SELECT * FROM setup_instruments;
+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
...
| wait/synch/mutex/sql/LOCK_global_read_lock                 | YES     | YES   |
| wait/synch/mutex/sql/LOCK_global_system_variables          | YES     | YES   |
| wait/synch/mutex/sql/LOCK_lock_db                          | YES     | YES   |
| wait/synch/mutex/sql/LOCK_manager                          | YES     | YES   |
...
| wait/synch/rwlock/sql/LOCK_grant                           | YES     | YES   |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger                  | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_sys_init_connect                | YES     | YES   |
| wait/synch/rwlock/sql/LOCK_sys_init_slave                  | YES     | YES   |
...
| wait/io/file/sql/binlog                                    | YES     | YES   |
| wait/io/file/sql/binlog_index                              | YES     | YES   |
| wait/io/file/sql/casetest                                  | YES     | YES   |
| wait/io/file/sql/dbopt                                     | YES     | YES   |
...

To control whether an instrument is enabled, set its ENABLED column to YES or NO. To configure whether to collect timing information for an instrument, set its TIMED value to YES or NO. Setting the TIMED column affects Performance Schema table contents as described in Section 21.2.3.1, “Performance Schema Event Timing”.

The following examples demonstrate possible operations on the setup_instruments table. These changes, like other pre-filtering operations, affect all users.

Modifications to the setup_instruments table affect monitoring immediately.

The setup_objects table controls whether particular objects are monitored. Currently, the Performance Schema uses it to control monitoring of table objects. The initial setup_objects contents look like this:

mysql> SELECT * FROM setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+

The OBJECT_SCHEMA and OBJECT_NAME columns should contain a literal schema or table name, or '%' to match any name.

When the Performance Schema checks for a match in setup_objects, it tries to find more specific matches first. For example, with a table db1.t1, it looks for a match for 'db1' and 't1', then for 'db1' and '%', then for '%' and '%'. The order in which matching occurs matters because different setup_objects rows can have different ENABLED and TIMED values.

The effect of the default object configuration is to instrument all tables except those in the mysql, INFORMATION_SCHEMA, and performance_schema databases. Tables in the INFORMATION_SCHEMA database are not instrumented regardless of the contents of setup_objects; the row for information_schema.% simply makes this default explicit.

For table-related events, the Performance Schema combines the contents of setup_objects with setup_instruments to determine whether to enable instruments and whether to time enabled instruments:

Suppose that setup_objects contains the following rows:

+-------------+---------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+---------------+-------------+---------+-------+
| TABLE       | db1           | t1          | YES     | YES   |
| TABLE       | db1           | t2          | YES     | NO    |
| TABLE       | db2           | %           | YES     | YES   |
| TABLE       | db3           | %           | YES     | NO    |
| TABLE       | %             | %           | YES     | YES   |
+-------------+---------------+-------------+---------+-------+

If a table-related instrument in setup_instruments has a TIMED value of NO, no events for the instrument are timed. If the TIMED value is YES, event timing occurs as follows:

If a persistent table and a temporary table have the same name, matching against setup_objects rows occurs the same way for both. It is not possible to enable monitoring for one table but not the other. However, each table is instrumented separately.

Modifications to the setup_objects table affect object monitoring immediately.

The ENABLED column was added in MySQL 5.6.3. For earlier versions that have no ENABLED column, setup_objects is used only to enable monitoring for objects that match some row in the table. There is no way to explicitly disable instrumentation with the table.

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. For the Performance Schema to monitor a thread, these things must be true:

The INSTRUMENTED column in the threads table indicates the monitoring state for each thread. For foreground threads (resulting from client connections), the initial INSTRUMENTED value is determined by whether the user account associated with the thread matches any row in the setup_actors table. For background threads, INSTRUMENTED is YES 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 life of the thread.

The initial setup_actors contents look like this:

mysql> SELECT * FROM setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+

The Performance Schema uses the HOST and USER columns to match each new foreground thread. (ROLE is unused.) The INSTRUMENTED value for the thread becomes YES if any row matches. This enables instrumenting to be applied selectively per host, user, or combination of host and user.

The HOST and USER columns should contain a literal host or user name, or '%' to match any name. By default, monitoring is enabled for all new foreground threads because the setup_actors table initially contains a row with '%' for both HOST and USER. To perform more limited matching such as to enable monitoring only for some foreground threads, you must delete this row because it matches any connection.

Suppose that you modify setup_actors as follows:

DELETE FROM setup_actors;

Now setup_actors is empty and there are no rows that could match incoming connections. Consequently, the Performance Schema will set the INSTRUMENTED column for all new foreground threads to NO.

Suppose that you further modify setup_actors:

INSERT INTO setup_actors (HOST,USER,ROLE) VALUES('localhost','joe','%');
INSERT INTO setup_actors (HOST,USER,ROLE) VALUES('%','sam','%');

Now the Performance Schema determines how to set the INSTRUMENTED value for new connection threads as follows:

Modifications to the setup_actors table do not affect existing threads.

The setup_consumers table lists the available consumer types and which are enabled:

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

Modify the setup_consumers table to affect pre-filtering at the consumer stage and determine the destinations to which events are sent. To enable or disable a consumer, set its ENABLED value to YES or NO.

Modifications to the setup_consumers table affect monitoring immediately.

If you disable a consumer, the server does not spend time maintaining it. For example, you can disable history consumers if you do not care about historical event information:

mysql> UPDATE setup_consumers
    -> SET ENABLED = 'NO' WHERE NAME LIKE '%history%';

The consumer settings in the setup_consumers table form a hierarchy from higher levels to lower. The following principles apply:

The following lists describe the available consumer values. For discussion of several representative consumer configurations and their effect on instrumentation, see Section 21.2.3.2.1.5, “Example Consumer Configurations”.

Global and Thread Consumers

Statement Digest Consumer

This consumer requires global_instrumentation to be YES or it is not checked. There is no dependency on the Statement Event consumers. This means you can obtain statistics per digest without having to collect statistics in events_statements_current, which is advantageous in terms of overhead.

Wait Event Consumers

These consumers require both global_instrumentation and thread_instrumentation to be YES or they are not checked.

Stage Event Consumers

These consumers require both global_instrumentation and thread_instrumentation to be YES or they are not checked.

Statement Event Consumers

These consumers require both global_instrumentation and thread_instrumentation to be YES or they are not checked.

The consumer settings in the setup_consumers table form a hierarchy from higher levels to lower. The following discussion describes how consumers work, showing specific configurations and their effects as consumer settings are enabled progressively from high to low. The consumer values shown are representative. The general principles described here apply to other consumer values that may be available.

The configuration descriptions occur in order of increasing functionality and overhead. If you do not need the information provided by enabling lower-level settings, disable them and the Performance Schema will execute less code on your behalf and you will have less information to sift through.

Suppose that the setup_consumers table contains the following hierarchy of values:

global_instrumentation
  thread_instrumentation
    events_waits_current
      events_waits_history
      events_waits_history_long

If a given consumer setting is NO, the Performance Schema disables the instrumentation associated with the consumer and ignores all lower-level settings. If a given setting is YES, the Performance Schema enables the instrumentation associated with it and checks the settings at the next lowest level.

Each configuration description indicates which setup elements the Performance Schema checks and which output tables it maintains (that is, for which tables it collects information).

Server configuration state:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | NO      |
...
+---------------------------+---------+

In this configuration, nothing is instrumented.

Setup elements checked:

Output tables maintained:

Server configuration state:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | NO      |
...
+---------------------------+---------+

In this configuration, instrumentation is maintained only for global states. Per-thread instrumentation is disabled.

Additional setup elements checked, relative to the preceding configuration:

Additional output tables maintained, relative to the preceding configuration:

Server configuration state:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | YES     |
| events_waits_current      | NO      |
...
+---------------------------+---------+

In this configuration, instrumentation is maintained globally and per thread. No individual wait events are collected in the current-events or event-history tables.

Additional setup elements checked, relative to the preceding configuration:

Additional output tables maintained, relative to the preceding configuration:

Server configuration state:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | YES     |
| events_waits_current      | YES     |
| events_waits_history      | NO      |
| events_waits_history_long | NO      |
...
+---------------------------+---------+

In this configuration, instrumentation is maintained globally and per thread. Individual wait events are collected in the current-events table, but not in the event-history tables.

Additional setup elements checked, relative to the preceding configuration:

Additional output tables maintained, relative to the preceding configuration:

The preceding configuration collects no wait event history because the events_waits_history and events_waits_history_long consumers are disabled. Those consumers can be enabled separately or together to collect event history per thread, globally, or both.

This configuration collects event history per thread, but not globally:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | YES     |
| events_waits_current      | YES     |
| events_waits_history      | YES     |
| events_waits_history_long | NO      |
...
+---------------------------+---------+

Event-history tables maintained for this configuration:

This configuration collects event history globally, but not per thread:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | YES     |
| events_waits_current      | YES     |
| events_waits_history      | NO      |
| events_waits_history_long | YES     |
...
+---------------------------+---------+

Event-history tables maintained for this configuration:

This configuration collects event history per thread and globally:

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | YES     |
| events_waits_current      | YES     |
| events_waits_history      | YES     |
| events_waits_history_long | YES     |
...
+---------------------------+---------+

Event-history tables maintained for this configuration: