25.3 Performance Schema Startup Configuration

To use the MySQL Performance Schema, it must be enabled at server startup to enable event collection to occur.

Assuming that the Performance Schema is available, it is enabled by default. To enable or disable it explicitly, start the server with the 'performance_schema' variable set to an appropriate value. For example, use these lines in your 'my.cnf' file:

 [mysqld]
 performance_schema=ON

If the server is unable to allocate any internal buffer during Performance Schema initialization, the Performance Schema disables itself and sets 'performance_schema' to 'OFF', and the server runs without instrumentation.

The Performance Schema also permits instrument and consumer configuration at server startup.

To control an instrument at server startup, use an option of this form:

 --performance-schema-instrument='INSTRUMENT_NAME=VALUE'

Here, INSTRUMENT_NAME is an instrument name such as 'wait/synch/mutex/sql/LOCK_open', and VALUE is one of these values:

Each '--performance-schema-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:

 --performance-schema-instrument='wait/synch/cond/%=COUNTED'

To disable all instruments, use this option:

 --performance-schema-instrument='%=OFF'

Exception: The 'memory/performance_schema/%' instruments are built in and cannot be disabled at startup.

Longer instrument name strings take precedence over shorter pattern names, regardless of order. For information about specifying patterns to select instruments, see *note performance-schema-filtering-names::.

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:

 --performance-schema-consumer-CONSUMER_NAME=VALUE

Here, CONSUMER_NAME is a consumer name such as 'events_waits_history', and VALUE is one of these values:

For example, to enable the 'events_waits_history' consumer, use this option:

 --performance-schema-consumer-events-waits-history=ON

The permitted consumer names can be found by examining the note 'setup_consumers': performance-schema-setup-consumers-table. table. Patterns are not permitted. Consumer names in the note 'setup_consumers': performance-schema-setup-consumers-table. 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:

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

The 'performance_schema' variable is 'ON' or 'OFF' to indicate whether the Performance Schema is enabled or disabled. The other variables indicate table sizes (number of rows) or memory allocation values.

Note:

With the Performance Schema enabled, the number of Performance Schema instances affects the server memory footprint, perhaps to a large extent. The Performance Schema autoscales many parameters to use memory only as required; see *note performance-schema-memory-model::.

To change the value of Performance Schema system variables, set them at server startup. For example, put the following lines in a '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

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. the Performance Schema allocates memory incrementally, scaling its memory use to actual server load, instead of allocating all the memory it needs during server startup. Consequently, many sizing parameters need not be set at all. To see which parameters are autosized or autoscaled, use note 'mysqld --verbose --help': mysqld. and examine the option descriptions, or see note performance-schema-system-variables::.

For each autosized parameter that is not set at server startup, 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 or autoscaling 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.

At runtime, *note 'SHOW VARIABLES': show-variables. displays the actual values that autosized parameters were set to. Autoscaled parameters display with a value of −1.

If the Performance Schema is disabled, its autosized and autoscaled parameters remain set to −1 and *note 'SHOW VARIABLES': show-variables. displays −1.

 File: manual.info.tmp, Node: performance-schema-runtime-configuration, Next: performance-schema-queries, Prev: performance-schema-startup-configuration, Up: performance-schema