Movatterモバイル変換

MySQL 9.0 Reference Manual

29.4.8 Example Consumer Configurations

The consumer settings in thesetup_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.

Thesetup_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 isNO, the Performance Schema disables the instrumentation associated with the consumer and ignores all lower-level settings. If a given setting isYES, 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, seeSection 29.4.7, “Pre-Filtering by Consumer”.

For example, ifglobal_instrumentation is enabled,thread_instrumentation is checked. Ifthread_instrumentation is enabled, theevents_xxx_current consumers are checked. If of theseevents_waits_current is enabled,events_waits_history andevents_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:

Tablesetup_consumers, consumerglobal_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:

Tablesetup_consumers, consumerthread_instrumentation
Tablesetup_instruments
Tablesetup_objects

Additional output tables maintained, relative to the preceding configuration:

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:

Tablesetup_consumers, consumersevents_xxx_current, wherexxx iswaits,stages,statements,transactions
Tablesetup_actors
Columnthreads.instrumented

Additional output tables maintained, relative to the preceding configuration:

events_xxx_summary_by_yyy_by_event_name, wherexxx iswaits,stages,statements,transactions; andyyy isthread,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:

Consumersevents_xxx_history, wherexxx iswaits,stages,statements,transactions
Consumersevents_xxx_history_long, wherexxx iswaits,stages,statements,transactions

Additional output tables maintained, relative to the preceding configuration:

events_xxx_current, wherexxx iswaits,stages,statements,transactions

Global, Thread, Current-Event, and Event-History instrumentation

The preceding configuration collects no event history because theevents_xxx_history andevents_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, wherexxx iswaits,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, wherexxx iswaits,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, wherexxx iswaits,stages,statements,transactions
events_xxx_history_long, wherexxx iswaits,stages,statements,transactions

PREV HOME UP NEXT