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:

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.

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:

[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 how monitoring is configured:

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_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;
+------+------------+
| 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 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.