- 21 MySQL Performance Schema
- 21.1 Performance Schema Quick Start
- 21.2 Performance Schema Configuration
- 21.3 Performance Schema Queries
- 21.4 Performance Schema Instrument Naming Conventions
- 21.5 Performance Schema Status Monitoring
- 21.6 Performance Schema General Table Characteristics
- 21.7 Performance Schema Table Descriptions
- 21.8 Performance Schema Option and Variable Reference
- 21.9 Performance Schema System Variables
- 21.10 Performance Schema Status Variables
- 21.11 Performance Schema and Plugins
- 21.12 Using the Performance Schema to Diagnose Problems
The 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 |
+---------------------------------------------------+---------+
Performance Schema system variables can be set at server startup on the command line or in option files, and many can be set at runtime. See Section 21.8, “Performance Schema Option and Variable Reference”.
Performance Schema variables have the following meanings:
-
Version Introduced 5.5.3 Command-Line Format --performance_schema=#Option-File Format performance_schema=#Variable Name performance_schemaVariable Scope Global Dynamic Variable No The value of this variable is
ONorOFFto indicate whether the 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_sizeVersion Introduced 5.5.3 Command-Line Format --performance_schema_events_waits_history_long_size=#Option-File Format performance_schema_events_waits_history_long_size=#Variable Name performance_schema_events_waits_history_long_sizeVariable Scope Global Dynamic Variable No The number of rows in the
events_waits_history_longtable.performance_schema_events_waits_history_sizeVersion Introduced 5.5.3 Command-Line Format --performance_schema_events_waits_history_size=#Option-File Format performance_schema_events_waits_history_size=#Variable Name performance_schema_events_waits_history_sizeVariable Scope Global Dynamic Variable No The number of rows per thread in the
events_waits_historytable.performance_schema_max_cond_classesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_cond_classes=#Option-File Format performance_schema_max_cond_classes=#Variable Name performance_schema_max_cond_classesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 80The maximum number of condition instruments.
performance_schema_max_cond_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_cond_instances=#Option-File Format performance_schema_max_cond_instances=#Variable Name performance_schema_max_cond_instancesVariable Scope Global Dynamic Variable No The maximum number of instrumented condition objects.
performance_schema_max_file_classesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_file_classes=#Option-File Format performance_schema_max_file_classes=#Variable Name performance_schema_max_file_classesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 50The maximum number of file instruments.
performance_schema_max_file_handlesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_file_handles=#Option-File Format performance_schema_max_file_handles=#Variable Name performance_schema_max_file_handlesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 32768The 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_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_file_instances=#Option-File Format performance_schema_max_file_instances=#Variable Name performance_schema_max_file_instancesVariable Scope Global Dynamic Variable No The maximum number of instrumented file objects.
performance_schema_max_mutex_classesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_mutex_classes=#Option-File Format performance_schema_max_mutex_classes=#Variable Name performance_schema_max_mutex_classesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 200The maximum number of mutex instruments.
performance_schema_max_mutex_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_mutex_instances=#Option-File Format performance_schema_max_mutex_instances=#Variable Name performance_schema_max_mutex_instancesVariable Scope Global Dynamic Variable No The maximum number of instrumented mutex objects.
performance_schema_max_rwlock_classesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_rwlock_classes=#Option-File Format performance_schema_max_rwlock_classes=#Variable Name performance_schema_max_rwlock_classesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 20The maximum number of rwlock instruments.
performance_schema_max_rwlock_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_rwlock_instances=#Option-File Format performance_schema_max_rwlock_instances=#Variable Name performance_schema_max_rwlock_instancesVariable Scope Global Dynamic Variable No The maximum number of instrumented rwlock objects.
performance_schema_max_table_handlesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_table_handles=#Option-File Format performance_schema_max_table_handles=#Variable Name performance_schema_max_table_handlesVariable Scope Global Dynamic Variable No The maximum number of opened table objects.
performance_schema_max_table_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_table_instances=#Option-File Format performance_schema_max_table_instances=#Variable Name performance_schema_max_table_instancesVariable Scope Global Dynamic Variable No The maximum number of instrumented table objects.
performance_schema_max_thread_classesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_thread_classes=#Option-File Format performance_schema_max_thread_classes=#Variable Name performance_schema_max_thread_classesVariable Scope Global Dynamic Variable No Permitted Values Type numericDefault 50The maximum number of thread instruments.
performance_schema_max_thread_instancesVersion Introduced 5.5.3 Command-Line Format --performance_schema_max_thread_instances=#Option-File Format performance_schema_max_thread_instances=#Variable Name performance_schema_max_thread_instancesVariable Scope Global Dynamic Variable No The 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.