Assuming that the Performance Schema is available, it is enabled
by default as of MySQL 5.6.6. Before 5.6.6, it is disabled by
default. To enable or disable it explicitly, start the server
variable set to an appropriate value. For example, use these
lines in your
If the server is unable to allocate any internal buffer during
Performance Schema initialization, the Performance Schema
disables itself and sets
OFF, and the server runs without
As of MySQL 5.6.4, the Performance Schema permits instrument and
consumer configuration at server startup, which previously was
possible only at runtime using
UPDATE statements for the
setup_consumers tables. This change
was made because configuration at runtime is too late to disable
instruments that have already been initialized during server
startup. For example, the
wait/synch/mutex/sql/LOCK_open mutex is
initialized once during server startup, so attempts to disable
the corresponding instrument at runtime have no effect.
To control an instrument at server startup, use an option of this form:
instrument_name is an
instrument name such as
value is one of these values:
0: Disable the instrument
1: Enable and time the instrument
COUNTED: Enable and count (rather than
time) the instrument
option can specify only one instrument name, but multiple
instances of the option can be given to configure multiple
instruments. In addition, patterns are permitted in instrument
names to configure instruments that match the pattern. To
configure all condition synchronization instruments as enabled
and counted, use this option:
To disable all instruments, use this option:
Longer instrument name strings take precedence over shorter pattern names, regardless of order. For information about specifying patterns to select instruments, see Section 18.104.22.168, “Naming Instruments or Consumers for Filtering Operations”.
An unrecognized instrument name is ignored. It is possible that a plugin installed later may create the instrument, at which time the name is recognized and configured.
To control a consumer at server startup, use an option of this form:
consumer_name is a consumer
name such as
value is one of these values:
0: Do not collect events for the consumer
1: Collect events for the consumer
For example, to enable the
events_waits_history consumer, use this
The permitted consumer names can be found by examining the
setup_consumers table. Patterns are
not permitted. Consumer names in the
setup_consumers table use
underscores, but for consumers set at startup, dashes and
underscores within the name are equivalent.
The Performance Schema includes several system variables that provide configuration information:
SHOW VARIABLES LIKE 'perf%';+--------------------------------------------------------+---------+ | Variable_name | Value | +--------------------------------------------------------+---------+ | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 200 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | ...
OFF to indicate
whether the Performance Schema is enabled or disabled. The other
variables indicate table sizes (number of rows) or memory
With the Performance Schema enabled, the number of Performance Schema instances affects the server memory footprint, perhaps to a large extent. It may be necessary to tune the values of Performance Schema system variables to find the number of instances that balances insufficient instrumentation against excessive memory consumption.
To change the value of Performance Schema system variables, set
them at server startup. For example, put the following lines in
my.cnf file to change the sizes of the
history tables for wait events:
[mysqld] performance_schema performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000
As of MySQL 5.6.6, the Performance Schema automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For example, it sizes the parameters that control the sizes of the events waits tables this way. To see which parameters are autosized under this policy, use mysqld --verbose --help and look for those with a default value of −1, or see Section 22.12, “Performance Schema System Variables”.
For each autosized parameter that is not set at server startup (or is set to −1), the Performance Schema determines how to set its value based on the value of the following system values, which are considered as “hints” about how you have configured your MySQL server:
max_connections open_files_limit table_definition_cache table_open_cache
To override autosizing for a given parameter, set it to a value other than −1 at startup. In this case, the Performance Schema assigns it the specified value.
displays the actual values that autosized parameters were set
If the Performance Schema is disabled, its autosized parameters
remain set to −1 and
VARIABLES displays −1.