Skip navigation links
MySQL Performance Schema :: 2 Performance Schema Configuration :: 2.2 Performance Schema Startup and Runtime Configuration
2.3 Event Collection Pre-Filtering and Post-Filtering »
Section Navigation      [Toggle]

2.2. Performance Schema Startup and Runtime Configuration

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 Performance Schema by enabling the performance_schema variable.

  • Performance schema disabled (reason: init failed) indicates that you enabled performance_schema but some kind of error occurred that prevented 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, Performance Schema disables itself and sets performance_schema to OFF, and the server runs without instrumentation.

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            | 1000   |
| performance_schema_max_rwlock_classes             | 20     |
| performance_schema_max_rwlock_instances           | 1000   |
| performance_schema_max_table_handles              | 100000 |
| performance_schema_max_table_instances            | 50000  |
| performance_schema_max_thread_classes             | 50     |
| performance_schema_max_thread_instances           | 1000   |
+---------------------------------------------------+--------+

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

To change the value of these 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: 

[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

Performance Schema setup tables contain information about monitoring configuration: 

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE 'SETUP%';
+-------------------+
| TABLE_NAME        |
+-------------------+
| SETUP_CONSUMERS   |
| SETUP_INSTRUMENTS |
| SETUP_OBJECTS     |
| SETUP_TIMERS      |
+-------------------+

You can examine the contents of these tables to obtain information about Performance Schema monitoring characteristics. If you have the UPDATE privilege, you can change Performance Schema operation by modifying setup tables to affect how monitoring occurs. For additional details about these tables, see Section 7.1, “Performance Schema Setup Tables”.

To see which event timer is selected, query the SETUP_TIMERS tables: 

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+

The NAME value indicates the type of instrument to which the timer applies, and TIMER_NAME indicates which timer applies to those instruments. The timer applies to instruments where their name begins with a component matching the NAME value. Currently, there are only “wait” instruments, so this table has only one row and the timer applies to all instruments.

To change the timer, update the NAME value. For example, to use the NANOSECONDS timer: 

mysql> UPDATE SETUP_TIMERS SET TIMER_NAME = 'NANOSECOND';

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | NANOSECOND |
+------+------------+

Timers are discussed further in Section 4, “Performance Schema Event Timing”.

The SETUP_INSTRUMENTS and SETUP_CONSUMERS tables list the instruments for which events can be collected and the destination tables in which event information can be stored, respectively. Section 2.3, “Event Collection Pre-Filtering and Post-Filtering”, discusses how you can modify these tables to affect event collection.