- 22 MySQL Performance Schema
- 22.1 Performance Schema Quick Start
- 22.2 Performance Schema Configuration
- 22.3 Performance Schema Queries
- 22.4 Performance Schema Instrument Naming Conventions
- 22.5 Performance Schema Status Monitoring
- 22.6 Performance Schema Atom and Molecule Events
- 22.7 Performance Schema Statement Digests
- 22.8 Performance Schema General Table Characteristics
- 22.9 Performance Schema Table Descriptions
- 22.10 Performance Schema Option and Variable Reference
- 22.11 Performance Schema Command Options
- 22.12 Performance Schema System Variables
- 22.13 Performance Schema Status Variables
- 22.14 Performance Schema and Plugins
- 22.15 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_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
| performance_schema_users_size | 100 |
+--------------------------------------------------------+---------+
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 22.10, “Performance Schema Option and Variable Reference”.
As of MySQL 5.6.6, the Performance Schema automatically sizes the values of several of its parameters at server startup if they are not set explicitly. For more information, see Section 22.2.2, “Performance Schema Startup Configuration”.
Performance Schema system variables have the following meanings:
-
Command-Line Format --performance_schema=#System Variable Name performance_schemaVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type booleanDefault OFFPermitted Values (>= 5.6.6) Type booleanDefault ONThe value of this variable is
ONorOFFto indicate whether the Performance Schema is enabled. By default, the value isONby default as of MySQL 5.6.6 andOFFbefore that. At server startup, you can specify this variable with no value or a value ofONor 1 to enable it, or with a value ofOFFor 0 to disable it. performance_schema_accounts_sizeIntroduced 5.6.3 Command-Line Format --performance_schema_accounts_size=#System Variable Name performance_schema_accounts_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Min Value 0Max Value 1048576Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)Min Value -1Max Value 1048576The 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_digests_sizeIntroduced 5.6.5 Command-Line Format --performance_schema_digests_size=#System Variable Name performance_schema_digests_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault -1 (autosized)Min Value -1Max Value 1048576The maximum number of rows in the
events_statements_summary_by_digesttable. This variable was added in MySQL 5.6.5. If this maximum is exceeded such that a digest cannot be instrumented, the Performance Schema increments thePerformance_schema_digest_loststatus variable.performance_schema_events_stages_history_long_sizeIntroduced 5.6.3 Command-Line Format --performance_schema_events_stages_history_long_size=#System Variable Name performance_schema_events_stages_history_long_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The number of rows in the
events_stages_history_longtable. This variable was added in MySQL 5.6.3.performance_schema_events_stages_history_sizeIntroduced 5.6.3 Command-Line Format --performance_schema_events_stages_history_size=#System Variable Name performance_schema_events_stages_history_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The 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_sizeIntroduced 5.6.3 Command-Line Format --performance_schema_events_statements_history_long_size=#System Variable Name performance_schema_events_statements_history_long_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The number of rows in the
events_statements_history_longtable. This variable was added in MySQL 5.6.3.performance_schema_events_statements_history_sizeIntroduced 5.6.3 Command-Line Format --performance_schema_events_statements_history_size=#System Variable Name performance_schema_events_statements_history_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The 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_sizeCommand-Line Format --performance_schema_events_waits_history_long_size=#System Variable Name performance_schema_events_waits_history_long_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The number of rows in the
events_waits_history_longtable.performance_schema_events_waits_history_sizeCommand-Line Format --performance_schema_events_waits_history_size=#System Variable Name performance_schema_events_waits_history_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The number of rows per thread in the
events_waits_historytable.-
Introduced 5.6.3 Command-Line Format --performance_schema_hosts_size=#System Variable Name performance_schema_hosts_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Min Value 0Max Value 1048576Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)Min Value -1Max Value 1048576The 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_classesCommand-Line Format --performance_schema_max_cond_classes=#System Variable Name performance_schema_max_cond_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 80The maximum number of condition instruments.
performance_schema_max_cond_instancesCommand-Line Format --performance_schema_max_cond_instances=#System Variable Name performance_schema_max_cond_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented condition objects.
performance_schema_max_file_classesCommand-Line Format --performance_schema_max_file_classes=#System Variable Name performance_schema_max_file_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 50The maximum number of file instruments.
performance_schema_max_file_handlesCommand-Line Format --performance_schema_max_file_handles=#System Variable Name performance_schema_max_file_handlesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 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_instancesCommand-Line Format --performance_schema_max_file_instances=#System Variable Name performance_schema_max_file_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented file objects.
performance_schema_max_mutex_classesCommand-Line Format --performance_schema_max_mutex_classes=#System Variable Name performance_schema_max_mutex_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 200The maximum number of mutex instruments.
performance_schema_max_mutex_instancesCommand-Line Format --performance_schema_max_mutex_instances=#System Variable Name performance_schema_max_mutex_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented mutex objects.
performance_schema_max_rwlock_classesCommand-Line Format --performance_schema_max_rwlock_classes=#System Variable Name performance_schema_max_rwlock_classesVariable Scope Global Dynamic Variable No Permitted Values (5.6.0) Type integerDefault 20Permitted Values (>= 5.6.1, <= 5.6.14) Type integerDefault 30Permitted Values (>= 5.6.15) Type integerDefault 40The maximum number of rwlock instruments.
performance_schema_max_rwlock_instancesCommand-Line Format --performance_schema_max_rwlock_instances=#System Variable Name performance_schema_max_rwlock_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented rwlock objects.
performance_schema_max_socket_classesIntroduced 5.6.3 Command-Line Format --performance_schema_max_socket_classes=#System Variable Name performance_schema_max_socket_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 10The maximum number of socket instruments. This variable was added in MySQL 5.6.3.
performance_schema_max_socket_instancesIntroduced 5.6.3 Command-Line Format --performance_schema_max_socket_instances=#System Variable Name performance_schema_max_socket_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented socket objects. This variable was added in MySQL 5.6.3.
performance_schema_max_stage_classesIntroduced 5.6.3 Command-Line Format --performance_schema_max_stage_classes=#System Variable Name performance_schema_max_stage_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 150The maximum number of stage instruments. This variable was added in MySQL 5.6.3.
performance_schema_max_statement_classesIntroduced 5.6.3 Command-Line Format --performance_schema_max_statement_classes=#System Variable Name performance_schema_max_statement_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault (autosized)The maximum number of statement instruments. The default value is calculated at server build time based on the number of commands in the client/server protocol and the number of SQL statement types supported by the server.
This variable should not be changed, unless to set it to 0 to disable all statement instrumentation and save all memory associated with it. Setting the variable to nonzero values other than the default has no benefit; in particular, values larger than the default cause more memory to be allocated then is needed.
This variable was added in MySQL 5.6.3.
performance_schema_max_table_handlesCommand-Line Format --performance_schema_max_table_handles=#System Variable Name performance_schema_max_table_handlesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 100000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of opened table objects.
performance_schema_max_table_instancesCommand-Line Format --performance_schema_max_table_instances=#System Variable Name performance_schema_max_table_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 50000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented table objects.
performance_schema_max_thread_classesCommand-Line Format --performance_schema_max_thread_classes=#System Variable Name performance_schema_max_thread_classesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 50The maximum number of thread instruments.
performance_schema_max_thread_instancesCommand-Line Format --performance_schema_max_thread_instances=#System Variable Name performance_schema_max_thread_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1000Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)The maximum number of instrumented thread objects. The value controls the size of the
threadstable. If this maximum is exceeded such that a thread cannot be instrumented, the Performance Schema increments thePerformance_schema_thread_instances_loststatus variable.The
max_connectionssystem variable affects how many threads are run in the server.performance_schema_max_thread_instancesaffects how many of these running threads can be instrumented. The default value ofperformance_schema_max_thread_instancesis autosized based on the value ofmax_connections.performance_schema_session_connect_attrs_sizeIntroduced 5.6.6 Command-Line Format --performance_schema_session_connect_attrs_size=#System Variable Name performance_schema_session_connect_attrs_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault -1 (autosized)Min Value -1Max Value 1048576The amount of preallocated memory per thread used to hold connection attribute strings. If the connection attribute strings are larger than the reserved storage, the
Performance_schema_session_connect_attrs_loststatus variable is incremented. This variable was added in MySQL 5.6.7.performance_schema_setup_actors_sizeIntroduced 5.6.1 Command-Line Format --performance_schema_setup_actors_size=#System Variable Name performance_schema_setup_actors_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 100The number of rows in the
setup_actorstable.performance_schema_setup_objects_sizeIntroduced 5.6.1 Command-Line Format --performance_schema_setup_objects_size=#System Variable Name performance_schema_setup_objects_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 100The number of rows in the
setup_objectstable.-
Introduced 5.6.3 Command-Line Format --performance_schema_users_size=#System Variable Name performance_schema_users_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 10Min Value 0Max Value 1048576Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)Min Value -1Max Value 1048576The 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