Menu:
performance-schema-instrumentation-checking:: Determining What Is Instrumented
Specific Performance Schema features can be enabled at runtime to control which types of event collection occur.
Performance Schema setup tables contain information about monitoring configuration:
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'performance_schema'
AND TABLE_NAME LIKE 'setup%';
+-------------------+
| TABLE_NAME |
+-------------------+
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_timers |
+-------------------+You can examine the contents of these tables to obtain information about Performance Schema monitoring characteristics. If you have the 'UPDATE' privilege, you can change Performance Schema operation by modifying setup tables to affect how monitoring occurs. For additional details about these tables, see *note performance-schema-setup-tables::.
To see which event timers are selected, query the *note 'setup_timers': performance-schema-setup-timers-table. tables:
mysql> SELECT * FROM performance_schema.setup_timers;
+-------------+-------------+
| NAME | TIMER_NAME |
+-------------+-------------+
| idle | MICROSECOND |
| wait | CYCLE |
| stage | NANOSECOND |
| statement | NANOSECOND |
| transaction | NANOSECOND |
+-------------+-------------+The 'NAME' value indicates the type of instrument to which the timer applies, and 'TIMER_NAME' indicates which timer applies to those instruments. The timer applies to instruments where their name begins with an element matching the 'NAME' value.
To change the timer, update the 'NAME' value. For example, to use the 'NANOSECOND' timer for the 'wait' timer:
mysql> UPDATE performance_schema.setup_timers
SET TIMER_NAME = 'NANOSECOND'
WHERE NAME = 'wait';
mysql> SELECT * FROM performance_schema.setup_timers;
+-------------+-------------+
| NAME | TIMER_NAME |
+-------------+-------------+
| idle | MICROSECOND |
| wait | NANOSECOND |
| stage | NANOSECOND |
| statement | NANOSECOND |
| transaction | NANOSECOND |
+-------------+-------------+For discussion of timers, see *note performance-schema-timing::.
The note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_consumers': performance-schema-setup-consumers-table. tables list the instruments for which events can be collected and the types of consumers for which event information actually is collected, respectively. Other setup tables enable further modification of the monitoring configuration. *note performance-schema-filtering::, discusses how you can modify these tables to affect event collection.
If there are Performance Schema configuration changes that must be made at runtime using SQL statements and you would like these changes to take effect each time the server starts, put the statements in a file and start the server with the 'init_file' system variable set to name the file. This strategy can also be useful if you have multiple monitoring configurations, each tailored to produce a different kind of monitoring, such as casual server health monitoring, incident investigation, application behavior troubleshooting, and so forth. Put the statements for each monitoring configuration into their own file and specify the appropriate file as the 'init_file' value when you start the server.
File: manual.info.tmp, Node: performance-schema-timing, Next: performance-schema-filtering, Prev: performance-schema-runtime-configuration, Up: performance-schema-runtime-configuration
Events are collected by means of instrumentation added to the server source code. Instruments time events, which is how the Performance Schema provides an idea of how long events take. It is also possible to configure instruments not to collect timing information. This section discusses the available timers and their characteristics, and how timing values are represented in events.
Performance Schema Timers
Two Performance Schema tables provide timer information:
*note 'performance_timers': performance-schema-performance-timers-table. lists the available timers and their characteristics.
*note 'setup_timers': performance-schema-setup-timers-table. indicates which timers are used for which instruments.
Each timer row in note 'setup_timers': performance-schema-setup-timers-table. must refer to one of the timers listed in note 'performance_timers': performance-schema-performance-timers-table.
Timers vary in precision and amount of overhead. To see what timers are available and their characteristics, check the *note 'performance_timers': performance-schema-performance-timers-table. table:
mysql> SELECT * FROM performance_schema.performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE | 2389029850 | 1 | 72 |
| NANOSECOND | 1000000000 | 1 | 112 |
| MICROSECOND | 1000000 | 1 | 136 |
| MILLISECOND | 1036 | 1 | 168 |
| TICK | 105 | 1 | 2416 |
+-------------+-----------------+------------------+----------------+If the values associated with a given timer name are 'NULL', that timer is not supported on your platform. The rows that do not contain 'NULL' indicate which timers you can use in *note 'setup_timers': performance-schema-setup-timers-table.
The columns have these meanings:
The 'TIMER_NAME' column shows the names of the available timers. 'CYCLE' refers to the timer that is based on the CPU (processor) cycle counter. The timers in *note 'setup_timers': performance-schema-setup-timers-table. that you can use are those that do not have 'NULL' in the other columns. If the values associated with a given timer name are 'NULL', that timer is not supported on your platform.
'TIMER_FREQUENCY' indicates the number of timer units per second. For a cycle timer, the frequency is generally related to the CPU speed. The value shown was obtained on a system with a 2.4GHz processor. The other timers are based on fixed fractions of seconds. For 'TICK', the frequency may vary by platform (for example, some use 100 ticks/second, others 1000 ticks/second).
'TIMER_RESOLUTION' indicates the number of timer units by which timer values increase at a time. If a timer has a resolution of 10, its value increases by 10 each time.
'TIMER_OVERHEAD' is the minimal number of cycles of overhead to obtain one timing with the given timer. The overhead per event is twice the value displayed because the timer is invoked at the beginning and end of the event.
To see which timers are in effect or to change timers, access the *note 'setup_timers': performance-schema-setup-timers-table. table:
mysql> SELECT * FROM performance_schema.setup_timers;
+-------------+-------------+
| NAME | TIMER_NAME |
+-------------+-------------+
| idle | MICROSECOND |
| wait | CYCLE |
| stage | NANOSECOND |
| statement | NANOSECOND |
| transaction | NANOSECOND |
+-------------+-------------+
mysql> UPDATE performance_schema.setup_timers
SET TIMER_NAME = 'MICROSECOND'
WHERE NAME = 'idle';
mysql> SELECT * FROM performance_schema.setup_timers;
+-------------+-------------+
| NAME | TIMER_NAME |
+-------------+-------------+
| idle | MICROSECOND |
| wait | CYCLE |
| stage | NANOSECOND |
| statement | NANOSECOND |
| transaction | NANOSECOND |
+-------------+-------------+By default, the Performance Schema uses the best timer available for each instrument type, but you can select a different one.
To time wait events, the most important criterion is to reduce overhead, at the possible expense of the timer accuracy, so using the 'CYCLE' timer is the best.
The time a statement (or stage) takes to execute is in general orders of magnitude larger than the time it takes to execute a single wait. To time statements, the most important criterion is to have an accurate measure, which is not affected by changes in processor frequency, so using a timer which is not based on cycles is the best. The default timer for statements is 'NANOSECOND'. The extra 'overhead' compared to the 'CYCLE' timer is not significant, because the overhead caused by calling a timer twice (once when the statement starts, once when it ends) is orders of magnitude less compared to the CPU time used to execute the statement itself. Using the 'CYCLE' timer has no benefit here, only drawbacks.
The precision offered by the cycle counter depends on processor speed. If the processor runs at 1 GHz (one billion cycles/second) or higher, the cycle counter delivers sub-nanosecond precision. Using the cycle counter is much cheaper than getting the actual time of day. For example, the standard 'gettimeofday()' function can take hundreds of cycles, which is an unacceptable overhead for data gathering that may occur thousands or millions of times per second.
Cycle counters also have disadvantages:
End users expect to see timings in wall-clock units, such as fractions of a second. Converting from cycles to fractions of seconds can be expensive. For this reason, the conversion is a quick and fairly rough multiplication operation.
Processor cycle rate might change, such as when a laptop goes into power-saving mode or when a CPU slows down to reduce heat generation. If a processor's cycle rate fluctuates, conversion from cycles to real-time units is subject to error.
Cycle counters might be unreliable or unavailable depending on the processor or the operating system. For example, on Pentiums, the instruction is 'RDTSC' (an assembly-language rather than a C instruction) and it is theoretically possible for the operating system to prevent user-mode programs from using it.
Some processor details related to out-of-order execution or multiprocessor synchronization might cause the counter to seem fast or slow by up to 1000 cycles.
MySQL works with cycle counters on x386 (Windows, macOS, Linux, Solaris, and other Unix flavors), PowerPC, and IA-64.
Performance Schema Timer Representation in Events
Rows in Performance Schema tables that store current events and historical events have three columns to represent timing information: 'TIMER_START' and 'TIMER_END' indicate when an event started and finished, and 'TIMER_WAIT' indicates event duration.
The *note 'setup_instruments': performance-schema-setup-instruments-table. table has an 'ENABLED' column to indicate the instruments for which to collect events. The table also has a 'TIMED' column to indicate which instruments are timed. If an instrument is not enabled, it produces no events. If an enabled instrument is not timed, events produced by the instrument have 'NULL' for the 'TIMER_START', 'TIMER_END', and 'TIMER_WAIT' timer values. This in turn causes those values to be ignored when calculating aggregate time values in summary tables (sum, minimum, maximum, and average).
Internally, times within events are stored in units given by the timer in effect when event timing begins. For display when events are retrieved from Performance Schema tables, times are shown in picoseconds (trillionths of a second) to normalize them to a standard unit, regardless of which timer is selected.
Modifications to the note 'setup_timers': performance-schema-setup-timers-table. table affect monitoring immediately. Events already in progress may use the original timer for the begin time and the new timer for the end time. To avoid unpredictable results after you make timer changes, use note 'TRUNCATE TABLE': truncate-table. to reset Performance Schema statistics.
The timer baseline ('time zero') occurs at Performance Schema initialization during server startup. 'TIMER_START' and 'TIMER_END' values in events represent picoseconds since the baseline. 'TIMER_WAIT' values are durations in picoseconds.
Picosecond values in events are approximate. Their accuracy is subject to the usual forms of error associated with conversion from one unit to another. If the 'CYCLE' timer is used and the processor rate varies, there might be drift. For these reasons, it is not reasonable to look at the 'TIMER_START' value for an event as an accurate measure of time elapsed since server startup. On the other hand, it is reasonable to use 'TIMER_START' or 'TIMER_WAIT' values in 'ORDER BY' clauses to order events by start time or duration.
The choice of picoseconds in events rather than a value such as microseconds has a performance basis. One implementation goal was to show results in a uniform time unit, regardless of the timer. In an ideal world this time unit would look like a wall-clock unit and be reasonably precise; in other words, microseconds. But to convert cycles or nanoseconds to microseconds, it would be necessary to perform a division for every instrumentation. Division is expensive on many platforms. Multiplication is not expensive, so that is what is used. Therefore, the time unit is an integer multiple of the highest possible 'TIMER_FREQUENCY' value, using a multiplier large enough to ensure that there is no major precision loss. The result is that the time unit is 'picoseconds.' This precision is spurious, but the decision enables overhead to be minimized.
While a wait, stage, statement, or transaction event is executing, the respective current-event tables display current-event timing information:
events_waits_current
events_stages_current
events_statements_current
events_transactions_currentTo make it possible to determine how long a not-yet-completed event has been running, the timer columns are set as follows:
'TIMER_START' is populated.
'TIMER_END' is populated with the current timer value.
'TIMER_WAIT' is populated with the time elapsed so far ('TIMER_END' − 'TIMER_START').
Events that have not yet completed have an 'END_EVENT_ID' value of 'NULL'. To assess time elapsed so far for an event, use the 'TIMER_WAIT' column. Therefore, to identify events that have not yet completed and have taken longer than N picoseconds thus far, monitoring applications can use this expression in queries:
WHERE END_EVENT_ID IS NULL AND TIMER_WAIT > NEvent identification as just described assumes that the corresponding instruments have 'ENABLED' and 'TIMED' set to 'YES' and that the relevant consumers are enabled.
File: manual.info.tmp, Node: performance-schema-filtering, Next: performance-schema-pre-filtering, Prev: performance-schema-timing, Up: performance-schema-runtime-configuration
Events are processed in a producer/consumer fashion:
Instrumented code is the source for events and produces events to be collected. The *note 'setup_instruments': performance-schema-setup-instruments-table. table lists the instruments for which events can be collected, whether they are enabled, and (for enabled instruments) whether to collect timing information:
mysql> SELECT * FROM performance_schema.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 note 'setup_instruments': performance-schema-setup-instruments-table. 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 note performance-schema-pre-filtering::.
Performance Schema tables are the destinations for events and consume events. The *note 'setup_consumers': performance-schema-setup-consumers-table. table lists the types of consumers to which event information can be sent and whether they are enabled:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_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. To do this, enable or disable instruments or consumers. Pre-filtering is done by the Performance Schema and has a global effect that applies to all users.
Reasons to use pre-filtering:
* To reduce overhead. Performance Schema 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.
* To 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.
* To avoid maintaining some kinds of event tables. If you
disable a consumer, the server does not spend time maintaining
destinations for that consumer. 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. Post-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 *note performance-schema-queries::.
File: manual.info.tmp, Node: performance-schema-pre-filtering, Next: performance-schema-instrument-filtering, Prev: performance-schema-filtering, Up: performance-schema-runtime-configuration
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:
* *note 'setup_instruments':
performance-schema-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.
* *note 'setup_objects': performance-schema-setup-objects-table.
controls whether the Performance Schema monitors particular
table and stored program objects.
* *note 'threads': performance-schema-threads-table. indicates
whether monitoring is enabled for each server thread.
* *note 'setup_actors': performance-schema-setup-actors-table.
determines the initial monitoring state for new foreground
threads.To configure pre-filtering at the consumer stage, modify the note 'setup_consumers': performance-schema-setup-consumers-table. table. This determines the destinations to which events are sent. note 'setup_consumers': performance-schema-setup-consumers-table. also implicitly affects event production. If a given event is not e sent to any destination (is not be consumed), the Performance Schema does not produce it.
Modifications to any of these tables affect monitoring immediately, with some exceptions:
Modifications to some instruments in the *note 'setup_instruments': performance-schema-setup-instruments-table. table 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. This restriction is lifted as of MySQL 5.7.12.
Modifications to the *note 'setup_actors': performance-schema-setup-actors-table. table affect only foreground threads created subsequent to the modification, not existing threads.
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 *note 'TRUNCATE TABLE': truncate-table. to empty the history tables.
After making instrumentation changes, you might want to truncate the summary tables. Generally, the effect is to reset the summary columns to 0 or 'NULL', not to remove rows. This enables you to clear collected values and restart aggregation. That might be useful, for example, after you have made a runtime configuration change. Exceptions to this truncation behavior are noted in individual summary table sections.
The following sections describe how to use specific tables to control Performance Schema pre-filtering.
File: manual.info.tmp, Node: performance-schema-instrument-filtering, Next: performance-schema-object-filtering, Prev: performance-schema-pre-filtering, Up: performance-schema-runtime-configuration
The *note 'setup_instruments': performance-schema-setup-instruments-table. table lists the available instruments:
mysql> SELECT * FROM performance_schema.setup_instruments;
+---------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+---------------------------------------------------+---------+-------+
...
| stage/sql/end | NO | NO |
| stage/sql/executing | NO | NO |
| stage/sql/init | NO | NO |
| stage/sql/insert | NO | NO |
...
| statement/sql/load | YES | YES |
| statement/sql/grant | YES | YES |
| statement/sql/check | YES | YES |
| statement/sql/flush | YES | YES |
...
| 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 enabled instrument, set its 'TIMED' value to 'YES' or 'NO'. Setting the 'TIMED' column affects Performance Schema table contents as described in *note performance-schema-timing::.
Modifications to most *note 'setup_instruments': performance-schema-setup-instruments-table. 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.
The note 'setup_instruments': performance-schema-setup-instruments-table. 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 note performance-schema-pre-filtering::.
The following examples demonstrate possible operations on the note 'setup_instruments': performance-schema-setup-instruments-table. table. These changes, like other pre-filtering operations, affect all users. Some of these queries use the 'LIKE' operator and a pattern match instrument names. For additional information about specifying patterns to select instruments, see note performance-schema-filtering-names::.
Disable all instruments:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO';Now no events are collected.
Disable all file instruments, adding them to the current set of disabled instruments:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO'
WHERE NAME LIKE 'wait/io/file/%';Disable only file instruments, enable all other instruments:
UPDATE performance_schema.setup_instruments
SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');Enable all but those instruments in the 'mysys' library:
UPDATE performance_schema.setup_instruments
SET ENABLED = CASE WHEN NAME LIKE '%/mysys/%' THEN 'YES' ELSE 'NO' END;Disable a specific instrument:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO'
WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';To toggle the state of an instrument, 'flip' its 'ENABLED' value:
UPDATE performance_schema.setup_instruments
SET ENABLED = IF(ENABLED = 'YES', 'NO', 'YES')
WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';Disable timing for all events:
UPDATE performance_schema.setup_instruments
SET TIMED = 'NO';File: manual.info.tmp, Node: performance-schema-object-filtering, Next: performance-schema-thread-filtering, Prev: performance-schema-instrument-filtering, Up: performance-schema-runtime-configuration
The note 'setup_objects': performance-schema-setup-objects-table. table controls whether the Performance Schema monitors particular table and stored program objects. The initial note 'setup_objects': performance-schema-setup-objects-table. contents look like this:
mysql> SELECT * FROM performance_schema.setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT | mysql | % | NO | NO |
| EVENT | performance_schema | % | NO | NO |
| EVENT | information_schema | % | NO | NO |
| EVENT | % | % | YES | YES |
| FUNCTION | mysql | % | NO | NO |
| FUNCTION | performance_schema | % | NO | NO |
| FUNCTION | information_schema | % | NO | NO |
| FUNCTION | % | % | YES | YES |
| PROCEDURE | mysql | % | NO | NO |
| PROCEDURE | performance_schema | % | NO | NO |
| PROCEDURE | information_schema | % | NO | NO |
| PROCEDURE | % | % | YES | YES |
| TABLE | mysql | % | NO | NO |
| TABLE | performance_schema | % | NO | NO |
| TABLE | information_schema | % | NO | NO |
| TABLE | % | % | YES | YES |
| TRIGGER | mysql | % | NO | NO |
| TRIGGER | performance_schema | % | NO | NO |
| TRIGGER | information_schema | % | NO | NO |
| TRIGGER | % | % | YES | YES |
+-------------+--------------------+-------------+---------+-------+Modifications to the *note 'setup_objects': performance-schema-setup-objects-table. table affect object monitoring immediately.
The 'OBJECT_TYPE' column indicates the type of object to which a row applies. 'TABLE' filtering affects table I/O events ('wait/io/table/sql/handler' instrument) and table lock events ('wait/lock/table/sql/handler' instrument).
The 'OBJECT_SCHEMA' and 'OBJECT_NAME' columns should contain a literal schema or object name, or ''%'' to match any name.
The 'ENABLED' column indicates whether matching objects are monitored, and 'TIMED' indicates whether to collect timing information. Setting the 'TIMED' column affects Performance Schema table contents as described in *note performance-schema-timing::.
The effect of the default object configuration is to instrument all objects 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 *note 'setup_objects': performance-schema-setup-objects-table.; the row for 'information_schema.%' simply makes this default explicit.)
When the Performance Schema checks for a match in *note 'setup_objects': performance-schema-setup-objects-table, it tries to find more specific matches first. For rows that match a given 'OBJECT_TYPE', the Performance Schema checks rows in this order:
Rows with 'OBJECT_SCHEMA='LITERAL'' and 'OBJECT_NAME='LITERAL''.
Rows with 'OBJECT_SCHEMA='LITERAL'' and 'OBJECT_NAME='%''.
Rows with 'OBJECT_SCHEMA='%'' and 'OBJECT_NAME='%''.
For example, with a table 'db1.t1', the Performance Schema looks in 'TABLE' rows for a match for ''db1'' and ''t1'', then for ''db1'' and ''%'', then for ''%'' and ''%''. The order in which matching occurs matters because different matching *note 'setup_objects': performance-schema-setup-objects-table. rows can have different 'ENABLED' and 'TIMED' values.
For table-related events, the Performance Schema combines the contents of note 'setup_objects': performance-schema-setup-objects-table. with note 'setup_instruments': performance-schema-setup-instruments-table. to determine whether to enable instruments and whether to time enabled instruments:
For tables that match a row in note 'setup_objects': performance-schema-setup-objects-table, table instruments produce events only if 'ENABLED' is 'YES' in both note 'setup_instruments': performance-schema-setup-instruments-table. and *note 'setup_objects': performance-schema-setup-objects-table.
The 'TIMED' values in the two tables are combined, so that timing information is collected only when both values are 'YES'.
For stored program objects, the Performance Schema takes the 'ENABLED' and 'TIMED' columns directly from the note 'setup_objects': performance-schema-setup-objects-table. row. There is no combining of values with note 'setup_instruments': performance-schema-setup-instruments-table.
Suppose that *note 'setup_objects': performance-schema-setup-objects-table. contains the following 'TABLE' rows that apply to 'db1', 'db2', and 'db3':
+-------------+---------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+---------------+-------------+---------+-------+
| TABLE | db1 | t1 | YES | YES |
| TABLE | db1 | t2 | NO | NO |
| TABLE | db2 | % | YES | YES |
| TABLE | db3 | % | NO | NO |
| TABLE | % | % | YES | YES |
+-------------+---------------+-------------+---------+-------+If an object-related instrument in note 'setup_instruments': performance-schema-setup-instruments-table. has an 'ENABLED' value of 'NO', events for the object are not monitored. If the 'ENABLED' value is 'YES', event monitoring occurs according to the 'ENABLED' value in the relevant note 'setup_objects': performance-schema-setup-objects-table. row:
'db1.t1' events are monitored
'db1.t2' events are not monitored
'db2.t3' events are monitored
'db3.t4' events are not monitored
'db4.t5' events are monitored
Similar logic applies for combining the 'TIMED' columns from the note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_objects': performance-schema-setup-objects-table. tables to determine whether to collect event timing information.
If a persistent table and a temporary table have the same name, matching against *note 'setup_objects': performance-schema-setup-objects-table. 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.
File: manual.info.tmp, Node: performance-schema-thread-filtering, Next: performance-schema-consumer-filtering, Prev: performance-schema-object-filtering, Up: performance-schema-runtime-configuration
The *note 'threads': performance-schema-threads-table. 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 *note 'setup_consumers': performance-schema-setup-consumers-table. table must be 'YES'.
The 'threads.INSTRUMENTED' column must be 'YES'.
Monitoring occurs only for those thread events produced from instruments that are enabled in the *note 'setup_instruments': performance-schema-setup-instruments-table. table.
The *note 'threads': performance-schema-threads-table. table also indicates for each server thread whether to perform historical event logging. This includes wait, stage, statement, and transaction events and affects logging to these tables:
events_waits_history
events_waits_history_long
events_stages_history
events_stages_history_long
events_statements_history
events_statements_history_long
events_transactions_history
events_transactions_history_longFor historical event logging to occur, these things must be true:
The appropriate history-related consumers in the note 'setup_consumers': performance-schema-setup-consumers-table. table must be enabled. For example, wait event logging in the note 'events_waits_history': performance-schema-events-waits-history-table. and *note 'events_waits_history_long': performance-schema-events-waits-history-long-table. tables requires the corresponding 'events_waits_history' and 'events_waits_history_long' consumers to be 'YES'.
The 'threads.HISTORY' column must be 'YES'.
Logging occurs only for those thread events produced from instruments that are enabled in the *note 'setup_instruments': performance-schema-setup-instruments-table. table.
For foreground threads (resulting from client connections), the initial values of the 'INSTRUMENTED' and 'HISTORY' columns in note 'threads': performance-schema-threads-table. table rows are determined by whether the user account associated with a thread matches any row in the note 'setup_actors': performance-schema-setup-actors-table. table. The values come from the 'ENABLED' and 'HISTORY' columns of the matching *note 'setup_actors': performance-schema-setup-actors-table. table row.
For background threads, there is no associated user. 'INSTRUMENTED' and 'HISTORY' are 'YES' by default and *note 'setup_actors': performance-schema-setup-actors-table. is not consulted.
The initial *note 'setup_actors': performance-schema-setup-actors-table. contents look like this:
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+The 'HOST' and 'USER' columns should contain a literal host or user name, or ''%'' to match any name.
The 'ENABLED' and 'HISTORY' columns indicate whether to enable instrumentation and historical event logging for matching threads, subject to the other conditions described previously.
When the Performance Schema checks for a match for each new foreground thread in 'setup_actors', it tries to find more specific matches first, using the 'USER' and 'HOST' columns ('ROLE' is unused):
Rows with 'USER='LITERAL'' and 'HOST='LITERAL''.
Rows with 'USER='LITERAL'' and 'HOST='%''.
Rows with 'USER='%'' and 'HOST='LITERAL''.
Rows with 'USER='%'' and 'HOST='%''.
The order in which matching occurs matters because different matching *note 'setup_actors': performance-schema-setup-actors-table. rows can have different 'USER' and 'HOST' values. This enables instrumenting and historical event logging to be applied selectively per host, user, or account (user and host combination), based on the 'ENABLED' and 'HISTORY' column values:
When the best match is a row with 'ENABLED=YES', the 'INSTRUMENTED' value for the thread becomes 'YES'. When the best match is a row with 'HISTORY=YES', the 'HISTORY' value for the thread becomes 'YES'.
When the best match is a row with 'ENABLED=NO', the 'INSTRUMENTED' value for the thread becomes 'NO'. When the best match is a row with 'HISTORY=NO', the 'HISTORY' value for the thread becomes 'NO'.
When no match is found, the 'INSTRUMENTED' and 'HISTORY' values for the thread become 'NO'.
The 'ENABLED' and 'HISTORY' columns in *note 'setup_actors': performance-schema-setup-actors-table. rows can be set to 'YES' or 'NO' independent of one another. This means you can enable instrumentation separately from whether you collect historical events.
By default, monitoring and historical event collection are enabled for all new foreground threads because the *note 'setup_actors': performance-schema-setup-actors-table. 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 change this row because it matches any connection, and add rows for more specific 'HOST'/'USER' combinations.
Suppose that you modify *note 'setup_actors': performance-schema-setup-actors-table. as follows:
UPDATE performance_schema.setup_actors
SET ENABLED = 'NO', HISTORY = 'NO'
WHERE HOST = '%' AND USER = '%';
INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','joe','%','YES','YES');
INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('hosta.example.com','joe','%','YES','NO');
INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('%','sam','%','NO','YES');The note 'UPDATE': update. statement changes the default match to disable instrumentation and historical event collection. The note 'INSERT': insert. statements add rows for more specific matches.
Now the Performance Schema determines how to set the 'INSTRUMENTED' and 'HISTORY' values for new connection threads as follows:
If 'joe' connects from the local host, the connection matches the first inserted row. The 'INSTRUMENTED' and 'HISTORY' values for the thread become 'YES'.
If 'joe' connects from 'hosta.example.com', the connection matches the second inserted row. The 'INSTRUMENTED' value for the thread becomes 'YES' and the 'HISTORY' value becomes 'NO'.
If 'joe' connects from any other host, there is no match. The 'INSTRUMENTED' and 'HISTORY' values for the thread become 'NO'.
If 'sam' connects from any host, the connection matches the third inserted row. The 'INSTRUMENTED' value for the thread becomes 'NO' and the 'HISTORY' value becomes 'YES'.
For any other connection, the row with 'HOST' and 'USER' set to ''%'' matches. This row now has 'ENABLED' and 'HISTORY' set to 'NO', so the 'INSTRUMENTED' and 'HISTORY' values for the thread become 'NO'.
Modifications to the note 'setup_actors': performance-schema-setup-actors-table. table affect only foreground threads created subsequent to the modification, not existing threads. To affect existing threads, modify the 'INSTRUMENTED' and 'HISTORY' columns of note 'threads': performance-schema-threads-table. table rows.
File: manual.info.tmp, Node: performance-schema-consumer-filtering, Next: performance-schema-consumer-configurations, Prev: performance-schema-thread-filtering, Up: performance-schema-runtime-configuration
The *note 'setup_consumers': performance-schema-setup-consumers-table. table lists the available consumer types and which are enabled:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_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 *note 'setup_consumers': performance-schema-setup-consumers-table. 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 *note 'setup_consumers': performance-schema-setup-consumers-table. table affect monitoring immediately.
If you disable a consumer, the server does not spend time maintaining destinations for that consumer. For example, if you do not care about historical event information, disable the history consumers:
UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME LIKE '%history%';The consumer settings in the *note 'setup_consumers': performance-schema-setup-consumers-table. 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 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 *note performance-schema-consumer-configurations::.
*note performance-schema-consumer-filtering-global-thread::
*note performance-schema-consumer-filtering-wait-event::
*note performance-schema-consumer-filtering-stage-event::
*note performance-schema-consumer-filtering-statement-event::
*note performance-schema-consumer-filtering-transaction-event::
*note performance-schema-consumer-filtering-statement-digest::
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.
Wait Event Consumers
These consumers require both 'global_instrumentation' and 'thread_instrumentation' to be 'YES' or they are not checked. If checked, they act as follows:
'events_waits_current', if 'NO', disables collection of individual wait events in the *note 'events_waits_current': performance-schema-events-waits-current-table. 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, an 'events_waits_history' value of 'NO' or 'YES' disables or enables collection of wait events in the *note 'events_waits_history': performance-schema-events-waits-history-table. table.
'events_waits_history_long' is not checked if 'event_waits_current' is 'NO'. Otherwise, an 'events_waits_history_long' value of 'NO' or 'YES' disables or enables collection of wait events in the *note 'events_waits_history_long': performance-schema-events-waits-history-long-table. table.
Stage Event Consumers
These consumers require both 'global_instrumentation' and 'thread_instrumentation' to be 'YES' or they are not checked. If checked, they act as follows:
'events_stages_current', if 'NO', disables collection of individual stage events in the *note 'events_stages_current': performance-schema-events-stages-current-table. 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, an 'events_stages_history' value of 'NO' or 'YES' disables or enables collection of stage events in the *note 'events_stages_history': performance-schema-events-stages-history-table. table.
'events_stages_history_long' is not checked if 'event_stages_current' is 'NO'. Otherwise, an 'events_stages_history_long' value of 'NO' or 'YES' disables or enables collection of stage events in the *note 'events_stages_history_long': performance-schema-events-stages-history-long-table. table.
Statement Event Consumers
These consumers require both 'global_instrumentation' and 'thread_instrumentation' to be 'YES' or they are not checked. If checked, they act as follows:
'events_statements_current', if 'NO', disables collection of individual statement events in the *note 'events_statements_current': performance-schema-events-statements-current-table. 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, an 'events_statements_history' value of 'NO' or 'YES' disables or enables collection of statement events in the *note 'events_statements_history': performance-schema-events-statements-history-table. table.
'events_statements_history_long' is not checked if 'events_statements_current' is 'NO'. Otherwise, an 'events_statements_history_long' value of 'NO' or 'YES' disables or enables collection of statement events in the *note 'events_statements_history_long': performance-schema-events-statements-history-long-table. table.
Transaction Event Consumers
These consumers require both 'global_instrumentation' and 'thread_instrumentation' to be 'YES' or they are not checked. If checked, they act as follows:
'events_transactions_current', if 'NO', disables collection of individual transaction events in the *note 'events_transactions_current': performance-schema-events-transactions-current-table. table. If 'YES', it enables transaction event collection and the Performance Schema checks the 'events_transactions_history' and 'events_transactions_history_long' consumers.
'events_transactions_history' is not checked if 'events_transactions_current' is 'NO'. Otherwise, an 'events_transactions_history' value of 'NO' or 'YES' disables or enables collection of transaction events in the *note 'events_transactions_history': performance-schema-events-transactions-history-table. table.
'events_transactions_history_long' is not checked if 'events_transactions_current' is 'NO'. Otherwise, an 'events_transactions_history_long' value of 'NO' or 'YES' disables or enables collection of transaction events in the *note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table. table.
Statement Digest Consumer
The 'statements_digest' consumer requires 'global_instrumentation' to be 'YES' or it is not checked. There is no dependency on the statement event consumers, so you can obtain statistics per digest without having to collect statistics in note 'events_statements_current': performance-schema-events-statements-current-table, which is advantageous in terms of overhead. Conversely, you can get detailed statements in note 'events_statements_current': performance-schema-events-statements-current-table. without digests (the 'DIGEST' and 'DIGEST_TEXT' columns are 'NULL').
For more information about statement digesting, see *note performance-schema-statement-digests::.
File: manual.info.tmp, Node: performance-schema-consumer-configurations, Next: performance-schema-filtering-names, Prev: performance-schema-consumer-filtering, Up: performance-schema-runtime-configuration
The consumer settings in the *note 'setup_consumers': performance-schema-setup-consumers-table. 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 executes less code on your behalf and you have less information to sift through.
The *note 'setup_consumers': performance-schema-setup-consumers-table. table contains the following hierarchy of values:
global_instrumentation
thread_instrumentation
events_waits_current
events_waits_history
events_waits_history_long
events_stages_current
events_stages_history
events_stages_history_long
events_statements_current
events_statements_history
events_statements_history_long
events_transactions_current
events_transactions_history
events_transactions_history_long
statements_digestNote:
In the consumer hierarchy, the consumers for waits, stages, statements, and transactions are all at the same level. This differs from the event nesting hierarchy, for which wait events nest within stage events, which nest within statement events, which nest within transaction events.
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. For a description of the rules for each consumer, see *note performance-schema-consumer-filtering::.
For example, if 'global_instrumentation' is enabled, 'thread_instrumentation' is checked. If 'thread_instrumentation' is enabled, the 'events_XXX_current' consumers are checked. If of these 'events_waits_current' is enabled, 'events_waits_history' and 'events_waits_history_long' are checked.
Each of the following configuration descriptions indicates which setup elements the Performance Schema checks and which output tables it maintains (that is, for which tables it collects information).
*note performance-schema-consumer-configurations-no-instrumentation::
*note performance-schema-consumer-configurations-global-instrumentation-only::
*note performance-schema-consumer-configurations-global-and-thread-instrumentation-only::
*note performance-schema-consumer-configurations-global-thread-and-current-event-instrumentation::
*note performance-schema-consumer-configurations-global-thread-current-event-and-event-history-instrumentation::
No Instrumentation
Server configuration state:
mysql> SELECT * FROM performance_schema.setup_consumers;
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| global_instrumentation | NO |
...
+---------------------------+---------+In this configuration, nothing is instrumented.
Setup elements checked:
Output tables maintained:
Global Instrumentation Only
Server configuration state:
mysql> SELECT * FROM performance_schema.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 *note 'setup_consumers': performance-schema-setup-consumers-table, consumer 'thread_instrumentation'
Table *note 'setup_instruments': performance-schema-setup-instruments-table.
Table *note 'setup_objects': performance-schema-setup-objects-table.
Table *note 'setup_timers': performance-schema-setup-timers-table.
Additional output tables maintained, relative to the preceding configuration:
*note 'mutex_instances': performance-schema-mutex-instances-table.
*note 'rwlock_instances': performance-schema-rwlock-instances-table.
*note 'cond_instances': performance-schema-cond-instances-table.
*note 'file_instances': performance-schema-file-instances-table.
*note 'users': performance-schema-users-table.
*note 'hosts': performance-schema-hosts-table.
*note 'accounts': performance-schema-accounts-table.
*note 'socket_summary_by_event_name': performance-schema-socket-summary-tables.
*note 'file_summary_by_instance': performance-schema-file-summary-tables.
*note 'file_summary_by_event_name': performance-schema-file-summary-tables.
*note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table.
*note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables.
*note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table.
*note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table.
*note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table.
*note 'events_waits_summary_by_instance': performance-schema-wait-summary-tables.
*note 'events_waits_summary_global_by_event_name': performance-schema-wait-summary-tables.
*note 'events_stages_summary_global_by_event_name': performance-schema-stage-summary-tables.
*note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables.
*note 'events_transactions_summary_global_by_event_name': performance-schema-transaction-summary-tables.
Global and Thread Instrumentation Only
Server configuration state:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| global_instrumentation | YES |
| thread_instrumentation | YES |
| events_waits_current | NO |
...
| events_stages_current | NO |
...
| events_statements_current | NO |
...
| events_transactions_current | NO |
...
+----------------------------------+---------+In this configuration, instrumentation is maintained globally and per thread. No individual events are collected in the current-events or event-history tables.
Additional setup elements checked, relative to the preceding configuration:
Table *note 'setup_consumers': performance-schema-setup-consumers-table, consumers 'events_XXX_current', where XXX is 'waits', 'stages', 'statements', 'transactions'
Table *note 'setup_actors': performance-schema-setup-actors-table.
Column 'threads.instrumented'
Additional output tables maintained, relative to the preceding configuration:
Global, Thread, and Current-Event Instrumentation
Server configuration state:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| global_instrumentation | YES |
| thread_instrumentation | YES |
| events_waits_current | YES |
| events_waits_history | NO |
| events_waits_history_long | NO |
| events_stages_current | YES |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | NO |
| events_transactions_history_long | NO |
...
+----------------------------------+---------+In this configuration, instrumentation is maintained globally and per thread. Individual events are collected in the current-events table, but not in the event-history tables.
Additional setup elements checked, relative to the preceding configuration:
Consumers 'events_XXX_history', where XXX is 'waits', 'stages', 'statements', 'transactions'
Consumers 'events_XXX_history_long', where XXX is 'waits', 'stages', 'statements', 'transactions'
Additional output tables maintained, relative to the preceding configuration:
Global, Thread, Current-Event, and Event-History instrumentation
The preceding configuration collects no event history because the 'events_XXX_history' and 'events_XXX_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 performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| global_instrumentation | YES |
| thread_instrumentation | YES |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | NO |
| events_stages_current | YES |
| events_stages_history | YES |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
...
+----------------------------------+---------+Event-history tables maintained for this configuration:
This configuration collects event history globally, but not per thread:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| global_instrumentation | YES |
| thread_instrumentation | YES |
| events_waits_current | YES |
| events_waits_history | NO |
| events_waits_history_long | YES |
| events_stages_current | YES |
| events_stages_history | NO |
| events_stages_history_long | YES |
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | YES |
| events_transactions_current | YES |
| events_transactions_history | NO |
| events_transactions_history_long | YES |
...
+----------------------------------+---------+Event-history tables maintained for this configuration:
This configuration collects event history per thread and globally:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| global_instrumentation | YES |
| thread_instrumentation | YES |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | YES |
| events_stages_current | YES |
| events_stages_history | YES |
| events_stages_history_long | YES |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | YES |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | YES |
...
+----------------------------------+---------+Event-history tables maintained for this configuration:
'events_XXX_history', where XXX is 'waits', 'stages', 'statements', 'transactions'
'events_XXX_history_long', where XXX is 'waits', 'stages', 'statements', 'transactions'
File: manual.info.tmp, Node: performance-schema-filtering-names, Next: performance-schema-instrumentation-checking, Prev: performance-schema-consumer-configurations, Up: performance-schema-runtime-configuration
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:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO'
WHERE NAME = 'wait/synch/mutex/myisammrg/MYRG_INFO::mutex';
UPDATE performance_schema.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:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO'
WHERE NAME LIKE 'wait/synch/mutex/%';
UPDATE performance_schema.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/%';The pattern ''%/file/%'' matches other instruments that have an element of ''/file/'' anywhere in the name. Even less suitable is the pattern ''%file%'' because it matches instruments with ''file'' anywhere in the name, such as 'wait/synch/mutex/innodb/file_open_mutex'.
To check which instrument or consumer names a pattern matches, perform a simple test:
SELECT NAME FROM performance_schema.setup_instruments
WHERE NAME LIKE 'PATTERN';
SELECT NAME FROM performance_schema.setup_consumers
WHERE NAME LIKE 'PATTERN';For information about the types of names that are supported, see *note performance-schema-instrument-naming::.
File: manual.info.tmp, Node: performance-schema-instrumentation-checking, Prev: performance-schema-filtering-names, Up: performance-schema-runtime-configuration
It is always possible to determine what instruments the Performance Schema includes by checking the *note 'setup_instruments': performance-schema-setup-instruments-table. table. For example, to see what file-related events are instrumented for the 'InnoDB' storage engine, use this query:
mysql> SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/io/file/innodb/%';
+--------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------+---------+-------+
| wait/io/file/innodb/innodb_data_file | YES | YES |
| wait/io/file/innodb/innodb_log_file | YES | YES |
| wait/io/file/innodb/innodb_temp_file | YES | YES |
+--------------------------------------+---------+-------+An exhaustive description of precisely what is instrumented is not given in this documentation, for several reasons:
What is instrumented is the server code. Changes to this code occur often, which also affects the set of instruments.
It is not practical to list all the instruments because there are hundreds of them.
As described earlier, it is possible to find out by querying the *note 'setup_instruments': performance-schema-setup-instruments-table. table. This information is always up to date for your version of MySQL, also includes instrumentation for instrumented plugins you might have installed that are not part of the core server, and can be used by automated tools.
File: manual.info.tmp, Node: performance-schema-queries, Next: performance-schema-instrument-naming, Prev: performance-schema-runtime-configuration, Up: performance-schema