MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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 | -1 |
| performance_schema_digests_size | 10000 |
| 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_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | -1 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | -1 |
| performance_schema_max_index_stat | -1 |
| performance_schema_max_memory_classes | 320 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | -1 |
| performance_schema_max_prepared_statements_instances | -1 |
| performance_schema_max_program_instances | -1 |
| performance_schema_max_rwlock_classes | 40 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| performance_schema_max_sql_text_length | 1024 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 192 |
| performance_schema_max_statement_stack | 10 |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
+----------------------------------------------------------+-------+
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 24.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 24.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
or
OFF
to indicate whether the Performance
Schema is enabled. By default, the value is
ON
. At server startup, you can specify this
variable with no value or a value of ON
or
1 to enable it, or with a value of OFF
or 0
to disable it.
Even when the Performance Schema is disabled, it continues to
populate the global_variables
,
session_variables
,
global_status
, and
session_status
tables. This
occurs as necessary to permit the results for the
SHOW VARIABLES
and
SHOW STATUS
statements to be
drawn from those tables, depending on the setting of the
show_compatibiliy_56
system variable.
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 autoscaling; 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
accounts
table. If this variable
is 0, the Performance Schema does not maintain connection
statistics in the accounts
table
or status variable information in the
status_by_account
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 |
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 the
Performance_schema_digest_lost
status variable.
For more information about statement digesting, see Section 24.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) |
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) |
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) |
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) |
The number of rows per thread in the
events_statements_history
table.
performance_schema_events_transactions_history_long_size
Command-Line Format | --performance-schema-events-transactions-history-long-size=# |
---|---|
System Variable | performance_schema_events_transactions_history_long_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
The number of rows in the
events_transactions_history_long
table.
performance_schema_events_transactions_history_size
Command-Line Format | --performance-schema-events-transactions-history-size=# |
---|---|
System Variable | performance_schema_events_transactions_history_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
The number of rows per thread in the
events_transactions_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) |
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) |
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 autoscaling; 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 hosts
table. If this variable is 0, the Performance Schema does not
maintain connection statistics in the
hosts
table or status variable
information in the status_by_host
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 24.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 autoscaling; do not assign this literal value) |
The maximum number of instrumented condition objects. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_digest_length
Command-Line Format | --performance-schema-max-digest-length=# |
---|---|
System Variable | performance_schema_max_digest_length |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 1024 |
Minimum Value | 0 |
Maximum Value | 1048576 |
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 24.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 | 80 |
Minimum Value | 0 |
Maximum Value | 256 |
The maximum number of file instruments. For information about how to set and use this variable, see Section 24.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 |
The maximum number of opened file objects. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
The value of
performance_schema_max_file_handles
should be greater than the value of
open_files_limit
:
open_files_limit
affects the
maximum number of open file handles the server can support and
performance_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 autoscaling; 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 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_index_stat
Command-Line Format | --performance-schema-max-index-stat=# |
---|---|
System Variable | performance_schema_max_index_stat |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
The maximum number of indexes for which the Performance Schema
maintains statistics. If this maximum is exceeded such that
index statistics are lost, the Performance Schema increments
the
Performance_schema_index_stat_lost
status variable. The default value is autosized using the
value of
performance_schema_max_table_instances
.
performance_schema_max_memory_classes
Command-Line Format | --performance-schema-max-memory-classes=# |
---|---|
System Variable | performance_schema_max_memory_classes |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 320 |
The maximum number of memory instruments. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_metadata_locks
Command-Line Format | --performance-schema-max-metadata-locks=# |
---|---|
System Variable | performance_schema_max_metadata_locks |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autoscaling; do not assign this literal value) |
The maximum number of metadata lock instruments. This value
controls the size of the
metadata_locks
table. If this
maximum is exceeded such that a metadata lock cannot be
instrumented, the Performance Schema increments the
Performance_schema_metadata_lock_lost
status variable.
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 24.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) |
The maximum number of instrumented mutex objects. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_prepared_statements_instances
Command-Line Format | --performance-schema-max-prepared-statements-instances=# |
---|---|
System Variable | performance_schema_max_prepared_statements_instances |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autoscaling; do not assign this literal value) |
The maximum number of rows in the
prepared_statements_instances
table. If this maximum is exceeded such that a prepared
statement cannot be instrumented, the Performance Schema
increments the
Performance_schema_prepared_statements_lost
status variable. For information about how to set and use this
variable, see
Section 24.7, “Performance Schema Status Monitoring”.
The default value of this variable is autosized based on the
value of the
max_prepared_stmt_count
system variable.
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.7.25) | 50 |
Default Value (≤ 5.7.24) | 40 |
Minimum Value | 0 |
Maximum Value | 256 |
The maximum number of rwlock instruments. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_program_instances
Command-Line Format | --performance-schema-max-program-instances=# |
---|---|
System Variable | performance_schema_max_program_instances |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autoscaling; do not assign this literal value) |
The maximum number of stored programs for which the
Performance Schema maintains statistics. If this maximum is
exceeded, the Performance Schema increments the
Performance_schema_program_lost
status variable. For information about how to set and use this
variable, see
Section 24.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 autoscaling; do not assign this literal value) |
The maximum number of instrumented rwlock objects. For information about how to set and use this variable, see Section 24.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 24.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 autoscaling; do not assign this literal value) |
The maximum number of instrumented socket objects. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_sql_text_length
Command-Line Format | --performance-schema-max-sql-text-length=# |
---|---|
System Variable | performance_schema_max_sql_text_length |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 1024 |
Minimum Value | 0 |
Maximum Value | 1048576 |
The maximum number of bytes used to store SQL statements in
the SQL_TEXT
column of the
events_statements_current
,
events_statements_history
, and
events_statements_history_long
statement event tables. Any bytes in excess of
performance_schema_max_sql_text_length
are discarded and do not appear in the
SQL_TEXT
column. Statements differing only
after that many initial bytes are indistinguishable in this
column.
Decreasing the
performance_schema_max_sql_text_length
value reduces memory use but causes more statements to become
indistinguishable if they differ only at the end. Increasing
the value increases memory use but permits longer statements
to be distinguished.
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 24.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 |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
The maximum number of statement instruments. For information about how to set and use this variable, see Section 24.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_statement_stack
Command-Line Format | --performance-schema-max-statement-stack=# |
---|---|
System Variable | performance_schema_max_statement_stack |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 10 |
The maximum depth of nested stored program calls for which the
Performance Schema maintains statistics. When this maximum is
exceeded, the Performance Schema increments the
Performance_schema_nested_statement_lost
status variable for each stored program statement executed.
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 autoscaling; do not assign this literal value) |
The maximum number of opened table objects. This value
controls the size of the
table_handles
table. If this
maximum is exceeded such that a table handle cannot be
instrumented, the Performance Schema increments the
Performance_schema_table_handles_lost
status variable. For information about how to set and use this
variable, see
Section 24.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) |
The maximum number of instrumented table objects. For information about how to set and use this variable, see Section 24.7, “Performance Schema Status Monitoring”.
performance_schema_max_table_lock_stat
Command-Line Format | --performance-schema-max-table-lock-stat=# |
---|---|
System Variable | performance_schema_max_table_lock_stat |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | -1 (signifies autosizing; do not assign this literal value) |
The maximum number of tables for which the Performance Schema
maintains lock statistics. If this maximum is exceeded such
that table lock statistics are lost, the Performance Schema
increments the
Performance_schema_table_lock_stat_lost
status variable.
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 24.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 autoscaling; do not assign this literal value) |
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 the
Performance_schema_thread_instances_lost
status variable. For information about how to set and use this
variable, see
Section 24.7, “Performance Schema Status Monitoring”.
The max_connections
system
variable affects how many threads can run in the server.
performance_schema_max_thread_instances
affects how many of these running threads can be instrumented.
The variables_by_thread
and
status_by_thread
tables contain
system and status variable information only about foreground
threads. If not all threads are instrumented by the
Performance Schema, this table may miss some rows. In this
case, the
Performance_schema_thread_instances_lost
status variable is greater than zero.
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 |
Maximum Value | 1048576 |
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 the
log_error_verbosity
system
variable value is greater than 1.
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 set
performance_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 24.12.9, “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 | -1 (signifies autoscaling; do not assign this literal value) |
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 | -1 (signifies autoscaling; do not assign this literal value) |
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 autoscaling; 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 users
table. If this variable is 0, the Performance Schema does not
maintain connection statistics in the
users
table or status variable
information in the status_by_user
table.