- 20 MySQL Performance Schema
- 20.1 Performance Schema Quick Start
- 20.2 Performance Schema Configuration
- 20.3 Performance Schema Status Monitoring
- 20.4 Performance Schema Event Timing
- 20.5 Performance Schema Event Instrument Naming Conventions
- 20.6 Performance Schema Table General Characteristics
- 20.7 Performance Schema Table Descriptions
- 20.8 Performance Schema and Plugins
- 20.9 Performance Schema System Variables
- 20.10 Performance Schema Status Variables
- 20.11 Using Performance Schema to Diagnose Problems
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 | 250 |
| 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 |
+---------------------------------------------------+--------+
Table 20.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.
User Comments
Add your own comment.