Documentation Home
MySQL Performance Schema
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.8Mb
PDF (A4) - 0.8Mb
HTML Download (TGZ) - 138.6Kb
HTML Download (Zip) - 156.7Kb


MySQL Performance Schema  /  Performance Schema System Variables

Chapter 12 Performance Schema System Variables

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 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 Chapter 4, Performance Schema Startup Configuration.

Performance Schema system variables have the following meanings:

  • performance_schema

    Property Value
    Command-Line Format --performance-schema=#
    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

    Property Value
    Command-Line Format --performance-schema-accounts-size=#
    System Variable performance_schema_accounts_size
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)
    Minimum Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Minimum Value (<= 5.7.5) -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 the accounts table or status variable information in the status_by_account table.

  • performance_schema_digests_size

    Property Value
    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 Performance Schema Statement Digests.

  • performance_schema_events_stages_history_long_size

    Property Value
    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

    Property Value
    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

    Property Value
    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

    Property Value
    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

    Property Value
    Command-Line Format --performance-schema-events-transactions-history-long-size=#
    Introduced 5.7.3
    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

    Property Value
    Command-Line Format --performance-schema-events-transactions-history-size=#
    Introduced 5.7.3
    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

    Property Value
    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

    Property Value
    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.

  • performance_schema_hosts_size

    Property Value
    Command-Line Format --performance-schema-hosts-size=#
    System Variable performance_schema_hosts_size
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)
    Minimum Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Minimum Value (<= 5.7.5) -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 the hosts table or status variable information in the status_by_host table.

  • performance_schema_max_cond_classes

    Property Value
    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.

  • performance_schema_max_cond_instances

    Property Value
    Command-Line Format --performance-schema-max-cond-instances=#
    System Variable performance_schema_max_cond_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented condition objects.

  • performance_schema_max_digest_length

    Property Value
    Command-Line Format --performance-schema-max-digest-length=#
    Introduced 5.7.8
    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 available for storage of normalized statement digest values in the Performance Schema. This variable is related to max_digest_length; see the description of that variable in Server System Variables

    For more information about statement digesting, see Performance Schema Statement Digests.

  • performance_schema_max_file_classes

    Property Value
    Command-Line Format --performance-schema-max-file-classes=#
    System Variable performance_schema_max_file_classes
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.9) 80
    Default Value (<= 5.7.8) 50
    Minimum Value 0
    Maximum Value 256

    The maximum number of file instruments.

  • performance_schema_max_file_handles

    Property Value
    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.

    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

    Property Value
    Command-Line Format --performance-schema-max-file-instances=#
    System Variable performance_schema_max_file_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented file objects.

  • performance_schema_max_index_stat

    Property Value
    Command-Line Format --performance-schema-max-index-stat=#
    Introduced 5.7.6
    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

    Property Value
    Command-Line Format --performance-schema-max-memory-classes=#
    Introduced 5.7.2
    System Variable performance_schema_max_memory_classes
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.5) 320
    Default Value (<= 5.7.4) 250

    The maximum number of memory instruments.

  • performance_schema_max_metadata_locks

    Property Value
    Command-Line Format --performance-schema-max-metadata-locks=#
    Introduced 5.7.3
    System Variable performance_schema_max_metadata_locks
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; 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

    Property Value
    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.

  • performance_schema_max_mutex_instances

    Property Value
    Command-Line Format --performance-schema-max-mutex-instances=#
    System Variable performance_schema_max_mutex_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented mutex objects.

  • performance_schema_max_prepared_statements_instances

    Property Value
    Command-Line Format --performance-schema-max-prepared-statements-instances=#
    Introduced 5.7.4
    System Variable performance_schema_max_prepared_statements_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; 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. 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

    Property Value
    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.3) 40
    Default Value (<= 5.7.2) 30
    Minimum Value 0
    Maximum Value 256

    The maximum number of rwlock instruments.

  • performance_schema_max_program_instances

    Property Value
    Command-Line Format --performance-schema-max-program-instances=#
    Introduced 5.7.2
    System Variable performance_schema_max_program_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) 5000

    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.

  • performance_schema_max_rwlock_instances

    Property Value
    Command-Line Format --performance-schema-max-rwlock-instances=#
    System Variable performance_schema_max_rwlock_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented rwlock objects.

  • performance_schema_max_socket_classes

    Property Value
    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.

  • performance_schema_max_socket_instances

    Property Value
    Command-Line Format --performance-schema-max-socket-instances=#
    System Variable performance_schema_max_socket_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented socket objects.

  • performance_schema_max_sql_text_length

    Property Value
    Command-Line Format --performance-schema-max-sql-text-length=#
    Introduced 5.7.6
    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

    Property Value
    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.

  • performance_schema_max_statement_classes

    Property Value
    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. 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

    Property Value
    Command-Line Format --performance-schema-max-statement-stack=#
    Introduced 5.7.2
    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

    Property Value
    Command-Line Format --performance-schema-max-table-handles=#
    System Variable performance_schema_max_table_handles
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; 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.

  • performance_schema_max_table_instances

    Property Value
    Command-Line Format --performance-schema-max-table-instances=#
    System Variable performance_schema_max_table_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)

    The maximum number of instrumented table objects.

  • performance_schema_max_table_lock_stat

    Property Value
    Command-Line Format --performance-schema-max-table-lock-stat=#
    Introduced 5.7.6
    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

    Property Value
    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.

  • performance_schema_max_thread_instances

    Property Value
    Command-Line Format --performance-schema-max-thread-instances=#
    System Variable performance_schema_max_thread_instances
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; 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.

    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 information only about foreground threads. If not all threads are instrumented by the Performance Schema, this table will miss some rows. In this case, the Performance_schema_thread_instances_lost status variable will be greater than zero.

  • performance_schema_session_connect_attrs_size

    Property Value
    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 will accept. If a client attempts to send more than 64KB of attribute data, the server rejects the connection. For more information, see Section 10.9, “Performance Schema Connection Attribute Tables”.

  • performance_schema_setup_actors_size

    Property Value
    Command-Line Format --performance-schema-setup-actors-size=#
    System Variable performance_schema_setup_actors_size
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) 100

    The number of rows in the setup_actors table.

  • performance_schema_setup_objects_size

    Property Value
    Command-Line Format --performance-schema-setup-objects-size=#
    System Variable performance_schema_setup_objects_size
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) 100

    The number of rows in the setup_objects table.

  • performance_schema_users_size

    Property Value
    Command-Line Format --performance-schema-users-size=#
    System Variable performance_schema_users_size
    Scope Global
    Dynamic No
    Type integer
    Default Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Default Value (<= 5.7.5) -1 (signifies autosizing; do not assign this literal value)
    Minimum Value (>= 5.7.6) -1 (signifies autoscaling; do not assign this literal value)
    Minimum Value (<= 5.7.5) -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 the users table or status variable information in the status_by_user table.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.