- 20 MySQL Performance Schema
- 20.1 Performance Schema Quick Start
- 20.2 Performance Schema Configuration
- 20.3 Performance Schema Queries
- 20.4 Performance Schema Instrument Naming Conventions
- 20.5 Performance Schema Status Monitoring
- 20.6 Performance Schema Atom and Molecule Events
- 20.7 Performance Schema Statement Digests
- 20.8 Performance Schema General Table Characteristics
- 20.9 Performance Schema Table Descriptions
- 20.10 Performance Schema Command Options
- 20.11 Performance Schema System Variables
- 20.12 Performance Schema Status Variables
- 20.13 Performance Schema and Plugins
- 20.14 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_accounts_size | 100 |
| performance_schema_digests_size | 200 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | 100 |
| 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_socket_classes | 10 |
| performance_schema_max_socket_instances | 1000 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 165 |
| performance_schema_max_table_handles | 10000 |
| performance_schema_max_table_instances | 1000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
| performance_schema_users_size | 100 |
+--------------------------------------------------------+---------+
Table 20.2. Performance Schema Variable Reference
The variables have the following meanings:
-
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_accounts_sizeThe number of rows in the
accountstable. If this variable is 0, the Performance Schema does not maintain connection statistics in theaccountstable. This variable was added in MySQL 5.6.3. -
performance_schema_digest_sizeThe maximum number of rows in the
events_statements_summary_by_digesttable. This variable was added in MySQL 5.6.5. -
performance_schema_events_stages_history_long_sizeThe number of rows in the
events_stages_history_longtable. This variable was added in MySQL 5.6.3. -
performance_schema_events_stages_history_sizeThe number of rows per thread in the
events_stages_historytable. This variable was added in MySQL 5.6.3. -
performance_schema_events_statements_history_long_sizeThe number of rows in the
events_statements_history_longtable. This variable was added in MySQL 5.6.3. -
performance_schema_events_statements_history_sizeThe number of rows per thread in the
events_statements_historytable. This variable was added in MySQL 5.6.3. -
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. -
The number of rows in the
hoststable. If this variable is 0, the Performance Schema does not maintain connection statistics in thehoststable. This variable was added in MySQL 5.6.3. -
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_socket_classesThe maximum number of socket instruments. This variable was added in MySQL 5.6.3.
-
performance_schema_max_socket_instancesThe maximum number of instrumented socket objects. This variable was added in MySQL 5.6.3.
-
performance_schema_max_stage_classesThe maximum number of stage instruments. This variable was added in MySQL 5.6.3.
-
performance_schema_max_statement_classesThe maximum number of statement instruments. This variable was added in MySQL 5.6.3.
-
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. -
performance_schema_setup_actors_sizeThe number of rows in the
setup_actorstable. -
performance_schema_setup_objects_sizeThe number of rows in the
setup_objectstable. -
The number of rows in the
userstable. If this variable is 0, the Performance Schema does not maintain connection statistics in theuserstable. This variable was added in MySQL 5.6.3.
User Comments
Add your own comment.