Performance Schema implements 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 |
+---------------------------------------------------+---------+
Table 21.1. Performance Schema Variable Reference
The variables have the following meanings:
The value of this variable is
ONorOFFto indicate whether Performance Schema is enabled. By default, the value isOFF. At server startup, you can specify this variable with no value or a value of 1 to enable it, or with a value of 0 to disable it.performance_schema_events_waits_history_long_sizeThe number of rows in the
events_waits_history_longtable.performance_schema_events_waits_history_sizeThe number of rows per thread in the
events_waits_historytable.performance_schema_max_cond_classesThe maximum number of condition instruments.
performance_schema_max_cond_instancesThe maximum number of instrumented condition objects.
performance_schema_max_file_classesThe maximum number of file instruments.
performance_schema_max_file_handlesThe maximum number of opened file objects.
The value of
performance_schema_max_file_handlesshould be greater than the value ofopen_files_limit:open_files_limitaffects the maximum number of open file handles the server can support andperformance_schema_max_file_handlesaffects how many of these file handles can be instrumented.performance_schema_max_file_instancesThe maximum number of instrumented file objects.
performance_schema_max_mutex_classesThe maximum number of mutex instruments.
performance_schema_max_mutex_instancesThe maximum number of instrumented mutex objects.
performance_schema_max_rwlock_classesThe maximum number of rwlock instruments.
performance_schema_max_rwlock_instancesThe maximum number of instrumented rwlock objects.
performance_schema_max_table_handlesThe maximum number of opened table objects.
performance_schema_max_table_instancesThe maximum number of instrumented table objects.
performance_schema_max_thread_classesThe maximum number of thread instruments.
performance_schema_max_thread_instancesThe maximum number of instrumented thread objects.
The
max_connectionsandmax_delayed_threadssystem variables affect how many threads are run in the server.performance_schema_max_thread_instancesaffects how many of these running threads can be instrumented. If you increasemax_connectionsormax_delayed_threads, you should consider increasingperformance_schema_max_thread_instancesso thatperformance_schema_max_thread_instancesis greater than the sum ofmax_connectionsandmax_delayed_threads.