21.2.3.2. Performance Schema Event Filtering

Events are processed in a producer/consumer fashion:

Instrumented code is the source for events and produces events to be collected. The setup_instruments table lists the instruments for which events can be collected, whether they are enabled, and whether to collect timing information:

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   |...

The setup_instruments table provides the most basic form of control over event production. To further refine event production based on the type of object or thread being monitored, other tables may be used as described in Section 21.2.3.2.1, "Event Pre-Filtering".

Performance Schema tables are the destinations for events and consume events. The setup_consumers table lists the types of consumers to which event information can be sent 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     |+--------------------------------+---------+

Filtering can be done at different stages of performance monitoring:

Pre-filtering. This is done by modifying Performance Schema configuration so that only certain types of events are collected from producers, and collected events update only certain consumers. This type of filtering is done by the Performance Schema and has a global effect that applies to all users.

Reasons to use pre-filtering:
- Pre-filtering reduces overhead. The overhead should be minimal even with all instruments enabled, but perhaps you want to reduce it further. Or you do not care about timing events and want to disable the timing code to eliminate timing overhead.
- You can avoid filling the current-events or history tables with events in which you have no interest. Pre-filtering leaves more "room" in these tables for instances of rows for enabled instrument types. If you enable only file instruments with pre-filtering, no rows are collected for nonfile instruments. With post-filtering, nonfile events are collected, leaving fewer rows for file events.
- You can avoid maintaining some kinds of event tables. If you disable a consumer, the server does not spend time maintaining it. For example, if you do not care about event histories, you can disable the history table consumers to improve performance.
Post-filtering. This involves the use of WHERE clauses in queries that select information from Performance Schema tables, to specify which of the available events you want to see. This type of filtering is performed on a per-user basis because individual users select which of the available events are of interest.

Reasons to use post-filtering:
- To avoid making decisions for individual users about which event information is of interest.
- To use the Performance Schema to investigate a performance issue when the restrictions to impose using pre-filtering are not known in advance.

The following sections provide more detail about pre-filtering and provide guidelines for naming instruments or consumers in filtering operations. For information about writing queries to retrieve information (post-filtering), see Section 21.3, "Performance Schema Queries".

21.2.3.2.1. Event Pre-Filtering

Pre-filtering is done by the Performance Schema and has a global effect that applies to all users. Pre-filtering can be applied to either the producer or consumer stage of event processing:

To configure pre-filtering at the producer stage, several tables can be used:
- The setup_instruments table indicates which instruments are available. An instrument disabled in this table produces no events regardless of the contents of the other production-related setup tables. An instrument enabled in this table is permitted to produce events, subject to the contents of the other tables.
- The setup_objects table determines whether particular objects are monitored. Currently, the Performance Schema uses it to control monitoring of table objects.
- The threads table indicates whether monitoring is enabled for each server thread.
- The setup_actors table determines the initial monitoring state for new foreground threads.
To configure pre-filtering at the consumer stage, modify the setup_consumers table. This determines the destinations to which events are sent.

The setup_consumers table also implicitly affects event production. If a given event will not be sent to any destination (that is, will not be consumed), the Performance Schema does not produce it.

Modifications to any of these tables affect monitoring immediately, with the exception of setup_actors. Modifications to setup_actors affect only foreground threads created thereafter.

When you change the monitoring configuration, the Performance Schema does not flush the history tables. Events already collected remain in the current-events and history tables until displaced by newer events. If you disable instruments, you might need to wait a while before events for them are displaced by newer events of interest. Alternatively, use TRUNCATE TABLE to empty the history tables.

After making instrumentation changes, you might want to truncate the summary tables as well to clear aggregate information for previously collected events. Except for events_statements_summary_by_digest, the effect of TRUNCATE TABLE for summary tables is to reset the summary columns to 0 or NULL, not to remove rows.

The following sections describe how to use specific tables to control how the Performance Schema performs pre-filtering.

21.2.3.2.1.1. Pre-Filtering by Instrument

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.

Disable all instruments:

mysql> UPDATE setup_instruments
                            SET ENABLED = 'NO';

Now no events will be collected.

Disable all file instruments, adding them to the current set of disabled instruments:

mysql> UPDATE setup_instruments
                            SET ENABLED = 'NO'    -> WHERE NAME LIKE
                            'wait/io/file/%';

Disable only file instruments, enable all other instruments:
```
mysql> UPDATE
                            setup_instruments    -> SET ENABLED =
                            IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');
```
The preceding queries use the LIKE operator and the pattern 'wait/io/file/%' to match all instrument names that begin with 'wait/io/file/. For additional information about specifying patterns to select instruments, see Section 21.2.3.2.2, "Naming Instruments or Consumers for Filtering Operations".

Enable all but those instruments in the mysys library:

mysql> UPDATE
                            setup_instruments    -> SET ENABLED =
                            CASE WHEN NAME LIKE '%/mysys/%' THEN 'YES' ELSE 'NO' END;

Disable a specific instrument:

mysql> UPDATE setup_instruments
                            SET ENABLED = 'NO'    -> WHERE NAME =
                            'wait/synch/mutex/mysys/TMPDIR_mutex';

To toggle the state of an instrument, "flip" its ENABLED value:

mysql> UPDATE
                            setup_instruments    -> SET ENABLED =
                            IF(ENABLED = 'YES', 'NO', 'YES')    -> WHERE
                            NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';

Disable timing for all events:

mysql> UPDATE setup_instruments
                            SET TIMED = 'NO';

Modifications to the setup_instruments table affect monitoring immediately.

21.2.3.2.1.2. Pre-Filtering by Object

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:

For tables that match a row in setup_objects, table instruments produce events only if they are enabled in both setup_instruments and setup_objects.
The TIMED values in the two tables are combined, so that timing information is collected only with both values are YES.

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:

db1.t1 events are timed
db1.t2 events are not timed
db2.t3 events are timed
db3.t4 events are not timed
db4.t5 events are timed

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.

21.2.3.2.1.3. Pre-Filtering by Thread

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 thread_instrumentation consumer in the setup_consumers table must be YES.
The thread.INSTRUMENTED column must be YES.
Monitoring occurs only for those thread events produced from instruments that are enabled, as specified in the setup_instruments table.

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:

If joe connects from the local host, the connection matches the first inserted row.
If joe connects from any other host, there is no match.
If sam connects from any host, the connection matches the second inserted row.
For any other connection, there is no match.

Modifications to the setup_actors table do not affect existing threads.

21.2.3.2.1.4. Pre-Filtering by Consumer

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:

Destinations associated with a consumer receive no events unless the Performance Schema checks the consumer and the consumer is enabled.
A consumer is checked only if all consumers that it depends on (if any) are enabled.
If a consumer is not checked, or is checked but is disabled, other consumers that depend on it are not checked.
Dependent consumers may have their own dependent consumers.
If an event would not be sent to any destination, the Performance Schema does not produce it.

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

global_instrumentation is the highest level consumer. If global_instrumentation is NO, it disables global instrumentation. All other settings are lower level and are not checked; it does not matter what they are set to. No global or per thread information is maintained and no individual events are collected in the current-events or event-history tables. If global_instrumentation is YES, the Performance Schema maintains information for global states and also checks the thread_instrumentation consumer.
thread_instrumentation is checked only if global_instrumentation is YES. Otherwise, if thread_instrumentation is NO, it disables thread-specific instrumentation and all lower-level settings are ignored. No information is maintained per thread and no individual events are collected in the current-events or event-history tables. If thread_instrumentation is YES, the Performance Schema maintains thread-specific information and also checks events_xxx_current 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.

events_waits_current, if NO, disables collection of individual wait events in the events_waits_current table. If YES, it enables wait event collection and the Performance Schema checks the events_waits_history and events_waits_history_long consumers.
events_waits_history is not checked if event_waits_current is NO. Otherwise, a events_waits_history value of NO or YES disables or enables collection of wait events in the events_waits_history table.
events_waits_history_long is not checked if event_waits_current is NO. Otherwise, a events_waits_history_long value of NO or YES disables or enables collection of wait events in the events_waits_history_long table.

Stage Event Consumers

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

events_stages_current, if NO, disables collection of individual stage events in the events_stages_current table. If YES, it enables stage event collection and the Performance Schema checks the events_stages_history and events_stages_history_long consumers.
events_stages_history is not checked if event_stages_current is NO. Otherwise, a events_stages_history value of NO or YES disables or enables collection of stage events in the events_stages_history table.
events_stages_history_long is not checked if event_stages_current is NO. Otherwise, a events_stages_history_long value of NO or YES disables or enables collection of stage events in the events_stages_history_long table.

Statement Event Consumers

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

events_statements_current, if NO, disables collection of individual statement events in the events_statements_current table. If YES, it enables statement event collection and the Performance Schema checks the events_statements_history and events_statements_history_long consumers.
events_statements_history is not checked if events_statements_current is NO. Otherwise, a events_statements_history value of NO or YES disables or enables collection of statement events in the events_statements_history table.
events_statements_history_long is not checked if events_statements_current is NO. Otherwise, a events_statements_history_long value of NO or YES disables or enables collection of statement events in the events_statements_history_long table.

21.2.3.2.1.5. Example Consumer Configurations

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

No Instrumentation

Server configuration state:

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

In this configuration, nothing is instrumented.

Setup elements checked:

Table setup_consumers, consumer global_instrumentation

Output tables maintained:

None

Global Instrumentation Only

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:

Table setup_consumers, consumer thread_instrumentation
Table setup_instruments
Table setup_objects
Table setup_timers

Additional output tables maintained, relative to the preceding configuration:

mutex_instances
rwlock_instances
cond_instances
file_instances
file_summary_by_instance
file_summary_by_event_name
objects_summary_global_by_type
table_lock_waits_summary_by_table
table_io_waits_summary_by_index_usage
table_io_waits_summary_by_table
events_waits_summary_by_instance
events_waits_summary_global_by_event_name

Global and Thread Instrumentation Only

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:

Table setup_consumers, consumer events_waits_current
Table setup_actors
Column threads.instrumented

Additional output tables maintained, relative to the preceding configuration:

events_waits_summary_by_thread_by_event_name

Global, Thread, and Current-Event Instrumentation

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:

Consumer events_waits_history
Consumer events_waits_history_long

Additional output tables maintained, relative to the preceding configuration:

events_waits_current

Global, Thread, Current-Event, and Event-History instrumentation

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:

events_waits_history

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:

events_waits_history_long

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:

21.2.3.2.2. Naming Instruments or Consumers for Filtering Operations

Names given for filtering operations can be as specific or general as required. To indicate a single instrument or consumer, specify its name in full:

mysql> UPDATE setup_instruments    -> SET ENABLED = 'NO'    -> WHERE
            NAME = 'wait/synch/mutex/myisammrg/MYRG_INFO::mutex';mysql> UPDATE setup_consumers    -> SET
            ENABLED = 'NO' WHERE NAME = 'events_waits_current';

To specify a group of instruments or consumers, use a pattern that matches the group members:

mysql> UPDATE setup_instruments    -> SET ENABLED = 'NO'    -> WHERE
            NAME LIKE 'wait/synch/mutex/%';mysql> UPDATE
            setup_consumers    -> SET ENABLED = 'NO' WHERE NAME LIKE
            '%history%';

If you use a pattern, it should be chosen so that it matches all the items of interest and no others. For example, to select all file I/O instruments, it is better to use a pattern that includes the entire instrument name prefix:

... WHERE NAME LIKE 'wait/io/file/%';

A pattern of '%/file/%' will match other instruments that have a component of '/file/' anywhere in the name. Even less suitable is the pattern '%file%' because it will match instruments with 'file' anywhere in the name, such as wait/synch/mutex/sql/LOCK_des_key_file.

To check which instrument or consumer names a pattern matches, perform a simple test:

mysql> SELECT NAME FROM setup_instruments WHERE
            NAME LIKE 'pattern';mysql> SELECT NAME FROM setup_consumers WHERE NAME LIKE 'pattern';

For information about the types of names that are supported, see Section 21.4, "Performance Schema Instrument Naming Conventions".