Performance Schema Startup Configuration
The Performance Schema is disabled by default. To enable it, start the server with the performance_schema
variable enabled. For example, use these lines in your my.cnf
file:
[mysqld] performance_schema
When the server starts, it writes Performance Schema status information to the error log:
Performance schema enabled
indicates successful initialization.Performance schema disabled (reason: start parameters)
indicates that you did not enable the Performance Schema by enabling theperformance_schema
variable.Performance schema disabled (reason: init failed)
indicates that you enabledperformance_schema
but some kind of error occurred that prevented the Performance Schema from initializing successfully. For example, you may have specified other Performance Schema variables with values too large for memory allocation to succeed.
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.
As of MariaDB 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_instruments
and 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/sync/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:
--performance_schema_instrument='instrument_name
=value
'
Here, instrument_name
is an instrument name such as wait/sync/mutex/sql/LOCK_open
, and value
is one of these values:
off
,false
, or0
: Disable the instrumenton
,true
, or1
: Enable and time the instrumentcounted
: Enable and count (rather than time) the instrument
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'
Longer instrument name strings take precedence over shorter pattern names, regardless of order. For information about specifying patterns to select instruments, see , "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:
--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:
off
,false
, or0
: Do not collect events for the consumeron
,true
, or1
: Collect events for the consumer
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 setup_consumers
table. Patterns are not permitted.
The Performance Schema includes several system variables that provide configuration information:
mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name | Value |
+---------------------------------------------------+---------+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 10000 |
| performance_schema_max_table_instances | 1000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
+---------------------------------------------------+---------+
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. 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 a my.cnf
file to change the sizes of the history tables: