29.4.8 Example Consumer Configurations

The consumer settings in the setup_consumers table form a hierarchy from higher levels to lower. The following discussion describes how consumers work, showing specific configurations and their effects as consumer settings are enabled progressively from high to low. The consumer values shown are representative. The general principles described here apply to other consumer values that may be available.

The configuration descriptions occur in order of increasing functionality and overhead. If you do not need the information provided by enabling lower-level settings, disable them so that the Performance Schema executes less code on your behalf and there is 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 29.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

• Global Instrumentation Only

• Global and Thread Instrumentation Only

• Global, Thread, and Current-Event Instrumentation

• 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:

• Table setup_consumers, consumer global_instrumentation

Output tables maintained:

• None

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:

• mutex_instances

• rwlock_instances

• cond_instances

• file_instances

• users

• hosts

• accounts

• socket_summary_by_event_name

• file_summary_by_instance

• file_summary_by_event_name

• objects_summary_global_by_type

• memory_summary_global_by_event_name

• table_lock_waits_summary_by_table

• table_io_waits_summary_by_index_usage

• table_io_waits_summary_by_table

• events_waits_summary_by_instance

• events_waits_summary_global_by_event_name

• events_stages_summary_global_by_event_name

• events_statements_summary_global_by_event_name

• events_transactions_summary_global_by_event_name

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

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

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