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.13, “Performance Schema Option and Variable Reference”.
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.3, “Performance Schema Startup Configuration”.
Performance Schema system variables have the following meanings:
-
Command-Line Format --performance-schema[={OFF|ON}]
System Variable performance_schema
Scope Global Dynamic No Type Boolean Default Value ON
The value of this variable is
ON
orOFF
to indicate whether the Performance Schema is enabled. By default, the value isON
. At server startup, you can specify this variable with no value or a value ofON
or 1 to enable it, or with a value ofOFF
or 0 to disable it. performance_schema_accounts_size
Command-Line Format --performance-schema-accounts-size=#
System Variable performance_schema_accounts_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autosizing; do not assign this literal value)Maximum Value 1048576
The number of rows in the
accounts
table. If this variable is 0, the Performance Schema does not maintain connection statistics in theaccounts
table.performance_schema_digests_size
Command-Line Format --performance-schema-digests-size=#
System Variable performance_schema_digests_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The maximum number of rows in the
events_statements_summary_by_digest
table. If this maximum is exceeded such that a digest cannot be instrumented, the Performance Schema increments thePerformance_schema_digest_lost
status variable.For more information about statement digesting, see Section 22.10, “Performance Schema Statement Digests”.
performance_schema_events_stages_history_long_size
Command-Line Format --performance-schema-events-stages-history-long-size=#
System Variable performance_schema_events_stages_history_long_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The number of rows in the
events_stages_history_long
table.performance_schema_events_stages_history_size
Command-Line Format --performance-schema-events-stages-history-size=#
System Variable performance_schema_events_stages_history_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1024
The number of rows per thread in the
events_stages_history
table.performance_schema_events_statements_history_long_size
Command-Line Format --performance-schema-events-statements-history-long-size=#
System Variable performance_schema_events_statements_history_long_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The number of rows in the
events_statements_history_long
table.performance_schema_events_statements_history_size
Command-Line Format --performance-schema-events-statements-history-size=#
System Variable performance_schema_events_statements_history_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1024
The number of rows per thread in the
events_statements_history
table.performance_schema_events_waits_history_long_size
Command-Line Format --performance-schema-events-waits-history-long-size=#
System Variable performance_schema_events_waits_history_long_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The number of rows in the
events_waits_history_long
table.performance_schema_events_waits_history_size
Command-Line Format --performance-schema-events-waits-history-size=#
System Variable performance_schema_events_waits_history_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1024
The number of rows per thread in the
events_waits_history
table.-
Command-Line Format --performance-schema-hosts-size=#
System Variable performance_schema_hosts_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autosizing; do not assign this literal value)Maximum Value 1048576
The number of rows in the
hosts
table. If this variable is 0, the Performance Schema does not maintain connection statistics in thehosts
table. performance_schema_max_cond_classes
Command-Line Format --performance-schema-max-cond-classes=#
System Variable performance_schema_max_cond_classes
Scope Global Dynamic No Type Integer Default Value 80
Minimum Value 0
Maximum Value 256
The maximum number of condition instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_cond_instances
Command-Line Format --performance-schema-max-cond-instances=#
System Variable performance_schema_max_cond_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1024
The maximum number of instrumented condition objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_digest_length
Command-Line Format --performance-schema-max-digest-length=#
Introduced 5.6.26 System Variable performance_schema_max_digest_length
Scope Global Dynamic No Type Integer Default Value 1024
Minimum Value 0
Maximum Value 1048576
Unit bytes The maximum number of bytes of memory reserved per statement for computation of normalized statement digest values in the Performance Schema. This variable is related to
max_digest_length
; see the description of that variable in Section 5.1.7, “Server System Variables”.For more information about statement digesting, including considerations regarding memory use, see Section 22.10, “Performance Schema Statement Digests”.
performance_schema_max_file_classes
Command-Line Format --performance-schema-max-file-classes=#
System Variable performance_schema_max_file_classes
Scope Global Dynamic No Type Integer Default Value 50
Minimum Value 0
Maximum Value 256
The maximum number of file instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_file_handles
Command-Line Format --performance-schema-max-file-handles=#
System Variable performance_schema_max_file_handles
Scope Global Dynamic No Type Integer Default Value 32768
Minimum Value 0
Maximum Value 1048576
The maximum number of opened file objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
The value of
performance_schema_max_file_handles
should be greater than the value ofopen_files_limit
:open_files_limit
affects the maximum number of open file handles the server can support andperformance_schema_max_file_handles
affects how many of these file handles can be instrumented.performance_schema_max_file_instances
Command-Line Format --performance-schema-max-file-instances=#
System Variable performance_schema_max_file_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)The maximum number of instrumented file objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_mutex_classes
Command-Line Format --performance-schema-max-mutex-classes=#
System Variable performance_schema_max_mutex_classes
Scope Global Dynamic No Type Integer Default Value 200
Minimum Value 0
Maximum Value 256
The maximum number of mutex instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_mutex_instances
Command-Line Format --performance-schema-max-mutex-instances=#
System Variable performance_schema_max_mutex_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autoscaling; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 104857600
The maximum number of instrumented mutex objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_rwlock_classes
Command-Line Format --performance-schema-max-rwlock-classes=#
System Variable performance_schema_max_rwlock_classes
Scope Global Dynamic No Type Integer Default Value (≥ 5.6.15) 40
Default Value (≤ 5.6.14) 30
Minimum Value 0
Maximum Value 256
The maximum number of rwlock instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_rwlock_instances
Command-Line Format --performance-schema-max-rwlock-instances=#
System Variable performance_schema_max_rwlock_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autosizing; do not assign this literal value)Maximum Value 104857600
The maximum number of instrumented rwlock objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_socket_classes
Command-Line Format --performance-schema-max-socket-classes=#
System Variable performance_schema_max_socket_classes
Scope Global Dynamic No Type Integer Default Value 10
Minimum Value 0
Maximum Value 256
The maximum number of socket instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_socket_instances
Command-Line Format --performance-schema-max-socket-instances=#
System Variable performance_schema_max_socket_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The maximum number of instrumented socket objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_stage_classes
Command-Line Format --performance-schema-max-stage-classes=#
System Variable performance_schema_max_stage_classes
Scope Global Dynamic No Type Integer Default Value 150
Minimum Value 0
Maximum Value 256
The maximum number of stage instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_statement_classes
Command-Line Format --performance-schema-max-statement-classes=#
System Variable performance_schema_max_statement_classes
Scope Global Dynamic No Type Integer Minimum Value 0
Maximum Value 256
The maximum number of statement instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
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.
performance_schema_max_table_handles
Command-Line Format --performance-schema-max-table-handles=#
System Variable performance_schema_max_table_handles
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)The maximum number of opened table objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_table_instances
Command-Line Format --performance-schema-max-table-instances=#
System Variable performance_schema_max_table_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autoscaling; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The maximum number of instrumented table objects. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_thread_classes
Command-Line Format --performance-schema-max-thread-classes=#
System Variable performance_schema_max_thread_classes
Scope Global Dynamic No Type Integer Default Value 50
Minimum Value 0
Maximum Value 256
The maximum number of thread instruments. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.
performance_schema_max_thread_instances
Command-Line Format --performance-schema-max-thread-instances=#
System Variable performance_schema_max_thread_instances
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autoscaling; do not assign this literal value)Maximum Value 1048576
The maximum number of instrumented thread objects. The value controls the size of the
threads
table. If this maximum is exceeded such that a thread cannot be instrumented, the Performance Schema increments thePerformance_schema_thread_instances_lost
status variable. For information about how to set and use this variable, see Section 22.7, “Performance Schema Status Monitoring”.The
max_connections
system variable affects how many threads are run in the server.performance_schema_max_thread_instances
affects how many of these running threads can be instrumented. The default value ofperformance_schema_max_thread_instances
is autosized based on the value ofmax_connections
.performance_schema_session_connect_attrs_size
Command-Line Format --performance-schema-session-connect-attrs-size=#
System Variable performance_schema_session_connect_attrs_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autosizing; do not assign this literal value)Maximum Value 1048576
Unit bytes The amount of preallocated memory per thread reserved to hold connection attribute key-value pairs. If the aggregate size of connection attribute data sent by a client is larger than this amount, the Performance Schema truncates the attribute data, increments the
Performance_schema_session_connect_attrs_lost
status variable, and writes a message to the error log indicating that truncation occurred if thelog_warnings
system variable value is greater than zero.The default value of
performance_schema_session_connect_attrs_size
is autosized at server startup. This value may be small, so if truncation occurs (Performance_schema_session_connect_attrs_lost
becomes nonzero), you may wish to setperformance_schema_session_connect_attrs_size
explicitly to a larger value.Although the maximum permitted
performance_schema_session_connect_attrs_size
value is 1MB, the effective maximum is 64KB because the server imposes a limit of 64KB on the aggregate size of connection attribute data it can accept. If a client attempts to send more than 64KB of attribute data, the server rejects the connection. For more information, see Section 22.12.8, “Performance Schema Connection Attribute Tables”.performance_schema_setup_actors_size
Command-Line Format --performance-schema-setup-actors-size=#
System Variable performance_schema_setup_actors_size
Scope Global Dynamic No Type Integer Default Value 100
Minimum Value 0
Maximum Value 1048576
The number of rows in the
setup_actors
table.performance_schema_setup_objects_size
Command-Line Format --performance-schema-setup-objects-size=#
System Variable performance_schema_setup_objects_size
Scope Global Dynamic No Type Integer Default Value 100
Minimum Value 0
Maximum Value 1048576
The number of rows in the
setup_objects
table.-
Command-Line Format --performance-schema-users-size=#
System Variable performance_schema_users_size
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value -1
(signifies autosizing; do not assign this literal value)Maximum Value 1048576
The number of rows in the
users
table. If this variable is 0, the Performance Schema does not maintain connection statistics in theusers
table.