MySQL 5.5 Reference Manual :: 21 MySQL Performance Schema :: 21.2 Performance Schema Configuration :: 21.2.3 Performance Schema Runtime Configuration :: 21.2.3.2 Performance Schema Event Filtering

« 21.2.3.1 Performance Schema Event Timing

21.2.3.3 Determining What Is Instrumented »

Section Navigation [Toggle]

21.2.3 Performance Schema Runtime Configuration
21.2.3.1 Performance Schema Event Timing
21.2.3.2 Performance Schema Event Filtering
- 21.2.3.2.1 Event Pre-Filtering
- 21.2.3.2.2 Naming Instruments or Consumers for Filtering Operations
21.2.3.3 Determining What Is Instrumented

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

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:

mysql> SELECT * FROM setup_consumers;
+----------------------------------------------+---------+
| NAME                                         | ENABLED |
+----------------------------------------------+---------+
| events_waits_current                         | YES     |
| events_waits_history                         | YES     |
| events_waits_history_long                    | YES     |
| events_waits_summary_by_thread_by_event_name | YES     |
| events_waits_summary_by_event_name           | YES     |
| events_waits_summary_by_instance             | YES     |
| file_summary_by_event_name                   | YES     |
| file_summary_by_instance                     | 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 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.

Pre-filtering can be applied to either the producer or consumer stage of event processing:

To affect pre-filtering at the producer stage, modify the setup_instruments table. An instrument can be enabled or disabled by setting its ENABLED value to YES or NO. An instrument can be configured whether to collect timing information by setting its TIMED value to YES or NO.
To affect pre-filtering at the consumer stage, modify the setup_consumers table. A consumer can be enabled or disabled by setting its ENABLED value to YES or NO.

Here are some examples that show the types of pre-filtering operations available:

Disable all instruments:
```
mysql> UPDATE setup_instruments SET ENABLED = 'NO';
```
Now no events will be collected. This change, like other pre-filtering operations, affects other users as well, even if they want to see event information.
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';

Setting the TIMED column for instruments affects Performance Schema table contents as described in Section 21.2.3.1, “Performance Schema Event Timing”.

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. The effect of TRUNCATE TABLE for summary tables is to reset the summary columns to 0 or NULL, not to remove rows.

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

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

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 = 'file_summary_by_instance';

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

Previous / Next / Up / Table of Contents

User Comments

Add your own comment.

Top / Previous / Next / Up / Table of Contents