Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
This schema provides a simplified and user-friendly view of Performance Schema and Information Schema data, aiding in database diagnostics and performance tuning.
The sys_config table holds persistent configuration options for the Sys Schema, stored using the Aria storage engine to maintain settings across restarts.
The sys.sys_config table holds configuration options for the Sys Schema.
This is a persistent table (using the Aria storage engine), with the configuration persisting across upgrades (new options are added with INSERT IGNORE.
The table also has two related triggers, which maintain the user that INSERTs or UPDATEs the configuration - sys_config_insert_set_user and sys_config_update_set_user respectively.
Its structure is as follows:
Note, when functions check for configuration options, they first check whether a similar named user variable exists with a value, and if this is not set then pull the configuration option from this table in to that named user variable. This is done for performance reasons (to not continually SELECT from the table), however this comes with the side effect that once inited, the values last with the session, somewhat like how session variables are inited from global variables. If the values within this table are changed, they will not take effect until the user logs in again.
Some early versions of sys_config were stored in format.
This page is licensed: CC BY-SA / Gnu FDL
+----------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+-----------------------------+
| variable | varchar(128) | NO | PRI | NULL | |
| value | varchar(128) | YES | | NULL | |
| set_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| set_by | varchar(128) | YES | | NULL | |
+----------+--------------+------+-----+-------------------+-----------------------------+Explore sys_schema stored procedures in MariaDB Server. These procedures simplify complex administrative and diagnostic tasks, offering streamlined access to performance and configuration insights.
Explore sys_schema stored functions in MariaDB Server. These functions simplify querying performance and configuration data, offering a user-friendly interface for database diagnostics.
Explore sys_schema views in MariaDB Server. These views offer simplified, aggregated insights into server performance, I/O, and memory usage, making diagnostics and monitoring easier.
diagnostics.include_raw
OFF
Set to 'ON' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics") for the diagnostics procedure.
ps_thread_trx_info.max_length
65535
Sets the maximum output length for JSON object output by the ps_thread_trx_info() function.
statement_truncate_len
64
Sets the size to truncate statements to, for the format_statement function.
statement_performance_analyzer.limit
100
The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view). If not set the limit is 100.
statement_performance_analyzer.view
NULL
Used together with the 'custom' view. If the value contains a space, it is considered a query, otherwise it must be an existing view querying the performance_schema.events_statements_summary_by_digest table.
diagnostics.allow_i_s_tables
OFF
Specifies whether it is allowed to do table scan queries on information_schema.TABLES for the diagnostics procedure.
ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
s - seconds
m - minutes
h - hours
d - days
w - weeks
This function is very similar to the FORMAT_PICO_TIME function introduced in , but with the following differences:
Represents minutes as m rather than min.
Represent weeks.
This page is licensed: CC BY-SA / Gnu FDL
Given a file path, it returns the table name.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
sys.extract_table_from_file_name(path)It returns YES if the given Performance Schema instrument is timed by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_is_instrument_default_timed(instrument)The list_drop function removes a specified value from a comma-separated list, returning the modified list.
sys.list_drop(list,value)list_drop is a stored function available with the .
It takes a list to be modified and a value to be dropped from the list, returning the resulting value. This can be used, for example, to remove a value from a system variable taking a comma-delimited list of options, such as .
The related function can be used to add a value to a list.
This page is licensed: CC BY-SA / Gnu FDL
This function formats and truncates an SQL statement string to a specified length, making it suitable for display in summary views.
sys.format_statement(statement)Returns a reduced length string. The length is specified by the statement_truncate_len configuration option (default 64), and the removed part of the string (if any) is replaced with an ellipsis (three dots).
The function is intended for use in formatting lengthy SQL statements to a fixed length.
Default truncation length 64:
Reducing the truncation length to 48:
This page is licensed: CC BY-SA / Gnu FDL
This function returns a JSON-formatted stack trace of all statements, stages, and events for a given thread, useful for deep diagnostics.
sys.ps_thread_stack(thread_id, verbose)ps_thread_stack is a available with the that, for a given thread_id, returns all statements, stages, and events within the Performance Schema, as a JSON formatted stack.
The boolean verbose argument specifies whether or not to include file:lineno information in the events.
This page is licensed: CC BY-SA / Gnu FDL
The sys_get_config function retrieves the value of a specified configuration option from the sys_config table, or a user variable if set.
sys.sys_get_config(name,default)sys_get_config is a available with the .
The function returns a configuration option value from the . It takes two arguments; name, a configuration option name, and default, which is returned if the given option does not exist in the table.
Both arguments are VARCHAR(128) and can be NULL. Returns NULL if name is NULL, or if the given option is not found and default is NULL.
This page is licensed: CC BY-SA / Gnu FDL
This function checks whether a specific user account is enabled for instrumentation in the Performance Schema setup_actors table.
sys.ps_is_account_enabled(host,user)ps_is_account_enabled is a available with the .
It takes host and user arguments, and returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given account is enabled.
This page is licensed: CC BY-SA / Gnu FDL
These views summarize statement execution stages grouped by host, showing where threads spend the most time during query processing.
The host_summary_by_stages and x$host_summary_by_stages views summarize statement stages, grouped by host. Rows are sorted by host and descending total latency by default. The host_summary_by_stages view is intended to be easier for human reading, while the x$host_summary_by_stages view provides the data in raw form, intended for tools that process the data.
sys.format_time(picoseconds)SELECT
sys.format_time(43) AS ps,
sys.format_time(4321) AS ns,
sys.format_time(43211234) AS us,
sys.format_time(432112344321) AS ms,
sys.format_time(43211234432123) AS s,
sys.format_time(432112344321234) AS m,
sys.format_time(4321123443212345) AS h,
sys.format_time(432112344321234545) AS d,
sys.format_time(43211234432123444543) AS w;
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| ps | ns | us | ms | s | m | h | d | w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| 43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 m | 1.20 h | 5.00 d | 71.45 w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+SELECT sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+---------------------------------------------------------------------+
| sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+---------------------------------------------------------------------+
| t1 |
+---------------------------------------------------------------------+SELECT sys.ps_is_instrument_default_timed('statement/sql/select');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('statement/sql/select') |
+------------------------------------------------------------+
| YES |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/udf_mem');
+----------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_mem') |
+----------------------------------------------------------+
| NO |
+----------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/nonexistent');
+-------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_memsds') |
+-------------------------------------------------------------+
| NO |
+-------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed(NULL);
+------------------------------------------+
| sys.ps_is_instrument_default_timed(NULL) |
+------------------------------------------+
| NO |
+------------------------------------------+Returns NULL if the thread_id is not found.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_account(thread_id)It returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given consumer is enabled, and NULL if not given a valid consumer name.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_is_consumer_enabled(consumer)It takes a list to be modified and a value to be added to the list, returning the resulting value. This can be used, for example, to add a value to a system variable taking a comma-delimited list of options, such as sql_mode.
The related function list_drop can be used to drop a value from a list.
This page is licensed: CC BY-SA / Gnu FDL
sys.list_add(list,value)host
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
event_name
Stage event name.
total
Total number of occurrences of the file stage event for the host.
total_latency
Total wait time of timed occurrences of the stage event for the host.
avg_latency
Average wait time per timed occurrence of the stage event for the host.
This page is licensed: CC BY-SA / Gnu FDL
Takes a source database name db_name and synonym name and creates a synonym database with views that point to all of the tables within the source database. Useful for example for creating a synonym for the performance_schema or information_schema databases.
Returns an error if the source database doesn't exist, or the synonym already exists.
This page is licensed: CC BY-SA / Gnu FDL
create_synonym_db(db_name,synonym)
# db_name (VARCHAR(64))
# synonym (VARCHAR(64))Given a database in_db_name and table name in_table_name, returns the table type in the OUT parameter out_table_type. The return value is an ENUM field containing one of:
'' - the table does not exist
'BASE TABLE' - a regular table
'VIEW' - a view
'TEMPORARY' - a temporary table
This page is licensed: CC BY-SA / Gnu FDL
table_exists(in_db_name,in_table_name, out_table_type)
# in_db_name VARCHAR(64)
# in_table_name VARCHAR(64)
# out_table_type ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY')It returns the MariaDB Server minor release version.
This page is licensed: CC BY-SA / Gnu FDL
sys.version_minor()The procedure resets all aggregated instrumentation as a snapshot, producing a result set indicating the number of truncated tables. The boolean parameter display specifies whether to display each TRUNCATE TABLE statement before execution.
This page is licensed: CC BY-SA / Gnu FDL
ps_truncate_all_tables(bool display)CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+NULL, returns the thread_id for the current connection.This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_id(connection_id)SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+-------------------------------------------------------------------+
| formatted_statement |
+-------------------------------------------------------------------+
| SELECT field1, field2, field3, ... d4, field5, field6 FROM table1 |
+-------------------------------------------------------------------+SET @sys.statement_truncate_len = 48;
SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+---------------------------------------------------+
| formatted_statement |
+---------------------------------------------------+
| SELECT field1, field2, ... d5, field6 FROM table1 |
+---------------------------------------------------+SELECT sys.ps_thread_stack(13, FALSE) AS thread_stack\G
*************************** 1. row ***************************
thread_stack: {"rankdir": "LR","nodesep": "0.10",
"stack_created": "2022-03-28 16:01:06",
"mysql_version": "10.8.2-MariaDB",
"mysql_user": "msandbox@localhost",
"events": []}SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES |
+------------------------------------------------+The ps_trace_thread procedure captures a trace of Performance Schema instrumentation for a specific thread and dumps it to a .dot formatted graph file.
ps_trace_thread(thread_id, outfile, max_runtime, interval, start_fresh, auto_setup, debug)ps_trace_thread is a available with the .
Parameters:
thread_id INT: The thread to trace.
outfile VARCHAR(255): Name of the .dot file to be create.
max_runtime DECIMAL(20,2): Maximum time in seconds to collect data. Fractional seconds can be used, and NULL results in data being collected for the default sixty seconds.
Dumps all Performance Schema data for an instrumented thread to a .dot formatted graph file (for use with the ). All returned result sets should be used for a complete graph.
Session is disabled during execution, by adjusting the session value (note the permissions required).
This page is licensed: CC BY-SA / Gnu FDL
Convert bytes to a human-readable string. This function formats a numeric byte count into units like KiB, MiB, GiB, up to EiB.
sys.format_bytes(double)format_bytes is a available with the .
Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), or PiB (pebibytes).
The binary prefixes (kibi, mebi, gibi, tebi and pebi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera and peta can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
From , the built-in function can instead be used. The functions are similar, except that FORMAT_BYTES also displays exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
The format_path function replaces specific system paths in a given string with their corresponding system variable names for better readability.
sys.format_path(path)format_path is a available with the that, given a path, returns a modified path after replacing subpaths matching the values of various system variables with the variable name.
The system variables that are matched are, in order:
This page is licensed: CC BY-SA / Gnu FDL
These helper functions allow you to easily enable or disable specific optimizer_switch flags for the current session.
The above procedures can be used to check which optimizer_switch options are on or off. The result set is sorted according to option name.
sys.optimizer_switch_on() is a shortcut for sys.optimizer_switch_choice("on") .
sys.optimizer_switch_off() is a shortcut for sys.optimizer_switch_choice("off") .
Documentation for optimizer_switch
This page is licensed: CC BY-SA / Gnu FDL
The ps_thread_trx_info function returns a JSON object detailing the current transaction and executed statements for a specific thread.
sys.ps_thread_trx_info(thread_id)ps_thread_trx_info is a available with the .
It returns a JSON object with information about the thread specified by the given thread_id. This information includes:
the current transaction;
executed statements (derived from the and the (full data will only returned if the consumers for those tables are enabled).
The maximum length of the returned JSON object is determined by the value of the (by default 65535). If the returned value exceeds this length, a JSON object error is returned.
This page is licensed: CC BY-SA / Gnu FDL
This function checks if a given Performance Schema instrument is enabled by default, returning YES or NO.
sys.ps_is_instrument_default_enabled(instrument)ps_is_instrument_default_enabled is a available with the .
It returns YES if the given Performance Schema instrument is enabled by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
The ps_is_thread_instrumented function checks whether a specific thread ID is currently instrumented by the Performance Schema.
sys.ps_is_thread_instrumented(connection_id)ps_is_thread_instrumented is a available with the that returns whether or not Performance Schema instrumentation for the given connection_id is enabled.
YES - instrumentation is enabled
NO - instrumentation is not enabled
UNKNOWN - the connection ID is unknown
This page is licensed: CC BY-SA / Gnu FDL
These views summarize file I/O events grouped by client host, detailing total I/O counts and latency to identify hosts generating heavy disk traffic.
The host_summary_by_file_io and x$host_summary_by_file_io views summarize file I/O, grouped by host. Rows are sorted by descending total file I/O latency by default.
The host_summary_by_file_io view is intended to be easier for human reading, while the andx$host_summary_by_file_ioview provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
This diagnostic procedure creates a report of the statements currently running or recently run on the server, aiding in performance troubleshooting.
statement_performance_analyzer(in_action,in_table, in_views)
# in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp',
'create_table', 'save', 'cleanup')
# in_table VARCHAR(129)
# in_views SET ('with_runtimes_in_95th_percentile', 'analysis',
'with_errors_or_warnings', 'with_full_table_scans',
'with_sorting', 'with_temp_tables', 'custom')statement_performance_analyzer is a available with the which returns a report on running statements.
The following options from the table impact the output:
statement_performance_analyzer.limit - maximum number of rows (default 100) returned for views that have no built-in limit.
statement_performance_analyzer.view - custom query/view to be used (default NULL). If the statement_performance_analyzer.limit configuration option is greater than 0, there can't be a LIMIT clause in the query/view definition.
If the debug option is set (default OFF), the procedure will also produce debugging output.
This page is licensed: CC BY-SA / Gnu FDL
This function takes a file path and returns the schema name it belongs to, assuming the file is located within the schema's directory.
sys.extract_schema_from_file_name(path)extract_schema_from_file_name is a available with the .
Given a file path, it returns the schema (database) name. The file name is assumed to be within the schema directory, and therefore the function will not return the expected result with partitions, or when tables are defined using the DATA_DIRECTORY table option.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
These views display the most recent file I/O events, providing a real-time look at which files are being accessed by the server.
The latest_file_io and x$latest_file_io views summarize file I/O activity, grouped by file and thread. Rows are sorted by most recent I/O by default.
The latest_file_io view is intended to be easier for human reading, while the x$latest_file_io view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
The schema_object_overview view provides a count of different database objects (such as tables, views, and routines) within each schema on the server.
This function returns the major version number of the MariaDB Server instance (e.g., 10 from 10.6.4).
sys.version_major()version_major is a stored function available with the .
It returns the MariaDB Server major release version.
This page is licensed: CC BY-SA / Gnu FDL
This procedure traces a specific statement digest in the Performance Schema, capturing details about its execution for performance analysis.
ps_trace_statement_digest(in_digest, in_runtime, in_interval, in_start_fresh, in_auto_enable)ps_trace_statement_digest is a available with the .
Parameters:
in_digest VARCHAR(32): The statement digest identifier to analyze.
in_runtime INT: Specifies the duration to run the analysis in seconds.
in_interval DECIMAL(2,2): The analysis interval measured in seconds (including fraction values), at which snapshots are taken.
This page is licensed: CC BY-SA / Gnu FDL
The schema_auto_increment_columns view lists tables with AUTO_INCREMENT columns, showing the current value and the maximum allowed value for the column type.
These views summarize current memory usage grouped by client host, enabling administrators to track memory consumption per connected host.
The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes summarize memory use grouped by host. Rows by default are sorted by descending amount of memory used.
The memory_by_host_by_current_bytes view is intended to be easier for human reading, while the x$memory_by_host_by_current_bytes
SELECT sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID()));
+----------------------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID())) |
+----------------------------------------------------------+
| msandbox@localhost |
+----------------------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(2042));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(2042)) |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(NULL));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(NULL)) |
+-----------------------------------------------+
| msandbox@localhost |
+-----------------------------------------------+SELECT sys.ps_is_consumer_enabled('global_instrumentation');
+------------------------------------------------------+
| sys.ps_is_consumer_enabled('global_instrumentation') |
+------------------------------------------------------+
| YES |
+------------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('events_stages_current');
+-----------------------------------------------------+
| sys.ps_is_consumer_enabled('events_stages_current') |
+-----------------------------------------------------+
| NO |
+-----------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('nonexistent_consumer');
+----------------------------------------------------+
| sys.ps_is_consumer_enabled('nonexistent_consumer') |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_add(@@sql_mode, 'NO_ZERO_DATE');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+SELECT * FROM sys.host_summary_by_stages\G
*************************** 1. row ***************************
host: background
event_name: stage/innodb/buffer pool load
total: 1
total_latency: 3.75 ms
avg_latency: 3.75 ms
SELECT * FROM sys.x$host_summary_by_stages\G
*************************** 1. row ***************************
host: background
event_name: stage/innodb/buffer pool load
total: 1
total_latency: 3747098000
avg_latency: 3747098000SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
CALL sys.create_synonym_db('performance_schema', 'perf');
+-----------------------------------------+
| summary |
+-----------------------------------------+
| Created 81 views in the `perf` database |
+-----------------------------------------+
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| perf |
| performance_schema |
| sys |
| test |
+--------------------+
SHOW FULL TABLES FROM perf;
+------------------------------------------------------+------------+
| Tables_in_perf | Table_type |
+------------------------------------------------------+------------+
| accounts | VIEW |
| cond_instances | VIEW |
| events_stages_current | VIEW |
| events_stages_history | VIEW |
| events_stages_history_long | VIEW |
...CALL sys.table_exists('mysql', 'time_zone', @table_type); SELECT @table_type;
+-------------+
| @table_type |
+-------------+
| BASE TABLE |
+-------------+
CALL sys.table_exists('mysql', 'user', @table_type); SELECT @table_type;
+-------------+
| @table_type |
+-------------+
| VIEW |
+-------------+SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+CALL sys.ps_truncate_all_tables(true);
+------------------------------------------------------------------+
| status |
+------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_history |
+------------------------------------------------------------------+
...
+------------------------------------------------------------------------------+
| status |
+------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.table_lock_waits_summary_by_table |
+------------------------------------------------------------------------------+
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+SELECT * FROM performance_schema.threads\G
*************************** 13. row ***************************
THREAD_ID: 13
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: msandbox
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: SELECT * FROM performance_schema.threads
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 24379
SELECT sys.ps_thread_id(3);
+---------------------+
| sys.ps_thread_id(3) |
+---------------------+
| 13 |
+---------------------+
SELECT sys.ps_thread_id(NULL);
+------------------------+
| sys.ps_thread_id(NULL) |
+------------------------+
| 13 |
+------------------------+SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_drop(@@sql_mode, 'NO_ENGINE_SUBSTITUTION');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER |
+-----------------------------------------------------------------------+SELECT sys.sys_get_config('ps_thread_trx_info.max_length',NULL);
+----------------------------------------------------------+
| sys.sys_get_config('ps_thread_trx_info.max_length',NULL) |
+----------------------------------------------------------+
| 65535 |
+----------------------------------------------------------+optimizer_switch_on()
optimizer_switch_off()
optimizer_switch_choice("on" | "off")thread
Account associated with the thread for foreground threads (port number for TCP/IP connections), or thread name and thread ID for background threads.
total
Total number of occurrences of the I/O event.
file
File path name.
latency
Wait time of the file I/O event.
operation
Type of operation
requested
Number of bytes requested for the file I/O event.
They contain the following columns:
host
Host from which the client connected. If the HOST column in the underlying Performance Schema table is NULL, rows are assumed to be for background threads, and the background host name is used.
current_count_used
Current number of allocated memory blocks that have not yet been freed for the host.
current_allocated
Current number of allocated bytes that have not yet been freed for the host.
current_avg_alloc
Current number of allocated bytes per memory block for the host.
current_max_alloc
Largest single current memory allocation in bytes for the host.
total_allocated
Total memory allocation in bytes for the host.
This page is licensed: CC BY-SA / Gnu FDL
interval DECIMAL(20,2): Time in seconds to sleep between data collection. Fractional seconds can be used, and NULL results in the sleep being the default one second.
start_fresh BOOLEAN: Whether to reset all Performance Schema data before tracing.
auto_setup BOOLEAN: Whether to disable all other threads, enable all instruments and consumers, and reset the settings at the end of the run.
debug BOOLEAN: Whether to include file:lineno information in the graph.
NULL - NULL valuehost
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
ios
Total file I/O events for the host.
ios_latency
Total wait time of timed file I/O events for the host.
db
Schema name
object_type
Object name
count
Count of the number of objects
in_start_fresh BOOLEAN: Determines whether to truncate the Performance Schema events_statements_history_long and events_stages_history_long tables before starting.
in_auto_enable BOOLEAN: Determines whether to automatically enable required consumers.
It returns the MariaDB Server patch release version.
This page is licensed: CC BY-SA / Gnu FDL
CALL sys.ps_trace_thread(25, CONCAT('/tmp/stack-', REPLACE(NOW(), ' ', '-'), '.dot'),
NULL, NULL, TRUE, TRUE, TRUE);
--------------------+
| summary |
+--------------------+
| Disabled 0 threads |
+--------------------+
+---------------------------------------------+
| Info |
+---------------------------------------------+
| Data collection starting for THREAD_ID = 25 |
+---------------------------------------------+
+-----------------------------------------------------------+
| Info |
+-----------------------------------------------------------+
| Stack trace written to /tmp/stack-2023-04-05-19:06:29.dot |
+-----------------------------------------------------------+
+-------------------------------------------------------------------+
| Convert to PDF |
+-------------------------------------------------------------------+
| dot -Tpdf -o /tmp/stack_25.pdf /tmp/stack-2023-04-05-19:06:29.dot |
+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
| Convert to PNG |
+-------------------------------------------------------------------+
| dot -Tpng -o /tmp/stack_25.png /tmp/stack-2023-04-05-19:06:29.dot |
+-------------------------------------------------------------------+SELECT sys.format_bytes(1000),sys.format_bytes(1024);
+------------------------+------------------------+
| sys.format_bytes(1000) | sys.format_bytes(1024) |
+------------------------+------------------------+
| 1000 bytes | 1.00 KiB |
+------------------------+------------------------+
SELECT sys.format_bytes(1000000),sys.format_bytes(1048576);
+---------------------------+---------------------------+
| sys.format_bytes(1000000) | sys.format_bytes(1048576) |
+---------------------------+---------------------------+
| 976.56 KiB | 1.00 MiB |
+---------------------------+---------------------------+
SELECT sys.format_bytes(1000000000),sys.format_bytes(1073741874);
+------------------------------+------------------------------+
| sys.format_bytes(1000000000) | sys.format_bytes(1073741874) |
+------------------------------+------------------------------+
| 953.67 MiB | 1.00 GiB |
+------------------------------+------------------------------+
SELECT sys.format_bytes(1000000000000),sys.format_bytes(1099511627776);
+---------------------------------+---------------------------------+
| sys.format_bytes(1000000000000) | sys.format_bytes(1099511627776) |
+---------------------------------+---------------------------------+
| 931.32 GiB | 1.00 TiB |
+---------------------------------+---------------------------------+
SELECT sys.format_bytes(1000000000000000),sys.format_bytes(1125899906842624);
+------------------------------------+------------------------------------+
| sys.format_bytes(1000000000000000) | sys.format_bytes(1125899906842624) |
+------------------------------------+------------------------------------+
| 909.49 TiB | 1.00 PiB |
+------------------------------------+------------------------------------+SELECT @@tmpdir;
+------------------------------------+
| @@tmpdir |
+------------------------------------+
| /home/ian/sandboxes/msb_10_8_2/tmp |
+------------------------------------+
SELECT sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd');
+------------------------------------------------------------------+
| sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd') |
+------------------------------------------------------------------+
| @@tmpdir/testdb.ibd |
+------------------------------------------------------------------+SELECT @@optimizer_switch\G
*************************** 1. row ***************************
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,
in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,
semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,
orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,
condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,
not_null_range_scan=off
call sys.optimizer_switch_on();
+---------------------------------+------+
| option | opt |
+---------------------------------+------+
| condition_pushdown_for_derived | on |
| condition_pushdown_for_subquery | on |
| condition_pushdown_from_having | on |
| derived_merge | on |
| derived_with_keys | on |
| exists_to_in | on |
| extended_keys | on |
| firstmatch | on |
| index_condition_pushdown | on |
| index_merge | on |
| index_merge_intersection | on |
| index_merge_sort_union | on |
| index_merge_union | on |
| in_to_exists | on |
| join_cache_bka | on |
| join_cache_hashed | on |
| join_cache_incremental | on |
| loosescan | on |
| materialization | on |
| optimize_join_buffer_size | on |
| orderby_uses_equalities | on |
| outer_join_with_cache | on |
| partial_match_rowid_merge | on |
| partial_match_table_scan | on |
| rowid_filter | on |
| semijoin | on |
| semijoin_with_cache | on |
| split_materialized | on |
| subquery_cache | on |
| table_elimination | on |
+---------------------------------+------+
call sys.optimizer_switch_off();
+-------------------------------+------+
| option | opt |
+-------------------------------+------+
| engine_condition_pushdown | off |
| index_merge_sort_intersection | off |
| mrr | off |
| mrr_cost_based | off |
| mrr_sort_keys | off |
| not_null_range_scan | off |
+-------------------------------+------+SELECT sys.ps_is_instrument_default_enabled('statement/sql/select');
+--------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('statement/sql/select') |
+--------------------------------------------------------------+
| YES |
+--------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/udf_mem');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/udf_mem') |
+------------------------------------------------------------+
| NO |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/nonexistent');
+----------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/nonexistent') |
+----------------------------------------------------------------+
| NO |
+----------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled(NULL);
+--------------------------------------------+
| sys.ps_is_instrument_default_enabled(NULL) |
+--------------------------------------------+
| NO |
+--------------------------------------------+SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+------------------------------------------------+
| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+------------------------------------------------+
| YES |
+------------------------------------------------+
SELECT sys.ps_is_thread_instrumented(2042);
+-------------------------------------+
| sys.ps_is_thread_instrumented(2042) |
+-------------------------------------+
| UNKNOWN |
+-------------------------------------+
SELECT sys.ps_is_thread_instrumented(NULL);
+-------------------------------------+
| sys.ps_is_thread_instrumented(NULL) |
+-------------------------------------+
| NULL |
+-------------------------------------+SELECT * FROM sys.host_summary_by_file_io\G
*************************** 1. row ***************************
host: localhost
ios: 6526
io_latency: 490.28 ms
*************************** 2. row ***************************
host: background
ios: 457
io_latency: 151.39 ms
SELECT * FROM sys.x$host_summary_by_file_io\G
*************************** 1. row ***************************
host: localhost
ios: 6532
io_latency: 490447878974
*************************** 2. row ***************************
host: background
ios: 457
io_latency: 151388125856SELECT sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+----------------------------------------------------------------------+
| sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+----------------------------------------------------------------------+
| db |
+----------------------------------------------------------------------+SELECT * FROM sys.schema_object_overview;
+--------------------+---------------+-------+
| db | object_type | count |
+--------------------+---------------+-------+
| information_schema | SYSTEM VIEW | 79 |
| mysql | BASE TABLE | 30 |
| mysql | INDEX (BTREE) | 76 |
| mysql | PROCEDURE | 2 |
| mysql | VIEW | 1 |
| performance_schema | BASE TABLE | 81 |
| sys | BASE TABLE | 1 |
| sys | FUNCTION | 22 |
| sys | INDEX (BTREE) | 1 |
| sys | PROCEDURE | 26 |
| sys | VIEW | 100 |
+--------------------+---------------+-------+SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 5, 0.5, TRUE, TRUE);sys.version_patch()SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+table_schema
Schema name containing the table with the auto_increment attribute.
table_name
Table containing the auto_increment attribute.
column_name
Name of the column containing the auto_increment attribute.
data_type
of the auto_increment column, for example .
column_type
of the auto_increment column, plus additional information for example tinyint(3) unsigned.
is_signed
1 if the column is ,0 if not.
is_unsigned
This page is licensed: CC BY-SA / Gnu FDL
These views provide a granular breakdown of InnoDB buffer pool usage by individual tables, sorted by buffer allocation size.
Querying these views can have a noticeable performance impact on a production server.
The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table views summarize information from the Information Schema INNODB_BUFFER_PAGE table, grouped by schema and table. Rows are sorted by descending buffer size by default.
The innodb_buffer_stats_by_table view is intended to be easier for human reading, while the x$innodb_buffer_stats_by_table view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
These views aggregate executed statements by type (e.g., SELECT, INSERT) and host, providing statistics on frequency and latency.
The host_summary_by_statement_type and x$host_summary_by_statement_type views summarize information about executed statements, grouped by host and statement type. Rows are sorted by host and descending total latency by default.
The host_summary_by_statement_type view is intended to be easier for human reading, while the x$host_summary_by_statement_type view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
These views summarize InnoDB buffer pool usage, showing the amount of memory and number of pages allocated to each database schema.
Querying these views can have a noticeable performance impact on a production server.
The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema views summarize information from the Information Schema INNODB_BUFFER_PAGE table, grouped by schema. Rows are sorted by descending buffer size by default.
The innodb_buffer_stats_by_schema view is intended to be easier for human reading, while the x$innodb_buffer_stats_by_schema view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
These views summarize global I/O consumers by event name, displaying total bytes read and written to identify high-throughput events.
The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes views summarize global I/O consumers, displaying amount of I/O and time waiting for I/O, grouped by event. Rows are sorted by descending total I/O (bytes read and written) by default.
The io_global_by_wait_by_bytes view is intended to be easier for human reading, while the x$io_global_by_wait_by_bytes
These views summarize statement statistics grouped by host, sorted by total latency, helping to identify hosts running the most time-consuming queries.
The host_summary_by_statement_latency and x$host_summary_by_statement_latency views summarize statement statistics, grouped by host. Rows are sorted by descending total latency by default. The host_summary_by_statement_latency view is intended to be easier for human reading, while the x$host_summary_by_statement_latency view provides the data in raw form, intended for tools that process the data.
These views summarize lock wait information, linking waiting transactions to blocking transactions to help diagnose deadlocks and contention.
The innodb_lock_waits and x$innodb_lock_waits views summarize InnoDB locks that transactions are waiting upon, by default sorted in descending buffer size.
The innodb_lock_waits view is intended to be easier for human reading, while the x$innodb_lock_waits view provides the data in raw form, intended for tools that process the data.
These views summarize global I/O consumers by event name, sorted by total latency, helping to pinpoint slow I/O event types.
The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency views summarize global I/O consumers, displaying I/O and time waiting for I/O, grouped by event. Rows are sorted by descending total latency by default.
The io_global_by_wait_by_latency view is intended to be easier for human reading, while the x$io_global_by_wait_by_latency
These views summarize global file I/O statistics grouped by file path, sorted by total latency to highlight slow I/O targets.
The io_global_by_file_by_latency and x$io_global_by_file_by_latency views summarize global I/O consumers to display time waiting for I/O, grouped by file. Rows are sorted by descending total latency by default.
The io_global_by_file_by_latency view is intended to be easier for human reading, while the x$io_global_by_file_by_latency
The metrics view consolidates key performance metrics from Global Status, InnoDB Metrics, and memory summaries into a single unified table.
The metrics view contains global status variables (as found in the ), metrics (as found in the , current and total memory allocation, based on the Performance Schema memory instrumentation, as well the current time in human readable and Unix timestamp formats.
It contains the following columns:
CREATE OR REPLACE TABLE animals (
id TINYINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('fox'),('whale'),('ostrich');
SELECT * FROM sys.schema_auto_increment_columns\G
*************************** 1. row ***************************
table_schema: test
table_name: animals
column_name: id
data_type: tinyint
column_type: tinyint(4)
is_signed: 1
is_unsigned: 0
max_value: 127
auto_increment: 7
auto_increment_ratio: 0.05511 if the column is unsigned,0 if it is.
max_value
Maximum possible value for the column, for example 255 for an unsigned tinyint.
auto_increment
Current auto_increment value for the column.
auto_increment_ratio
Ratio of used to maximum value for the auto_increment column.
pages_old
Total number of old pages allocated for the table.
rows_cached
Total number of cached rows for the table.
object_schema
Object schema name, or InnoDB System if the table belongs to the InnoDB storage engine.
object_name
Table name.
allocated
Total number of bytes allocated for the table.
data
Number of data bytes allocated for the table.
pages
Total number of pages allocated for the table.
pages_hashed
Total number of hashed pages allocated for the table.
rows_sent
Total number of rows returned by occurrences of the statement event for the host.
rows_examined
Total number of rows read from storage engines by occurrences of the statement event for the host.
rows_affected
Total number of rows affected by occurrences of the statement event for the host.
full_scans
Total number of full table scans by occurrences of the statement event for the host.
host
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
statement
Final component of the statement event name, for example create_table or select.
total
Total number of statement occurrences for the host.
total_latency
Total wait time of timed statements of the statement event for the host.
max_latency
Maximum single wait time of timed occurrences of the statement event for the host.
lock_latency
Total time spent by timed occurrences of the statement event for the host waiting for locks.
rows_cached
Total number of cached rows for the schema.
object_schema
Object schema name, or InnoDB System if the table belongs to the InnoDB storage engine.
allocated
Total number of bytes allocated for the schema.
data
Total number of data bytes allocated for the schema.
pages
Total number of pages allocated for the schema.
pages_hashed
Total number of hashed pages allocated for the schema.
pages_old
Total number of old pages allocated for the schema.
SELECT * FROM sys.innodb_buffer_stats_by_table\G
*************************** 1. row ***************************
object_schema: mysql
object_name: transaction_registry
allocated: 64.00 KiB
data: 0 bytes
pages: 4
pages_hashed: 4
pages_old: 4
rows_cached: 0
*************************** 2. row ***************************
object_schema: InnoDB System
object_name: SYS_FOREIGN
allocated: 48.00 KiB
data: 0 bytes
pages: 3
pages_hashed: 3
pages_old: 3
rows_cached: 0
*************************** 3. row ***************************
object_schema: InnoDB System
object_name: SYS_TABLES
allocated: 32.00 KiB
data: 1.07 KiB
pages: 2
pages_hashed: 2
pages_old: 2
rows_cached: 10
...
SELECT * FROM sys.x$innodb_buffer_stats_by_table\G
*************************** 1. row ***************************
object_schema: mysql
object_name: transaction_registry
allocated: 65536
data: 0
pages: 4
pages_hashed: 0
pages_old: 0
rows_cached: 0
*************************** 2. row ***************************
object_schema: InnoDB System
object_name: SYS_FOREIGN
allocated: 49152
data: 0
pages: 3
pages_hashed: 0
pages_old: 0
rows_cached: 0
*************************** 3. row ***************************
object_schema: InnoDB System
object_name: SYS_TABLES
allocated: 32768
data: 1100
pages: 2
pages_hashed: 0
pages_old: 0
rows_cached: 10
....SELECT * FROM sys.host_summary_by_statement_type\G
*************************** 1. row ***************************
host: localhost
statement: create_table
total: 18
total_latency: 366.93 ms
max_latency: 48.02 ms
lock_latency: 3.16 ms
rows_sent: 0
rows_examined: 0
rows_affected: 0
full_scans: 0
*************************** 2. row ***************************
host: localhost
statement: select
total: 27
total_latency: 339.16 ms
max_latency: 64.51 ms
lock_latency: 205.61 ms
rows_sent: 750599937895926
rows_examined: 13925
rows_affected: 0
full_scans: 21
...
SELECT * FROM sys.x$host_summary_by_statement_type\G
*************************** 1. row ***************************
host: localhost
statement: create_table
total: 18
total_latency: 366927804000
max_latency: 48023563000
lock_latency: 3156000000
rows_sent: 0
rows_examined: 0
rows_affected: 0
full_scans: 0
*************************** 2. row ***************************
host: localhost
statement: select
total: 28
total_latency: 343873182000
max_latency: 64507216000
lock_latency: 205984000000
rows_sent: 750678474440767
rows_examined: 14370
rows_affected: 0
full_scans: 22SELECT * FROM sys.innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
object_schema: InnoDB System
allocated: 160.00 KiB
data: 6.21 KiB
pages: 10
pages_hashed: 10
pages_old: 10
rows_cached: 21
*************************** 2. row ***************************
object_schema: mysql
allocated: 112.00 KiB
data: 1.73 KiB
pages: 7
pages_hashed: 7
pages_old: 7
rows_cached: 5
*************************** 3. row ***************************
object_schema: test
allocated: 64.00 KiB
data: 0 bytes
pages: 4
pages_hashed: 4
pages_old: 4
rows_cached: 0
SELECT * FROM sys.x$innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
object_schema: InnoDB System
allocated: 163840
data: 6362
pages: 10
pages_hashed: 0
pages_old: 0
rows_cached: 21
*************************** 2. row ***************************
object_schema: mysql
allocated: 114688
data: 1775
pages: 7
pages_hashed: 0
pages_old: 0
rows_cached: 5
*************************** 3. row ***************************
object_schema: test
allocated: 65536
data: 0
pages: 4
pages_hashed: 0
pages_old: 0
rows_cached: 0They contain the following columns:
event_name
I/O event name. The wait/io/file prefix is stripped.
total
Total number of occurrences of the I/O event.
total_latency
Total wait time of timed occurrences of the I/O event
min_latency
Minimum single wait time of timed occurrences of the I/O event.
avg_latency
Average wait time per timed occurrence of the I/O event.
max_latency
Maximum single wait time of timed occurrences of the I/O event.
This page is licensed: CC BY-SA / Gnu FDL
host
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
total
Total number of statements for the host.
max_latency
Maximum single wait time of timed statements for the host.
lock_latency
Total time spent by timed statements for the host waiting for locks.
total_latency
Total wait time of timed statements for the host.
rows_sent
Total number of rows returned by statements for the host.
This page is licensed: CC BY-SA / Gnu FDL
They contain the following columns:
wait_started
Time that lock wait began.
wait_age
TIME value for the length of the lock wait.
wait_age_secs
Seconds value for the length of the lock wait.
locked_table_schema
Schema containing the locked table.
locked_table_name
Name of the locked table.
locked_table_partition
Name of the locked partition, or NULL if none.
This page is licensed: CC BY-SA / Gnu FDL
They contain the following columns:
event_name
I/O event name. The wait/io/file prefix is stripped.
total
Total number of occurrences of the I/O event.
total_latency
Total wait time of timed occurrences of the I/O event.
min_latency
Minimum single wait time of timed occurrences of the I/O event.
avg_latency
Average wait time per timed occurrence of the I/O event.
max_latency
Maximum single wait time of timed occurrences of the I/O event.
This page is licensed: CC BY-SA / Gnu FDL
They contain the following columns:
file
File path name.
total
Total number of I/O events for the file.
total_latency
Total wait time of timed I/O events for the file.
count_read
Total number of read I/O events for the file.
read_latency
Total wait time of timed read I/O events for the file.
count_write
Total number of write I/O events for the file.
This page is licensed: CC BY-SA / Gnu FDL
Variable_name
The name of the metric. One of the if a global_status table, the if an InnoDB metric, or a description for other metrics.
Variable_value
The metric value. One of the if a global status variable, the for InnoDB metrics, the related column from the , the value of or .
Type
Metric type. One of Global Status, InnoDB Metrics - % (with % being the value of the in the INNODB_METRICS table, Performance Schema or System Time
Enabled
Whether the metric is enabled. Always YES for global status variables and the current time. For InnoDB metrics, YES only if the , otherwise NO. For memory metrics: YES, NO or PARTIAL (for metrics where not all memory/% instruments are enabled). Performance Schema memory instruments are always enabled)
This page is licensed: CC BY-SA / Gnu FDL
The privileges_by_table_by_level view lists granted privileges broken down by table and the level (Global, Schema, or Table) at which they were granted.
Shows granted privileges broken down by table on which they allow access and level on which they were granted.
For example, if a user x has SELECT privilege granted ON db.*, this view will list all tables in the db schema with the user x having SELECT privilege on them. This is different from , which only lists privileges granted on the table level.
(description of the privileges and how to grant them)
This page is licensed: CC BY-SA / Gnu FDL
These views summarize global file I/O statistics grouped by file path, sorted by total bytes read and written.
The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes views summarize global I/O consumers showing I/O in bytes, grouped by file. Rows are sorted by descending total I/O (bytes read and written) by default.
The io_global_by_file_by_bytes view is intended to be easier for human reading, while the x$io_global_by_file_by_bytes view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM sys.io_global_by_wait_by_bytes\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 220
total_latency: 38.96 ms
min_latency: 0 ps
avg_latency: 177.09 us
max_latency: 4.07 ms
count_read: 174
total_read: 4.73 MiB
avg_read: 27.86 KiB
count_write: 0
total_written: 0 bytes
avg_written: 0 bytes
total_requested: 4.73 MiB
*************************** 2. row ***************************
event_name: aria/MAD
total: 1107
total_latency: 18.27 ms
min_latency: 0 ps
avg_latency: 16.50 us
max_latency: 204.97 us
count_read: 105
total_read: 840.00 KiB
avg_read: 8.00 KiB
count_write: 0
total_written: 0 bytes
avg_written: 0 bytes
total_requested: 840.00 KiB
...
SELECT * FROM sys.x$io_global_by_wait_by_bytes\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 220
total_latency: 38959722138
min_latency: 0
avg_latency: 177089374
max_latency: 4065566778
count_read: 174
total_read: 4964352
avg_read: 28530.7586
count_write: 0
total_written: 0
avg_written: 0.0000
total_requested: 4964352
*************************** 2. row ***************************
event_name: aria/MAD
total: 1107
total_latency: 18270683624
min_latency: 0
avg_latency: 16504546
max_latency: 204973168
count_read: 105
total_read: 860160
avg_read: 8192.0000
count_write: 0
total_written: 0
avg_written: 0.0000
total_requested: 860160
...SELECT * FROM sys.host_summary_by_statement_latency\G
*************************** 1. row ***************************
host: localhost
total: 1042
total_latency: 816.89 ms
max_latency: 64.51 ms
lock_latency: 215.64 ms
rows_sent: 750599937895985
rows_examined: 13548
rows_affected: 6
full_scans: 33
*************************** 2. row ***************************
host: background
total: 0
total_latency: 0 ps
max_latency: 0 ps
lock_latency: 0 ps
rows_sent: 0
rows_examined: 0
rows_affected: 0
full_scans: 0
SELECT * FROM sys.x$host_summary_by_statement_latency\G
*************************** 1. row ***************************
host: localhost
total: 1041
total_latency: 812132706000
max_latency: 64507216000
lock_latency: 215301000000
rows_sent: 750599937895983
rows_examined: 13110
rows_affected: 6
full_scans: 32
*************************** 2. row ***************************
host: background
total: 0
total_latency: 0
max_latency: 0
lock_latency: 0
rows_sent: 0
rows_examined: 0
rows_affected: 0
full_scans: 0SELECT * FROM sys.io_global_by_wait_by_latency\G
*************************** 1. row ***************************
event_name: sql/global_ddl_log
total: 223
total_latency: 288.66 ms
avg_latency: 1.29 ms
max_latency: 26.07 ms
read_latency: 0 ps
write_latency: 2.59 ms
misc_latency: 286.07 ms
count_read: 0
total_read: 0 bytes
avg_read: 0 bytes
count_write: 114
total_written: 220.17 KiB
avg_written: 1.93 KiB
*************************** 2. row ***************************
event_name: innodb/innodb_log_file
total: 95
total_latency: 165.29 ms
avg_latency: 1.74 ms
max_latency: 26.48 ms
read_latency: 61.04 us
write_latency: 1.31 ms
misc_latency: 163.92 ms
count_read: 6
total_read: 66.50 KiB
avg_read: 11.08 KiB
count_write: 43
total_written: 81.00 KiB
avg_written: 1.88 KiB
...
SELECT * FROM sys.x$io_global_by_wait_by_latency\G
*************************** 1. row ***************************
event_name: sql/global_ddl_log
total: 223
total_latency: 288663966666
avg_latency: 1294456930
max_latency: 26072142152
read_latency: 0
write_latency: 2594925264
misc_latency: 286069041402
count_read: 0
total_read: 0
avg_read: 0.0000
count_write: 114
total_written: 225459
avg_written: 1977.7105
*************************** 2. row ***************************
event_name: innodb/innodb_log_file
total: 95
total_latency: 165291020006
avg_latency: 1739905288
max_latency: 26478157582
read_latency: 61040974
write_latency: 1310187820
misc_latency: 163919791212
count_read: 6
total_read: 68096
avg_read: 11349.3333
count_write: 43
total_written: 82944
avg_written: 1928.9302
...SELECT * FROM sys.io_global_by_file_by_latency\G
*************************** 1. row ***************************
file: @@datadir/ddl_recovery.log
total: 222
total_latency: 288.64 ms
count_read: 0
read_latency: 0 ps
count_write: 114
write_latency: 2.59 ms
count_misc: 108
misc_latency: 286.05 ms
*************************** 2. row ***************************
file: @@datadir/ib_logfile0
total: 95
total_latency: 165.29 ms
count_read: 6
read_latency: 61.04 us
count_write: 43
write_latency: 1.31 ms
count_misc: 46
misc_latency: 163.92 ms
...
SELECT * FROM sys.x$io_global_by_file_by_latency\G
*************************** 1. row ***************************
file: /home/ian/sandboxes/msb_10_6_19/data/ddl_recovery.log
total: 222
total_latency: 288641408158
count_read: 0
read_latency: 0
count_write: 114
write_latency: 2594925264
count_misc: 108
misc_latency: 286046482894
*************************** 2. row ***************************
file: /home/ian/sandboxes/msb_10_6_19/data/ib_logfile0
total: 95
total_latency: 165291020006
count_read: 6
read_latency: 61040974
count_write: 43
write_latency: 1310187820
count_misc: 46
misc_latency: 163919791212
...SELECT * FROM sys.metrics\G
*************************** 1. row ***************************
Variable_name: aborted_clients
Variable_value: 0
Type: Global Status
Enabled: YES
*************************** 2. row ***************************
Variable_name: aborted_connects
Variable_value: 0
Type: Global Status
Enabled: YES
...
*************************** 578. row ***************************
Variable_name: trx_undo_slots_used
Variable_value: 0
Type: InnoDB Metrics - transaction
Enabled: YES
*************************** 579. row ***************************
Variable_name: NOW()
Variable_value: 2024-09-09 16:16:08.745
Type: System Time
Enabled: YES
*************************** 580. row ***************************
Variable_name: UNIX_TIMESTAMP()
Variable_value: 1725891368.745
Type: System Time
Enabled: YEScount_read
Total number of read I/O events for the file.
total_read
Total number of bytes read for the I/O event.
avg_read
Average number of bytes per read for the I/O event.
count_write
Total number of write requests for the I/O event.
total_written
Number of bytes written for the I/O event.
avg_written
Average number of bytes per write for the I/O event.
total_requested
Total number of bytes (read and write) for the I/O event.
rows_examined
Total number of rows read from storage engines by statements for the host.
rows_affected
Total number of rows affected by statements for the host.
full_scans
Total number of full table scans by statements for the host.
locked_table_subpartition
Name of the locked subpartition, or NULL if none.
locked_index
Name of the locked index.
locked_type
Type of the waiting lock.
waiting_trx_id
ID of the waiting transaction.
waiting_trx_started
Time that the waiting transaction started.
waiting_trx_age
TIME value for the length of time that the transaction has been waiting.
waiting_trx_rows_locked
Number of rows locked by the waiting transaction.
waiting_trx_rows_modified
Number of rows modified by the waiting transaction.
waiting_pid
Processlist ID of the waiting transaction.
waiting_query
Statement waiting for the lock.
waiting_lock_id
ID of the waiting lock.
waiting_lock_mode
Mode of the waiting lock.
blocking_trx_id
ID of the transaction blocking the waiting lock.
blocking_pid
Processlist ID of the blocking transaction.
blocking_query
Statement the blocking transaction is executing, or NULL if the session that issued the blocking query has become idle.
blocking_lock_id
ID of the lock blocking the waiting lock.
blocking_lock_mode
Mode of the lock blocking the waiting lock.
blocking_trx_started
Time the blocking transaction started.
blocking_trx_age
TIME value for how long the blocking transaction has been executing.
blocking_trx_rows_locked
Number of rows locked by the blocking transaction.
blocking_trx_rows_modified
Number of rows modified by the blocking transaction.
sql_kill_blocking_query
KILL statement that could be used to kill the blocking statement.
sql_kill_blocking_connection
KILL statement that could be used to kill the blocking statement session.
count_read
Total number of read request for the I/O event.
total_read
Total number of bytes read for the I/O event.
avg_read
Average number of bytes per read for the I/O event.
count_write
Total number of write requests for the I/O event.
total_written
Number of bytes written for the I/O event.
avg_written
Average number of bytes per write for the I/O event.
total_requested
Total number of bytes (read and write) for the I/O event.
write_latency
Total wait time of timed write I/O events for the file.
count_misc
Total number of other I/O events for the file.
misc_latency
Total wait time of timed other I/O events for the file.
TABLE_SCHEMA
Database name.
TABLE_NAME
Table name.
GRANTEE
Account name that was granted the privilege.
PRIVILEGE
Privilege, such as SELECT or DROP.
LEVEL
Privilege level, such as GLOBAL or SCHEMA.
avg_write
Average number of bytes per write to the file.
total
Total number of bytes read and written for the file.
write_pct
Percentage of total I/O bytes that were writes.
file
File path name.
count_read
Total number of read events for the file.
total_read
Total number of bytes read from the file.
avg_read
Average number of bytes per read from the file.
count_write
Total number of write events for the file.
total_written
Total number of bytes written to the file.
SELECT * FROM sys.privileges_by_table_by_level;
+--------------+------------+---------------------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME | GRANTEE | PRIVILEGE | LEVEL |
+--------------+------------+---------------------------+----------------+--------+
...
| test | t2 | 'root'@'localhost' | SELECT | GLOBAL |
| test | t1 | 'root'@'localhost' | SELECT | GLOBAL |
| test | t3 | 'root'@'localhost' | SELECT | GLOBAL |
| test | t2 | 'root'@'localhost' | INSERT | GLOBAL |
| test | t1 | 'root'@'localhost' | INSERT | GLOBAL |
| test | t3 | 'root'@'localhost' | INSERT | GLOBAL |
| test | t2 | 'root'@'localhost' | UPDATE | GLOBAL |
| test | t1 | 'root'@'localhost' | UPDATE | GLOBAL |
| test | t3 | 'root'@'localhost' | UPDATE | GLOBAL |
| test | t2 | 'root'@'localhost' | DELETE | GLOBAL |
| test | t1 | 'root'@'localhost' | DELETE | GLOBAL |
| test | t3 | 'root'@'localhost' | DELETE | GLOBAL |
| test | t2 | 'root'@'localhost' | CREATE | GLOBAL |
| test | t1 | 'root'@'localhost' | CREATE | GLOBAL |
| test | t3 | 'root'@'localhost' | CREATE | GLOBAL |
| test | t2 | 'root'@'localhost' | DROP | GLOBAL |
| test | t1 | 'root'@'localhost' | DROP | GLOBAL |
| test | t3 | 'root'@'localhost' | DROP | GLOBAL |
| test | t2 | 'root'@'localhost' | REFERENCES | GLOBAL |
| test | t1 | 'root'@'localhost' | REFERENCES | GLOBAL |
| test | t3 | 'root'@'localhost' | REFERENCES | GLOBAL |
| test | t2 | 'root'@'localhost' | INDEX | GLOBAL |
| test | t1 | 'root'@'localhost' | INDEX | GLOBAL |
| test | t3 | 'root'@'localhost' | INDEX | GLOBAL |
| test | t2 | 'root'@'localhost' | ALTER | GLOBAL |
| test | t1 | 'root'@'localhost' | ALTER | GLOBAL |
| test | t3 | 'root'@'localhost' | ALTER | GLOBAL |
| test | t2 | 'root'@'localhost' | SHOW VIEW | GLOBAL |
| test | t1 | 'root'@'localhost' | SHOW VIEW | GLOBAL |
| test | t3 | 'root'@'localhost' | SHOW VIEW | GLOBAL |
| test | t2 | 'root'@'localhost' | TRIGGER | GLOBAL |
| test | t1 | 'root'@'localhost' | TRIGGER | GLOBAL |
| test | t3 | 'root'@'localhost' | TRIGGER | GLOBAL |
| test | t2 | 'root'@'localhost' | DELETE HISTORY | GLOBAL |
| test | t1 | 'root'@'localhost' | DELETE HISTORY | GLOBAL |
| test | t3 | 'root'@'localhost' | DELETE HISTORY | GLOBAL |
| test | t2 | 'PUBLIC'@'' | SELECT | SCHEMA |
| test | t1 | 'PUBLIC'@'' | SELECT | SCHEMA |
| test | t3 | 'PUBLIC'@'' | SELECT | SCHEMA |
| test | t2 | 'PUBLIC'@'' | INSERT | SCHEMA |
| test | t1 | 'PUBLIC'@'' | INSERT | SCHEMA |
| test | t3 | 'PUBLIC'@'' | INSERT | SCHEMA |
| test | t2 | 'PUBLIC'@'' | UPDATE | SCHEMA |
| test | t1 | 'PUBLIC'@'' | UPDATE | SCHEMA |
| test | t3 | 'PUBLIC'@'' | UPDATE | SCHEMA |
| test | t2 | 'PUBLIC'@'' | DELETE | SCHEMA |
| test | t1 | 'PUBLIC'@'' | DELETE | SCHEMA |
| test | t3 | 'PUBLIC'@'' | DELETE | SCHEMA |
| test | t2 | 'PUBLIC'@'' | CREATE | SCHEMA |
| test | t1 | 'PUBLIC'@'' | CREATE | SCHEMA |
| test | t3 | 'PUBLIC'@'' | CREATE | SCHEMA |
| test | t2 | 'PUBLIC'@'' | DROP | SCHEMA |
| test | t1 | 'PUBLIC'@'' | DROP | SCHEMA |
| test | t3 | 'PUBLIC'@'' | DROP | SCHEMA |
| test | t2 | 'PUBLIC'@'' | REFERENCES | SCHEMA |
| test | t1 | 'PUBLIC'@'' | REFERENCES | SCHEMA |
| test | t3 | 'PUBLIC'@'' | REFERENCES | SCHEMA |
| test | t2 | 'PUBLIC'@'' | INDEX | SCHEMA |
| test | t1 | 'PUBLIC'@'' | INDEX | SCHEMA |
| test | t3 | 'PUBLIC'@'' | INDEX | SCHEMA |
| test | t2 | 'PUBLIC'@'' | ALTER | SCHEMA |
| test | t1 | 'PUBLIC'@'' | ALTER | SCHEMA |
| test | t3 | 'PUBLIC'@'' | ALTER | SCHEMA |
| test | t2 | 'PUBLIC'@'' | SHOW VIEW | SCHEMA |
| test | t1 | 'PUBLIC'@'' | SHOW VIEW | SCHEMA |
| test | t3 | 'PUBLIC'@'' | SHOW VIEW | SCHEMA |
| test | t2 | 'PUBLIC'@'' | TRIGGER | SCHEMA |
| test | t1 | 'PUBLIC'@'' | TRIGGER | SCHEMA |
| test | t3 | 'PUBLIC'@'' | TRIGGER | SCHEMA |
| test | t2 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA |
| test | t1 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA |
| test | t3 | 'PUBLIC'@'' | DELETE HISTORY | SCHEMA |
+--------------+------------+---------------------------+----------------+--------+SELECT * FROM sys.io_global_by_file_by_bytes\G
...
*************************** 3. row ***************************
file: @@datadir/ddl_recovery.log
count_read: 0
total_read: 0 bytes
avg_read: 0 bytes
count_write: 114
total_written: 220.17 KiB
avg_write: 1.93 KiB
total: 220.17 KiB
write_pct: 100.00
*************************** 4. row ***************************
file: @@datadir/ib_logfile0
count_read: 6
total_read: 66.50 KiB
avg_read: 11.08 KiB
count_write: 43
total_written: 81.00 KiB
avg_write: 1.88 KiB
total: 147.50 KiB
write_pct: 54.92
...
SELECT * FROM sys.x$io_global_by_file_by_bytes\G
...
*************************** 3. row ***************************
file: /home/ian/sandboxes/msb_10_6_19/data/ddl_recovery.log
count_read: 0
total_read: 0
avg_read: 0.0000
count_write: 114
total_written: 225459
avg_write: 1977.7105
total: 225459
write_pct: 100.00
*************************** 4. row ***************************
file: /home/ian/sandboxes/msb_10_6_19/data/ib_logfile0
count_read: 6
total_read: 68096
avg_read: 11349.3333
count_write: 43
total_written: 82944
avg_write: 1928.9302
total: 151040
write_pct: 54.92
...This function quotes a given string with backticks to ensure it can be safely used as an identifier in SQL statements.
sys.quote_identifier(str)quote_identifier is a available with the .
It quotes a string to produce a result that can be used as an identifier in an SQL statement. The string is returned enclosed by backticks ("") and with each instance of backtick ("") doubled. If the argument is NULL, the return value is the word "NULL" without enclosing backticks.
This page is licensed: CC BY-SA / Gnu FDL
The host_summary views provide a high-level overview of activity per client host, including statement counts, file I/O, and connection statistics.
The host_summary and x$host_summary views contain host activity information, grouped by host. The host_summary view is intended to be easier for human reading, while the x$host_summary view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
SELECT sys.quote_identifier("Identifier with spaces");
+------------------------------------------------+
| sys.quote_identifier("Identifier with spaces") |
+------------------------------------------------+
| `Identifier with spaces` |
+------------------------------------------------+
SELECT sys.quote_identifier("Identifier` containing `backticks");
+-----------------------------------------------------------+
| sys.quote_identifier("Identifier` containing `backticks") |
+-----------------------------------------------------------+
| `Identifier`` containing ``backticks` |
+-----------------------------------------------------------+host
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
statements
Total number of statements for the host.
statement_latency
Total wait time of timed statements for the host.
statement_avg_latency
Average wait time per timed statement for the host.
table_scans
Total table scans for the host.
file_ios
Total file I/O events for the host.
This page is licensed: CC BY-SA / Gnu FDL
These views rank threads by I/O latency, making it easier to identify specific threads that are consuming the most disk resources.
The io_by_thread_by_latency and x$io_by_thread_by_latency view summarize I/O consumers to display time waiting for I/O, grouped by thread. Rows are sorted by descending total I/O latency by default.
The io_by_thread_by_latency view is intended to be easier for human reading, while the x$io_by_thread_by_latency view provides the data in raw form, intended for tools that process the data.
They contain the following columns:
This page is licensed: CC BY-SA / Gnu FDL
These views provide a breakdown of file I/O events by event type (e.g., read, write) for each host, helping to characterize the I/O workload.
The host_summary_by_file_io_type and x$host_summary_by_file_io_type views summarize file I/O, grouped by host and event type. Rows are sorted by host and descending total I/O latency by default. The host_summary_by_file_io_type view is intended to be easier for human reading, while the x$host_summary_by_file_io_type view provides the data in raw form, intended for tools that process the data.
SELECT * FROM sys.host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 59
statement_latency: 148.11 ms
statement_avg_latency: 2.51 ms
table_scans: 11
file_ios: 2065
file_io_latency: 79.57 ms
current_connections: 1
total_connections: 3
unique_users: 1
current_memory: -2672 bytes
total_memory_allocated: 0 bytes
SELECT * FROM sys.x$host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 98
statement_latency: 160926285000
statement_avg_latency: 1642104948.9796
table_scans: 12
file_ios: 2071
file_io_latency: 79742533755
current_connections: 1
total_connections: 3
unique_users: 1
current_memory: -2672
total_memory_allocated: 0file_io_latency
Total wait time of timed file I/O events for the host.
current_connections
Current connections for the host.
total_connections
Total connections for the host.
unique_users
Number of distinct users for the host.
current_memory
Current allocated memory for the host.
total_memory_allocated
Total allocated memory for the host.
thread_id
Thread id.
processlist_id
Processlist id for foreground threads, or NULL for background threads.
user
The account associated with a foreground thread, or the background thread name
total
Total number of I/O events allocated for the thread.
total_latency
Total wait time of timed I/O events for the thread.
min_latency
Minimum single wait time of timed I/O events for the thread.
avg_latency
Average wait time per timed I/O event for the thread.
min_latency
Maximum single wait time of timed I/O events for the thread.
host
Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).
event_name
File I/O event name.
total
Total number of occurrences of the file I/O event for the host.
total_latency
Total wait time of timed occurrences of the file I/O event for the host.
max_latency
Maximum single wait time of timed occurrences of the file I/O event for the host.
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM sys.io_by_thread_by_latency\G
*************************** 1. row ***************************
user: main
total: 378
total_latency: 40.11 ms
min_latency: 570.21 ns
avg_latency: 206.02 us
max_latency: 4.33 ms
thread_id: 1
processlist_id: NULL
*************************** 2. row ***************************
user: msandbox@localhost
total: 521
total_latency: 10.28 ms
min_latency: 775.04 ns
avg_latency: 21.79 us
max_latency: 977.79 us
thread_id: 89
processlist_id: 7
...
SELECT * FROM sys.x$io_by_thread_by_latency\G
*************************** 1. row ***************************
user: main
total: 378
total_latency: 40106340880
min_latency: 570208
avg_latency: 206016046.6000
max_latency: 4327780456
thread_id: 1
processlist_id: NULL
*************************** 2. row ***************************
user: msandbox@localhost
total: 498
total_latency: 9637694714
min_latency: 775040
avg_latency: 21364289.0000
max_latency: 977787350
thread_id: 89
processlist_id: 7
...SELECT * FROM sys.host_summary_by_file_io_type;
+------------+----------------------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | max_latency |
+------------+----------------------------------------------+-------+---------------+-------------+
| background | wait/io/file/innodb/innodb_log_file | 45 | 109.80 ms | 26.48 ms |
| background | wait/io/file/innodb/innodb_data_file | 195 | 29.47 ms | 1.23 ms |
| background | wait/io/file/sql/global_ddl_log | 4 | 4.45 ms | 4.33 ms |
...
| localhost | wait/io/file/csv/data | 4 | 25.98 us | 9.60 us |
| localhost | wait/io/file/partition/ha_partition::parfile | 1 | 14.19 us | 14.19 us |
| localhost | wait/io/file/myisam/kfile | 1 | 11.95 us | 11.95 us |
+------------+----------------------------------------------+-------+---------------+-------------+
SELECT * FROM sys.x$host_summary_by_file_io_type;
+------------+----------------------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | max_latency |
+------------+----------------------------------------------+-------+---------------+-------------+
| background | wait/io/file/innodb/innodb_log_file | 45 | 109804643160 | 26478157582 |
| background | wait/io/file/innodb/innodb_data_file | 195 | 29469738630 | 1226986584 |
| background | wait/io/file/sql/global_ddl_log | 4 | 4447263252 | 4327780456 |
| localhost | wait/io/file/csv/data | 4 | 25978718 | 9603922 |
| localhost | wait/io/file/partition/ha_partition::parfile | 1 | 14191190 | 14191190 |
| localhost | wait/io/file/myisam/kfile | 1 | 11954300 | 11954300 |
+------------+----------------------------------------------+-------+---------------+-------------+