Menu:
sys-schema-functions:: sys Schema Stored Functions
The *note 'sys': sys-schema. schema includes tables and triggers, views, and stored procedures and functions. The following sections provide details for each of these objects.
File: manual.info.tmp, Node: sys-schema-object-index, Next: sys-schema-tables, Prev: sys-schema-reference, Up: sys-schema-reference
The following tables list *note 'sys': sys-schema. schema objects and provide a short description of each one.
sys Schema Tables and Triggers
Table or Trigger Description Name
*note 'sys_config': sys-sys-config.sys schema configuration options table
*note 'sys_config_insert_set_user': sys-sys-config-insert-set-user.sys_config insert trigger
*note 'sys_config_update_set_user': sys-sys-config-update-set-user.sys_config update trigger
sys Schema Views
View Name Description Deprecated
*note 'host_summary': ('x$host_summary')sys-host-summary.
Statement activity, file I/O, and connections, grouped by host
*note 'host_summary_by_file_io': ('x$host_summary_by_file_io')sys-host-summary-by-file-io.
File I/O, grouped by host
*note 'host_summary_by_file_io_type': ('x$host_summary_by_file_io_type')sys-host-summary-by-file-io-type.
File I/O, grouped by host and event type
*note 'host_summary_by_stages': ('x$host_summary_by_stages')sys-host-summary-by-stages.
Statement stages, grouped by host
*note 'host_summary_by_statement_latency': ('x$host_summary_by_statement_latency')sys-host-summary-by-statement-latency.
Statement statistics, grouped by host
*note 'host_summary_by_statement_type': ('x$host_summary_by_statement_type')sys-host-summary-by-statement-type.
Statements executed, grouped by host and statement
*note 'innodb_buffer_stats_by_schema': ('x$innodb_buffer_stats_by_schema')sys-innodb-buffer-stats-by-schema.
InnoDB buffer information, grouped by schema
*note 'innodb_buffer_stats_by_table': ('x$innodb_buffer_stats_by_table')sys-innodb-buffer-stats-by-table.
InnoDB buffer information, grouped by schema and table
*note 'innodb_lock_waits': ('x$innodb_lock_waits')sys-innodb-lock-waits.
InnoDB lock information
*note 'io_by_thread_by_latency': ('x$io_by_thread_by_latency')sys-io-by-thread-by-latency.
I/O consumers, grouped by thread
*note 'io_global_by_file_by_bytes': ('x$io_global_by_file_by_bytes')sys-io-global-by-file-by-bytes.
Global I/O consumers, grouped by file and bytes
*note 'io_global_by_file_by_latency': ('x$io_global_by_file_by_latency')sys-io-global-by-file-by-latency.
Global I/O consumers, grouped by file and latency
*note 'io_global_by_wait_by_bytes': ('x$io_global_by_wait_by_bytes')sys-io-global-by-wait-by-bytes.
Global I/O consumers, grouped by bytes
*note 'io_global_by_wait_by_latency': ('x$io_global_by_wait_by_latency')sys-io-global-by-wait-by-latency.
Global I/O consumers, grouped by latency
*note 'latest_file_io': ('x$latest_file_io')sys-latest-file-io.
Most recent I/O, grouped by file and thread
*note 'memory_by_host_by_current_bytes': ('x$memory_by_host_by_current_bytes')sys-memory-by-host-by-current-bytes.
Memory use, grouped by host
*note 'memory_by_thread_by_current_bytes': ('x$memory_by_thread_by_current_bytes')sys-memory-by-thread-by-current-bytes.
Memory use, grouped by thread
*note 'memory_by_user_by_current_bytes': ('x$memory_by_user_by_current_bytes')sys-memory-by-user-by-current-bytes.
Memory use, grouped by user
*note 'memory_global_by_current_bytes': ('x$memory_global_by_current_bytes')sys-memory-global-by-current-bytes.
Memory use, grouped by allocation type
*note 'memory_global_total': ('x$memory_global_total')sys-memory-global-total.
Total memory use
*note 'metrics': sys-metrics.
Server metrics
*note 'processlist': ('x$processlist')sys-processlist.
Processlist information
*note 'ps_check_lost_instrumentation': sys-ps-check-lost-instrumentation.
Variables that have lost instruments
*note 'schema_auto_increment_columns': sys-schema-auto-increment-columns.
AUTO_INCREMENT column information
*note 'schema_index_statistics': ('x$schema_index_statistics')sys-schema-index-statistics.
Index statistics
*note 'schema_object_overview': sys-schema-object-overview.
Types of objects within each schema
*note 'schema_redundant_indexes': sys-schema-redundant-indexes.
Duplicate or redundant indexes
*note 'schema_table_lock_waits': ('x$schema_table_lock_waits')sys-schema-table-lock-waits.
Sessions waiting for metadata locks
*note 'schema_table_statistics': ('x$schema_table_statistics')sys-schema-table-statistics.
Table statistics
*note 'schema_table_statistics_with_buffer': ('x$schema_table_statistics_with_buffer')sys-schema-table-statistics-with-buffer.
Table statistics, including InnoDB buffer pool statistics
*note 'schema_tables_with_full_table_scans': ('x$schema_tables_with_full_table_scans')sys-schema-tables-with-full-table-scans.
Tables being accessed with full scans
*note 'schema_unused_indexes': sys-schema-unused-indexes.
Indexes not in active use
*note 'session': ('x$session')sys-session.
Processlist information for user sessions
*note 'session_ssl_status': sys-session-ssl-status.
Connection SSL information
*note 'statement_analysis': ('x$statement_analysis')sys-statement-analysis.
Statement aggregate statistics
*note 'statements_with_errors_or_warnings': ('x$statements_with_errors_or_warnings')sys-statements-with-errors-or-warnings.
Statements that have produced errors or warnings
*note 'statements_with_full_table_scans': ('x$statements_with_full_table_scans')sys-statements-with-full-table-scans.
Statements that have done full table scans
*note 'statements_with_runtimes_in_95th_percentile': ('x$statements_with_runtimes_in_95th_percentile')sys-statements-with-runtimes-in-95th-percentile.
Statements with highest average runtime
*note 'statements_with_sorting': ('x$statements_with_sorting')sys-statements-with-sorting.
Statements that performed sorts
*note 'statements_with_temp_tables': ('x$statements_with_temp_tables')sys-statements-with-temp-tables.
Statements that used temporary tables
*note 'user_summary': ('x$user_summary')sys-user-summary.
User statement and connection activity
*note 'user_summary_by_file_io': ('x$user_summary_by_file_io')sys-user-summary-by-file-io.
File I/O, grouped by user
*note 'user_summary_by_file_io_type': ('x$user_summary_by_file_io_type')sys-user-summary-by-file-io-type.
File I/O, grouped by user and event
*note 'user_summary_by_stages': ('x$user_summary_by_stages')sys-user-summary-by-stages.
Stage events, grouped by user
*note 'user_summary_by_statement_latency': ('x$user_summary_by_statement_latency')sys-user-summary-by-statement-latency.
Statement statistics, grouped by user
*note 'user_summary_by_statement_type': ('x$user_summary_by_statement_type')sys-user-summary-by-statement-type.
Statements executed, grouped by user and statement
*note 'version': sys-version.
Current sys 5.7.28 schema and MySQL
server versions
*note 'wait_classes_global_by_avg_latency': ('x$wait_classes_global_by_avg_latency')sys-wait-classes-global-by-avg-latency.
Wait class average latency, grouped by event class
*note 'wait_classes_global_by_latency': ('x$wait_classes_global_by_latency')sys-wait-classes-global-by-latency.
Wait class total latency, grouped by event class
*note 'waits_by_host_by_latency': ('x$waits_by_host_by_latency')sys-waits-by-host-by-latency.
Wait events, grouped by host and event
*note 'waits_by_user_by_latency': ('x$waits_by_user_by_latency')sys-waits-by-user-by-latency.
Wait events, grouped by user and event
*note 'waits_global_by_latency': ('x$waits_global_by_latency')sys-waits-global-by-latency.
Wait events, grouped by event
*note 'x$ps_digest_95th_percentile_by_avg_us': sys-statements-with-runtimes-in-95th-percentile.
Helper view for 95th-percentile views
*note 'x$ps_digest_avg_latency_distribution': sys-statements-with-runtimes-in-95th-percentile.
Helper view for 95th-percentile views
*note 'x$ps_schema_table_statistics_io': sys-schema-table-statistics.
Helper view for table-statistics views
*note 'x$schema_flattened_keys': sys-schema-redundant-indexes.
Helper view for schema_redundant_indexes
sys Schema Stored Procedures
Procedure Name Description
*note 'create_synonym_db()': sys-create-synonym-db.Create synonym for schema
*note 'diagnostics()': sys-diagnostics.Collect system diagnostic information
*note 'execute_prepared_stmt()': sys-execute-prepared-stmt.Execute prepared statement
*note 'ps_setup_disable_background_threads()': sys-ps-setup-disable-background-threads.Disable background thread instrumentation
*note 'ps_setup_disable_consumer()': sys-ps-setup-disable-consumer.Disable consumers
*note 'ps_setup_disable_instrument()': sys-ps-setup-disable-instrument.Disable instruments
*note 'ps_setup_disable_thread()': sys-ps-setup-disable-thread.Disable instrumentation for thread
*note 'ps_setup_enable_background_threads()': sys-ps-setup-enable-background-threads.Enable background thread instrumentation
*note 'ps_setup_enable_consumer()': sys-ps-setup-enable-consumer.Enable consumers
*note 'ps_setup_enable_instrument()': sys-ps-setup-enable-instrument.Enable instruments
*note 'ps_setup_enable_thread()': sys-ps-setup-enable-thread.Enable instrumentation for thread
*note 'ps_setup_reload_saved()': sys-ps-setup-reload-saved.Reload saved Performance Schema configuration
*note 'ps_setup_reset_to_default()': sys-ps-setup-reset-to-default.Reset saved Performance Schema configuration
*note 'ps_setup_save()': sys-ps-setup-save.Save Performance Schema configuration
*note 'ps_setup_show_disabled()': sys-ps-setup-show-disabled.Display disabled Performance Schema configuration
*note 'ps_setup_show_disabled_consumers()': sys-ps-setup-show-disabled-consumers.Display disabled Performance Schema consumers
*note 'ps_setup_show_disabled_instruments()': sys-ps-setup-show-disabled-instruments.Display disabled Performance Schema instruments
*note 'ps_setup_show_enabled()': sys-ps-setup-show-enabled.Display enabled Performance Schema configuration
*note 'ps_setup_show_enabled_consumers()': sys-ps-setup-show-enabled-consumers.Display enabled Performance Schema consumers
*note 'ps_setup_show_enabled_instruments()': sys-ps-setup-show-enabled-instruments.Display enabled Performance Schema instruments
*note 'ps_statement_avg_latency_histogram()': sys-ps-statement-avg-latency-histogram.Display statement latency histogram
*note 'ps_trace_statement_digest()': sys-ps-trace-statement-digest.Trace Performance Schema instrumentation for digest
*note 'ps_trace_thread()': sys-ps-trace-thread.Dump Performance Schema data for thread
*note 'ps_truncate_all_tables()': sys-ps-truncate-all-tables.Truncate Performance Schema summary tables
*note 'statement_performance_analyzer()': sys-statement-performance-analyzer.Report of statements running on server
*note 'table_exists()': sys-table-exists.Whether a table exists
sys Schema Stored Functions
Function Name Description Introduced
*note 'extract_schema_from_file_name()': sys-extract-schema-from-file-name.
Extract schema name part of file name
*note 'extract_table_from_file_name()': sys-extract-table-from-file-name.
Extract table name part of file name
*note 'format_bytes()': sys-format-bytes.
Convert byte count to value with units
*note 'format_path()': sys-format-path.
Replace directories in path name with symbolic system variable names
*note 'format_statement()': sys-format-statement.
Truncate long statement to fixed length
*note 'format_time()': sys-format-time.
Convert picoseconds time to value with units
*note 'list_add()': sys-list-add.
Add item to list
*note 'list_drop()': sys-list-drop.
Remove item from list
*note 'ps_is_account_enabled()': sys-ps-is-account-enabled.
Whether Performance Schema instrumentation for account is enabled
*note 'ps_is_consumer_enabled()': sys-ps-is-consumer-enabled.
Whether Performance Schema consumer is enabled
*note 'ps_is_instrument_default_enabled()': sys-ps-is-instrument-default-enabled.
Whether Performance Schema instrument is enabled by default
*note 'ps_is_instrument_default_timed()': sys-ps-is-instrument-default-timed.
Whether Performance Schema instrument is timed by default
*note 'ps_is_thread_instrumented()': sys-ps-is-thread-instrumented.
Whether Performance Schema instrumentation for connection ID is enabled
*note 'ps_thread_account()': sys-ps-thread-account.
Account associated with Performance Schema thread ID
*note 'ps_thread_id()': sys-ps-thread-id.
Performance Schema thread ID associated with connection ID
*note 'ps_thread_stack()': sys-ps-thread-stack.
Event information for connection ID
*note 'ps_thread_trx_info()': sys-ps-thread-trx-info.
Transaction information for thread ID
*note 'quote_identifier()': sys-quote-identifier.
Quote string as 5.7.14 identifier
*note 'sys_get_config()': sys-sys-get-config.
sys schema configuration option value
*note 'version_major()': sys-version-major.
MySQL server major version number
*note 'version_minor()': sys-version-minor.
MySQL server minor version number
*note 'version_patch()': sys-version-patch.
MySQL server patch release version number
File: manual.info.tmp, Node: sys-schema-tables, Next: sys-schema-views, Prev: sys-schema-object-index, Up: sys-schema-reference
Menu:
sys-sys-config-update-set-user:: The sys_config_update_set_user Trigger
The following sections describe *note 'sys': sys-schema. schema tables and triggers.
File: manual.info.tmp, Node: sys-sys-config, Next: sys-sys-config-insert-set-user, Prev: sys-schema-tables, Up: sys-schema-tables
26.4.2.1 The sys_config Table .............................
This table contains *note 'sys': sys-schema. schema configuration options, one row per option. Configuration changes made by updating this table persist across client sessions and server restarts.
The *note 'sys_config': sys-sys-config. table has these columns:
'variable'
The configuration option name.
'value'
The configuration option value.
'set_time'
The timestamp of the most recent modification to the row.
'set_by'
The account that made the most recent modification to the row. The value is 'NULL' if the row has not been changed since the *note 'sys': sys-schema. schema was installed.
As an efficiency measure to minimize the number of direct reads from the note 'sys_config': sys-sys-config. table, note 'sys': sys-schema. schema functions that use a value from this table check for a user-defined variable with a corresponding name, which is the user-defined variable having the same name plus a '@sys.' prefix. (For example, the variable corresponding to the 'diagnostics.include_raw' option is '@sys.diagnostics.include_raw'.) If the user-defined variable exists in the current session and is non-'NULL', the function uses its value in preference to the value in the *note 'sys_config': sys-sys-config. table. Otherwise, the function reads and uses the value from the table. In the latter case, the calling function conventionally also sets the corresponding user-defined variable to the table value so that further references to the configuration option within the same session use the variable and need not read the table again.
For example, the 'statement_truncate_len' option controls the maximum length of statements returned by the *note 'format_statement()': sys-format-statement. function. The default is 64. To temporarily change the value to 32 for your current session, set the corresponding '@sys.statement_truncate_len' user-defined variable:
mysql> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
mysql> SELECT sys.format_statement(@stmt);
+----------------------------------------------------------+
| sys.format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
mysql> SET @sys.statement_truncate_len = 32;
mysql> SELECT sys.format_statement(@stmt);
+-----------------------------------+
| sys.format_statement(@stmt) |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+Subsequent invocations of *note 'format_statement()': sys-format-statement. within the session continue to use the user-defined variable value (32), rather than the value stored in the table (64).
To stop using the user-defined variable and revert to using the value in the table, set the variable to 'NULL' within your session:
mysql> SET @sys.statement_truncate_len = NULL;
mysql> SELECT sys.format_statement(@stmt);
+----------------------------------------------------------+
| sys.format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+Alternatively, end your current session (causing the user-defined variable to no longer exist) and begin a new session.
The conventional relationship just described between options in the *note 'sys_config': sys-sys-config. table and user-defined variables can be exploited to make temporary configuration changes that end when your session ends. However, if you set a user-defined variable and then subsequently change the corresponding table value within the same session, the changed table value is not used in that session as long as the user-defined variable exists and is not 'NULL'. (The changed table value is used in other sessions that do not have the user-defined variable assigned.)
The following list describes the options in the *note 'sys_config': sys-sys-config. table and the corresponding user-defined variables:
'diagnostics.allow_i_s_tables', '@sys.diagnostics.allow_i_s_tables'
If this option is 'ON', the note 'diagnostics()': sys-diagnostics. procedure is permitted to perform table scans on the Information Schema note 'TABLES': information-schema-tables-table. table. This can be expensive if there are many tables. The default is 'OFF'.
'diagnostics.include_raw', '@sys.diagnostics.include_raw'
If this option is 'ON', the note 'diagnostics()': sys-diagnostics. procedure includes the raw output from querying the note 'metrics': sys-metrics. view. The default is 'OFF'.
'ps_thread_trx_info.max_length', '@sys.ps_thread_trx_info.max_length'
The maximum length for JSON output produced by the *note 'ps_thread_trx_info()': sys-ps-thread-trx-info. function. The default is 65535.
'statement_performance_analyzer.limit', '@sys.statement_performance_analyzer.limit'
The maximum number of rows to return for views that have no built-in limit. (For example, the *note 'statements_with_runtimes_in_95th_percentile': sys-statements-with-runtimes-in-95th-percentile. view has a built-in limit in the sense that it returns only statements with average execution time in the 95th percentile.) The default is'statement_performance_analyzer.view', '@sys.statement_performance_analyzer.view'
The custom query or view to be used by the note 'statement_performance_analyzer()': sys-statement-performance-analyzer. procedure (which is itself invoked by the note 'diagnostics()': sys-diagnostics. procedure). If the option value contains a space, it is interpreted as a query. Otherwise, it must be the name of an existing view that queries the Performance Schema *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. table. There cannot be any 'LIMIT' clause in the query or view definition if the 'statement_performance_analyzer.limit' configuration option is greater than 0. The default is 'NULL' (no custom view defined).
'statement_truncate_len', '@sys.statement_truncate_len'
The maximum length of statements returned by the *note 'format_statement()': sys-format-statement. function. Longer statements are truncated to this length. The default is 64.
Other options can be added to the note 'sys_config': sys-sys-config. table. For example, the note 'diagnostics()': sys-diagnostics. and note 'execute_prepared_stmt()': sys-execute-prepared-stmt. procedures use the 'debug' option if it exists, but this option is not part of the note 'sys_config': sys-sys-config. table by default because debug output normally is enabled only temporarily, by setting the corresponding '@sys.debug' user-defined variable. To enable debug output without having to set that variable in individual sessions, add the option to the table:
mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON');To change the debug setting in the table, do two things. First, modify the value in the table itself:
mysql> UPDATE sys.sys_config
SET value = 'OFF'
WHERE variable = 'debug';Second, to also ensure that procedure invocations within the current session use the changed value from the table, set the corresponding user-defined variable to 'NULL':
mysql> SET @sys.debug = NULL;File: manual.info.tmp, Node: sys-sys-config-insert-set-user, Next: sys-sys-config-update-set-user, Prev: sys-sys-config, Up: sys-schema-tables
26.4.2.2 The sys_config_insert_set_user Trigger ...............................................
For rows added to the note 'sys_config': sys-sys-config. table by 'INSERT' statements, the note 'sys_config_insert_set_user': sys-sys-config-insert-set-user. trigger sets the 'set_by' column to the current user.
File: manual.info.tmp, Node: sys-sys-config-update-set-user, Prev: sys-sys-config-insert-set-user, Up: sys-schema-tables
26.4.2.3 The sys_config_update_set_user Trigger ...............................................
The note 'sys_config_update_set_user': sys-sys-config-update-set-user. trigger for the note 'sys_config': sys-sys-config. table is similar to the note 'sys_config_insert_set_user': sys-sys-config-insert-set-user. trigger, but for note 'UPDATE': update. statements.
File: manual.info.tmp, Node: sys-schema-views, Next: sys-schema-procedures, Prev: sys-schema-tables, Up: sys-schema-reference
Menu:
sys-waits-global-by-latency:: The waits_global_by_latency and x$waits_global_by_latency Views
The following sections describe *note 'sys': sys-schema. schema views.
The 'sys' schema contains many views that summarize Performance Schema tables in various ways. Most of these views come in pairs, such that one member of the pair has the same name as the other member, plus a 'x$' prefix. For example, the *note 'host_summary_by_file_io': sys-host-summary-by-file-io. view summarizes file I/O grouped by host and displays latencies converted from picoseconds to more readable values (with units);
mysql> SELECT * FROM sys.host_summary_by_file_io;
+------------+-------+------------+
| host | ios | io_latency |
+------------+-------+------------+
| localhost | 67570 | 5.38 s |
| background | 3468 | 4.18 s |
+------------+-------+------------+The *note 'x$host_summary_by_file_io': sys-host-summary-by-file-io. view summarizes the same data but displays unformatted picosecond latencies:
mysql> SELECT * FROM sys.x$host_summary_by_file_io;
+------------+-------+---------------+
| host | ios | io_latency |
+------------+-------+---------------+
| localhost | 67574 | 5380678125144 |
| background | 3474 | 4758696829416 |
+------------+-------+---------------+The view without the 'x′prefixisintendedtoprovideoutputthatismoreuserfriendlyandeasiertoread.Theviewwiththe′x' prefix that displays the same values in raw form is intended more for use with other tools that perform their own processing on the data.
Views without the 'x′prefixdifferfromthecorresponding′x' views in these ways:
Byte counts are formatted with size units using *note 'format_bytes()': sys-format-bytes.
Time values are formatted with temporal units using *note 'format_time()': sys-format-time.
SQL statements are truncated to a maximum display width using *note 'format_statement()': sys-format-statement.
Path name are shortened using *note 'format_path()': sys-format-path.
File: manual.info.tmp, Node: sys-host-summary, Next: sys-host-summary-by-file-io, Prev: sys-schema-views, Up: sys-schema-views
26.4.3.1 The host_summary and x$host_summary Views ..................................................
These views summarize statement activity, file I/O, and connections, grouped by host.
The note 'host_summary': sys-host-summary. and note 'x$host_summary': sys-host-summary. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'statements'
The total number of statements for the host.
'statement_latency'
The total wait time of timed statements for the host.
'statement_avg_latency'
The average wait time per timed statement for the host.
'table_scans'
The total number of table scans for the host.
'file_ios'
The total number of file I/O events for the host.
'file_io_latency'
The total wait time of timed file I/O events for the host.
'current_connections'
The current number of connections for the host.
'total_connections'
The total number of connections for the host.
'unique_users'
The number of distinct users for the host.
'current_memory'
The current amount of allocated memory for the host.
'total_memory_allocated'
The total amount of allocated memory for the host.
File: manual.info.tmp, Node: sys-host-summary-by-file-io, Next: sys-host-summary-by-file-io-type, Prev: sys-host-summary, Up: sys-schema-views
26.4.3.2 The host_summary_by_file_io and x$host_summary_by_file_io Views ........................................................................
These views summarize file I/O, grouped by host. By default, rows are sorted by descending total file I/O latency.
The note 'host_summary_by_file_io': sys-host-summary-by-file-io. and note 'x$host_summary_by_file_io': sys-host-summary-by-file-io. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'ios'
The total number of file I/O events for the host.
'io_latency'
The total wait time of timed file I/O events for the host.
File: manual.info.tmp, Node: sys-host-summary-by-file-io-type, Next: sys-host-summary-by-stages, Prev: sys-host-summary-by-file-io, Up: sys-schema-views
26.4.3.3 The host_summary_by_file_io_type and x$host_summary_by_file_io_type Views ..................................................................................
These views summarize file I/O, grouped by host and event type. By default, rows are sorted by host and descending total I/O latency.
The note 'host_summary_by_file_io_type': sys-host-summary-by-file-io-type. and note 'x$host_summary_by_file_io_type': sys-host-summary-by-file-io-type. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'event_name'
The file I/O event name.
'total'
The total number of occurrences of the file I/O event for the host.
'total_latency'
The total wait time of timed occurrences of the file I/O event for the host.
'max_latency'
The maximum single wait time of timed occurrences of the file I/O event for the host.
File: manual.info.tmp, Node: sys-host-summary-by-stages, Next: sys-host-summary-by-statement-latency, Prev: sys-host-summary-by-file-io-type, Up: sys-schema-views
26.4.3.4 The host_summary_by_stages and x$host_summary_by_stages Views ......................................................................
These views summarize statement stages, grouped by host. By default, rows are sorted by host and descending total latency.
The note 'host_summary_by_stages': sys-host-summary-by-stages. and note 'x$host_summary_by_stages': sys-host-summary-by-stages. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'event_name'
The stage event name.
'total'
The total number of occurrences of the stage event for the host.
'total_latency'
The total wait time of timed occurrences of the stage event for the host.
'avg_latency'
The average wait time per timed occurrence of the stage event for the host.
File: manual.info.tmp, Node: sys-host-summary-by-statement-latency, Next: sys-host-summary-by-statement-type, Prev: sys-host-summary-by-stages, Up: sys-schema-views
26.4.3.5 The host_summary_by_statement_latency and x$host_summary_by_statement_latency Views ............................................................................................
These views summarize overall statement statistics, grouped by host. By default, rows are sorted by descending total latency.
The note 'host_summary_by_statement_latency': sys-host-summary-by-statement-latency. and note 'x$host_summary_by_statement_latency': sys-host-summary-by-statement-latency. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'total'
The total number of statements for the host.
'total_latency'
The total wait time of timed statements for the host.
'max_latency'
The maximum single wait time of timed statements for the host.
'lock_latency'
The total time waiting for locks by timed statements for the host.
'rows_sent'
The total number of rows returned by statements for the host.
'rows_examined'
The total number of rows read from storage engines by statements for the host.
'rows_affected'
The total number of rows affected by statements for the host.
'full_scans'
The total number of full table scans by statements for the host.
File: manual.info.tmp, Node: sys-host-summary-by-statement-type, Next: sys-innodb-buffer-stats-by-schema, Prev: sys-host-summary-by-statement-latency, Up: sys-schema-views
26.4.3.6 The host_summary_by_statement_type and x$host_summary_by_statement_type Views ......................................................................................
These views summarize informaion about statements executed, grouped by host and statement type. By default, rows are sorted by host and descending total latency.
The note 'host_summary_by_statement_type': sys-host-summary-by-statement-type. and note 'x$host_summary_by_statement_type': sys-host-summary-by-statement-type. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'statement'
The final component of the statement event name.
'total'
The total number of occurrences of the statement event for the host.
'total_latency'
The total wait time of timed occurrences of the statement event for the host.
'max_latency'
The maximum single wait time of timed occurrences of the statement event for the host.
'lock_latency'
The total time waiting for locks by timed occurrences of the statement event for the host.
'rows_sent'
The total number of rows returned by occurrences of the statement event for the host.
'rows_examined'
The total number of rows read from storage engines by occurrences of the statement event for the host.
'rows_affected'
The total number of rows affected by occurrences of the statement event for the host.
'full_scans'
The total number of full table scans by occurrences of the statement event for the host.
File: manual.info.tmp, Node: sys-innodb-buffer-stats-by-schema, Next: sys-innodb-buffer-stats-by-table, Prev: sys-host-summary-by-statement-type, Up: sys-schema-views
26.4.3.7 The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema Views ....................................................................................
These views summarize the information in the 'INFORMATION_SCHEMA' *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table, grouped by schema. By default, rows are sorted by descending buffer size.
Warning:
Querying views that access the *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table can affect performance. Do not query these views on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.
The note 'innodb_buffer_stats_by_schema': sys-innodb-buffer-stats-by-schema. and note 'x$innodb_buffer_stats_by_schema': sys-innodb-buffer-stats-by-schema. views have these columns:
'object_schema'
The schema name for the object, or 'InnoDB System' if the table belongs to the 'InnoDB' storage engine.
'allocated'
The total number of bytes allocated for the schema.
'data'
The total number of data bytes allocated for the schema.
'pages'
The total number of pages allocated for the schema.
'pages_hashed'
The total number of hashed pages allocated for the schema.
'pages_old'
The total number of old pages allocated for the schema.
'rows_cached'
The total number of cached rows for the schema.
File: manual.info.tmp, Node: sys-innodb-buffer-stats-by-table, Next: sys-innodb-lock-waits, Prev: sys-innodb-buffer-stats-by-schema, Up: sys-schema-views
26.4.3.8 The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table Views ..................................................................................
These views summarize the information in the 'INFORMATION_SCHEMA' *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table, grouped by schema and table. By default, rows are sorted by descending buffer size.
Warning:
Querying views that access the *note 'INNODB_BUFFER_PAGE': information-schema-innodb-buffer-page-table. table can affect performance. Do not query these views on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.
The note 'innodb_buffer_stats_by_table': sys-innodb-buffer-stats-by-table. and note 'x$innodb_buffer_stats_by_table': sys-innodb-buffer-stats-by-table. views have these columns:
'object_schema'
The schema name for the object, or 'InnoDB System' if the table belongs to the 'InnoDB' storage engine.
'object_name'
The table name.
'allocated'
The total number of bytes allocated for the table.
'data'
The number of data bytes allocated for the table.
'pages'
The total number of pages allocated for the table.
'pages_hashed'
The number of hashed pages allocated for the table.
'pages_old'
The number of old pages allocated for the table.
'rows_cached'
The number of cached rows for the table.
File: manual.info.tmp, Node: sys-innodb-lock-waits, Next: sys-io-by-thread-by-latency, Prev: sys-innodb-buffer-stats-by-table, Up: sys-schema-views
26.4.3.9 The innodb_lock_waits and x$innodb_lock_waits Views ............................................................
These views summarize the 'InnoDB' locks that transactions are waiting for. By default, rows are sorted by descending lock age.
The note 'innodb_lock_waits': sys-innodb-lock-waits. and note 'x$innodb_lock_waits': sys-innodb-lock-waits. views have these columns:
'wait_started'
The time at which the lock wait started.
'wait_age'
How long the lock has been waited for, as a *note 'TIME': time. value.
'wait_age_secs'
How long the lock has been waited for, in seconds.
'locked_table'
The name of the locked table. This column contains combined schema/table name values.
'locked_index'
The name of the locked index.
'locked_type'
The type of the waiting lock.
'waiting_trx_id'
The ID of the waiting transaction.
'waiting_trx_started'
The time at which the waiting transaction started.
'waiting_trx_age'
How long the waiting transaction has been waiting, as a *note 'TIME': time. value.
'waiting_trx_rows_locked'
The number of rows locked by the waiting transaction.
'waiting_trx_rows_modified'
The number of rows modified by the waiting transaction.
'waiting_pid'
The processlist ID of the waiting transaction.
'waiting_query'
The statement that is waiting for the lock.
'waiting_lock_id'
The ID of the waiting lock.
'waiting_lock_mode'
The mode of the waiting lock.
'blocking_trx_id'
The ID of the transaction that is blocking the waiting lock.
'blocking_pid'
The processlist ID of the blocking transaction.
'blocking_query'
The statement the blocking transaction is executing. This field reports NULL if the session that issued the blocking query becomes idle. For more information, see *note innodb-information-schema-examples-null-blocking-query::.
'blocking_lock_id'
The ID of the lock that is blocking the waiting lock.
'blocking_lock_mode'
The mode of the lock that is blocking the waiting lock.
'blocking_trx_started'
The time at which the blocking transaction started.
'blocking_trx_age'
How long the blocking transaction has been executing, as a *note 'TIME': time. value.
'blocking_trx_rows_locked'
The number of rows locked by the blocking transaction.
'blocking_trx_rows_modified'
The number of rows modified by the blocking transaction.
'sql_kill_blocking_query'
The *note 'KILL': kill. statement to execute to kill the blocking statement.
'sql_kill_blocking_connection'
The *note 'KILL': kill. statement to execute to kill the session running the blocking statement.
File: manual.info.tmp, Node: sys-io-by-thread-by-latency, Next: sys-io-global-by-file-by-bytes, Prev: sys-innodb-lock-waits, Up: sys-schema-views
26.4.3.10 The io_by_thread_by_latency and x$io_by_thread_by_latency Views .........................................................................
These views summarize I/O consumers to display time waiting for I/O, grouped by thread. By default, rows are sorted by descending total I/O latency.
The note 'io_by_thread_by_latency': sys-io-by-thread-by-latency. and note 'x$io_by_thread_by_latency': sys-io-by-thread-by-latency. views have these columns:
'user'
For foreground threads, the account associated with the thread. For background threads, the thread name.
'total'
The total number of I/O events for the thread.
'total_latency'
The total wait time of timed I/O events for the thread.
'min_latency'
The minimum single wait time of timed I/O events for the thread.
'avg_latency'
The average wait time per timed I/O event for the thread.
'max_latency'
The maximum single wait time of timed I/O events for the thread.
'thread_id'
The thread ID.
'processlist_id'
For foreground threads, the processlist ID of the thread. For background threads, 'NULL'.
File: manual.info.tmp, Node: sys-io-global-by-file-by-bytes, Next: sys-io-global-by-file-by-latency, Prev: sys-io-by-thread-by-latency, Up: sys-schema-views
26.4.3.11 The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Views ...............................................................................
These views summarize global I/O consumers to display amount of I/O, grouped by file. By default, rows are sorted by descending total I/O (bytes read and written).
The note 'io_global_by_file_by_bytes': sys-io-global-by-file-by-bytes. and note 'x$io_global_by_file_by_bytes': sys-io-global-by-file-by-bytes. views have these columns:
'file'
The file path name.
'count_read'
The total number of read events for the file.
'total_read'
The total number of bytes read from the file.
'avg_read'
The average number of bytes per read from the file.
'count_write'
The total number of write events for the file.
'total_written'
The total number of bytes written to the file.
'avg_write'
The average number of bytes per write to the file.
'total'
The total number of bytes read and written for the file.
'write_pct'
The percentage of total bytes of I/O that were writes.
File: manual.info.tmp, Node: sys-io-global-by-file-by-latency, Next: sys-io-global-by-wait-by-bytes, Prev: sys-io-global-by-file-by-bytes, Up: sys-schema-views
26.4.3.12 The io_global_by_file_by_latency and x$io_global_by_file_by_latency Views ...................................................................................
These views summarize global I/O consumers to display time waiting for I/O, grouped by file. By default, rows are sorted by descending total latency.
The note 'io_global_by_file_by_latency': sys-io-global-by-file-by-latency. and note 'x$io_global_by_file_by_latency': sys-io-global-by-file-by-latency. views have these columns:
'file'
The file path name.
'total'
The total number of I/O events for the file.
'total_latency'
The total wait time of timed I/O events for the file.
'count_read'
The total number of read I/O events for the file.
'read_latency'
The total wait time of timed read I/O events for the file.
'count_write'
The total number of write I/O events for the file.
'write_latency'
The total wait time of timed write I/O events for the file.
'count_misc'
The total number of other I/O events for the file.
'misc_latency'
The total wait time of timed other I/O events for the file.
File: manual.info.tmp, Node: sys-io-global-by-wait-by-bytes, Next: sys-io-global-by-wait-by-latency, Prev: sys-io-global-by-file-by-latency, Up: sys-schema-views
26.4.3.13 The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Views ...............................................................................
These views summarize global I/O consumers to display amount of I/O and time waiting for I/O, grouped by event. By default, rows are sorted by descending total I/O (bytes read and written).
The note 'io_global_by_wait_by_bytes': sys-io-global-by-wait-by-bytes. and note 'x$io_global_by_wait_by_bytes': sys-io-global-by-wait-by-bytes. views have these columns:
'event_name'
The I/O event name, with the 'wait/io/file/' prefix stripped.
'total'
The total number of occurrences of the I/O event.
'total_latency'
The total wait time of timed occurrences of the I/O event.
'min_latency'
The minimum single wait time of timed occurrences of the I/O event.
'avg_latency'
The average wait time per timed occurrence of the I/O event.
'max_latency'
The maximum single wait time of timed occurrences of the I/O event.
'count_read'
The number of read requests for the I/O event.
'total_read'
The number of bytes read for the I/O event.
'avg_read'
The average number of bytes per read for the I/O event.
'count_write'
The number of write requests for the I/O event.
'total_written'
The number of bytes written for the I/O event.
'avg_written'
The average number of bytes per write for the I/O event.
'total_requested'
The total number of bytes read and written for the I/O event.
File: manual.info.tmp, Node: sys-io-global-by-wait-by-latency, Next: sys-latest-file-io, Prev: sys-io-global-by-wait-by-bytes, Up: sys-schema-views
26.4.3.14 The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Views ...................................................................................
These views summarize global I/O consumers to display amount of I/O and time waiting for I/O, grouped by event. By default, rows are sorted by descending total latency.
The note 'io_global_by_wait_by_latency': sys-io-global-by-wait-by-latency. and note 'x$io_global_by_wait_by_latency': sys-io-global-by-wait-by-latency. views have these columns:
'event_name'
The I/O event name, with the 'wait/io/file/' prefix stripped.
'total'
The total number of occurrences of the I/O event.
'total_latency'
The total wait time of timed occurrences of the I/O event.
'avg_latency'
The average wait time per timed occurrence of the I/O event.
'max_latency'
The maximum single wait time of timed occurrences of the I/O event.
'read_latency'
The total wait time of timed read occurrences of the I/O event.
'write_latency'
The total wait time of timed write occurrences of the I/O event.
'misc_latency'
The total wait time of timed other occurrences of the I/O event.
'count_read'
The number of read requests for the I/O event.
'total_read'
The number of bytes read for the I/O event.
'avg_read'
The average number of bytes per read for the I/O event.
'count_write'
The number of write requests for the I/O event.
'total_written'
The number of bytes written for the I/O event.
'avg_written'
The average number of bytes per write for the I/O event.
File: manual.info.tmp, Node: sys-latest-file-io, Next: sys-memory-by-host-by-current-bytes, Prev: sys-io-global-by-wait-by-latency, Up: sys-schema-views
26.4.3.15 The latest_file_io and x$latest_file_io Views .......................................................
These views summarize file I/O activity, grouped by file and thread. By default, rows are sorted with most recent I/O first.
The note 'latest_file_io': sys-latest-file-io. and note 'x$latest_file_io': sys-latest-file-io. views have these columns:
'thread'
For foreground threads, the account associated with the thread (and port number for TCP/IP connections). For background threads, the thread name and thread ID
'file'
The file path name.
'latency'
The wait time of the file I/O event.
'operation'
The type of operation.
'requested'
The number of data bytes requested for the file I/O event.
File: manual.info.tmp, Node: sys-memory-by-host-by-current-bytes, Next: sys-memory-by-thread-by-current-bytes, Prev: sys-latest-file-io, Up: sys-schema-views
26.4.3.16 The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes Views .........................................................................................
These views summarize memory use, grouped by host. By default, rows are sorted by descending amount of memory used.
The note 'memory_by_host_by_current_bytes': sys-memory-by-host-by-current-bytes. and note 'x$memory_by_host_by_current_bytes': sys-memory-by-host-by-current-bytes. views have these columns:
'host'
The host from which the client connected. Rows for which the 'HOST' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'current_count_used'
The current number of allocated memory blocks that have not been freed yet for the host.
'current_allocated'
The current number of allocated bytes that have not been freed yet for the host.
'current_avg_alloc'
The current number of allocated bytes per memory block for the host.
'current_max_alloc'
The largest single current memory allocation in bytes for the host.
'total_allocated'
The total memory allocation in bytes for the host.
File: manual.info.tmp, Node: sys-memory-by-thread-by-current-bytes, Next: sys-memory-by-user-by-current-bytes, Prev: sys-memory-by-host-by-current-bytes, Up: sys-schema-views
26.4.3.17 The memory_by_thread_by_current_bytes and x$memory_by_thread_by_current_bytes Views .............................................................................................
These views summarize memory use, grouped by thread. By default, rows are sorted by descending amount of memory used.
The note 'memory_by_thread_by_current_bytes': sys-memory-by-thread-by-current-bytes. and note 'x$memory_by_thread_by_current_bytes': sys-memory-by-thread-by-current-bytes. views have these columns:
'thread_id'
The thread ID.
'user'
The thread user or thread name.
'current_count_used'
The current number of allocated memory blocks that have not been freed yet for the thread.
'current_allocated'
The current number of allocated bytes that have not been freed yet for the thread.
'current_avg_alloc'
The current number of allocated bytes per memory block for the thread.
'current_max_alloc'
The largest single current memory allocation in bytes for the thread.
'total_allocated'
The total memory allocation in bytes for the thread.
File: manual.info.tmp, Node: sys-memory-by-user-by-current-bytes, Next: sys-memory-global-by-current-bytes, Prev: sys-memory-by-thread-by-current-bytes, Up: sys-schema-views
26.4.3.18 The memory_by_user_by_current_bytes and x$memory_by_user_by_current_bytes Views .........................................................................................
These views summarize memory use, grouped by user. By default, rows are sorted by descending amount of memory used.
The note 'memory_by_user_by_current_bytes': sys-memory-by-user-by-current-bytes. and note 'x$memory_by_user_by_current_bytes': sys-memory-by-user-by-current-bytes. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'current_count_used'
The current number of allocated memory blocks that have not been freed yet for the user.
'current_allocated'
The current number of allocated bytes that have not been freed yet for the user.
'current_avg_alloc'
The current number of allocated bytes per memory block for the user.
'current_max_alloc'
The largest single current memory allocation in bytes for the user.
'total_allocated'
The total memory allocation in bytes for the user.
File: manual.info.tmp, Node: sys-memory-global-by-current-bytes, Next: sys-memory-global-total, Prev: sys-memory-by-user-by-current-bytes, Up: sys-schema-views
26.4.3.19 The memory_global_by_current_bytes and x$memory_global_by_current_bytes Views .......................................................................................
These views summarize memory use, grouped by allocation type (that is, by event). By default, rows are sorted by descending amount of memory used.
The note 'memory_global_by_current_bytes': sys-memory-global-by-current-bytes. and note 'x$memory_global_by_current_bytes': sys-memory-global-by-current-bytes. views have these columns:
'event_name'
The memory event name.
'current_count'
The total number of occurrences of the event.
'current_alloc'
The current number of allocated bytes that have not been freed yet for the event.
'current_avg_alloc'
The current number of allocated bytes per memory block for the event.
'high_count'
The high-water mark for number of memory blocks allocated for the event.
'high_alloc'
The high-water mark for number of bytes allocated for the event.
'high_avg_alloc'
The high-water mark for average number of bytes per memory block allocated for the event.
File: manual.info.tmp, Node: sys-memory-global-total, Next: sys-metrics, Prev: sys-memory-global-by-current-bytes, Up: sys-schema-views
26.4.3.20 The memory_global_total and x$memory_global_total Views .................................................................
These views summarize total memory use within the server.
The note 'memory_global_total': sys-memory-global-total. and note 'x$memory_global_total': sys-memory-global-total. views have these columns:
'total_allocated'
The total bytes of memory allocated within the server.
File: manual.info.tmp, Node: sys-metrics, Next: sys-processlist, Prev: sys-memory-global-total, Up: sys-schema-views
26.4.3.21 The metrics View ..........................
This view summarizes MySQL server metrics to show variable names, values, types, and whether they are enabled. By default, rows are sorted by variable type and name.
The *note 'metrics': sys-metrics. view includes this information:
Global status variables from the Performance Schema *note 'global_status': performance-schema-status-variable-tables. table
'InnoDB' metrics from the 'INFORMATION_SCHEMA' *note 'INNODB_METRICS': information-schema-innodb-metrics-table. table
Current and total memory allocation, based on the Performance Schema memory instrumentation
The current time (human readable and Unix timestamp formats)
There is some duplication of information between the note 'global_status': performance-schema-status-variable-tables. and note 'INNODB_METRICS': information-schema-innodb-metrics-table. tables, which the *note 'metrics': sys-metrics. view eliminates.
The *note 'metrics': sys-metrics. view has these columns:
'Variable_name'
The metric name. The metric type determines the source from which the name is taken:
* For global status variables: The 'VARIABLE_NAME' column of the
*note 'global_status':
performance-schema-status-variable-tables. table
* For 'InnoDB' metrics: The 'NAME' column of the *note
'INNODB_METRICS': information-schema-innodb-metrics-table.
table
* For other metrics: A view-provided descriptive string'Variable_value'
The metric value. The metric type determines the source from which the value is taken:
* For global status variables: The 'VARIABLE_VALUE' column of
the *note 'global_status':
performance-schema-status-variable-tables. table
* For 'InnoDB' metrics: The 'COUNT' column of the *note
'INNODB_METRICS': information-schema-innodb-metrics-table.
table
* For memory metrics: The relevant column from the Performance
Schema *note 'memory_summary_global_by_event_name':
performance-schema-memory-summary-tables. table
* For the current time: The value of 'NOW(3)' or
'UNIX_TIMESTAMP(NOW(3))''Type'
The metric type:
* For global status variables: 'Global Status'
* For 'InnoDB' metrics: 'InnoDB Metrics - %', where '%' is
replaced by the value of the 'SUBSYSTEM' column of the *note
'INNODB_METRICS': information-schema-innodb-metrics-table.
table
* For memory metrics: 'Performance Schema'
* For the current time: 'System Time''Enabled'
Whether the metric is enabled:
* For global status variables: 'YES'
* For 'InnoDB' metrics: 'YES' if the 'STATUS' column of the
*note 'INNODB_METRICS':
information-schema-innodb-metrics-table. table is 'enabled',
'NO' otherwise
* For memory metrics: 'NO', 'YES', or 'PARTIAL' (currently,
'PARTIAL' occurs only for memory metrics and indicates that
not all 'memory/%' instruments are enabled; Performance Schema
memory instruments are always enabled)
* For the current time: 'YES'File: manual.info.tmp, Node: sys-processlist, Next: sys-ps-check-lost-instrumentation, Prev: sys-metrics, Up: sys-schema-views
26.4.3.22 The processlist and x$processlist Views .................................................
The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. The note 'processlist': sys-processlist. and note 'x$processlist': sys-processlist. views summarize process information. They provide more complete information than the note 'SHOW PROCESSLIST': show-processlist. statement and the 'INFORMATION_SCHEMA' note 'PROCESSLIST': information-schema-processlist-table. table, and are also nonblocking. By default, rows are sorted by descending process time and descending wait time. For a comparison of process information sources, see *note processlist-sources::.
The column descriptions here are brief. For additional information, see the description of the Performance Schema note 'threads': performance-schema-threads-table. table at note performance-schema-threads-table::.
The note 'processlist': sys-processlist. and note 'x$processlist': sys-processlist. views have these columns:
'thd_id'
The thread ID.
'conn_id'
The connection ID.
'user'
The thread user or thread name.
'db'
The default database for the thread, or 'NULL' if there is none.
'command'
For foreground threads, the type of command the thread is executing on behalf of the client, or 'Sleep' if the session is idle.
'state'
An action, event, or state that indicates what the thread is doing.
'time'
The time in seconds that the thread has been in its current state.
'current_statement'
The statement the thread is executing, or 'NULL' if it is not executing any statement.
'statement_latency'
How long the statement has been executing.
'progress'
The percentage of work completed for stages that support progress reporting. See *note sys-schema-progress-reporting::.
'lock_latency'
The time spent waiting for locks by the current statement.
'rows_examined'
The number of rows read from storage engines by the current statement.
'rows_sent'
The number of rows returned by the current statement.
'rows_affected'
The number of rows affected by the current statement.
'tmp_tables'
The number of internal in-memory temporary tables created by the current statement.
'tmp_disk_tables'
The number of internal on-disk temporary tables created by the current statement.
'full_scan'
The number of full table scans performed by the current statement.
'last_statement'
The last statement executed by the thread, if there is no currently executing statement or wait.
'last_statement_latency'
How long the last statement executed.
'current_memory'
The number of bytes allocated by the thread.
'last_wait'
The name of the most recent wait event for the thread.
'last_wait_latency'
The wait time of the most recent wait event for the thread.
'source'
The source file and line number containing the instrumented code that produced the event.
'trx_latency'
The wait time of the current transaction for the thread.
'trx_state'
The state for the current transaction for the thread.
'trx_autocommit'
Whether autocommit mode was enabled when the current transaction started.
'pid'
The client process ID.
'program_name'
The client program name.
File: manual.info.tmp, Node: sys-ps-check-lost-instrumentation, Next: sys-schema-auto-increment-columns, Prev: sys-processlist, Up: sys-schema-views
26.4.3.23 The ps_check_lost_instrumentation View ................................................
This view returns information about lost Performance Schema instruments, to indicate whether the Performance Schema is unable to monitor all runtime data.
The *note 'ps_check_lost_instrumentation': sys-ps-check-lost-instrumentation. view has these columns:
'variable_name'
The Performance Schema status variable name indicating which type of instrument was lost.
'variable_value'
The number of instruments lost.
File: manual.info.tmp, Node: sys-schema-auto-increment-columns, Next: sys-schema-index-statistics, Prev: sys-ps-check-lost-instrumentation, Up: sys-schema-views
26.4.3.24 The schema_auto_increment_columns View ................................................
This view indicates which tables have 'AUTO_INCREMENT' columns and provides information about those columns, such as the current and maximum column values and the usage ratio (ratio of used to possible values). By default, rows are sorted by descending usage ratio and maximum column value.
Tables in these schemas are excluded from view output: 'mysql', 'sys', 'INFORMATION_SCHEMA', 'performance_schema'.
The *note 'schema_auto_increment_columns': sys-schema-auto-increment-columns. view has these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table that contains the 'AUTO_INCREMENT' column.
'column_name'
The name of the 'AUTO_INCREMENT' column.
'data_type'
The data type of the column.
'column_type'
The column type of the column, which is the data type plus possibly other information. For example, for a column with a 'bigint(20) unsigned' column type, the data type is just 'bigint'.
'is_signed'
Whether the column type is signed.
'is_unsigned'
Whether the column type is unsigned.
'max_value'
The maximum permitted value for the column.
'auto_increment'
The current 'AUTO_INCREMENT' value for the column.
'auto_increment_ratio'
The ratio of used to permitted values for the column. This indicates how much of the sequence of values is 'used up.'
File: manual.info.tmp, Node: sys-schema-index-statistics, Next: sys-schema-object-overview, Prev: sys-schema-auto-increment-columns, Up: sys-schema-views
26.4.3.25 The schema_index_statistics and x$schema_index_statistics Views .........................................................................
These views provide index statistics. By default, rows are sorted by descending total index latency.
The note 'schema_index_statistics': sys-schema-index-statistics. and note 'x$schema_index_statistics': sys-schema-index-statistics. views have these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table that contains the index.
'index_name'
The name of the index.
'rows_selected'
The total number of rows read using the index.
'select_latency'
The total wait time of timed reads using the index.
'rows_inserted'
The total number of rows inserted into the index.
'insert_latency'
The total wait time of timed inserts into the index.
'rows_updated'
The total number of rows updated in the index.
'update_latency'
The total wait time of timed updates in the index.
'rows_deleted'
The total number of rows deleted from the index.
'delete_latency'
The total wait time of timed deletes from the index.
File: manual.info.tmp, Node: sys-schema-object-overview, Next: sys-schema-redundant-indexes, Prev: sys-schema-index-statistics, Up: sys-schema-views
26.4.3.26 The schema_object_overview View .........................................
This view summarizes the types of objects within each schema. By default, rows are sorted by schema and object type.
Note:
For MySQL instances with a large number of objects, this view might take a long time to execute.
The *note 'schema_object_overview': sys-schema-object-overview. view has these columns:
'db'
The schema name.
'object_type'
The object type: 'BASE TABLE', 'INDEX (INDEX_TYPE)', 'EVENT', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW'.
'count'
The number of objects in the schema of the given type.
File: manual.info.tmp, Node: sys-schema-redundant-indexes, Next: sys-schema-table-lock-waits, Prev: sys-schema-object-overview, Up: sys-schema-views
26.4.3.27 The schema_redundant_indexes and x$schema_flattened_keys Views ........................................................................
The note 'schema_redundant_indexes': sys-schema-redundant-indexes. view displays indexes that duplicate other indexes or are made redundant by them. The note 'x$schema_flattened_keys': sys-schema-redundant-indexes. view is a helper view for *note 'schema_redundant_indexes': sys-schema-redundant-indexes.
In the following column descriptions, the dominant index is the one that makes the redundant index redundant.
The *note 'schema_redundant_indexes': sys-schema-redundant-indexes. view has these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table that contains the index.
'redundant_index_name'
The name of the redundant index.
'redundant_index_columns'
The names of the columns in the redundant index.
'redundant_index_non_unique'
The number of nonunique columns in the redundant index.
'dominant_index_name'
The name of the dominant index.
'dominant_index_columns'
The names of the columns in the dominant index.
'dominant_index_non_unique'
The number of nonunique columns in the dominant index.
'subpart_exists'
Whether the index indexes only part of a column.
'sql_drop_index'
The statement to execute to drop the redundant index.
The *note 'x$schema_flattened_keys': sys-schema-redundant-indexes. view has these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table that contains the index.
'index_name'
An index name.
'non_unique'
The number of nonunique columns in the index.
'subpart_exists'
Whether the index indexes only part of a column.
'index_columns'
The name of the columns in the index.
File: manual.info.tmp, Node: sys-schema-table-lock-waits, Next: sys-schema-table-statistics, Prev: sys-schema-redundant-indexes, Up: sys-schema-views
26.4.3.28 The schema_table_lock_waits and x$schema_table_lock_waits Views .........................................................................
These views display which sessions are blocked waiting on metadata locks, and what is blocking them.
The column descriptions here are brief. For additional information, see the description of the Performance Schema note 'metadata_locks': performance-schema-metadata-locks-table. table at note performance-schema-metadata-locks-table::.
The note 'schema_table_lock_waits': sys-schema-table-lock-waits. and note 'x$schema_table_lock_waits': sys-schema-table-lock-waits. views have these columns:
'object_schema'
The schema containing the object to be locked.
'object_name'
The name of the instrumented object.
'waiting_thread_id'
The thread ID of the thread that is waiting for the lock.
'waiting_pid'
The processlist ID of the thread that is waiting for the lock.
'waiting_account'
The account associated with the session that is waiting for the lock.
'waiting_lock_type'
The type of the waiting lock.
'waiting_lock_duration'
How long the waiting lock has been waiting.
'waiting_query'
The statement that is waiting for the lock.
'waiting_query_secs'
How long the statement has been waiting, in seconds.
'waiting_query_rows_affected'
The number of rows affected by the statement.
'waiting_query_rows_examined'
The number of rows read from storage engines by the statement.
'blocking_thread_id'
The thread ID of the thread that is blocking the waiting lock.
'blocking_pid'
The processlist ID of the thread that is blocking the waiting lock.
'blocking_account'
The account associated with the thread that is blocking the waiting lock.
'blocking_lock_type'
The type of lock that is blocking the waiting lock.
'blocking_lock_duration'
How long the blocking lock has been held.
'sql_kill_blocking_query'
The *note 'KILL': kill. statement to execute to kill the blocking statement.
'sql_kill_blocking_connection'
The *note 'KILL': kill. statement to execute to kill the session running the blocking statement.
File: manual.info.tmp, Node: sys-schema-table-statistics, Next: sys-schema-table-statistics-with-buffer, Prev: sys-schema-table-lock-waits, Up: sys-schema-views
26.4.3.29 The schema_table_statistics and x$schema_table_statistics Views .........................................................................
These views summarize table statistics. By default, rows are sorted by descending total wait time (tables with most contention first).
These views user a helper view, 'x$ps_schema_table_statistics_io'.
The note 'schema_table_statistics': sys-schema-table-statistics. and note 'x$schema_table_statistics': sys-schema-table-statistics. views have these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table name.
'total_latency'
The total wait time of timed I/O events for the table.
'rows_fetched'
The total number of rows read from the table.
'fetch_latency'
The total wait time of timed read I/O events for the table.
'rows_inserted'
The total number of rows inserted into the table.
'insert_latency'
The total wait time of timed insert I/O events for the table.
'rows_updated'
The total number of rows updated in the table.
'update_latency'
The total wait time of timed update I/O events for the table.
'rows_deleted'
The total number of rows deleted from the table.
'delete_latency'
The total wait time of timed delete I/O events for the table.
'io_read_requests'
The total number of read requests for the table.
'io_read'
The total number of bytes read from the table.
'io_read_latency'
The total wait time of reads from the table.
'io_write_requests'
The total number of write requests for the table.
'io_write'
The total number of bytes written to the table.
'io_write_latency'
The total wait time of writes to the table.
'io_misc_requests'
The total number of miscellaneous I/O requests for the table.
'io_misc_latency'
The total wait time of miscellaneous I/O requests for the table.
File: manual.info.tmp, Node: sys-schema-table-statistics-with-buffer, Next: sys-schema-tables-with-full-table-scans, Prev: sys-schema-table-statistics, Up: sys-schema-views
26.4.3.30 The schema_table_statistics_with_buffer and x$schema_table_statistics_with_buffer Views .................................................................................................
These views summarize table statistics, including 'InnoDB' buffer pool statistics. By default, rows are sorted by descending total wait time (tables with most contention first).
These views user a helper view, 'x$ps_schema_table_statistics_io'.
The note 'schema_table_statistics_with_buffer': sys-schema-table-statistics-with-buffer. and note 'x$schema_table_statistics_with_buffer': sys-schema-table-statistics-with-buffer. views have these columns:
'table_schema'
The schema that contains the table.
'table_name'
The table name.
'rows_fetched'
The total number of rows read from the table.
'fetch_latency'
The total wait time of timed read I/O events for the table.
'rows_inserted'
The total number of rows inserted into the table.
'insert_latency'
The total wait time of timed insert I/O events for the table.
'rows_updated'
The total number of rows updated in the table.
'update_latency'
The total wait time of timed update I/O events for the table.
'rows_deleted'
The total number of rows deleted from the table.
'delete_latency'
The total wait time of timed delete I/O events for the table.
'io_read_requests'
The total number of read requests for the table.
'io_read'
The total number of bytes read from the table.
'io_read_latency'
The total wait time of reads from the table.
'io_write_requests'
The total number of write requests for the table.
'io_write'
The total number of bytes written to the table.
'io_write_latency'
The total wait time of writes to the table.
'io_misc_requests'
The total number of miscellaneous I/O requests for the table.
'io_misc_latency'
The total wait time of miscellaneous I/O requests for the table.
'innodb_buffer_allocated'
The total number of 'InnoDB' buffer bytes allocated for the table.
'innodb_buffer_data'
The total number of 'InnoDB' data bytes allocated for the table.
'innodb_buffer_free'
The total number of 'InnoDB' nondata bytes allocated for the table ('innodb_buffer_allocated' − 'innodb_buffer_data').
'innodb_buffer_pages'
The total number of 'InnoDB' pages allocated for the table.
'innodb_buffer_pages_hashed'
The total number of 'InnoDB' hashed pages allocated for the table.
'innodb_buffer_pages_old'
The total number of 'InnoDB' old pages allocated for the table.
'innodb_buffer_rows_cached'
The total number of 'InnoDB' cached rows for the table.
File: manual.info.tmp, Node: sys-schema-tables-with-full-table-scans, Next: sys-schema-unused-indexes, Prev: sys-schema-table-statistics-with-buffer, Up: sys-schema-views
26.4.3.31 The schema_tables_with_full_table_scans and x$schema_tables_with_full_table_scans Views .................................................................................................
These views display which tables are being accessed with full table scans. By default, rows are sorted by descending rows scanned.
The note 'schema_tables_with_full_table_scans': sys-schema-tables-with-full-table-scans. and note 'x$schema_tables_with_full_table_scans': sys-schema-tables-with-full-table-scans. views have these columns:
'object_schema'
The schema name.
'object_name'
The table name.
'rows_full_scanned'
The total number of rows scanned by full scans of the table.
'latency'
The total wait time of full scans of the table.
File: manual.info.tmp, Node: sys-schema-unused-indexes, Next: sys-session, Prev: sys-schema-tables-with-full-table-scans, Up: sys-schema-views
26.4.3.32 The schema_unused_indexes View ........................................
These views display indexes for which there are no events, which indicates that they are not being used. By default, rows are sorted by schema and table.
This view is most useful when the server has been up and processing long enough that its workload is representative. Otherwise, presence of an index in this view may not be meaningful.
The *note 'schema_unused_indexes': sys-schema-unused-indexes. view has these columns:
'object_schema'
The schema name.
'object_name'
The table name.
'index_name'
The unused index name.
File: manual.info.tmp, Node: sys-session, Next: sys-session-ssl-status, Prev: sys-schema-unused-indexes, Up: sys-schema-views
26.4.3.33 The session and x$session Views .........................................
These views are similar to note 'processlist': sys-processlist. and note 'x$processlist': sys-processlist, but they filter out background processes to display only user sessions. For descriptions of the columns, see *note sys-processlist::.
File: manual.info.tmp, Node: sys-session-ssl-status, Next: sys-statement-analysis, Prev: sys-session, Up: sys-schema-views
26.4.3.34 The session_ssl_status View .....................................
For each connection, this view displays the SSL version, cipher, and count of reused SSL sessions.
The *note 'session_ssl_status': sys-session-ssl-status. view has these columns:
'thread_id'
The thread ID for the connection.
'ssl_version'
The version of SSL used for the connection.
'ssl_cipher'
The SSL cipher used for the connection.
'ssl_sessions_reused'
The number of reused SSL sessions for the connection.
File: manual.info.tmp, Node: sys-statement-analysis, Next: sys-statements-with-errors-or-warnings, Prev: sys-session-ssl-status, Up: sys-schema-views
26.4.3.35 The statement_analysis and x$statement_analysis Views ...............................................................
These views list normalized statements with aggregated statistics. The content mimics the MySQL Enterprise Monitor Query Analysis view. By default, rows are sorted by descending total latency.
The note 'statement_analysis': sys-statement-analysis. and note 'x$statement_analysis': sys-statement-analysis. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'full_scan'
The total number of full table scans performed by occurrences of the statement.
'exec_count'
The total number of times the statement has executed.
'err_count'
The total number of errors produced by occurrences of the statement.
'warn_count'
The total number of warnings produced by occurrences of the statement.
'total_latency'
The total wait time of timed occurrences of the statement.
'max_latency'
The maximum single wait time of timed occurrences of the statement.
'avg_latency'
The average wait time per timed occurrence of the statement.
'lock_latency'
The total time waiting for locks by timed occurrences of the statement.
'rows_sent'
The total number of rows returned by occurrences of the statement.
'rows_sent_avg'
The average number of rows returned per occurrence of the statement.
'rows_examined'
The total number of rows read from storage engines by occurrences of the statement.
'rows_examined_avg'
The average number of rows read from storage engines per occurrence of the statement.
'rows_affected'
The total number of rows affected by occurrences of the statement.
'rows_affected_avg'
The average number of rows affected per occurrence of the statement.
'tmp_tables'
The total number of internal in-memory temporary tables created by occurrences of the statement.
'tmp_disk_tables'
The total number of internal on-disk temporary tables created by occurrences of the statement.
'rows_sorted'
The total number of rows sorted by occurrences of the statement.
'sort_merge_passes'
The total number of sort merge passes by occurrences of the statement.
'digest'
The statement digest.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
File: manual.info.tmp, Node: sys-statements-with-errors-or-warnings, Next: sys-statements-with-full-table-scans, Prev: sys-statement-analysis, Up: sys-schema-views
26.4.3.36 The statements_with_errors_or_warnings and x$statements_with_errors_or_warnings Views ...............................................................................................
These views display normalized statements that have produced errors or warnings. By default, rows are sorted by descending error and warning counts.
The note 'statements_with_errors_or_warnings': sys-statements-with-errors-or-warnings. and note 'x$statements_with_errors_or_warnings': sys-statements-with-errors-or-warnings. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'exec_count'
The total number of times the statement has executed.
'errors'
The total number of errors produced by occurrences of the statement.
'error_pct'
The percentage of statement occurrences that produced errors.
'warnings'
The total number of warnings produced by occurrences of the statement.
'warning_pct'
The percentage of statement occurrences that produced warnings.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
'digest'
The statement digest.
File: manual.info.tmp, Node: sys-statements-with-full-table-scans, Next: sys-statements-with-runtimes-in-95th-percentile, Prev: sys-statements-with-errors-or-warnings, Up: sys-schema-views
26.4.3.37 The statements_with_full_table_scans and x$statements_with_full_table_scans Views ...........................................................................................
These views display normalized statements that have done full table scans. By default, rows are sorted by descending percentage of time a full scan was done and descending total latency.
The note 'statements_with_full_table_scans': sys-statements-with-full-table-scans. and note 'x$statements_with_full_table_scans': sys-statements-with-full-table-scans. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'exec_count'
The total number of times the statement has executed.
'total_latency'
The total wait time of timed statement events for the statement.
'no_index_used_count'
The total number of times no index was used to scan the table.
'no_good_index_used_count'
The total number of times no good index was used to scan the table.
'no_index_used_pct'
The percentage of the time no index was used to scan the table.
'rows_sent'
The total number of rows returned from the table.
'rows_examined'
The total number of rows read from the storage engine for the table.
'rows_sent_avg'
The average number of rows returned from the table.
'rows_examined_avg'
The average number of rows read from the storage engine for the table.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
'digest'
The statement digest.
File: manual.info.tmp, Node: sys-statements-with-runtimes-in-95th-percentile, Next: sys-statements-with-sorting, Prev: sys-statements-with-full-table-scans, Up: sys-schema-views
26.4.3.38 The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views .................................................................................................................
These views list statements with runtimes in the 95th percentile. By default, rows are sorted by descending average latency.
Both views use two helper views, 'xpsdigestavglatencydistribution′and′xps_digest_95th_percentile_by_avg_us'.
The note 'statements_with_runtimes_in_95th_percentile': sys-statements-with-runtimes-in-95th-percentile. and note 'x$statements_with_runtimes_in_95th_percentile': sys-statements-with-runtimes-in-95th-percentile. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'full_scan'
The total number of full table scans performed by occurrences of the statement.
'exec_count'
The total number of times the statement has executed.
'err_count'
The total number of errors produced by occurrences of the statement.
'warn_count'
The total number of warnings produced by occurrences of the statement.
'total_latency'
The total wait time of timed occurrences of the statement.
'max_latency'
The maximum single wait time of timed occurrences of the statement.
'avg_latency'
The average wait time per timed occurrence of the statement.
'rows_sent'
The total number of rows returned by occurrences of the statement.
'rows_sent_avg'
The average number of rows returned per occurrence of the statement.
'rows_examined'
The total number of rows read from storage engines by occurrences of the statement.
'rows_examined_avg'
The average number of rows read from storage engines per occurrence of the statement.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
'digest'
The statement digest.
File: manual.info.tmp, Node: sys-statements-with-sorting, Next: sys-statements-with-temp-tables, Prev: sys-statements-with-runtimes-in-95th-percentile, Up: sys-schema-views
26.4.3.39 The statements_with_sorting and x$statements_with_sorting Views .........................................................................
These views list normalized statements that have performed sorts. By default, rows are sorted by descending total latency.
The note 'statements_with_sorting': sys-statements-with-sorting. and note 'x$statements_with_sorting': sys-statements-with-sorting. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'exec_count'
The total number of times the statement has executed.
'total_latency'
The total wait time of timed occurrences of the statement.
'sort_merge_passes'
The total number of sort merge passes by occurrences of the statement.
'avg_sort_merges'
The average number of sort merge passes per occurrence of the statement.
'sorts_using_scans'
The total number of sorts using table scans by occurrences of the statement.
'sort_using_range'
The total number of sorts using range accesses by occurrences of the statement.
'rows_sorted'
The total number of rows sorted by occurrences of the statement.
'avg_rows_sorted'
The average number of rows sorted per occurrence of the statement.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
'digest'
The statement digest.
File: manual.info.tmp, Node: sys-statements-with-temp-tables, Next: sys-user-summary, Prev: sys-statements-with-sorting, Up: sys-schema-views
26.4.3.40 The statements_with_temp_tables and x$statements_with_temp_tables Views .................................................................................
These views list normalized statements that have used temporary tables. By default, rows are sorted by descending number of on-disk temporary tables used and descending number of in-memory temporary tables used.
The note 'statements_with_temp_tables': sys-statements-with-temp-tables. and note 'x$statements_with_temp_tables': sys-statements-with-temp-tables. views have these columns:
'query'
The normalized statement string.
'db'
The default database for the statement, or 'NULL' if there is none.
'exec_count'
The total number of times the statement has executed.
'total_latency'
The total wait time of timed occurrences of the statement.
'memory_tmp_tables'
The total number of internal in-memory temporary tables created by occurrences of the statement.
'disk_tmp_tables'
The total number of internal on-disk temporary tables created by occurrences of the statement.
'avg_tmp_tables_per_query'
The average number of internal temporary tables created per occurrence of the statement.
'tmp_tables_to_disk_pct'
The percentage of internal in-memory temporary tables that were converted to on-disk tables.
'first_seen'
The time at which the statement was first seen.
'last_seen'
The time at which the statement was most recently seen.
'digest'
The statement digest.
File: manual.info.tmp, Node: sys-user-summary, Next: sys-user-summary-by-file-io, Prev: sys-statements-with-temp-tables, Up: sys-schema-views
26.4.3.41 The user_summary and x$user_summary Views ...................................................
These views summarize statement activity, file I/O, and connections, grouped by user. By default, rows are sorted by descending total latency.
The note 'user_summary': sys-user-summary. and note 'x$user_summary': sys-user-summary. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'statements'
The total number of statements for the user.
'statement_latency'
The total wait time of timed statements for the user.
'statement_avg_latency'
The average wait time per timed statement for the user.
'table_scans'
The total number of table scans for the user.
'file_ios'
The total number of file I/O events for the user.
'file_io_latency'
The total wait time of timed file I/O events for the user.
'current_connections'
The current number of connections for the user.
'total_connections'
The total number of connections for the user.
'unique_hosts'
The number of distinct hosts from which connections for the user have originated.
'current_memory'
The current amount of allocated memory for the user.
'total_memory_allocated'
The total amount of allocated memory for the user.
File: manual.info.tmp, Node: sys-user-summary-by-file-io, Next: sys-user-summary-by-file-io-type, Prev: sys-user-summary, Up: sys-schema-views
26.4.3.42 The user_summary_by_file_io and x$user_summary_by_file_io Views .........................................................................
These views summarize file I/O, grouped by user. By default, rows are sorted by descending total file I/O latency.
The note 'user_summary_by_file_io': sys-user-summary-by-file-io. and note 'x$user_summary_by_file_io': sys-user-summary-by-file-io. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'ios'
The total number of file I/O events for the user.
'io_latency'
The total wait time of timed file I/O events for the user.
File: manual.info.tmp, Node: sys-user-summary-by-file-io-type, Next: sys-user-summary-by-stages, Prev: sys-user-summary-by-file-io, Up: sys-schema-views
26.4.3.43 The user_summary_by_file_io_type and x$user_summary_by_file_io_type Views ...................................................................................
These views summarize file I/O, grouped by user and event type. By default, rows are sorted by user and descending total latency.
The note 'user_summary_by_file_io_type': sys-user-summary-by-file-io-type. and note 'x$user_summary_by_file_io_type': sys-user-summary-by-file-io-type. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'event_name'
The file I/O event name.
'total'
The total number of occurrences of the file I/O event for the user.
'latency'
The total wait time of timed occurrences of the file I/O event for the user.
'max_latency'
The maximum single wait time of timed occurrences of the file I/O event for the user.
File: manual.info.tmp, Node: sys-user-summary-by-stages, Next: sys-user-summary-by-statement-latency, Prev: sys-user-summary-by-file-io-type, Up: sys-schema-views
26.4.3.44 The user_summary_by_stages and x$user_summary_by_stages Views .......................................................................
These views summarize stages, grouped by user. By default, rows are sorted by user and descending total stage latency.
The note 'user_summary_by_stages': sys-user-summary-by-stages. and note 'x$user_summary_by_stages': sys-user-summary-by-stages. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'event_name'
The stage event name.
'total'
The total number of occurrences of the stage event for the user.
'total_latency'
The total wait time of timed occurrences of the stage event for the user.
'avg_latency'
The average wait time per timed occurrence of the stage event for the user.
File: manual.info.tmp, Node: sys-user-summary-by-statement-latency, Next: sys-user-summary-by-statement-type, Prev: sys-user-summary-by-stages, Up: sys-schema-views
26.4.3.45 The user_summary_by_statement_latency and x$user_summary_by_statement_latency Views .............................................................................................
These views summarize overall statement statistics, grouped by user. By default, rows are sorted by descending total latency.
The note 'user_summary_by_statement_latency': sys-user-summary-by-statement-latency. and note 'x$user_summary_by_statement_latency': sys-user-summary-by-statement-latency. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'total'
The total number of statements for the user.
'total_latency'
The total wait time of timed statements for the user.
'max_latency'
The maximum single wait time of timed statements for the user.
'lock_latency'
The total time waiting for locks by timed statements for the user.
'rows_sent'
The total number of rows returned by statements for the user.
'rows_examined'
The total number of rows read from storage engines by statements for the user.
'rows_affected'
The total number of rows affected by statements for the user.
'full_scans'
The total number of full table scans by statements for the user.
File: manual.info.tmp, Node: sys-user-summary-by-statement-type, Next: sys-version, Prev: sys-user-summary-by-statement-latency, Up: sys-schema-views
26.4.3.46 The user_summary_by_statement_type and x$user_summary_by_statement_type Views .......................................................................................
These views summarize informaion about statements executed, grouped by user and statement type. By default, rows are sorted by user and descending total latency.
The note 'user_summary_by_statement_type': sys-user-summary-by-statement-type. and note 'x$user_summary_by_statement_type': sys-user-summary-by-statement-type. views have these columns:
'user'
The client user name. Rows for which the 'USER' column in the underlying Performance Schema table is 'NULL' are assumed to be for background threads and are reported with a host name of 'background'.
'statement'
The final component of the statement event name.
'total'
The total number of occurrences of the statement event for the user.
'total_latency'
The total wait time of timed occurrences of the statement event for the user.
'max_latency'
The maximum single wait time of timed occurrences of the statement event for the user.
'lock_latency'
The total time waiting for locks by timed occurrences of the statement event for the user.
'rows_sent'
The total number of rows returned by occurrences of the statement event for the user.
'rows_examined'
The total number of rows read from storage engines by occurrences of the statement event for the user.
'rows_affected'
The total number of rows affected by occurrences of the statement event for the user.
'full_scans'
The total number of full table scans by occurrences of the statement event for the user.
File: manual.info.tmp, Node: sys-version, Next: sys-wait-classes-global-by-avg-latency, Prev: sys-user-summary-by-statement-type, Up: sys-schema-views
26.4.3.47 The version View ..........................
This view provides the current *note 'sys': sys-schema. schema and MySQL server versions.
Note:
As of MySQL 5.7.28, this view is deprecated and subject to removal in a future MySQL version. Applications that use it should be migrated to use an alternative instead. For example, use the 'VERSION()' function to retrieve the MySQL server version.
The *note 'version': sys-version. view has these columns:
'sys_version'
The *note 'sys': sys-schema. schema version.
'mysql_version'
The MySQL server version.
File: manual.info.tmp, Node: sys-wait-classes-global-by-avg-latency, Next: sys-wait-classes-global-by-latency, Prev: sys-version, Up: sys-schema-views
26.4.3.48 The wait_classes_global_by_avg_latency and x$wait_classes_global_by_avg_latency Views ...............................................................................................
These views summarize wait class average latencies, grouped by event class. By default, rows are sorted by descending average latency. Idle events are ignored.
An event class is determined by stripping from the event name everything after the first three components. For example, the class for 'wait/io/file/sql/slow_log' is 'wait/io/file'.
The note 'wait_classes_global_by_avg_latency': sys-wait-classes-global-by-avg-latency. and note 'x$wait_classes_global_by_avg_latency': sys-wait-classes-global-by-avg-latency. views have these columns:
'event_class'
The event class.
'total'
The total number of occurrences of events in the class.
'total_latency'
The total wait time of timed occurrences of events in the class.
'min_latency'
The minimum single wait time of timed occurrences of events in the class.
'avg_latency'
The average wait time per timed occurrence of events in the class.
'max_latency'
The maximum single wait time of timed occurrences of events in the class.
File: manual.info.tmp, Node: sys-wait-classes-global-by-latency, Next: sys-waits-by-host-by-latency, Prev: sys-wait-classes-global-by-avg-latency, Up: sys-schema-views
26.4.3.49 The wait_classes_global_by_latency and x$wait_classes_global_by_latency Views .......................................................................................
These views summarize wait class total latencies, grouped by event class. By default, rows are sorted by descending total latency. Idle events are ignored.
An event class is determined by stripping from the event name everything after the first three components. For example, the class for 'wait/io/file/sql/slow_log' is 'wait/io/file'.
The note 'wait_classes_global_by_latency': sys-wait-classes-global-by-latency. and note 'x$wait_classes_global_by_latency': sys-wait-classes-global-by-latency. views have these columns:
'event_class'
The event class.
'total'
The total number of occurrences of events in the class.
'total_latency'
The total wait time of timed occurrences of events in the class.
'min_latency'
The minimum single wait time of timed occurrences of events in the class.
'avg_latency'
The average wait time per timed occurrence of events in the class.
'max_latency'
The maximum single wait time of timed occurrences of events in the class.
File: manual.info.tmp, Node: sys-waits-by-host-by-latency, Next: sys-waits-by-user-by-latency, Prev: sys-wait-classes-global-by-latency, Up: sys-schema-views
26.4.3.50 The waits_by_host_by_latency and x$waits_by_host_by_latency Views ...........................................................................
These views summarize wait events, grouped by host and event. By default, rows are sorted by host and descending total latency. Idle events are ignored.
The note 'waits_by_host_by_latency': sys-waits-by-host-by-latency. and note 'x$waits_by_host_by_latency': sys-waits-by-host-by-latency. views have these columns:
'host'
The host from which the connection originated.
'event'
The event name.
'total'
The total number of occurrences of the event for the host.
'total_latency'
The total wait time of timed occurrences of the event for the host.
'avg_latency'
The average wait time per timed occurrence of the event for the host.
'max_latency'
The maximum single wait time of timed occurrences of the event for the host.
File: manual.info.tmp, Node: sys-waits-by-user-by-latency, Next: sys-waits-global-by-latency, Prev: sys-waits-by-host-by-latency, Up: sys-schema-views
26.4.3.51 The waits_by_user_by_latency and x$waits_by_user_by_latency Views ...........................................................................
These views summarize wait events, grouped by user and event. By default, rows are sorted by user and descending total latency. Idle events are ignored.
The note 'waits_by_user_by_latency': sys-waits-by-user-by-latency. and note 'x$waits_by_user_by_latency': sys-waits-by-user-by-latency. views have these columns:
'user'
The user associated with the connection.
'event'
The event name.
'total'
The total number of occurrences of the event for the user.
'total_latency'
The total wait time of timed occurrences of the event for the user.
'avg_latency'
The average wait time per timed occurrence of the event for the user.
'max_latency'
The maximum single wait time of timed occurrences of the event for the user.
File: manual.info.tmp, Node: sys-waits-global-by-latency, Prev: sys-waits-by-user-by-latency, Up: sys-schema-views
26.4.3.52 The waits_global_by_latency and x$waits_global_by_latency Views .........................................................................
These views summarize wait events, grouped by event. By default, rows are sorted by descending total latency. Idle events are ignored.
The note 'waits_global_by_latency': sys-waits-global-by-latency. and note 'x$waits_global_by_latency': sys-waits-global-by-latency. views have these columns:
'events'
The event name.
'total'
The total number of occurrences of the event.
'total_latency'
The total wait time of timed occurrences of the event.
'avg_latency'
The average wait time per timed occurrence of the event.
'max_latency'
The maximum single wait time of timed occurrences of the event.
File: manual.info.tmp, Node: sys-schema-procedures, Next: sys-schema-functions, Prev: sys-schema-views, Up: sys-schema-reference
Menu:
sys-table-exists:: The table_exists() Procedure
The following sections describe *note 'sys': sys-schema. schema stored procedures.
File: manual.info.tmp, Node: sys-create-synonym-db, Next: sys-diagnostics, Prev: sys-schema-procedures, Up: sys-schema-procedures
26.4.4.1 The create_synonym_db() Procedure ..........................................
Given a schema name, this procedure creates a synonym schema containing views that refer to all the tables and views in the original schema. This can be used, for example, to create a shorter name by which to refer to a schema with a long name (such as 'info' rather than 'INFORMATION_SCHEMA').
Parameters
'in_db_name VARCHAR(64)': The name of the schema for which to create the synonym.
'in_synonym VARCHAR(64)': The name to use for the synonym schema. This schema must not already exist.
Example
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
mysql> CALL sys.create_synonym_db('INFORMATION_SCHEMA', 'info');
+---------------------------------------+
| summary |
+---------------------------------------+
| Created 63 views in the info database |
+---------------------------------------+
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| info |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
mysql> SHOW FULL TABLES FROM info;
+---------------------------------------+------------+
| Tables_in_info | Table_type |
+---------------------------------------+------------+
| character_sets | VIEW |
| collation_character_set_applicability | VIEW |
| collations | VIEW |
| column_privileges | VIEW |
| columns | VIEW |
...File: manual.info.tmp, Node: sys-diagnostics, Next: sys-execute-prepared-stmt, Prev: sys-create-synonym-db, Up: sys-schema-procedures
26.4.4.2 The diagnostics() Procedure ....................................
Creates a report of the current server status for diagnostic purposes.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Data collected for *note 'diagnostics()': sys-diagnostics. includes this information:
Information from the note 'metrics': sys-metrics. view (see note sys-metrics::)
Information from other relevant 'sys' schema views, such as the one that detemines queries in the 95th percentile
Information from the 'ndbinfo' schema, if the MySQL server is part of NDB Cluster
Replication status (both source and replica)
Some of the sys schema views are calculated as initial (optional), overall, and delta values:
The initial view is the content of the view at the start of the *note 'diagnostics()': sys-diagnostics. procedure. This output is the same as the start values used for the delta view. The initial view is included if the 'diagnostics.include_raw' configuration option is 'ON'.
The overall view is the content of the view at the end of the *note 'diagnostics()': sys-diagnostics. procedure. This output is the same as the end values used for the delta view. The overall view is always included.
The delta view is the difference from the beginning to the end of procedure execution. The minimum and maximum values are the minimum and maximum values from the end view, respectively. They do not necessarily reflect the minimum and maximum values in the monitored period. Except for the *note 'metrics': sys-metrics. view, the delta is calculated only between the first and last outputs.
Parameters
'in_max_runtime INT UNSIGNED': The maximum data collection time in seconds. Use 'NULL' to collect data for the default of 60 seconds. Otherwise, use a value greater than 0.
'in_interval INT UNSIGNED': The sleep time between data collections in seconds. Use 'NULL' to sleep for the default of 30 seconds. Otherwise, use a value greater than 0.
'in_auto_config ENUM('current', 'medium', 'full')': The Performance Schema configuration to use. Permitted values are:
* 'current': Use the current instrument and consumer settings.
* 'medium': Enable some instruments and consumers.
* 'full': Enable all instruments and consumers.Note:
The more instruments and consumers enabled, the more impact on MySQL server performance. Be careful with the 'medium' setting and especially the 'full' setting, which has a large performance impact.
Use of the 'medium' or 'full' setting requires the 'SUPER' privilege.
If a setting other than 'current' is chosen, the current settings are restored at the end of the procedure.
Configuration Options
note 'diagnostics()': sys-diagnostics. operation can be modified using the following configuration options or their corresponding user-defined variables (see note sys-sys-config::):
'debug', '@sys.debug'
If this option is 'ON', produce debugging output. The default is 'OFF'.
'diagnostics.allow_i_s_tables', '@sys.diagnostics.allow_i_s_tables'
If this option is 'ON', the note 'diagnostics()': sys-diagnostics. procedure is permitted to perform table scans on the Information Schema note 'TABLES': information-schema-tables-table. table. This can be expensive if there are many tables. The default is 'OFF'.
'diagnostics.include_raw', '@sys.diagnostics.include_raw'
If this option is 'ON', the note 'diagnostics()': sys-diagnostics. procedure output includes the raw output from querying the note 'metrics': sys-metrics. view. The default is 'OFF'.
'statement_truncate_len', '@sys.statement_truncate_len'
The maximum length of statements returned by the *note 'format_statement()': sys-format-statement. function. Longer statements are truncated to this length. The default is 64.
Example
Create a diagnostics report that starts an iteration every 30 seconds and runs for at most 120 seconds using the current Performance Schema settings:
mysql> CALL sys.diagnostics(120, 30, 'current');To capture the output from the 'diagnostics()' procedure in a file as it runs, use the note 'mysql': mysql. client 'tee filename' and 'notee' commands (see note mysql-commands::):
mysql> tee diag.out;
mysql> CALL sys.diagnostics(120, 30, 'current');
mysql> notee;File: manual.info.tmp, Node: sys-execute-prepared-stmt, Next: sys-ps-setup-disable-background-threads, Prev: sys-diagnostics, Up: sys-schema-procedures
26.4.4.3 The execute_prepared_stmt() Procedure ..............................................
Given an SQL statement as a string, executes it as a prepared statement. The prepared statement is deallocated after execution, so it is not subject to reuse. Thus, this procedure is useful primarily for executing dynamic statements on a one-time basis.
This procedure uses 'sys_execute_prepared_stmt' as the prepared statement name. If that statement name exists when the procedure is called, its previous content is destroyed.
Parameters
Configuration Options
note 'execute_prepared_stmt()': sys-execute-prepared-stmt. operation can be modified using the following configuration options or their corresponding user-defined variables (see note sys-sys-config::):
'debug', '@sys.debug'
If this option is 'ON', produce debugging output. The default is 'OFF'.
Example
mysql> CALL sys.execute_prepared_stmt('SELECT COUNT(*) FROM mysql.user');
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+File: manual.info.tmp, Node: sys-ps-setup-disable-background-threads, Next: sys-ps-setup-disable-consumer, Prev: sys-execute-prepared-stmt, Up: sys-schema-procedures
26.4.4.4 The ps_setup_disable_background_threads() Procedure ............................................................
Disables Performance Schema instrumentation for all background threads. Produces a result set indicating how many background threads were disabled. Already disabled threads do not count.
Parameters
None.
Example
mysql> CALL sys.ps_setup_disable_background_threads();
+--------------------------------+
| summary |
+--------------------------------+
| Disabled 24 background threads |
+--------------------------------+File: manual.info.tmp, Node: sys-ps-setup-disable-consumer, Next: sys-ps-setup-disable-instrument, Prev: sys-ps-setup-disable-background-threads, Up: sys-schema-procedures
26.4.4.5 The ps_setup_disable_consumer() Procedure ..................................................
Disables Performance Schema consumers with names that contain the argument. Produces a result set indicating how many consumers were disabled. Already disabled consumers do not count.
Parameters
'consumer VARCHAR(128)': The value used to match consumer names, which are identified by using '%consumer%' as an operand for a 'LIKE' pattern match.
A value of '''' matches all consumers.
Example
Disable all statement consumers:
mysql> CALL sys.ps_setup_disable_consumer('statement');
+----------------------+
| summary |
+----------------------+
| Disabled 4 consumers |
+----------------------+File: manual.info.tmp, Node: sys-ps-setup-disable-instrument, Next: sys-ps-setup-disable-thread, Prev: sys-ps-setup-disable-consumer, Up: sys-schema-procedures
26.4.4.6 The ps_setup_disable_instrument() Procedure ....................................................
Disables Performance Schema instruments with names that contain the argument. Produces a result set indicating how many instruments were disabled. Already disabled instruments do not count.
Parameters
'in_pattern VARCHAR(128)': The value used to match instrument names, which are identified by using '%in_pattern%' as an operand for a 'LIKE' pattern match.
A value of '''' matches all instruments.
Example
Disable a specific instrument:
mysql> CALL sys.ps_setup_disable_instrument('wait/lock/metadata/sql/mdl');
+-----------------------+
| summary |
+-----------------------+
| Disabled 1 instrument |
+-----------------------+Disable all mutex instruments:
mysql> CALL sys.ps_setup_disable_instrument('mutex');
+--------------------------+
| summary |
+--------------------------+
| Disabled 177 instruments |
+--------------------------+File: manual.info.tmp, Node: sys-ps-setup-disable-thread, Next: sys-ps-setup-enable-background-threads, Prev: sys-ps-setup-disable-instrument, Up: sys-schema-procedures
26.4.4.7 The ps_setup_disable_thread() Procedure ................................................
Given a connection ID, disables Performance Schema instrumentation for the thread. Produces a result set indicating how many threads were disabled. Already disabled threads do not count.
Parameters
Example
Disable a specific connection by its connection ID:
mysql> CALL sys.ps_setup_disable_thread(225);
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+Disable the current connection:
mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+File: manual.info.tmp, Node: sys-ps-setup-enable-background-threads, Next: sys-ps-setup-enable-consumer, Prev: sys-ps-setup-disable-thread, Up: sys-schema-procedures
26.4.4.8 The ps_setup_enable_background_threads() Procedure ...........................................................
Enables Performance Schema instrumentation for all background threads. Produces a result set indicating how many background threads were enabled. Already enabled threads do not count.
Parameters
None.
Example
mysql> CALL sys.ps_setup_enable_background_threads();
+-------------------------------+
| summary |
+-------------------------------+
| Enabled 24 background threads |
+-------------------------------+File: manual.info.tmp, Node: sys-ps-setup-enable-consumer, Next: sys-ps-setup-enable-instrument, Prev: sys-ps-setup-enable-background-threads, Up: sys-schema-procedures
26.4.4.9 The ps_setup_enable_consumer() Procedure .................................................
Enables Performance Schema consumers with names that contain the argument. Produces a result set indicating how many consumers were enabled. Already enabled consumers do not count.
Parameters
'consumer VARCHAR(128)': The value used to match consumer names, which are identified by using '%consumer%' as an operand for a 'LIKE' pattern match.
A value of '''' matches all consumers.
Example
Enable all statement consumers:
mysql> CALL sys.ps_setup_enable_consumer('statement');
+---------------------+
| summary |
+---------------------+
| Enabled 4 consumers |
+---------------------+File: manual.info.tmp, Node: sys-ps-setup-enable-instrument, Next: sys-ps-setup-enable-thread, Prev: sys-ps-setup-enable-consumer, Up: sys-schema-procedures
26.4.4.10 The ps_setup_enable_instrument() Procedure ....................................................
Enables Performance Schema instruments with names that contain the argument. Produces a result set indicating how many instruments were enabled. Already enabled instruments do not count.
Parameters
'in_pattern VARCHAR(128)': The value used to match instrument names, which are identified by using '%in_pattern%' as an operand for a 'LIKE' pattern match.
A value of '''' matches all instruments.
Example
Enable a specific instrument:
mysql> CALL sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
+----------------------+
| summary |
+----------------------+
| Enabled 1 instrument |
+----------------------+Enable all mutex instruments:
mysql> CALL sys.ps_setup_enable_instrument('mutex');
+-------------------------+
| summary |
+-------------------------+
| Enabled 177 instruments |
+-------------------------+File: manual.info.tmp, Node: sys-ps-setup-enable-thread, Next: sys-ps-setup-reload-saved, Prev: sys-ps-setup-enable-instrument, Up: sys-schema-procedures
26.4.4.11 The ps_setup_enable_thread() Procedure ................................................
Given a connection ID, enables Performance Schema instrumentation for the thread. Produces a result set indicating how many threads were enabled. Already enabled threads do not count.
Parameters
Example
Enable a specific connection by its connection ID:
mysql> CALL sys.ps_setup_enable_thread(225);
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+Enable the current connection:
mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID());
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+File: manual.info.tmp, Node: sys-ps-setup-reload-saved, Next: sys-ps-setup-reset-to-default, Prev: sys-ps-setup-enable-thread, Up: sys-schema-procedures
26.4.4.12 The ps_setup_reload_saved() Procedure ...............................................
Reloads a Performance Schema configuration saved earlier within the same session using note 'ps_setup_save()': sys-ps-setup-save. For more information, see the description of note 'ps_setup_save()': sys-ps-setup-save.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Parameters
None.
File: manual.info.tmp, Node: sys-ps-setup-reset-to-default, Next: sys-ps-setup-save, Prev: sys-ps-setup-reload-saved, Up: sys-schema-procedures
26.4.4.13 The ps_setup_reset_to_default() Procedure ...................................................
Resets the Performance Schema configuration to its default settings.
Parameters
Example
mysql> CALL sys.ps_setup_reset_to_default(TRUE)\G
*************************** 1. row ***************************
status: Resetting: setup_actors
DELETE
FROM performance_schema.setup_actors
WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%')
*************************** 1. row ***************************
status: Resetting: setup_actors
INSERT IGNORE INTO performance_schema.setup_actors
VALUES ('%', '%', '%')
...File: manual.info.tmp, Node: sys-ps-setup-save, Next: sys-ps-setup-show-disabled, Prev: sys-ps-setup-reset-to-default, Up: sys-schema-procedures
26.4.4.14 The ps_setup_save() Procedure .......................................
Saves the current Performance Schema configuration. This enables you to alter the configuration temporarily for debugging or other purposes, then restore it to the previous state by invoking the *note 'ps_setup_reload_saved()': sys-ps-setup-reload-saved. procedure.
To prevent other simultaneous calls to save the configuration, note 'ps_setup_save()': sys-ps-setup-save. acquires an advisory lock named 'sys.ps_setup_save' by calling the 'GET_LOCK()' function. note 'ps_setup_save()': sys-ps-setup-save. takes a timeout parameter to indicate how many seconds to wait if the lock already exists (which indicates that some other session has a saved configuration outstanding). If the timeout expires without obtaining the lock, *note 'ps_setup_save()': sys-ps-setup-save. fails.
It is intended you call note 'ps_setup_reload_saved()': sys-ps-setup-reload-saved. later within the same session as note 'ps_setup_save()': sys-ps-setup-save. because the configuration is saved in 'TEMPORARY' tables. note 'ps_setup_save()': sys-ps-setup-save. drops the temporary tables and releases the lock. If you end your session without invoking note 'ps_setup_save()': sys-ps-setup-save, the tables and lock disappear automatically.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Parameters
Example
mysql> CALL sys.ps_setup_save(10);
... MAKE PERFORMANCE SCHEMA CONFIGURATION CHANGES ...
mysql> CALL sys.ps_setup_reload_saved();File: manual.info.tmp, Node: sys-ps-setup-show-disabled, Next: sys-ps-setup-show-disabled-consumers, Prev: sys-ps-setup-save, Up: sys-schema-procedures
26.4.4.15 The ps_setup_show_disabled() Procedure ................................................
Displays all currently disabled Performance Schema configuration.
Parameters
'in_show_instruments BOOLEAN': Whether to display disabled instruments. This might be a long list.
'in_show_threads BOOLEAN': Whether to display disabled threads.
Example
mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
| 1 |
+----------------------------+
+---------------+
| enabled_users |
+---------------+
| '%'@'%' |
+---------------+
+-------------+----------------------+---------+-------+
| object_type | objects | enabled | timed |
+-------------+----------------------+---------+-------+
| EVENT | mysql.% | NO | NO |
| EVENT | performance_schema.% | NO | NO |
| EVENT | information_schema.% | NO | NO |
| FUNCTION | mysql.% | NO | NO |
| FUNCTION | performance_schema.% | NO | NO |
| FUNCTION | information_schema.% | NO | NO |
| PROCEDURE | mysql.% | NO | NO |
| PROCEDURE | performance_schema.% | NO | NO |
| PROCEDURE | information_schema.% | NO | NO |
| TABLE | mysql.% | NO | NO |
| TABLE | performance_schema.% | NO | NO |
| TABLE | information_schema.% | NO | NO |
| TRIGGER | mysql.% | NO | NO |
| TRIGGER | performance_schema.% | NO | NO |
| TRIGGER | information_schema.% | NO | NO |
+-------------+----------------------+---------+-------+
...File: manual.info.tmp, Node: sys-ps-setup-show-disabled-consumers, Next: sys-ps-setup-show-disabled-instruments, Prev: sys-ps-setup-show-disabled, Up: sys-schema-procedures
26.4.4.16 The ps_setup_show_disabled_consumers() Procedure ..........................................................
Displays all currently disabled Performance Schema consumers.
Parameters
None.
Example
mysql> CALL sys.ps_setup_show_disabled_consumers();
+----------------------------------+
| disabled_consumers |
+----------------------------------+
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_statements_history |
| events_statements_history_long |
| events_transactions_history |
| events_transactions_history_long |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
+----------------------------------+File: manual.info.tmp, Node: sys-ps-setup-show-disabled-instruments, Next: sys-ps-setup-show-enabled, Prev: sys-ps-setup-show-disabled-consumers, Up: sys-schema-procedures
26.4.4.17 The ps_setup_show_disabled_instruments() Procedure ............................................................
Displays all currently disabled Performance Schema instruments. This might be a long list.
Parameters
None.
Example
mysql> CALL sys.ps_setup_show_disabled_instruments()\G
*************************** 1. row ***************************
disabled_instruments: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc
timed: NO
*************************** 2. row ***************************
disabled_instruments: wait/synch/mutex/sql/THD::LOCK_query_plan
timed: NO
*************************** 3. row ***************************
disabled_instruments: wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit
timed: NO
...File: manual.info.tmp, Node: sys-ps-setup-show-enabled, Next: sys-ps-setup-show-enabled-consumers, Prev: sys-ps-setup-show-disabled-instruments, Up: sys-schema-procedures
26.4.4.18 The ps_setup_show_enabled() Procedure ...............................................
Displays all currently enabled Performance Schema configuration.
Parameters
'in_show_instruments BOOLEAN': Whether to display enabled instruments. This might be a long list.
'in_show_threads BOOLEAN': Whether to display enabled threads.
Example
mysql> CALL sys.ps_setup_show_enabled(FALSE, FALSE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
+---------------+
| enabled_users |
+---------------+
| '%'@'%' |
+---------------+
1 row in set (0.00 sec)
+-------------+---------+---------+-------+
| object_type | objects | enabled | timed |
+-------------+---------+---------+-------+
| EVENT | %.% | YES | YES |
| FUNCTION | %.% | YES | YES |
| PROCEDURE | %.% | YES | YES |
| TABLE | %.% | YES | YES |
| TRIGGER | %.% | YES | YES |
+-------------+---------+---------+-------+
5 rows in set (0.00 sec)
+---------------------------+
| enabled_consumers |
+---------------------------+
| events_statements_current |
| events_statements_history |
| global_instrumentation |
| statements_digest |
| thread_instrumentation |
+---------------------------+File: manual.info.tmp, Node: sys-ps-setup-show-enabled-consumers, Next: sys-ps-setup-show-enabled-instruments, Prev: sys-ps-setup-show-enabled, Up: sys-schema-procedures
26.4.4.19 The ps_setup_show_enabled_consumers() Procedure .........................................................
Displays all currently enabled Performance Schema consumers.
Parameters
None.
Example
mysql> CALL sys.ps_setup_show_enabled_consumers();
+---------------------------+
| enabled_consumers |
+---------------------------+
| events_statements_current |
| events_statements_history |
| global_instrumentation |
| statements_digest |
| thread_instrumentation |
+---------------------------+File: manual.info.tmp, Node: sys-ps-setup-show-enabled-instruments, Next: sys-ps-statement-avg-latency-histogram, Prev: sys-ps-setup-show-enabled-consumers, Up: sys-schema-procedures
26.4.4.20 The ps_setup_show_enabled_instruments() Procedure ...........................................................
Displays all currently enabled Performance Schema instruments. This might be a long list.
Parameters
None.
Example
mysql> CALL sys.ps_setup_show_enabled_instruments()\G
*************************** 1. row ***************************
enabled_instruments: wait/io/file/sql/map
timed: YES
*************************** 2. row ***************************
enabled_instruments: wait/io/file/sql/binlog
timed: YES
*************************** 3. row ***************************
enabled_instruments: wait/io/file/sql/binlog_cache
timed: YES
...File: manual.info.tmp, Node: sys-ps-statement-avg-latency-histogram, Next: sys-ps-trace-statement-digest, Prev: sys-ps-setup-show-enabled-instruments, Up: sys-schema-procedures
26.4.4.21 The ps_statement_avg_latency_histogram() Procedure ............................................................
Displays a textual histogram graph of the average latency values across all normalized statements tracked within the Performance Schema *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. table.
This procedure can be used to display a very high-level picture of the latency distribution of statements running within this MySQL instance.
Parameters
None.
Example
The histogram output in statement units. For example, '* = 2 units' in the histogram legend means that each '*' character represents 2 statements.
mysql> CALL sys.ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram:
. = 1 unit
* = 2 units
# = 3 units
(0 - 66ms) 88 | #############################
(66 - 133ms) 14 | ..............
(133 - 199ms) 4 | ....
(199 - 265ms) 5 | **
(265 - 332ms) 1 | .
(332 - 398ms) 0 |
(398 - 464ms) 1 | .
(464 - 531ms) 0 |
(531 - 597ms) 0 |
(597 - 663ms) 0 |
(663 - 730ms) 0 |
(730 - 796ms) 0 |
(796 - 863ms) 0 |
(863 - 929ms) 0 |
(929 - 995ms) 0 |
(995 - 1062ms) 0 |
Total Statements: 114; Buckets: 16; Bucket Size: 66 ms;File: manual.info.tmp, Node: sys-ps-trace-statement-digest, Next: sys-ps-trace-thread, Prev: sys-ps-statement-avg-latency-histogram, Up: sys-schema-procedures
26.4.4.22 The ps_trace_statement_digest() Procedure ...................................................
Traces all Performance Schema instrumentation for a specific statement digest.
If you find a statement of interest within the Performance Schema *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. table, specify its 'DIGEST' column MD5 value to this procedure and indicate the polling duration and interval. The result is a report of all statistics tracked within Performance Schema for that digest for the interval.
The procedure also attempts to execute note 'EXPLAIN': explain. for the longest running example of the digest during the interval. This attempt might fail because the Performance Schema truncates long 'SQL_TEXT' values. Consequently, note 'EXPLAIN': explain. fails due to parse errors.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Parameters
'in_digest VARCHAR(32)': The statement digest identifier to analyze.
'in_runtime INT': How long to run the analysis in seconds.
'in_interval DECIMAL(2,2)': The analysis interval in seconds (which can be fractional) at which to try to take snapshots.
'in_start_fresh BOOLEAN': Whether to truncate the Performance Schema note 'events_statements_history_long': performance-schema-events-statements-history-long-table. and note 'events_stages_history_long': performance-schema-events-stages-history-long-table. tables before starting.
'in_auto_enable BOOLEAN': Whether to automatically enable required consumers.
Example
mysql> CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 10, 0.1, TRUE, TRUE);
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
1 row in set (9.11 sec)
+------------+-----------+-----------+-----------+---------------+------------+------------+
| executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans |
+------------+-----------+-----------+-----------+---------------+------------+------------+
| 21 | 4.11 ms | 2.00 ms | 0 | 21 | 0 | 0 |
+------------+-----------+-----------+-----------+---------------+------------+------------+
1 row in set (9.11 sec)
+------------------------------------------+-------+-----------+
| event_name | count | latency |
+------------------------------------------+-------+-----------+
| stage/sql/checking query cache for query | 16 | 724.37 us |
| stage/sql/statistics | 16 | 546.92 us |
| stage/sql/freeing items | 18 | 520.11 us |
| stage/sql/init | 51 | 466.80 us |
...
| stage/sql/cleaning up | 18 | 11.92 us |
| stage/sql/executing | 16 | 6.95 us |
+------------------------------------------+-------+-----------+
17 rows in set (9.12 sec)
+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
1 row in set (9.16 sec)
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| 166646 | 618.43 us | 1.00 ms | 0 | 1 | 0 | 0 |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
1 row in set (9.16 sec)
# Truncated for clarity...
+-----------------------------------------------------------------+
| sql_text |
+-----------------------------------------------------------------+
| select hibeventhe0_.id as id1382_, hibeventhe0_.createdTime ... |
+-----------------------------------------------------------------+
1 row in set (9.17 sec)
+------------------------------------------+-----------+
| event_name | latency |
+------------------------------------------+-----------+
| stage/sql/init | 8.61 us |
| stage/sql/Waiting for query cache lock | 453.23 us |
| stage/sql/init | 331.07 ns |
| stage/sql/checking query cache for query | 43.04 us |
...
| stage/sql/freeing items | 30.46 us |
| stage/sql/cleaning up | 662.13 ns |
+------------------------------------------+-----------+
18 rows in set (9.23 sec)
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
| 1 | SIMPLE | hibeventhe0_ | const | fixedTime | fixedTime | 775 | const,const | 1 | NULL |
+----+-------------+--------------+-------+---------------+-----------+---------+-------------+------+-------+
1 row in set (9.27 sec)
Query OK, 0 rows affected (9.28 sec)File: manual.info.tmp, Node: sys-ps-trace-thread, Next: sys-ps-truncate-all-tables, Prev: sys-ps-trace-statement-digest, Up: sys-schema-procedures
26.4.4.23 The ps_trace_thread() Procedure .........................................
Dumps all Performance Schema data for an instrumented thread to a '.dot' formatted graph file (for the DOT graph description language). Each result set returned from the procedure should be used for a complete graph.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Parameters
'in_thread_id INT': The thread to trace.
'in_outfile VARCHAR(255)': The name to use for the '.dot' output file.
'in_max_runtime DECIMAL(20,2)': The maximum number of seconds (which can be fractional) to collect data. Use 'NULL' to collect data for the default of 60 seconds.
'in_interval DECIMAL(20,2)': The number of seconds (which can be fractional) to sleep between data collections. Use 'NULL' to sleep for the default of 1 second.
'in_start_fresh BOOLEAN': Whether to reset all Performance Schema data before tracing.
'in_auto_setup BOOLEAN': Whether to disable all other threads and enable all instruments and consumers. This also resets the settings at the end of the run.
'in_debug BOOLEAN': Whether to include 'file:lineno' information in the graph.
Example
mysql> CALL sys.ps_trace_thread(25, CONCAT('/tmp/stack-', REPLACE(NOW(), ' ', '-'), '.dot'), NULL, NULL, TRUE, TRUE, TRUE);
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)
+---------------------------------------------+
| Info |
+---------------------------------------------+
| Data collection starting for THREAD_ID = 25 |
+---------------------------------------------+
1 row in set (0.03 sec)
+-----------------------------------------------------------+
| Info |
+-----------------------------------------------------------+
| Stack trace written to /tmp/stack-2014-02-16-21:18:41.dot |
+-----------------------------------------------------------+
1 row in set (60.07 sec)
+-------------------------------------------------------------------+
| Convert to PDF |
+-------------------------------------------------------------------+
| dot -Tpdf -o /tmp/stack_25.pdf /tmp/stack-2014-02-16-21:18:41.dot |
+-------------------------------------------------------------------+
1 row in set (60.07 sec)
+-------------------------------------------------------------------+
| Convert to PNG |
+-------------------------------------------------------------------+
| dot -Tpng -o /tmp/stack_25.png /tmp/stack-2014-02-16-21:18:41.dot |
+-------------------------------------------------------------------+
1 row in set (60.07 sec)
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (60.32 sec)File: manual.info.tmp, Node: sys-ps-truncate-all-tables, Next: sys-statement-performance-analyzer, Prev: sys-ps-trace-thread, Up: sys-schema-procedures
26.4.4.24 The ps_truncate_all_tables() Procedure ................................................
Truncates all Performance Schema summary tables, resetting all aggregated instrumentation as a snapshot. Produces a result set indicating how many tables were truncated.
Parameters
Example
mysql> CALL sys.ps_truncate_all_tables(FALSE);
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+File: manual.info.tmp, Node: sys-statement-performance-analyzer, Next: sys-table-exists, Prev: sys-ps-truncate-all-tables, Up: sys-schema-procedures
26.4.4.25 The statement_performance_analyzer() Procedure ........................................................
Creates a report of the statements running on the server. The views are calculated based on the overall and/or delta activity.
This procedure disables binary logging during its execution by manipulating the session value of the 'sql_log_bin' system variable. That is a restricted operation, so the procedure requires privileges sufficient to set restricted session variables. See *note system-variable-privileges::.
Parameters
'in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup')': The action to take. These values are permitted:
* 'snapshot': Store a snapshot. The default is to make a
snapshot of the current content of the Performance Schema
*note 'events_statements_summary_by_digest':
performance-schema-statement-summary-tables. table. By
setting 'in_table', this can be overwritten to copy the
content of the specified table. The snapshot is stored in the
*note 'sys': sys-schema. schema 'tmp_digests' temporary table.
* 'overall': Generate an analysis based on the content of the
table specified by 'in_table'. For the overall analysis,
'in_table' can be 'NOW()' to use a fresh snapshot. This
overwrites an existing snapshot. Use 'NULL' for 'in_table' to
use the existing snapshot. If 'in_table' is 'NULL' and no
snapshot exists, a new snapshot is created. The 'in_views'
parameter and the 'statement_performance_analyzer.limit'
configuration option affect the operation of this procedure.
* 'delta': Generate a delta analysis. The delta is calculated
between the reference table specified by 'in_table' and the
snapshot, which must exist. This action uses the *note 'sys':
sys-schema. schema 'tmp_digests_delta' temporary table. The
'in_views' parameter and the
'statement_performance_analyzer.limit' configuration option
affect the operation of this procedure.
* 'create_table': Create a regular table suitable for storing
the snapshot for later use (for example, for calculating
deltas).
* 'create_tmp': Create a temporary table suitable for storing
the snapshot for later use (for example, for calculating
deltas).
* 'save': Save the snapshot in the table specified by
'in_table'. The table must exist and have the correct
structure. If no snapshot exists, a new snapshot is created.
* 'cleanup': Remove the temporary tables used for the snapshot
and delta.'in_table VARCHAR(129)': The table parameter used for some of the actions specified by the 'in_action' parameter. Use the format DB_NAME.TBL_NAME or TBL_NAME without using any backtick ('`') identifier-quoting characters. Periods ('.') are not supported in database and table names.
The meaning of the 'in_table' value for each 'in_action' value is detailed in the individual 'in_action' value descriptions.
'in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom')': Which views to include. This parameter is a 'SET' value, so it can contain multiple view names, separated by commas. The default is to include all views except 'custom'. The following values are permitted:
* 'with_runtimes_in_95th_percentile': Use the *note
'statements_with_runtimes_in_95th_percentile':
sys-statements-with-runtimes-in-95th-percentile. view.
* 'analysis': Use the *note 'statement_analysis':
sys-statement-analysis. view.
* 'with_errors_or_warnings': Use the *note
'statements_with_errors_or_warnings':
sys-statements-with-errors-or-warnings. view.
* 'with_full_table_scans': Use the *note
'statements_with_full_table_scans':
sys-statements-with-full-table-scans. view.
* 'with_sorting': Use the *note 'statements_with_sorting':
sys-statements-with-sorting. view.
* 'with_temp_tables': Use the *note
'statements_with_temp_tables':
sys-statements-with-temp-tables. view.
* 'custom': Use a custom view. This view must be specified
using the 'statement_performance_analyzer.view' configuration
option to name a query or an existing view.Configuration Options
note 'statement_performance_analyzer()': sys-statement-performance-analyzer. operation can be modified using the following configuration options or their corresponding user-defined variables (see note sys-sys-config::):
'debug', '@sys.debug'
If this option is 'ON', produce debugging output. The default is 'OFF'.
'statement_performance_analyzer.limit', '@sys.statement_performance_analyzer.limit'
The maximum number of rows to return for views that have no built-in limit. The default is 100.
'statement_performance_analyzer.view', '@sys.statement_performance_analyzer.view'
The custom query or view to be used. If the option value contains a space, it is interpreted as a query. Otherwise, it must be the name of an existing view that queries the Performance Schema *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. table. There cannot be any 'LIMIT' clause in the query or view definition if the 'statement_performance_analyzer.limit' configuration option is greater than 0. If specifying a view, use the same format as for the 'in_table' parameter. The default is 'NULL' (no custom view defined).
Example
To create a report with the queries in the 95th percentile since the last truncation of *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. and with a one-minute delta period:
Create a temporary table to store the initial snapshot.
Create the initial snapshot.
Save the initial snapshot in the temporary table.
Wait one minute.
Create a new snapshot.
Perform analysis based on the new snapshot.
Perform analysis based on the delta between the initial and new snapshots.
mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL); Query OK, 0 rows affected (0.08 sec)
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); Query OK, 0 rows affected (0.02 sec)
mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL); Query OK, 0 rows affected (0.00 sec)
mysql> DO SLEEP(60); Query OK, 0 rows affected (1 min 0.00 sec)
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); Query OK, 0 rows affected (0.02 sec)
mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); +-----------------------------------------+ | Next Output | +-----------------------------------------+ | Queries with Runtime in 95th Percentile | +-----------------------------------------+ 1 row in set (0.05 sec)
...
mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile'); +-----------------------------------------+ | Next Output | +-----------------------------------------+ | Queries with Runtime in 95th Percentile | +-----------------------------------------+ 1 row in set (0.03 sec)
...
Create an overall report of the 95th percentile queries and the top 10 queries with full table scans:
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> SET @sys.statement_performance_analyzer.limit = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
+-----------------------------------------+
| Next Output |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)
...
+-------------------------------------+
| Next Output |
+-------------------------------------+
| Top 10 Queries with Full Table Scan |
+-------------------------------------+
1 row in set (0.09 sec)
...Use a custom view showing the top 10 queries sorted by total execution time, refreshing the view every minute using the 'watch' command in Linux:
mysql> CREATE OR REPLACE VIEW mydb.my_statements AS
SELECT sys.format_statement(DIGEST_TEXT) AS query,
SCHEMA_NAME AS db,
COUNT_STAR AS exec_count,
sys.format_time(SUM_TIMER_WAIT) AS total_latency,
sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC;
Query OK, 0 rows affected (0.10 sec)
mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
Query OK, 0 rows affected (0.10 sec)
$> watch -n 60 "mysql sys --table -e \"
> SET @sys.statement_performance_analyzer.view = 'mydb.my_statements';
> SET @sys.statement_performance_analyzer.limit = 10;
> CALL statement_performance_analyzer('snapshot', NULL, NULL);
> CALL statement_performance_analyzer('delta', 'mydb.digests_prev', 'custom');
> CALL statement_performance_analyzer('save', 'mydb.digests_prev', NULL);
> \""
Every 60.0s: mysql sys --table -e " ... Mon Dec 22 10:58:51 2014
+----------------------------------+
| Next Output |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...File: manual.info.tmp, Node: sys-table-exists, Prev: sys-statement-performance-analyzer, Up: sys-schema-procedures
26.4.4.26 The table_exists() Procedure ......................................
Tests whether a given table exists as a regular table, a 'TEMPORARY' table, or a view. The procedure returns the table type in an 'OUT' parameter. If both a temporary and a permanent table exist with the given name, 'TEMPORARY' is returned.
Parameters
'in_db VARCHAR(64)': The name of the database in which to check for table existance.
'in_table VARCHAR(64)': The name of the table to check the existance of.
'out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY')': The return value. This is an 'OUT' parameter, so it must be a variable into which the table type can be stored. When the procedure returns, the variable has one of the following values to indicate whether the table exists:
* '''': The table name does not exist as a base table,
'TEMPORARY' table, or view.
* 'BASE TABLE': The table name exists as a base (permanent)
table.
* 'VIEW': The table name exists as a view.
* 'TEMPORARY': The table name exists as a 'TEMPORARY' table.Example
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)
mysql> USE db1;
Database changed
mysql> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE t2 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.20 sec)
mysql> CREATE view v_t1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sys.table_exists('db1', 't1', @exists); SELECT @exists;
Query OK, 0 rows affected (0.01 sec)
+-----------+
| @exists |
+-----------+
| TEMPORARY |
+-----------+
1 row in set (0.00 sec)
mysql> CALL sys.table_exists('db1', 't2', @exists); SELECT @exists;
Query OK, 0 rows affected (0.02 sec)
+------------+
| @exists |
+------------+
| BASE TABLE |
+------------+
1 row in set (0.00 sec)
mysql> CALL sys.table_exists('db1', 'v_t1', @exists); SELECT @exists;
Query OK, 0 rows affected (0.02 sec)
+---------+
| @exists |
+---------+
| VIEW |
+---------+
1 row in set (0.00 sec)
mysql> CALL sys.table_exists('db1', 't3', @exists); SELECT @exists;
Query OK, 0 rows affected (0.00 sec)
+---------+
| @exists |
+---------+
| |
+---------+
1 row in set (0.00 sec)File: manual.info.tmp, Node: sys-schema-functions, Prev: sys-schema-procedures, Up: sys-schema-reference
Menu:
sys-version-patch:: The version_patch() Function
The following sections describe *note 'sys': sys-schema. schema stored functions.
File: manual.info.tmp, Node: sys-extract-schema-from-file-name, Next: sys-extract-table-from-file-name, Prev: sys-schema-functions, Up: sys-schema-functions
26.4.5.1 The extract_schema_from_file_name() Function .....................................................
Given a file path name, returns the path component that represents the schema name. This function assumes that the file name lies within the schema directory. For this reason, it does not work with partitions or tables defined using their own 'DATA_DIRECTORY' table option.
This function is useful when extracting file I/O information from the Performance Schema that includes file path names. It provides a convenient way to display schema names, which can be more easily understood than full path names, and can be used in joins against object schema names.
Parameters
Return Value
A 'VARCHAR(64)' value.
Example
mysql> SELECT sys.extract_schema_from_file_name('/usr/local/mysql/data/world/City.ibd');
+---------------------------------------------------------------------------+
| sys.extract_schema_from_file_name('/usr/local/mysql/data/world/City.ibd') |
+---------------------------------------------------------------------------+
| world |
+---------------------------------------------------------------------------+File: manual.info.tmp, Node: sys-extract-table-from-file-name, Next: sys-format-bytes, Prev: sys-extract-schema-from-file-name, Up: sys-schema-functions
26.4.5.2 The extract_table_from_file_name() Function ....................................................
Given a file path name, returns the path component that represents the table name.
This function is useful when extracting file I/O information from the Performance Schema that includes file path names. It provides a convenient way to display table names, which can be more easily understood than full path names, and can be used in joins against object table names.
Parameters
Return Value
A 'VARCHAR(64)' value.
Example
mysql> SELECT sys.extract_table_from_file_name('/usr/local/mysql/data/world/City.ibd');
+--------------------------------------------------------------------------+
| sys.extract_table_from_file_name('/usr/local/mysql/data/world/City.ibd') |
+--------------------------------------------------------------------------+
| City |
+--------------------------------------------------------------------------+File: manual.info.tmp, Node: sys-format-bytes, Next: sys-format-path, Prev: sys-extract-table-from-file-name, Up: sys-schema-functions
26.4.5.3 The format_bytes() Function ....................................
Given a byte count, converts it to human-readable format and returns a string consisting of a value and a units indicator. Depending on the size of the value, the units part is 'bytes', 'KiB' (kibibytes), 'MiB' (mebibytes), 'GiB' (gibibytes), 'TiB' (tebibytes), or 'PiB' (pebibytes).
Parameters
Return Value
A 'TEXT' value.
Example
mysql> SELECT sys.format_bytes(512), sys.format_bytes(18446644073709551615);
+-----------------------+----------------------------------------+
| sys.format_bytes(512) | sys.format_bytes(18446644073709551615) |
+-----------------------+----------------------------------------+
| 512 bytes | 16383.91 PiB |
+-----------------------+----------------------------------------+File: manual.info.tmp, Node: sys-format-path, Next: sys-format-statement, Prev: sys-format-bytes, Up: sys-schema-functions
26.4.5.4 The format_path() Function ...................................
Given a path name, returns the modified path name after replacing subpaths that match the values of the following system variables, in order:
datadir
tmpdir
slave_load_tmpdir
innodb_data_home_dir
innodb_log_group_home_dir
innodb_undo_directory
basedirA value that matches the value of system variable SYSVAR is replaced with the string '@@GLOBAL.SYSVAR'.
Prior to MySQL 5.7.14, backslashes in Windows path names are converted to forward slashes in the result.
Parameters
Return Value
A 'VARCHAR(512) CHARACTER SET utf8' value.
Example
mysql> SELECT sys.format_path('/usr/local/mysql/data/world/City.ibd');
+---------------------------------------------------------+
| sys.format_path('/usr/local/mysql/data/world/City.ibd') |
+---------------------------------------------------------+
| @@datadir/world/City.ibd |
+---------------------------------------------------------+File: manual.info.tmp, Node: sys-format-statement, Next: sys-format-time, Prev: sys-format-path, Up: sys-schema-functions
26.4.5.5 The format_statement() Function ........................................
Given a string (normally representing an SQL statement), reduces it to the length given by the 'statement_truncate_len' configuration option, and returns the result. No truncation occurs if the string is shorter than 'statement_truncate_len'. Otherwise, the middle part of the string is replaced by an ellipsis ('...').
This function is useful for formatting possibly lengthy statements retrieved from Performance Schema tables to a known fixed maximum length.
Parameters
Configuration Options
note 'format_statement()': sys-format-statement. operation can be modified using the following configuration options or their corresponding user-defined variables (see note sys-sys-config::):
'statement_truncate_len', '@sys.statement_truncate_len'
The maximum length of statements returned by the *note 'format_statement()': sys-format-statement. function. Longer statements are truncated to this length. The default is 64.
Return Value
A 'LONGTEXT' value.
Example
By default, *note 'format_statement()': sys-format-statement. truncates statements to be no more than 64 characters. Setting '@sys.statement_truncate_len' changes the truncation length for the current session:
mysql> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
mysql> SELECT sys.format_statement(@stmt);
+----------------------------------------------------------+
| sys.format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
mysql> SET @sys.statement_truncate_len = 32;
mysql> SELECT sys.format_statement(@stmt);
+-----------------------------------+
| sys.format_statement(@stmt) |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+File: manual.info.tmp, Node: sys-format-time, Next: sys-list-add, Prev: sys-format-statement, Up: sys-schema-functions
26.4.5.6 The format_time() Function ...................................
Given a Performance Schema latency or wait time in picoseconds, converts it to human-readable format and returns a string consisting of a value and a units indicator. Depending on the size of the value, the units part is 'ps' (picoseconds), 'ns' (nanoseconds), 'us' (microseconds), 'ms' (milliseconds), 's' (seconds), 'm' (minutes), 'h' (hours), 'd' (days), or 'w' (weeks).
Parameters
Return Value
A 'TEXT' value.
Example
mysql> SELECT sys.format_time(3501), sys.format_time(188732396662000);
+-----------------------+----------------------------------+
| sys.format_time(3501) | sys.format_time(188732396662000) |
+-----------------------+----------------------------------+
| 3.50 ns | 3.15 m |
+-----------------------+----------------------------------+File: manual.info.tmp, Node: sys-list-add, Next: sys-list-drop, Prev: sys-format-time, Up: sys-schema-functions
26.4.5.7 The list_add() Function ................................
Adds a value to a comma-separated list of values and returns the result.
This function and *note 'list_drop()': sys-list-drop. can be useful for manipulating the value of system variables such as 'sql_mode' and 'optimizer_switch' that take a comma-separated list of values.
Parameters
'in_list TEXT': The list to be modified.
'in_add_value TEXT': The value to add to the list.
Return Value
A 'TEXT' value.
Example
mysql> SELECT @@sql_mode;
+----------------------------------------+
| @@sql_mode |
+----------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES |
+----------------------------------------+
mysql> SET @@sql_mode = sys.list_add(@@sql_mode, 'NO_ENGINE_SUBSTITUTION');
mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
mysql> SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+File: manual.info.tmp, Node: sys-list-drop, Next: sys-ps-is-account-enabled, Prev: sys-list-add, Up: sys-schema-functions
26.4.5.8 The list_drop() Function .................................
Removes a value from a comma-separated list of values and returns the result. For more information, see the description of *note 'list_add()': sys-list-add.
Parameters
'in_list TEXT': The list to be modified.
'in_drop_value TEXT': The value to drop from the list.
Return Value
A 'TEXT' value.
File: manual.info.tmp, Node: sys-ps-is-account-enabled, Next: sys-ps-is-consumer-enabled, Prev: sys-list-drop, Up: sys-schema-functions
26.4.5.9 The ps_is_account_enabled() Function .............................................
Returns 'YES' or 'NO' to indicate whether Performance Schema instrumentation for a given account is enabled.
Parameters
'in_host VARCHAR(60)': The host name of the account to check.
'in_user VARCHAR(32)': The user name of the account to check.
Return Value
An 'ENUM('YES','NO')' value.
Example
mysql> SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES |
+------------------------------------------------+File: manual.info.tmp, Node: sys-ps-is-consumer-enabled, Next: sys-ps-is-instrument-default-enabled, Prev: sys-ps-is-account-enabled, Up: sys-schema-functions
26.4.5.10 The ps_is_consumer_enabled() Function ...............................................
Returns 'YES' or 'NO' to indicate whether a given Performance Schema consumer is enabled, or 'NULL' if the argument is 'NULL'. If the argument is not a valid consumer name, an error occurs. (Prior to MySQL 5.7.28, the function returns 'NULL' if the argument is not a valid consumer name.)
This function accounts for the consumer hierarchy, so a consumer is not considered enabled unless all consumers on which depends are also enabled. For information about the consumer hierarchy, see *note performance-schema-consumer-filtering::.
Parameters
Return Value
An 'ENUM('YES','NO')' value.
Example
mysql> SELECT sys.ps_is_consumer_enabled('thread_instrumentation');
+------------------------------------------------------+
| sys.ps_is_consumer_enabled('thread_instrumentation') |
+------------------------------------------------------+
| YES |
+------------------------------------------------------+File: manual.info.tmp, Node: sys-ps-is-instrument-default-enabled, Next: sys-ps-is-instrument-default-timed, Prev: sys-ps-is-consumer-enabled, Up: sys-schema-functions
26.4.5.11 The ps_is_instrument_default_enabled() Function .........................................................
Returns 'YES' or 'NO' to indicate whether a given Performance Schema instrument is enabled by default.
Parameters
Return Value
An 'ENUM('YES','NO')' value.
Example
mysql> SELECT sys.ps_is_instrument_default_enabled('memory/innodb/row_log_buf');
+-------------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/innodb/row_log_buf') |
+-------------------------------------------------------------------+
| NO |
+-------------------------------------------------------------------+
mysql> SELECT sys.ps_is_instrument_default_enabled('statement/sql/alter_user');
+------------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('statement/sql/alter_user') |
+------------------------------------------------------------------+
| YES |
+------------------------------------------------------------------+File: manual.info.tmp, Node: sys-ps-is-instrument-default-timed, Next: sys-ps-is-thread-instrumented, Prev: sys-ps-is-instrument-default-enabled, Up: sys-schema-functions
26.4.5.12 The ps_is_instrument_default_timed() Function .......................................................
Returns 'YES' or 'NO' to indicate whether a given Performance Schema instrument is timed by default.
Parameters
Return Value
An 'ENUM('YES','NO')' value.
Example
mysql> SELECT sys.ps_is_instrument_default_timed('memory/innodb/row_log_buf');
+-----------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/innodb/row_log_buf') |
+-----------------------------------------------------------------+
| NO |
+-----------------------------------------------------------------+
mysql> SELECT sys.ps_is_instrument_default_timed('statement/sql/alter_user');
+----------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('statement/sql/alter_user') |
+----------------------------------------------------------------+
| YES |
+----------------------------------------------------------------+File: manual.info.tmp, Node: sys-ps-is-thread-instrumented, Next: sys-ps-thread-account, Prev: sys-ps-is-instrument-default-timed, Up: sys-schema-functions
26.4.5.13 The ps_is_thread_instrumented() Function ..................................................
Returns 'YES' or 'NO' to indicate whether Performance Schema instrumentation for a given connection ID is enabled, 'UNKNOWN' if the ID is unknown, or 'NULL' if the ID is 'NULL'.
Parameters
Return Value
An 'ENUM('YES','NO','UNKNOWN')' value.
Example
mysql> SELECT sys.ps_is_thread_instrumented(43);
+-----------------------------------+
| sys.ps_is_thread_instrumented(43) |
+-----------------------------------+
| UNKNOWN |
+-----------------------------------+
mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+------------------------------------------------+
| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+------------------------------------------------+
| YES |
+------------------------------------------------+File: manual.info.tmp, Node: sys-ps-thread-account, Next: sys-ps-thread-id, Prev: sys-ps-is-thread-instrumented, Up: sys-schema-functions
26.4.5.14 The ps_thread_account() Function ..........................................
Given a Performance Schema thread ID, returns the 'USER_NAME@HOST_NAME' account associated with the thread.
Parameters
Return Value
A 'TEXT' value.
Example
mysql> SELECT sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID()));
+----------------------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID())) |
+----------------------------------------------------------+
| root@localhost |
+----------------------------------------------------------+File: manual.info.tmp, Node: sys-ps-thread-id, Next: sys-ps-thread-stack, Prev: sys-ps-thread-account, Up: sys-schema-functions
26.4.5.15 The ps_thread_id() Function .....................................
Returns the Performance Schema thread ID assigned to a given connection ID, or the thread ID for the current connection if the connection ID is 'NULL'.
Parameters
Return Value
A 'BIGINT UNSIGNED' value.
Example
mysql> SELECT sys.ps_thread_id(260);
+-----------------------+
| sys.ps_thread_id(260) |
+-----------------------+
| 285 |
+-----------------------+File: manual.info.tmp, Node: sys-ps-thread-stack, Next: sys-ps-thread-trx-info, Prev: sys-ps-thread-id, Up: sys-schema-functions
26.4.5.16 The ps_thread_stack() Function ........................................
Returns a JSON formatted stack of all statements, stages, and events within the Performance Schema for a given thread ID.
Parameters
'in_thread_id BIGINT': The ID of the thread to trace. The value should match the 'THREAD_ID' column from some Performance Schema *note 'threads': performance-schema-threads-table. table row.
'in_verbose BOOLEAN': Whether to include 'file:lineno' information in the events.
Return Value
A 'LONGTEXT CHARACTER SET latin1' value.
Example
mysql> SELECT sys.ps_thread_stack(37, FALSE) AS thread_stack\G
*************************** 1. row ***************************
thread_stack: {"rankdir": "LR","nodesep": "0.10",
"stack_created": "2014-02-19 13:39:03", "mysql_version": "5.7.3-m13",
"mysql_user": "root@localhost","events": [{"nesting_event_id": "0",
"event_id": "10", "timer_wait": 256.35, "event_info": "sql/select",
"wait_info": "select @@version_comment limit 1\nerrors: 0\nwarnings: 0\nlock time:
...File: manual.info.tmp, Node: sys-ps-thread-trx-info, Next: sys-quote-identifier, Prev: sys-ps-thread-stack, Up: sys-schema-functions
26.4.5.17 The ps_thread_trx_info() Function ...........................................
Returns a JSON object containing information about a given thread. The information includes the current transaction, and the statements it has already executed, derived from the Performance Schema note 'events_transactions_current': performance-schema-events-transactions-current-table. and note 'events_statements_history': performance-schema-events-statements-history-table. tables. (The consumers for those tables must be enabled to obtain full data in the JSON object.)
If the output exceeds the truncation length (65535 by default), a JSON error object is returned, such as:
{ "error": "Trx info truncated: Row 6 was cut by GROUP_CONCAT()" }Similar error objects are returned for other warnings and exceptions raised during function execution.
Parameters
Configuration Options
note 'ps_thread_trx_info()': sys-ps-thread-trx-info. operation can be modified using the following configuration options or their corresponding user-defined variables (see note sys-sys-config::):
'ps_thread_trx_info.max_length', '@sys.ps_thread_trx_info.max_length'
The maximum length of the output. The default is 65535.
Return Value
A 'LONGTEXT' value.
Example
mysql> SELECT sys.ps_thread_trx_info(48)\G
*************************** 1. row ***************************
sys.ps_thread_trx_info(48): [
{
"time": "790.70 us",
"state": "COMMITTED",
"mode": "READ WRITE",
"autocommitted": "NO",
"gtid": "AUTOMATIC",
"isolation": "REPEATABLE READ",
"statements_executed": [
{
"sql_text": "INSERT INTO info VALUES (1, \'foo\')",
"time": "471.02 us",
"schema": "trx",
"rows_examined": 0,
"rows_affected": 1,
"rows_sent": 0,
"tmp_tables": 0,
"tmp_disk_tables": 0,
"sort_rows": 0,
"sort_merge_passes": 0
},
{
"sql_text": "COMMIT",
"time": "254.42 us",
"schema": "trx",
"rows_examined": 0,
"rows_affected": 0,
"rows_sent": 0,
"tmp_tables": 0,
"tmp_disk_tables": 0,
"sort_rows": 0,
"sort_merge_passes": 0
}
]
},
{
"time": "426.20 us",
"state": "COMMITTED",
"mode": "READ WRITE",
"autocommitted": "NO",
"gtid": "AUTOMATIC",
"isolation": "REPEATABLE READ",
"statements_executed": [
{
"sql_text": "INSERT INTO info VALUES (2, \'bar\')",
"time": "107.33 us",
"schema": "trx",
"rows_examined": 0,
"rows_affected": 1,
"rows_sent": 0,
"tmp_tables": 0,
"tmp_disk_tables": 0,
"sort_rows": 0,
"sort_merge_passes": 0
},
{
"sql_text": "COMMIT",
"time": "213.23 us",
"schema": "trx",
"rows_examined": 0,
"rows_affected": 0,
"rows_sent": 0,
"tmp_tables": 0,
"tmp_disk_tables": 0,
"sort_rows": 0,
"sort_merge_passes": 0
}
]
}
]File: manual.info.tmp, Node: sys-quote-identifier, Next: sys-sys-get-config, Prev: sys-ps-thread-trx-info, Up: sys-schema-functions
26.4.5.18 The quote_identifier() Function .........................................
Given a string argument, this function produces a quoted identifier suitable for inclusion in SQL statements. This is useful when a value to be used as an identifier is a reserved word or contains backtick ('`') characters. It was added in MySQL 5.7.14.
Parameters
'in_identifier TEXT': The identifier to quote.
Return Value
A 'TEXT' value.
Example
mysql> SELECT sys.quote_identifier('plain');
+-------------------------------+
| sys.quote_identifier('plain') |
+-------------------------------+
| `plain` |
+-------------------------------+
mysql> SELECT sys.quote_identifier('trick`ier');
+-----------------------------------+
| sys.quote_identifier('trick`ier') |
+-----------------------------------+
| `trick``ier` |
+-----------------------------------+
mysql> SELECT sys.quote_identifier('integer');
+---------------------------------+
| sys.quote_identifier('integer') |
+---------------------------------+
| `integer` |
+---------------------------------+File: manual.info.tmp, Node: sys-sys-get-config, Next: sys-version-major, Prev: sys-quote-identifier, Up: sys-schema-functions
26.4.5.19 The sys_get_config() Function .......................................
Given a configuration option name, returns the option value from the *note 'sys_config': sys-sys-config. table, or the provided default value (which may be 'NULL') if the option does not exist in the table.
If *note 'sys_get_config()': sys-sys-get-config. returns the default value and that value is 'NULL', it is expected that the caller is able to handle 'NULL' for the given configuration option.
By convention, routines that call note 'sys_get_config()': sys-sys-get-config. first check whether the corresponding user-defined variable exists and is non-'NULL'. If so, the routine uses the variable value without reading the note 'sys_config': sys-sys-config. table. If the variable does not exist or is 'NULL', the routine reads the option value from the table and sets the user-defined variable to that value. For more information about the relationship between configuration options and their corresponding user-defined variables, see *note sys-sys-config::.
If you want to check whether the configuration option has already been set and, if not, use the return value of 'sys_get_config()', you can use 'IFNULL(...)' (see example later). However, this should not be done inside a loop (for example, for each row in a result set) because for repeated calls where the assignment is needed only in the first iteration, using 'IFNULL(...)' is expected to be significantly slower than using an 'IF (...) THEN ... END IF;' block (see example later).
Parameters
'in_variable_name VARCHAR(128)': The name of the configuration option for which to return the value.
'in_default_value VARCHAR(128)': The default value to return if the configuration option is not found in the 'sys_config' table.
Return Value
A 'VARCHAR(128)' value.
Example
Get a configuration value from the *note 'sys_config': sys-sys-config. table, falling back to 128 as the default if the option is not present in the table:
mysql> SELECT sys.sys_get_config('statement_truncate_len', 128) AS Value;
+-------+
| Value |
+-------+
| 64 |
+-------+One-liner example: Check whether the option is already set; if not, assign the 'IFNULL(...)' result (using the value from the *note 'sys_config': sys-sys-config. table):
mysql> SET @sys.statement_truncate_len =
IFNULL(@sys.statement_truncate_len,
sys.sys_get_config('statement_truncate_len', 64));'IF (...) THEN ... END IF;' block example: Check whether the option is already set; if not, assign the value from the *note 'sys_config': sys-sys-config. table:
IF (@sys.statement_truncate_len IS NULL) THEN
SET @sys.statement_truncate_len = sys.sys_get_config('statement_truncate_len', 64);
END IF;File: manual.info.tmp, Node: sys-version-major, Next: sys-version-minor, Prev: sys-sys-get-config, Up: sys-schema-functions
26.4.5.20 The version_major() Function ......................................
This function returns the major version of the MySQL server.
Parameters
None.
Return Value
A 'TINYINT UNSIGNED' value.
Example
mysql> SELECT VERSION(), sys.version_major();
+------------------+---------------------+
| VERSION() | sys.version_major() |
+------------------+---------------------+
| 5.7.24-debug-log | 5 |
+------------------+---------------------+File: manual.info.tmp, Node: sys-version-minor, Next: sys-version-patch, Prev: sys-version-major, Up: sys-schema-functions
26.4.5.21 The version_minor() Function ......................................
This function returns the minor version of the MySQL server.
Parameters
None.
Return Value
A 'TINYINT UNSIGNED' value.
Example
mysql> SELECT VERSION(), sys.version_minor();
+------------------+---------------------+
| VERSION() | sys.version_minor() |
+------------------+---------------------+
| 5.7.24-debug-log | 7 |
+------------------+---------------------+File: manual.info.tmp, Node: sys-version-patch, Prev: sys-version-minor, Up: sys-schema-functions
26.4.5.22 The version_patch() Function ......................................
This function returns the patch release version of the MySQL server.
Parameters
None.
Return Value
A 'TINYINT UNSIGNED' value.
Example
mysql> SELECT VERSION(), sys.version_patch();
+------------------+---------------------+
| VERSION() | sys.version_patch() |
+------------------+---------------------+
| 5.7.24-debug-log | 24 |
+------------------+---------------------+File: manual.info.tmp, Node: connectors-apis, Next: mysql-enterprise, Prev: sys-schema, Up: Top
27 Connectors and APIs **********************
Menu:
apis-eiffel:: MySQL Eiffel Wrapper
MySQL Connectors provide connectivity to the MySQL server for client programs. APIs provide low-level access to MySQL resources using either the classic MySQL protocol or X Protocol. Both Connectors and the APIs enable you to connect and execute MySQL statements from another language or environment, including ODBC, Java (JDBC), C++, Python, PHP, Perl, Ruby, and native C and embedded MySQL instances.
MySQL Connectors
Oracle develops a number of connectors:
Connector/C++ (https://dev.mysql.com/doc/connector-cpp/9.3/en/) enables C++ applications to connect to MySQL.
Connector/J (https://dev.mysql.com/doc/connector-j/en/) provides driver support for connecting to MySQL from Java applications using the standard Java Database Connectivity (JDBC) API.
Connector/NET (https://dev.mysql.com/doc/connector-net/en/) enables developers to create .NET applications that connect to MySQL. Connector/NET implements a fully functional ADO.NET interface and provides support for use with ADO.NET aware tools. Applications that use Connector/NET can be written in any supported .NET language.
Connector/ODBC (https://dev.mysql.com/doc/connector-odbc/en/) provides driver support for connecting to MySQL using the Open Database Connectivity (ODBC) API. Support is available for ODBC connectivity from Windows, Unix, and macOS platforms.
Connector/Python (https://dev.mysql.com/doc/connector-python/en/) provides driver support for connecting to MySQL from Python applications using an API that is compliant with the Python DB API version 2.0 (http://www.python.org/dev/peps/pep-0249/). No additional Python modules or MySQL client libraries are required.
The MySQL C API
For direct access to using MySQL natively within a C application, there are two methods:
The C API (https://dev.mysql.com/doc/c-api/5.7/en/) provides low-level access to the MySQL client/server protocol through the 'libmysqlclient' client library. This is the primary method used to connect to an instance of the MySQL server, and is used both by MySQL command-line clients and many of the MySQL Connectors and third-party APIs detailed here.
'libmysqlclient' is included in MySQL distributions.
'libmysqld' is an embedded MySQL server library that enables you to embed an instance of the MySQL server into your C applications.
'libmysqld' is included in MySQL distributions.
Note:
The 'libmysqld' embedded server library is deprecated as of MySQL 5.7.19 and is removed in MySQL 8.0.
See also MySQL C API Implementations (https://dev.mysql.com/doc/c-api/5.7/en/c-api-implementations.html).
To access MySQL from a C application, or to build an interface to MySQL for a language not supported by the Connectors or APIs in this chapter, the C API (https://dev.mysql.com/doc/c-api/5.7/en/) is where to start. A number of programmer's utilities are available to help with the process; see *note programs-development::.
Third-Party MySQL APIs
The remaining APIs described in this chapter provide an interface to MySQL from specific application languages. These third-party solutions are not developed or supported by Oracle. Basic information on their usage and abilities is provided here for reference purposes only.
All the third-party language APIs are developed using one of two methods, using 'libmysqlclient' or by implementing a native driver. The two solutions offer different benefits:
Using 'libmysqlclient' offers complete compatibility with MySQL because it uses the same libraries as the MySQL client applications. However, the feature set is limited to the implementation and interfaces exposed through 'libmysqlclient' and the performance may be lower as data is copied between the native language, and the MySQL API components.
Native drivers are an implementation of the MySQL network protocol entirely within the host language or environment. Native drivers are fast, as there is less copying of data between components, and they can offer advanced functionality not available through the standard MySQL API. Native drivers are also easier for end users to build and deploy because no copy of the MySQL client libraries is needed to build the native driver components.
*note connectors-apis-summary:: lists many of the libraries and interfaces available for MySQL.
MySQL APIs and Interfaces
EnvironmentAPI Type Notes
Ada
GNU 'libmysqlclient' See MySQL Ada Bindings MySQL for GNU Bindings Ada (http://gnade.sourceforge.net/)
C
C API 'libmysqlclient' See MySQL 5.7 C API Developer Guide (https://dev.mysql.com/doc/c-api/5.7/en/).
C++
Connector/C++'libmysqlclient' See MySQL Connector/C++ 9.3 Developer Guide (https://dev.mysql.com/doc/connector-cpp/9.3/en/).
MySQL++ 'libmysqlclient' See MySQL++ website (http://tangentsoft.net/mysql++/doc/).
MySQL 'libmysqlclient' See MySQL wrapped wrapped (http://www.alhem.net/project/mysql/).
Cocoa
MySQL-Cocoa'libmysqlclient' Compatible with the Objective-C Cocoa environment. See http://mysql-cocoa.sourceforge.net/
D
MySQL 'libmysqlclient' See MySQL for D for D (http://www.steinmole.de/d/).
Eiffel
Eiffel 'libmysqlclient' See MySQL *note apis-eiffel::.
Erlang
'erlang-mysql-driver''libmysqlclient'See 'erlang-mysql-driver'. (http://code.google.com/p/erlang-mysql-driver/)
Haskell
Haskell Native Driver See Brian MySQL O'Sullivan's Bindings pure Haskell MySQL bindings (http://www.serpentine.com/blog/software/mysql/).
'hsql-mysql''libmysqlclient' See MySQL driver for Haskell (http://hackage.haskell.org/cgi-bin/hackage-scripts/package/hsql-mysql-1.7).
Java/JDBC
Connector/JNative Driver See MySQL Connector/J Developer Guide (https://dev.mysql.com/doc/connector-j/en/).
Kaya
MyDB 'libmysqlclient' See MyDB (http://kayalang.org/library/latest/MyDB).
Lua
LuaSQL 'libmysqlclient' See LuaSQL (http://keplerproject.github.io/luasql/doc/us/).
.NET/Mono
Connector/NETNative Driver See MySQL Connector/NET Developer Guide (https://dev.mysql.com/doc/connector-net/en/).
Objective Caml
OBjective'libmysqlclient' See MySQL Caml Bindings MySQL for Bindings Objective Caml (http://raevnos.pennmush.org/code/ocaml-mysql/).
Octave
Database'libmysqlclient' See bindings Database for bindings GNU for GNU Octave Octave (http://octave.sourceforge.net/database/index.html).
ODBC
Connector/ODBC'libmysqlclient' See MySQL Connector/ODBC Developer Guide (https://dev.mysql.com/doc/connector-odbc/en/).
Perl
'DBI'/'DBD::mysql''libmysqlclient'See *note apis-perl::.
'Net::MySQL'Native Driver See 'Net::MySQL' (http://search.cpan.org/dist/Net-MySQL/MySQL.pm) at CPAN
PHP
'mysql','libmysqlclient' See MySQL 'ext/mysql' and PHP interface (https://dev.mysql.com/doc/apis-php/en/). (deprecated)
'mysqli','libmysqlclient' See MySQL 'ext/mysqli' and PHP interface (https://dev.mysql.com/doc/apis-php/en/).
'PDO_MYSQL''libmysqlclient' See MySQL and PHP (https://dev.mysql.com/doc/apis-php/en/).
PDO Native Driver mysqlnd
Python
Connector/PythonNative Driver See MySQL Connector/Python Developer Guide (https://dev.mysql.com/doc/connector-python/en/).
Python
Connector/Python'libmysqlclient' See MySQL C Connector/Python Extension Developer Guide (https://dev.mysql.com/doc/connector-python/en/).
MySQLdb 'libmysqlclient' See *note apis-python::.
Ruby
mysql2 'libmysqlclient' Uses 'libmysqlclient'. See *note apis-ruby::.
Scheme
'Myscsh''libmysqlclient' See 'Myscsh' (https://github.com/aehrisch/myscsh).
SPL
'sql_mysql''libmysqlclient' See 'sql_mysql' for SPL (http://www.clifford.at/spl/spldoc/sql_mysql.html).
Tcl
MySQLtcl'libmysqlclient' See *note apis-tcl::.
File: manual.info.tmp, Node: connector-cpp-info, Next: connector-j-info, Prev: connectors-apis, Up: connectors-apis