21.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:


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            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| 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.


With 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:


Performance Schema setup tables contain information about how monitoring is configured:

    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE 'setup%';
| TABLE_NAME        |
| setup_consumers   |
| setup_instruments |
| 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 21.7.1, “Performance Schema Setup Tables”.

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

mysql> SELECT * FROM setup_timers;
| 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;
| wait | NANOSECOND |

Timers are discussed further in Section 21.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 21.2.3, “Event Collection Pre-Filtering and Post-Filtering”, discusses how you can modify these tables to affect event collection.

If there are Performance Schema configuration changes that must be made at runtime that you would like to take effect each time the server starts, you can put the statements in a file and start the server with the --init-file=file_name option. This strategy can also be useful if you have multiple sets of configuration changes, tailored to produce different kinds of monitoring, such as casual server health monitorig, incident investigation, application behavior troubleshooting, and so forth. Put the statements to configure monitoring appriopriately into several files and specify the appropriate file as the --init-file argument when you start the server.

Copyright © 2010-2023 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout