26.4.8 Example Consumer Configurations

Contents

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.

The setup_consumers 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_digest

Note

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 Section 26.4.7, “Pre-Filtering by Consumer”.

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

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:

Table setup_consumers, consumer global_instrumentation

Output tables maintained:

None

Contents Haut

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 setup_consumers, consumer thread_instrumentation
Table setup_instruments
Table setup_objects

Additional output tables maintained, relative to the preceding configuration:

Contents Haut

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 setup_consumers, consumers events_xxx_current, where xxx is waits, stages, statements, transactions
Table setup_actors
Column threads.instrumented

Additional output tables maintained, relative to the preceding configuration:

events_xxx_summary_by_yyy_by_event_name, where xxx is waits, stages, statements, transactions; and yyy is thread, user, host, account

Contents Haut

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:

events_xxx_current, where xxx is waits, stages, statements, transactions

Contents Haut

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:

events_xxx_history, where xxx is waits, stages, statements, transactions

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:

events_xxx_history_long, where xxx is waits, stages, statements, transactions

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

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-performance-schema-consumer-configurations.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut