Events are processed in a producer/consumer fashion:
Instrumented code is the source for events and produces events to be collected. The
setup_instrumentstable lists the instruments for which events can be collected:mysql>
SELECT * FROM setup_instruments;+------------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +------------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/PAGE::lock | YES | YES | | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync | YES | YES | | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active | YES | YES | | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool | YES | YES | | wait/synch/mutex/sql/LOCK_des_key_file | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_prep_xids | YES | YES | | wait/synch/mutex/sql/Delayed_insert::mutex | YES | YES | ...Performance Schema tables are the destinations for events and consume events. The
setup_consumerstable lists the destination tables in which event information can be stored: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 | +----------------------------------------------+---------+
Pre-filtering refers to modifying Performance Schema configuration so that only certain types of events are collected or collected events are used to update only certain tables. This type of filtering is done by 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 by modifying the
setup_instruments or
setup_consumers table. An instrument or
consumer 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.
Post-filtering refers to the use of WHERE
clauses when selecting 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 pre-filtering include the following:
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 do not want to fill up 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 do not care about 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 aggregated event information, you can disable the summary table consumers to improve performance.
Example pre-filtering operations:
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/. Additional information about
specifying patterns to select instruments is given later in this
section.
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';
Changing which instruments are enabled does not flush the
history tables. Events already collected remain in the
current-events, history, and summary 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. You might want to
truncate the summary tables as well to discard aggregate
information for previously collected events.
Disable timing for all events:
mysql> UPDATE setup_instruments SET TIMED = 'NO';
Setting the TIMED column for instruments to
NO affects Performance Schema table contents
as described in Section 21.4, “Performance Schema Event Timing”.
If you disable a consumer, the server does not spend time maintaining it. For example, you can disable the summary table consumers if you do not care about aggregated event information:
mysql>UPDATE setup_consumers->SET ENABLED = 'NO' WHERE NAME LIKE '%summary%';
Pre-filtering limits which event information is collected and is
independent of any particular user. By contrast, post-filtering
is performed by individual users and is performed by use of
appropriate WHERE clauses that restrict what
event information to select from the information available after
pre-filtering has been applied.
Reasons to use post-filtering include the following:
To avoid making decisions for individual users about which event information is of interest.
To use Performance Schema to investigate a performance issue when the restrictions to impose using pre-filtering are not known in advance.
An example earlier in this section showed how to pre-filter for
file instruments. If the event tables contain both file and
nonfile information, post-filtering is another way to see
information only for file events. Add a WHERE
clause to queries to restrict event selection appropriately:
mysql>SELECT THREAD_ID, NUMBER_OF_BYTES->FROM events_waits_history->WHERE EVENT_NAME LIKE 'wait/io/file/%'->AND NUMBER_OF_BYTES IS NOT NULL;+-----------+-----------------+ | THREAD_ID | NUMBER_OF_BYTES | +-----------+-----------------+ | 11 | 66 | | 11 | 47 | | 11 | 139 | | 5 | 24 | | 5 | 834 | +-----------+-----------------+
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 '%summary%';
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/%';
If you use a pattern of '%/file/%', it 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 'mysql>pattern';SELECT NAME FROM setup_consumers WHERE NAME LIKE 'pattern';