29.15 Performance Schema System Variables

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

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_error_size                            | 5377  |
| 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                      | 150   |
| performance_schema_max_cond_instances                    | -1    |
| performance_schema_max_digest_length                     | 1024  |
| performance_schema_max_digest_sample_age                 | 60    |
| performance_schema_max_file_classes                      | 80    |
| performance_schema_max_file_handles                      | 32768 |
| performance_schema_max_file_instances                    | -1    |
| performance_schema_max_index_stat                        | -1    |
| performance_schema_max_memory_classes                    | 470   |
| performance_schema_max_metadata_locks                    | -1    |
| performance_schema_max_meter_classes                     | 30    |
| performance_schema_max_metric_classes                    | 600   |
| performance_schema_max_mutex_classes                     | 350   |
| performance_schema_max_mutex_instances                   | -1    |
| performance_schema_max_prepared_statements_instances     | -1    |
| performance_schema_max_program_instances                 | -1    |
| performance_schema_max_rwlock_classes                    | 100   |
| 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                     | 175   |
| performance_schema_max_statement_classes                 | 220   |
| 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                    | 100   |
| 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_show_processlist                      | OFF   |
| 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 29.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 29.3, “Performance Schema Startup Configuration”.

Performance Schema system variables have the following meanings:

• performance_schema

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. The Performance Schema also populates some of the replication tables when disabled. - performance_schema_accounts_size

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

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 29.10, “Performance Schema Statement Digests and Sampling”. - performance_schema_error_size

The number of instrumented server error codes. The default value is the actual number of server error codes. Although the value can be set anywhere from 0 to its maximum, the intended use is to set it to either its default (to instrument all errors) or 0 (to instrument no errors).

Error information is aggregated in summary tables; see Section 29.12.20.11, “Error Summary Tables”. If an error occurs that is not instrumented, information for the occurrence is aggregated to the NULL row in each summary table; that is, to the row with ERROR_NUMBER=0, ERROR_NAME=NULL, and SQLSTATE=NULL. - performance_schema_events_stages_history_long_size

The number of rows in the events_stages_history_long table. - performance_schema_events_stages_history_size

The number of rows per thread in the events_stages_history table. - performance_schema_events_statements_history_long_size

The number of rows in the events_statements_history_long table. - performance_schema_events_statements_history_size

The number of rows per thread in the events_statements_history table. - performance_schema_events_transactions_history_long_size

The number of rows in the events_transactions_history_long table. - performance_schema_events_transactions_history_size

The number of rows per thread in the events_transactions_history table. - performance_schema_events_waits_history_long_size

The number of rows in the events_waits_history_long table. - performance_schema_events_waits_history_size

The number of rows per thread in the events_waits_history table. - performance_schema_hosts_size

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

The maximum number of condition instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_cond_instances

The maximum number of instrumented condition objects. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_digest_length

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 7.1.8, “Server System Variables”.

For more information about statement digesting, including considerations regarding memory use, see Section 29.10, “Performance Schema Statement Digests and Sampling”. - performance_schema_max_digest_sample_age

This variable affects statement sampling for the events_statements_summary_by_digest table. When a new table row is inserted, the statement that produced the row digest value is stored as the current sample statement associated with the digest. Thereafter, when the server sees other statements with the same digest value, it determines whether to use the new statement to replace the current sample statement (that is, whether to resample). Resampling policy is based on the comparative wait times of the current sample statement and new statement and, optionally, the age of the current sample statement:

• Resampling based on wait times: If the new statement wait time has a wait time greater than that of the current sample statement, it becomes the current sample statement.
• Resampling based on age: If the performance_schema_max_digest_sample_age system variable has a value greater than zero and the current sample statement is more than that many seconds old, the current statement is considered “too old” and the new statement replaces it. This occurs even if the new statement wait time is less than that of the current sample statement.

For information about statement sampling, see Section 29.10, “Performance Schema Statement Digests and Sampling”. - performance_schema_max_file_classes

The maximum number of file instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_file_handles

The maximum number of opened file objects. For information about how to set and use this variable, see Section 29.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

The maximum number of instrumented file objects. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_index_stat

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

The maximum number of memory instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_metadata_locks

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_meter_classes

Maximum number of meter instruments which can be created - performance_schema_max_metric_classes

Maximum number of metric instruments which can be created. - performance_schema_max_mutex_classes

The maximum number of mutex instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_mutex_instances

The maximum number of instrumented mutex objects. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_prepared_statements_instances

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 29.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

The maximum number of rwlock instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_program_instances

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 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_rwlock_instances

The maximum number of instrumented rwlock objects. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_socket_classes

The maximum number of socket instruments. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_socket_instances

The maximum number of instrumented socket objects. For information about how to set and use this variable, see Section 29.7, “Performance Schema Status Monitoring”. - performance_schema_max_sql_text_length

The maximum number of bytes used to store SQL statements. The value applies to storage required for these columns:

• The SQL_TEXT column of the events_statements_current, events_statements_history, and events_statements_history_long statement event tables.
• The QUERY_SAMPLE_TEXT column of the events_statements_summary_by_digest summary table.

Any bytes in excess of performance_schema_max_sql_text_length are discarded and do not appear in the column. Statements differing only after that many initial bytes are indistinguishable in the 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

The maximum number of stage instruments. For information about how to set and use this v

（本文超出站点单文档体积上限，已截断）