WL#8159: Include Sys Schema in MySQL 5.7

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

The MySQL sys schema project (https://github.com/MarkLeith/mysql-sys) has become 
increasingly popular within our user base over the last year. 

Many DBAs/Developers within our user base say that it brings some needed 
simplicity for configuring and querying the Performance Schema. A recent blog
post illustrates this:
http://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/

Given this, there has been a lot of call for us to integrate the sys schema
within MySQL by default, so that all users can benefit both from the simplicity
of the use case driven objects, whilst also helping to bring the full power of 
Performance Schema more easily to their hands.

MySQL Workbench took the first step here, and now installs the sys schema at the 
click of a button, then uses it for reporting:

http://mysqlworkbench.org/2014/03/mysql-workbench-6-1-performance-schema-reports/

This worklog is to integrate the sys schema within MySQL fully, so that it is 
installed by default, and added on upgrade appropriately.

This fixes BUG#71207 - Please, add MySQL sys schema to the official MySQL server 
distribution.
==================================================================================
INSTALLATION
==================================================================================

F-1 mysql_install_db / mysqld --bootstrap should install the sys schema for fresh 
installs
- 1.1 In the case where Performance Schema is not compiled in, a variable should 
be added, called --skip-sys-schema, which allows the schema to not be installed

F-2 mysql_upgrade should check whether the sys schema exists and upgrade / install 
appropriately
- 2.1 If no sys schema is found, install fresh
- 2.2 If a database called sys is found, try selecting from the sys.version view 
for supported version
-- 2.2.1 If that view does not exist / returns an error and abort the 
mysql_upgrade (this could be a user created sys schema, and should be rectified 
before proceeding).
-- 2.2.2 If well formed version is returned from the view, compare it the compiled 
in version
--- 2.2.2.1 If lower, install the new version of the sys schema.
--- 2.2.2.2 If higher, continue, a later version is already installed
- 2.3 In the case where Performance Schema is not compiled in, a variable should 
be added, called --skip-sys-schema, which allows the schema to not be installed

F-3 mysqld --initialize should install the sys schema as standard on all operating 
systems.

F-4 A new schema called "sys" should exist post successful installation/upgrade

F-5 The following list of tables should exist within the schema
- 5.1 sys_config

F-6 The following list of triggers should exist within the schema
- 6.1 sys_config_insert_set_user
- 6.2 sys_config_update_set_user

F-7 The following list of functions should exist within the schema
- 7.1 sys_get_config
- 7.2 ps_thread_id
- 7.3 ps_thread_stack
- 7.4 ps_is_instrument_default_timed
- 7.5 ps_is_instrument_default_enabled
- 7.6 ps_is_account_enabled
- 7.7 format_time
- 7.8 format_statement
- 7.9 format_path
- 7.10 format_bytes
- 7.11 extract_table_from_file_name
- 7.12 extract_schema_from_file_name

F-8 The following list of procedures should exist within the schema
- 8.1 create_synonym_db
- 8.2 ps_setup_disable_background_threads
- 8.3 ps_setup_disable_consumer
- 8.4 ps_setup_disable_instrument
- 8.5 ps_setup_disable_thread
- 8.6 ps_setup_enable_background_threads
- 8.7 ps_setup_enable_consumer
- 8.8 ps_setup_enable_instrument
- 8.9 ps_setup_enable_thread
- 8.10 ps_setup_reload_saved
- 8.11 ps_setup_reset_to_default
- 8.12 ps_setup_save
- 8.13 ps_setup_show_disabled
- 8.14 ps_setup_show_disabled_consumers
- 8.15 ps_setup_show_disabled_instruments
- 8.16 ps_setup_show_enabled
- 8.17 ps_setup_show_enabled_consumers
- 8.18 ps_setup_show_enabled_instruments
- 8.19 ps_statement_avg_latency_histogram
- 8.20 ps_trace_statement_digest
- 8.21 ps_trace_thread
- 8.22 ps_truncate_all_tables

F-9 The following list of views should exist within the schema
host_summary
host_summary_by_file_io
host_summary_by_file_io_type
host_summary_by_stages
host_summary_by_statement_latency
host_summary_by_statement_type
innodb_buffer_stats_by_schema
innodb_buffer_stats_by_table
innodb_lock_waits
io_by_thread_by_latency
io_global_by_file_by_bytes
io_global_by_file_by_latency
io_global_by_wait_by_bytes
io_global_by_wait_by_latency
latest_file_io
memory_by_host_by_current_bytes
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
memory_global_by_current_bytes
memory_global_total
processlist
ps_check_lost_instrumentation
schema_index_statistics
schema_object_overview
schema_table_statistics
schema_table_statistics_with_buffer
schema_tables_with_full_table_scans
schema_unused_indexes
statement_analysis
statements_with_errors_or_warnings
statements_with_full_table_scans
statements_with_runtimes_in_95th_percentile
statements_with_sorting
statements_with_temp_tables
user_summary
user_summary_by_file_io
user_summary_by_file_io_type
user_summary_by_stages
user_summary_by_statement_latency
user_summary_by_statement_type
version
wait_classes_global_by_avg_latency
wait_classes_global_by_latency
waits_by_host_by_latency
waits_by_user_by_latency
waits_global_by_latency
x$host_summary
x$host_summary_by_file_io
x$host_summary_by_file_io_type
x$host_summary_by_stages
x$host_summary_by_statement_latency
x$host_summary_by_statement_type
x$innodb_buffer_stats_by_schema
x$innodb_buffer_stats_by_table
x$innodb_lock_waits
x$io_by_thread_by_latency
x$io_global_by_file_by_bytes
x$io_global_by_file_by_latency
x$io_global_by_wait_by_bytes
x$io_global_by_wait_by_latency
x$latest_file_io
x$memory_by_host_by_current_bytes
x$memory_by_thread_by_current_bytes
x$memory_by_user_by_current_bytes
x$memory_global_by_current_bytes
x$memory_global_total
x$processlist
x$ps_digest_95th_percentile_by_avg_us
x$ps_digest_avg_latency_distribution
x$ps_schema_table_statistics_io
x$schema_index_statistics
x$schema_table_statistics
x$schema_table_statistics_with_buffer
x$schema_tables_with_full_table_scans
x$statement_analysis
x$statements_with_errors_or_warnings
x$statements_with_full_table_scans
x$statements_with_runtimes_in_95th_percentile
x$statements_with_sorting
x$statements_with_temp_tables
x$user_summary
x$user_summary_by_file_io
x$user_summary_by_file_io_type
x$user_summary_by_stages
x$user_summary_by_statement_latency
x$user_summary_by_statement_type
x$wait_classes_global_by_avg_latency
x$wait_classes_global_by_latency
x$waits_by_host_by_latency
x$waits_by_user_by_latency
x$waits_global_by_latency

F10 Each object should be created with the DEFINER set as root@localhost (the 
default user)

F11 Each object should set SQL SECURITY as INVOKER. 
- To use the sys objects, the connected user should have rights on the underlying 
objects as well (typically SELECT on performance_schema tables, but in the case of 
the innod_buffer_* views, also the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, 
which requires the PROCESS privilege).

==================================================================================
FUNCTIONAL
==================================================================================

F-12 All views should work when selecting from them using the default settings 
(particularly the sql_mode). This for example means using ONLY_FULL_GROUP_BY.

F-13 Not all objects are expected to work with the default configuration, namely:
- 13.1 host_summary_by_stages / x$host_summary_by_stages 
-- (requires stage instruments enabled)
- 13.2 user_summary_by_stages / x$user_summary_by_stages
-- (requires stage instruments enabled)
- 13.3 latest_file_io / x$latest_file_io
-- (requires events_currents_history_long consumer enabled)
==================================================================================
NEW TABLES
==================================================================================

sys_config
==================================================================================

Description
===========

Holds configuration options for the sys schema. This is a persistent table (using 
the `InnoDB` 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:

CREATE TABLE `sys_config` (
  `variable` varchar(128) NOT NULL,
  `value` varchar(128) DEFAULT NULL,
  `set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
  `set_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Note, when sys 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.

Options included
================

Variable               | Default Value | Description

statement_truncate_len | 64            | Sets the size to truncate statements to, 
for the `format_statement()` function

==================================================================================
NEW VIEWS
==================================================================================

Many of the views in the sys schema have both a command line user friendly format 
output, as well as tooling friendly versions of any view that contains formatted 
output duplicated as an x$ table.

The examples below show output for only the formatted views, and note where there 
is an x$ counterpart available.

host_summary / x$host_summary
==================================================================================

Description
===========

Summarizes statement activity, file IO and connections by host.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| host                   | varchar(60)   | YES  |     | NULL    |       |
| statements             | decimal(64,0) | YES  |     | NULL    |       |
| statement_latency      | text          | YES  |     | NULL    |       |
| statement_avg_latency  | text          | YES  |     | NULL    |       |
| table_scans            | decimal(65,0) | YES  |     | NULL    |       |
| file_ios               | decimal(64,0) | YES  |     | NULL    |       |
| file_io_latency        | text          | YES  |     | NULL    |       |
| current_connections    | decimal(41,0) | YES  |     | NULL    |       |
| total_connections      | decimal(41,0) | YES  |     | NULL    |       |
| unique_users           | bigint(21)    | NO   |     | 0       |       |
| current_memory         | text          | YES  |     | NULL    |       |
| total_memory_allocated | text          | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
12 rows in set (0.15 sec)

mysql> desc x$host_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| host                   | varchar(60)   | YES  |     | NULL    |       |
| statements             | decimal(64,0) | YES  |     | NULL    |       |
| statement_latency      | decimal(64,0) | YES  |     | NULL    |       |
| statement_avg_latency  | decimal(65,4) | YES  |     | NULL    |       |
| table_scans            | decimal(65,0) | YES  |     | NULL    |       |
| file_ios               | decimal(64,0) | YES  |     | NULL    |       |
| file_io_latency        | decimal(64,0) | YES  |     | NULL    |       |
| current_connections    | decimal(41,0) | YES  |     | NULL    |       |
| total_connections      | decimal(41,0) | YES  |     | NULL    |       |
| unique_users           | bigint(21)    | NO   |     | 0       |       |
| current_memory         | decimal(63,0) | YES  |     | NULL    |       |
| total_memory_allocated | decimal(64,0) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

Example
=======

mysql> select * from host_summary\G
*************************** 1. row ***************************
                  host: localhost
            statements: 101
     statement_latency: 3.41 s
 statement_avg_latency: 33.72 ms
           table_scans: 6
              file_ios: 1710
       file_io_latency: 2.49 h
   current_connections: 1
     total_connections: 1
          unique_users: 1
        current_memory: 2.62 MiB
total_memory_allocated: 15.17 MiB


host_summary_by_file_io / x$host_summary_by_file_io
==================================================================================

Description
===========

Summarizes file IO totals per host.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary_by_file_io;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| host       | varchar(60)   | YES  |     | NULL    |       |
| ios        | decimal(42,0) | YES  |     | NULL    |       |
| io_latency | text          | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc x$host_summary_by_file_io;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| host       | varchar(60)   | YES  |     | NULL    |       |
| ios        | decimal(42,0) | YES  |     | NULL    |       |
| io_latency | decimal(42,0) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Example
=======

 mysql> select * from host_summary_by_file_io;
  +------------+-------+------------+
  | host       | ios   | io_latency |
  +------------+-------+------------+
  | hal1       | 26457 | 21.58 s    |
  | hal2       |  1189 | 394.21 ms  |
  +------------+-------+------------+


host_summary_by_file_io_type / x$host_summary_by_file_io_type
==================================================================================

Description
===========

Summarizes file IO by event type per host.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary_by_file_io_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.70 sec)

mysql> desc x$host_summary_by_file_io_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

Example
=======

  mysql> select * from host_summary_by_file_io_type limit 3\G
  *************************** 1. row ***************************
           host: background
     event_name: wait/io/file/innodb/innodb_data_file
          total: 598
  total_latency: 583.17 ms
    max_latency: 25.80 ms
  *************************** 2. row ***************************
           host: background
     event_name: wait/io/file/sql/FRM
          total: 871
  total_latency: 133.36 ms
    max_latency: 15.89 ms
  *************************** 3. row ***************************
           host: background
     event_name: wait/io/file/innodb/innodb_log_file
          total: 26
  total_latency: 45.66 ms
    max_latency: 23.26 ms
 

host_summary_by_stages / x$host_summary_by_stages
==================================================================================

Description
===========

Summarizes stages by host, ordered by host and total latency per stage.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary_by_stages;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.06 sec)

mysql> desc x$host_summary_by_stages;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.81 sec)

Example
=======

  mysql> select * from host_summary_by_stages limit 3\G
  *************************** 1. row ***************************
           host: localhost
     event_name: stage/sql/end
          total: 3
  total_latency: 40.26 ms
    avg_latency: 13.42 ms
  *************************** 2. row ***************************
           host: localhost
     event_name: stage/sql/Opening tables
          total: 71
  total_latency: 1.05 ms
    avg_latency: 14.79 us
  *************************** 3. row ***************************
           host: localhost
     event_name: stage/sql/Creating sort index
          total: 1
  total_latency: 490.57 us
    avg_latency: 490.57 us


host_summary_by_statement_latency / x$host_summary_by_statement_latency
==================================================================================

Description
===========

Summarizes overall statement statistics by host.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary_by_statement_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| host          | varchar(60)   | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | text          | YES  |     | NULL    |       |
| max_latency   | text          | YES  |     | NULL    |       |
| lock_latency  | text          | YES  |     | NULL    |       |
| rows_sent     | decimal(42,0) | YES  |     | NULL    |       |
| rows_examined | decimal(42,0) | YES  |     | NULL    |       |
| rows_affected | decimal(42,0) | YES  |     | NULL    |       |
| full_scans    | decimal(43,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.29 sec)

mysql> desc x$host_summary_by_statement_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| host          | varchar(60)   | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | decimal(42,0) | YES  |     | NULL    |       |
| max_latency   | decimal(42,0) | YES  |     | NULL    |       |
| lock_latency  | decimal(42,0) | YES  |     | NULL    |       |
| rows_sent     | decimal(42,0) | YES  |     | NULL    |       |
| rows_examined | decimal(42,0) | YES  |     | NULL    |       |
| rows_affected | decimal(42,0) | YES  |     | NULL    |       |
| full_scans    | decimal(43,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.54 sec)

Example
=======

  mysql> select * from host_summary_by_statement_latency\G
  *************************** 1. row ***************************
           host: localhost
          total: 379
  total_latency: 19.04 s
    max_latency: 3.87 s
   lock_latency: 2.36 s
      rows_sent: 644
  rows_examined: 3843
  rows_affected: 425
     full_scans: 35


host_summary_by_statement_type / x$host_summary_by_statement_type
==================================================================================

Description
===========

Summarizes the types of statements executed by each host.

When the host found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc host_summary_by_statement_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| statement     | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
| lock_latency  | text                | YES  |     | NULL    |       |
| rows_sent     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_examined | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_affected | bigint(20) unsigned | NO   |     | NULL    |       |
| full_scans    | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
10 rows in set (0.30 sec)

mysql> desc x$host_summary_by_statement_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| statement     | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| lock_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_sent     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_examined | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_affected | bigint(20) unsigned | NO   |     | NULL    |       |
| full_scans    | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
10 rows in set (0.76 sec)

Example
=======

mysql> select * from host_summary_by_statement_type limit 2\G
*************************** 1. row ***************************
         host: localhost
    statement: create_view
        total: 81
total_latency: 14.18 s
  max_latency: 349.16 ms
 lock_latency: 93.90 ms
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 2. row ***************************
         host: localhost
    statement: drop_db
        total: 1
total_latency: 1.07 s
  max_latency: 1.07 s
 lock_latency: 1.07 s
    rows_sent: 0
rows_examined: 0
rows_affected: 80
   full_scans: 0


innodb_buffer_stats_by_schema / x$innodb_buffer_stats_by_schema
==================================================================================

Description
===========

Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, 
aggregating by schema.

Structures
==========

mysql> desc innodb_buffer_stats_by_schema;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| object_schema | text          | YES  |     | NULL    |       |
| allocated     | text          | YES  |     | NULL    |       |
| data          | text          | YES  |     | NULL    |       |
| pages         | bigint(21)    | NO   |     | 0       |       |
| pages_hashed  | bigint(21)    | NO   |     | 0       |       |
| pages_old     | bigint(21)    | NO   |     | 0       |       |
| rows_cached   | decimal(44,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
7 rows in set (0.08 sec)

mysql> desc x$innodb_buffer_stats_by_schema;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| object_schema | text          | YES  |     | NULL    |       |
| allocated     | decimal(43,0) | YES  |     | NULL    |       |
| data          | decimal(43,0) | YES  |     | NULL    |       |
| pages         | bigint(21)    | NO   |     | 0       |       |
| pages_hashed  | bigint(21)    | NO   |     | 0       |       |
| pages_old     | bigint(21)    | NO   |     | 0       |       |
| rows_cached   | decimal(44,0) | NO   |     | 0       |       |
+---------------+---------------+------+-----+---------+-------+
7 rows in set (0.12 sec)

Example
=======

mysql> select * from innodb_buffer_stats_by_schema limit 2\G
*************************** 1. row ***************************
object_schema: downloads
    allocated: 480.00 KiB
         data: 232.20 KiB
        pages: 30
 pages_hashed: 30
    pages_old: 30
  rows_cached: 1376
*************************** 2. row ***************************
object_schema: test
    allocated: 272.00 KiB
         data: 128.79 KiB
        pages: 17
 pages_hashed: 17
    pages_old: 17
  rows_cached: 677


innodb_buffer_stats_by_table / x$innodb_buffer_stats_by_table
==================================================================================

Description
===========

Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, 
aggregating by schema and table name.

Structures
==========

mysql> desc innodb_buffer_stats_by_table;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| object_schema | text          | YES  |     | NULL    |       |
| object_name   | text          | YES  |     | NULL    |       |
| allocated     | text          | YES  |     | NULL    |       |
| data          | text          | YES  |     | NULL    |       |
| pages         | bigint(21)    | NO   |     | 0       |       |
| pages_hashed  | bigint(21)    | NO   |     | 0       |       |
| pages_old     | bigint(21)    | NO   |     | 0       |       |
| rows_cached   | decimal(44,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
8 rows in set (0.09 sec)

mysql> desc x$innodb_buffer_stats_by_table;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| object_schema | text          | YES  |     | NULL    |       |
| object_name   | text          | YES  |     | NULL    |       |
| allocated     | decimal(43,0) | YES  |     | NULL    |       |
| data          | decimal(43,0) | YES  |     | NULL    |       |
| pages         | bigint(21)    | NO   |     | 0       |       |
| pages_hashed  | bigint(21)    | NO   |     | 0       |       |
| pages_old     | bigint(21)    | NO   |     | 0       |       |
| rows_cached   | decimal(44,0) | NO   |     | 0       |       |
+---------------+---------------+------+-----+---------+-------+
8 rows in set (0.18 sec)

Example
=======

mysql> select * from innodb_buffer_stats_by_table limit 2\G
*************************** 1. row ***************************
object_schema: downloads
  object_name: mem_downloads
    allocated: 320.00 KiB
         data: 182.29 KiB
        pages: 20
 pages_hashed: 20
    pages_old: 20
  rows_cached: 1028
*************************** 2. row ***************************
object_schema: test
  object_name: mem_downloads
    allocated: 272.00 KiB
         data: 128.79 KiB
        pages: 17
 pages_hashed: 17
    pages_old: 17
  rows_cached: 677


innodb_lock_waits / x$innodb_lock_waits
==================================================================================

Description
===========

Gives a snapshot of which InnoDB locks transactions are waiting for.
The lock waits are ordered by the age of the lock descending.

Structures
==========

mysql> desc innodb_lock_waits;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| wait_started       | datetime            | YES  |     | NULL    |       |
| wait_age           | time                | YES  |     | NULL    |       |
| locked_table       | varchar(1024)       | NO   |     |         |       |
| locked_index       | varchar(1024)       | YES  |     | NULL    |       |
| locked_type        | varchar(32)         | NO   |     |         |       |
| waiting_trx_id     | varchar(18)         | NO   |     |         |       |
| waiting_pid        | bigint(21) unsigned | NO   |     | 0       |       |
| waiting_query      | longtext            | YES  |     | NULL    |       |
| waiting_lock_id    | varchar(81)         | NO   |     |         |       |
| waiting_lock_mode  | varchar(32)         | NO   |     |         |       |
| blocking_trx_id    | varchar(18)         | NO   |     |         |       |
| blocking_pid       | bigint(21) unsigned | NO   |     | 0       |       |
| blocking_query     | longtext            | YES  |     | NULL    |       |
| blocking_lock_id   | varchar(81)         | NO   |     |         |       |
| blocking_lock_mode | varchar(32)         | NO   |     |         |       |
+--------------------+---------------------+------+-----+---------+-------+
15 rows in set (0.23 sec)

mysql> desc x$innodb_lock_waits;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| wait_started       | datetime            | YES  |     | NULL    |       |
| wait_age           | time                | YES  |     | NULL    |       |
| locked_table       | varchar(1024)       | NO   |     |         |       |
| locked_index       | varchar(1024)       | YES  |     | NULL    |       |
| locked_type        | varchar(32)         | NO   |     |         |       |
| waiting_trx_id     | varchar(18)         | NO   |     |         |       |
| waiting_pid        | bigint(21) unsigned | NO   |     | 0       |       |
| waiting_query      | varchar(1024)       | YES  |     | NULL    |       |
| waiting_lock_id    | varchar(81)         | NO   |     |         |       |
| waiting_lock_mode  | varchar(32)         | NO   |     |         |       |
| blocking_trx_id    | varchar(18)         | NO   |     |         |       |
| blocking_pid       | bigint(21) unsigned | NO   |     | 0       |       |
| blocking_query     | varchar(1024)       | YES  |     | NULL    |       |
| blocking_lock_id   | varchar(81)         | NO   |     |         |       |
| blocking_lock_mode | varchar(32)         | NO   |     |         |       |
+--------------------+---------------------+------+-----+---------+-------+
15 rows in set (0.01 sec)

Example
=======

mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row ***************************
      wait_started: 2014-11-11 13:39:20
          wait_age: 00:00:07
      locked_table: `db1`.`t1`
      locked_index: PRIMARY
       locked_type: RECORD
    waiting_trx_id: 867158
       waiting_pid: 3
     waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
   waiting_lock_id: 867158:2363:3:3
 waiting_lock_mode: X
   blocking_trx_id: 867157
      blocking_pid: 4
    blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
  blocking_lock_id: 867157:2363:3:3
blocking_lock_mode: X
1 row in set (0.01 sec)


io_by_thread_by_latency / x$io_by_thread_by_latency
==================================================================================

Description
===========

Shows the top IO consumers by thread, ordered by total latency.

Structures
==========

mysql> desc io_by_thread_by_latency;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| user           | varchar(128)        | YES  |     | NULL    |       |
| total          | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency  | text                | YES  |     | NULL    |       |
| min_latency    | text                | YES  |     | NULL    |       |
| avg_latency    | text                | YES  |     | NULL    |       |
| max_latency    | text                | YES  |     | NULL    |       |
| thread_id      | bigint(20) unsigned | NO   |     | NULL    |       |
| processlist_id | bigint(20) unsigned | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
8 rows in set (0.14 sec)

mysql> desc x$io_by_thread_by_latency;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| user           | varchar(128)        | YES  |     | NULL    |       |
| total          | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency  | decimal(42,0)       | YES  |     | NULL    |       |
| min_latency    | bigint(20) unsigned | YES  |     | NULL    |       |
| avg_latency    | decimal(24,4)       | YES  |     | NULL    |       |
| max_latency    | bigint(20) unsigned | YES  |     | NULL    |       |
| thread_id      | bigint(20) unsigned | NO   |     | NULL    |       |
| processlist_id | bigint(20) unsigned | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
8 rows in set (0.03 sec)

Example
=======

mysql> select * from io_by_thread_by_latency limit 2\G
*************************** 1. row ***************************
          user: main
         total: 1461
 total_latency: 579.55 ms
   min_latency: 463.71 ns
   avg_latency: 930.18 us
   max_latency: 22.88 ms
     thread_id: 1
processlist_id: NULL
*************************** 2. row ***************************
          user: root@localhost
         total: 3032
 total_latency: 287.21 ms
   min_latency: 429.78 ns
   avg_latency: 66.25 us
   max_latency: 35.22 ms
     thread_id: 23
processlist_id: 4


io_global_by_file_by_bytes / x$io_global_by_file_by_bytes
==================================================================================

Description
===========

Shows the top global IO consumers by bytes usage by file.

Structures
==========

mysql> desc io_global_by_file_by_bytes;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| file          | varchar(260)        | YES  |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read    | text                | YES  |     | NULL    |       |
| avg_read      | text                | YES  |     | NULL    |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written | text                | YES  |     | NULL    |       |
| avg_write     | text                | YES  |     | NULL    |       |
| total         | text                | YES  |     | NULL    |       |
| write_pct     | decimal(26,2)       | NO   |     | 0.00    |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.15 sec)

mysql> desc x$io_global_by_file_by_bytes;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| file          | varchar(512)        | NO   |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read    | bigint(20)          | NO   |     | NULL    |       |
| avg_read      | decimal(23,4)       | NO   |     | 0.0000  |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written | bigint(20)          | NO   |     | NULL    |       |
| avg_write     | decimal(23,4)       | NO   |     | 0.0000  |       |
| total         | bigint(21)          | NO   |     | 0       |       |
| write_pct     | decimal(26,2)       | NO   |     | 0.00    |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.14 sec)

Example
=======

mysql> select * from io_global_by_file_by_bytes limit 2\G
*************************** 1. row ***************************
         file: @@datadir/ibdata1
   count_read: 412
   total_read: 8.41 MiB
     avg_read: 20.89 KiB
  count_write: 52
total_written: 1.58 MiB
    avg_write: 31.08 KiB
        total: 9.98 MiB
    write_pct: 15.81
*************************** 2. row ***************************
         file: @@datadir/mysql/proc.MYD
   count_read: 983
   total_read: 905.66 KiB
     avg_read: 943 bytes
  count_write: 232
total_written: 172.13 KiB
    avg_write: 760 bytes
        total: 1.05 MiB
    write_pct: 15.97


io_global_by_file_by_latency / x$io_global_by_file_by_latency
==================================================================================

Description
===========

Shows the top global IO consumers by latency by file.

Structures
==========

mysql> desc io_global_by_file_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| file          | varchar(260)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| read_latency  | text                | YES  |     | NULL    |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| write_latency | text                | YES  |     | NULL    |       |
| count_misc    | bigint(20) unsigned | NO   |     | NULL    |       |
| misc_latency  | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> desc x$io_global_by_file_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| file          | varchar(512)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| read_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| write_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| count_misc    | bigint(20) unsigned | NO   |     | NULL    |       |
| misc_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.07 sec)

Example
=======

mysql> select * from io_global_by_file_by_latency limit 2\G
*************************** 1. row ***************************
         file: @@datadir/ibdata1
        total: 473
total_latency: 400.98 ms
   count_read: 412
 read_latency: 377.46 ms
  count_write: 52
write_latency: 1.03 ms
   count_misc: 9
 misc_latency: 22.50 ms
*************************** 2. row ***************************
         file: @@datadir/sys/statements_with_runtimes_in_95th_percentile.frm~
        total: 6
total_latency: 146.06 ms
   count_read: 0
 read_latency: 0 ps
  count_write: 1
write_latency: 52.15 us
   count_misc: 5
 misc_latency: 146.00 ms


io_global_by_wait_by_bytes / x$io_global_by_wait_by_bytes
==================================================================================

Description
===========

Shows the top global IO consumer classes by bytes usage.

Structures
==========

mysql> desc io_global_by_wait_by_bytes;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| event_name      | varchar(128)        | YES  |     | NULL    |       |
| total           | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency   | text                | YES  |     | NULL    |       |
| min_latency     | text                | YES  |     | NULL    |       |
| avg_latency     | text                | YES  |     | NULL    |       |
| max_latency     | text                | YES  |     | NULL    |       |
| count_read      | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read      | text                | YES  |     | NULL    |       |
| avg_read        | text                | YES  |     | NULL    |       |
| count_write     | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written   | text                | YES  |     | NULL    |       |
| avg_written     | text                | YES  |     | NULL    |       |
| total_requested | text                | YES  |     | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+
13 rows in set (0.02 sec)

mysql> desc x$io_global_by_wait_by_bytes;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| event_name      | varchar(128)        | YES  |     | NULL    |       |
| total           | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| min_latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| count_read      | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read      | bigint(20)          | NO   |     | NULL    |       |
| avg_read        | decimal(23,4)       | NO   |     | 0.0000  |       |
| count_write     | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written   | bigint(20)          | NO   |     | NULL    |       |
| avg_written     | decimal(23,4)       | NO   |     | 0.0000  |       |
| total_requested | bigint(21)          | NO   |     | 0       |       |
+-----------------+---------------------+------+-----+---------+-------+
13 rows in set (0.01 sec)

Example
=======

mysql> select * from io_global_by_wait_by_bytes limit 1\G
*************************** 1. row ***************************
     event_name: innodb/innodb_data_file
          total: 619
  total_latency: 595.10 ms
    min_latency: 0 ps
    avg_latency: 961.39 us
    max_latency: 25.80 ms
     count_read: 476
     total_read: 9.41 MiB
       avg_read: 20.24 KiB
    count_write: 68
  total_written: 1.94 MiB
    avg_written: 29.18 KiB
total_requested: 11.34 MiB


io_global_by_wait_by_latency / x$io_global_by_wait_by_latency
==================================================================================

Description
===========

Shows the top global IO consumers by latency.

Structures
==========

mysql> desc io_global_by_wait_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| event_name    | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
| read_latency  | text                | YES  |     | NULL    |       |
| write_latency | text                | YES  |     | NULL    |       |
| misc_latency  | text                | YES  |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read    | text                | YES  |     | NULL    |       |
| avg_read      | text                | YES  |     | NULL    |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written | text                | YES  |     | NULL    |       |
| avg_written   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
14 rows in set (0.19 sec)

mysql> desc x$io_global_by_wait_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| event_name    | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| read_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
| write_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| misc_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
| count_read    | bigint(20) unsigned | NO   |     | NULL    |       |
| total_read    | bigint(20)          | NO   |     | NULL    |       |
| avg_read      | decimal(23,4)       | NO   |     | 0.0000  |       |
| count_write   | bigint(20) unsigned | NO   |     | NULL    |       |
| total_written | bigint(20)          | NO   |     | NULL    |       |
| avg_written   | decimal(23,4)       | NO   |     | 0.0000  |       |
+---------------+---------------------+------+-----+---------+-------+
14 rows in set (0.01 sec)

Example
=======

mysql> select * from io_global_by_wait_by_latency limit 1\G
*************************** 1. row ***************************
   event_name: sql/file_parser
        total: 689
total_latency: 2.56 s
  avg_latency: 3.71 ms
  max_latency: 145.53 ms
 read_latency: 1.67 ms
write_latency: 14.25 ms
 misc_latency: 2.54 s
   count_read: 3
   total_read: 1.63 KiB
     avg_read: 555 bytes
  count_write: 85
total_written: 279.57 KiB
  avg_written: 3.29 KiB


latest_file_io / x$latest_file_io
==================================================================================

Description
===========

Shows the latest file IO, by file / thread.

Structures
==========

mysql> desc latest_file_io;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| thread    | varchar(149) | YES  |     | NULL    |       |
| file      | varchar(260) | YES  |     | NULL    |       |
| latency   | text         | YES  |     | NULL    |       |
| operation | varchar(32)  | NO   |     | NULL    |       |
| requested | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.10 sec)

mysql> desc x$latest_file_io;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| thread    | varchar(149)        | YES  |     | NULL    |       |
| file      | varchar(512)        | YES  |     | NULL    |       |
| latency   | bigint(20) unsigned | YES  |     | NULL    |       |
| operation | varchar(32)         | NO   |     | NULL    |       |
| requested | bigint(20)          | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

Example
=======

mysql> select * from latest_file_io limit 3\G
*************************** 1. row ***************************
   thread: root@localhost:4
     file: @@tmpdir/#sqlf66d_4_db.MYD
  latency: 1.82 us
operation: read
requested: 20 bytes
*************************** 2. row ***************************
   thread: root@localhost:4
     file: @@tmpdir/#sqlf66d_4_db.MYD
  latency: 1.06 us
operation: read
requested: 126 bytes
*************************** 3. row ***************************
   thread: root@localhost:4
     file: @@tmpdir/#sqlf66d_4_db.MYD
  latency: 999.05 ns
operation: seek
requested: NULL


memory_by_host_by_current_bytes / x$memory_by_host_by_current_bytes
==================================================================================

Description
===========

Summarizes memory use by host using the 5.7 Performance Schema instrumentation.

When the host found is NULL, it is assumed to be a local "background" thread.

Structures
==========

mysql> desc memory_by_host_by_current_bytes;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| host               | varchar(60)   | YES  |     | NULL    |       |
| current_count_used | decimal(41,0) | YES  |     | NULL    |       |
| current_allocated  | text          | YES  |     | NULL    |       |
| current_avg_alloc  | text          | YES  |     | NULL    |       |
| current_max_alloc  | text          | YES  |     | NULL    |       |
| total_allocated    | text          | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
6 rows in set (0.24 sec)

mysql> desc x$memory_by_host_by_current_bytes;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| host               | varchar(60)   | YES  |     | NULL    |       |
| current_count_used | decimal(41,0) | YES  |     | NULL    |       |
| current_allocated  | decimal(41,0) | YES  |     | NULL    |       |
| current_avg_alloc  | decimal(45,4) | NO   |     | 0.0000  |       |
| current_max_alloc  | bigint(20)    | YES  |     | NULL    |       |
| total_allocated    | decimal(42,0) | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
6 rows in set (0.28 sec)

Example
=======

mysql> select * from memory_by_host_by_current_bytes\G
*************************** 1. row ***************************
              host: background
current_count_used: 29247
 current_allocated: 165.94 MiB
 current_avg_alloc: 5.81 KiB
 current_max_alloc: 131.06 MiB
   total_allocated: 197.01 MiB
*************************** 2. row ***************************
              host: localhost
current_count_used: 1246
 current_allocated: 1.74 MiB
 current_avg_alloc: 1.43 KiB
 current_max_alloc: 574.17 KiB
   total_allocated: 19.20 MiB


memory_by_thread_by_current_bytes / x$memory_by_thread_by_current_bytes
==================================================================================

Description
===========

Summarizes memory use by user using the 5.7 Performance Schema instrumentation.

The user columns shows either the background or foreground user name 
appropriately.

Structures
==========

mysql> desc memory_by_thread_by_current_bytes;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| thread_id          | bigint(20) unsigned | NO   |     | NULL    |       |
| user               | varchar(128)        | YES  |     | NULL    |       |
| current_count_used | decimal(41,0)       | YES  |     | NULL    |       |
| current_allocated  | text                | YES  |     | NULL    |       |
| current_avg_alloc  | text                | YES  |     | NULL    |       |
| current_max_alloc  | text                | YES  |     | NULL    |       |
| total_allocated    | text                | YES  |     | NULL    |       |
+--------------------+---------------------+------+-----+---------+-------+
7 rows in set (0.49 sec)

mysql> desc x$memory_by_thread_by_current_bytes;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| thread_id          | bigint(20) unsigned | NO   |     | NULL    |       |
| user               | varchar(128)        | YES  |     | NULL    |       |
| current_count_used | decimal(41,0)       | YES  |     | NULL    |       |
| current_allocated  | decimal(41,0)       | YES  |     | NULL    |       |
| current_avg_alloc  | decimal(45,4)       | NO   |     | 0.0000  |       |
| current_max_alloc  | bigint(20)          | YES  |     | NULL    |       |
| total_allocated    | decimal(42,0)       | YES  |     | NULL    |       |
+--------------------+---------------------+------+-----+---------+-------+
7 rows in set (0.25 sec)

Example
=======

mysql> select * from memory_by_thread_by_current_bytes limit 2\G
*************************** 1. row ***************************
         thread_id: 1
              user: sql/main
current_count_used: 29247
 current_allocated: 165.94 MiB
 current_avg_alloc: 5.81 KiB
 current_max_alloc: 131.06 MiB
   total_allocated: 196.03 MiB
*************************** 2. row ***************************
         thread_id: 24
              user: root@localhost
current_count_used: 216
 current_allocated: 1.18 MiB
 current_avg_alloc: 5.57 KiB
 current_max_alloc: 526.27 KiB
   total_allocated: 15.49 MiB


memory_by_user_by_current_bytes / x$memory_by_user_by_current_bytes
==================================================================================

Description
===========

Summarizes memory use by user using the 5.7 Performance Schema instrumentation.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc memory_by_user_by_current_bytes;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| user               | varchar(16)   | YES  |     | NULL    |       |
| current_count_used | decimal(41,0) | YES  |     | NULL    |       |
| current_allocated  | text          | YES  |     | NULL    |       |
| current_avg_alloc  | text          | YES  |     | NULL    |       |
| current_max_alloc  | text          | YES  |     | NULL    |       |
| total_allocated    | text          | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
6 rows in set (0.06 sec)

mysql> desc x$memory_by_user_by_current_bytes;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| user               | varchar(16)   | YES  |     | NULL    |       |
| current_count_used | decimal(41,0) | YES  |     | NULL    |       |
| current_allocated  | decimal(41,0) | YES  |     | NULL    |       |
| current_avg_alloc  | decimal(45,4) | NO   |     | 0.0000  |       |
| current_max_alloc  | bigint(20)    | YES  |     | NULL    |       |
| total_allocated    | decimal(42,0) | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
6 rows in set (0.12 sec)

Example
=======

mysql> select * from memory_by_user_by_current_bytes\G
*************************** 1. row ***************************
              user: background
current_count_used: 29247
 current_allocated: 165.94 MiB
 current_avg_alloc: 5.81 KiB
 current_max_alloc: 131.06 MiB
   total_allocated: 197.07 MiB
*************************** 2. row ***************************
              user: root
current_count_used: 1381
 current_allocated: 1.75 MiB
 current_avg_alloc: 1.30 KiB
 current_max_alloc: 574.17 KiB
   total_allocated: 37.06 MiB


memory_global_by_current_bytes / x$memory_global_by_current_bytes
==================================================================================

Description
===========

Shows the current memory usage within the server globally broken down by 
allocation type.

Structures
==========

mysql> desc memory_global_by_current_bytes;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| event_name        | varchar(128) | NO   |     | NULL    |       |
| current_count     | bigint(20)   | NO   |     | NULL    |       |
| current_alloc     | text         | YES  |     | NULL    |       |
| current_avg_alloc | text         | YES  |     | NULL    |       |
| high_count        | bigint(20)   | NO   |     | NULL    |       |
| high_alloc        | text         | YES  |     | NULL    |       |
| high_avg_alloc    | text         | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
7 rows in set (0.08 sec)

mysql> desc x$memory_global_by_current_bytes;
+-------------------+---------------+------+-----+---------+-------+
| Field             | Type          | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| event_name        | varchar(128)  | NO   |     | NULL    |       |
| current_count     | bigint(20)    | NO   |     | NULL    |       |
| current_alloc     | bigint(20)    | NO   |     | NULL    |       |
| current_avg_alloc | decimal(23,4) | NO   |     | 0.0000  |       |
| high_count        | bigint(20)    | NO   |     | NULL    |       |
| high_alloc        | bigint(20)    | NO   |     | NULL    |       |
| high_avg_alloc    | decimal(23,4) | NO   |     | 0.0000  |       |
+-------------------+---------------+------+-----+---------+-------+
7 rows in set (0.16 sec)

Example
=======

mysql> select * from memory_global_by_current_bytes limit 2\G
*************************** 1. row ***************************
       event_name: memory/performance_schema/internal_buffers
    current_count: 62
    current_alloc: 293.80 MiB
current_avg_alloc: 4.74 MiB
       high_count: 62
       high_alloc: 293.80 MiB
   high_avg_alloc: 4.74 MiB
*************************** 2. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 1
    current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
       high_count: 1
       high_alloc: 131.06 MiB
   high_avg_alloc: 131.06 MiB


memory_global_total / x$memory_global_total
==================================================================================

Description
===========

Shows the total memory usage within the server globally.

Structures
==========

mysql> desc memory_global_total;
+-----------------+------+------+-----+---------+-------+
| Field           | Type | Null | Key | Default | Extra |
+-----------------+------+------+-----+---------+-------+
| total_allocated | text | YES  |     | NULL    |       |
+-----------------+------+------+-----+---------+-------+
1 row in set (0.07 sec)

mysql> desc x$memory_global_total;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| total_allocated | decimal(41,0) | YES  |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Example
=======

mysql> select * from memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 458.44 MiB      |
+-----------------+


processlist / x$processlist
==================================================================================

Description
===========

A detailed non-blocking processlist view to replace [INFORMATION_SCHEMA. | SHOW 
FULL] PROCESSLIST.

Performs less locking than the legacy sources, whilst giving extra information.

Structures
==========

mysql> desc processlist;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| thd_id                 | bigint(20) unsigned | NO   |     | NULL    |       |
| conn_id                | bigint(20) unsigned | YES  |     | NULL    |       |
| user                   | varchar(128)        | YES  |     | NULL    |       |
| db                     | varchar(64)         | YES  |     | NULL    |       |
| command                | varchar(16)         | YES  |     | NULL    |       |
| state                  | varchar(64)         | YES  |     | NULL    |       |
| time                   | bigint(20)          | YES  |     | NULL    |       |
| current_statement      | longtext            | YES  |     | NULL    |       |
| lock_latency           | text                | YES  |     | NULL    |       |
| rows_examined          | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_sent              | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_affected          | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_tables             | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_disk_tables        | bigint(20) unsigned | YES  |     | NULL    |       |
| full_scan              | varchar(3)          | NO   |     |         |       |
| current_memory         | text                | YES  |     | NULL    |       |
| last_statement         | longtext            | YES  |     | NULL    |       |
| last_statement_latency | text                | YES  |     | NULL    |       |
| last_wait              | varchar(128)        | YES  |     | NULL    |       |
| last_wait_latency      | text                | YES  |     | NULL    |       |
| source                 | varchar(64)         | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> desc x$processlist;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| thd_id                 | bigint(20) unsigned | NO   |     | NULL    |       |
| conn_id                | bigint(20) unsigned | YES  |     | NULL    |       |
| user                   | varchar(128)        | YES  |     | NULL    |       |
| db                     | varchar(64)         | YES  |     | NULL    |       |
| command                | varchar(16)         | YES  |     | NULL    |       |
| state                  | varchar(64)         | YES  |     | NULL    |       |
| time                   | bigint(20)          | YES  |     | NULL    |       |
| current_statement      | longtext            | YES  |     | NULL    |       |
| lock_latency           | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_examined          | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_sent              | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_affected          | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_tables             | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_disk_tables        | bigint(20) unsigned | YES  |     | NULL    |       |
| full_scan              | varchar(3)          | NO   |     |         |       |
| current_memory         | decimal(41,0)       | YES  |     | NULL    |       |
| last_statement         | longtext            | YES  |     | NULL    |       |
| last_statement_latency | bigint(20) unsigned | YES  |     | NULL    |       |
| last_wait              | varchar(128)        | YES  |     | NULL    |       |
| last_wait_latency      | varchar(20)         | YES  |     | NULL    |       |
| source                 | varchar(64)         | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
21 rows in set (0.15 sec)

Example
=======

mysql> select * from processlist where conn_id is not null\G
*************************** 1. row ***************************
                thd_id: 31
               conn_id: 12
                  user: root@localhost
                    db: information_schema
               command: Query
                 state: Sending data
                  time: 0
     current_statement: select * from processlist limit 5
          lock_latency: 684.00 us
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 2
       tmp_disk_tables: 0
             full_scan: YES
        current_memory: 1.29 MiB
        last_statement: NULL
last_statement_latency: NULL
             last_wait: wait/synch/mutex/sql/THD::LOCK_query_plan
     last_wait_latency: 260.13 ns
                source: sql_optimizer.cc:1075


ps_check_lost_instrumentation
==================================================================================

Description
===========

Used to check whether Performance Schema is not able to monitor all runtime data - 
only returns variables that have lost instruments

Structure
=========

mysql> desc ps_check_lost_instrumentation;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| variable_name  | varchar(64)   | NO   |     |         |       |
| variable_value | varchar(1024) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

Example
=======

mysql> select * from ps_check_lost_instrumentation;
+----------------------------------------+----------------+
| variable_name                          | variable_value |
+----------------------------------------+----------------+
| Performance_schema_file_handles_lost   | 101223         |
| Performance_schema_file_instances_lost | 1231           |
+----------------------------------------+----------------+


schema_index_statistics / x$schema_index_statistics
==================================================================================

Description
===========

Statistics around indexes.

Ordered by the total wait time descending - top indexes are most contended.

Structures
==========

mysql> desc schema_index_statistics;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| table_schema   | varchar(64)         | YES  |     | NULL    |       |
| table_name     | varchar(64)         | YES  |     | NULL    |       |
| index_name     | varchar(64)         | YES  |     | NULL    |       |
| rows_selected  | bigint(20) unsigned | NO   |     | NULL    |       |
| select_latency | text                | YES  |     | NULL    |       |
| rows_inserted  | bigint(20) unsigned | NO   |     | NULL    |       |
| insert_latency | text                | YES  |     | NULL    |       |
| rows_updated   | bigint(20) unsigned | NO   |     | NULL    |       |
| update_latency | text                | YES  |     | NULL    |       |
| rows_deleted   | bigint(20) unsigned | NO   |     | NULL    |       |
| delete_latency | text                | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
11 rows in set (0.17 sec)

mysql> desc x$schema_index_statistics;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| table_schema   | varchar(64)         | YES  |     | NULL    |       |
| table_name     | varchar(64)         | YES  |     | NULL    |       |
| index_name     | varchar(64)         | YES  |     | NULL    |       |
| rows_selected  | bigint(20) unsigned | NO   |     | NULL    |       |
| select_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_inserted  | bigint(20) unsigned | NO   |     | NULL    |       |
| insert_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_updated   | bigint(20) unsigned | NO   |     | NULL    |       |
| update_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_deleted   | bigint(20) unsigned | NO   |     | NULL    |       |
| delete_latency | bigint(20) unsigned | NO   |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
11 rows in set (0.42 sec)

Example
=======

mysql> select * from schema_index_statistics\G
*************************** 1. row ***************************
  table_schema: downloads
    table_name: downloads
    index_name: description
 rows_selected: 59250
select_latency: 704.19 ms
 rows_inserted: 0
insert_latency: 0 ps
  rows_updated: 0
update_latency: 0 ps
  rows_deleted: 0
delete_latency: 0 ps
*************************** 2. row ***************************
  table_schema: downloads
    table_name: downloads
    index_name: timestamp
 rows_selected: 59250
select_latency: 260.08 ms
 rows_inserted: 0
insert_latency: 0 ps
  rows_updated: 0
update_latency: 0 ps
  rows_deleted: 0
delete_latency: 0 ps


schema_object_overview
==================================================================================

Description
===========

Shows an overview of the types of objects within each schema

Note: On instances with a large numbers of objects, this could take some time to 
execute, and may not be recommended.

Structure
=========

mysql> desc schema_object_overview;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| db          | varchar(64) | NO   |     |         |       |
| object_type | varchar(64) | NO   |     |         |       |
| count       | bigint(21)  | NO   |     | 0       |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec)

Example
=======

mysql> select * from schema_object_overview;
+--------------------+---------------+-------+
| db                 | object_type   | count |
+--------------------+---------------+-------+
| information_schema | SYSTEM VIEW   |    60 |
| mysql              | BASE TABLE    |    31 |
| mysql              | INDEX (BTREE) |    69 |
| performance_schema | BASE TABLE    |    76 |
| sys                | BASE TABLE    |     1 |
| sys                | FUNCTION      |    12 |
| sys                | INDEX (BTREE) |     1 |
| sys                | PROCEDURE     |    22 |
| sys                | TRIGGER       |     2 |
| sys                | VIEW          |    91 |
+--------------------+---------------+-------+
10 rows in set (1.58 sec)


schema_table_statistics / x$schema_table_statistics
==================================================================================

Description
===========

Statistics around tables.

Ordered by the total wait time descending - top tables are most contended.

Also includes the helper view (used by schema_table_statistics_with_buffer as 
well):

* x$ps_schema_table_statistics_io

Structures
==========

mysql> desc schema_table_statistics;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| table_schema      | varchar(64)         | YES  |     | NULL    |       |
| table_name        | varchar(64)         | YES  |     | NULL    |       |
| total_latency     | text                | YES  |     | NULL    |       |
| rows_fetched      | bigint(20) unsigned | NO   |     | NULL    |       |
| fetch_latency     | text                | YES  |     | NULL    |       |
| rows_inserted     | bigint(20) unsigned | NO   |     | NULL    |       |
| insert_latency    | text                | YES  |     | NULL    |       |
| rows_updated      | bigint(20) unsigned | NO   |     | NULL    |       |
| update_latency    | text                | YES  |     | NULL    |       |
| rows_deleted      | bigint(20) unsigned | NO   |     | NULL    |       |
| delete_latency    | text                | YES  |     | NULL    |       |
| io_read_requests  | decimal(42,0)       | YES  |     | NULL    |       |
| io_read           | text                | YES  |     | NULL    |       |
| io_read_latency   | text                | YES  |     | NULL    |       |
| io_write_requests | decimal(42,0)       | YES  |     | NULL    |       |
| io_write          | text                | YES  |     | NULL    |       |
| io_write_latency  | text                | YES  |     | NULL    |       |
| io_misc_requests  | decimal(42,0)       | YES  |     | NULL    |       |
| io_misc_latency   | text                | YES  |     | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.12 sec)

mysql> desc x$schema_table_statistics;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| table_schema      | varchar(64)         | YES  |     | NULL    |       |
| table_name        | varchar(64)         | YES  |     | NULL    |       |
| total_latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_fetched      | bigint(20) unsigned | NO   |     | NULL    |       |
| fetch_latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_inserted     | bigint(20) unsigned | NO   |     | NULL    |       |
| insert_latency    | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_updated      | bigint(20) unsigned | NO   |     | NULL    |       |
| update_latency    | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_deleted      | bigint(20) unsigned | NO   |     | NULL    |       |
| delete_latency    | bigint(20) unsigned | NO   |     | NULL    |       |
| io_read_requests  | decimal(42,0)       | YES  |     | NULL    |       |
| io_read           | decimal(41,0)       | YES  |     | NULL    |       |
| io_read_latency   | decimal(42,0)       | YES  |     | NULL    |       |
| io_write_requests | decimal(42,0)       | YES  |     | NULL    |       |
| io_write          | decimal(41,0)       | YES  |     | NULL    |       |
| io_write_latency  | decimal(42,0)       | YES  |     | NULL    |       |
| io_misc_requests  | decimal(42,0)       | YES  |     | NULL    |       |
| io_misc_latency   | decimal(42,0)       | YES  |     | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.13 sec)

mysql> desc x$ps_schema_table_statistics_io;
+---------------------------+---------------+------+-----+---------+-------+
| Field                     | Type          | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| table_schema              | varchar(64)   | YES  |     | NULL    |       |
| table_name                | varchar(64)   | YES  |     | NULL    |       |
| count_read                | decimal(42,0) | YES  |     | NULL    |       |
| sum_number_of_bytes_read  | decimal(41,0) | YES  |     | NULL    |       |
| sum_timer_read            | decimal(42,0) | YES  |     | NULL    |       |
| count_write               | decimal(42,0) | YES  |     | NULL    |       |
| sum_number_of_bytes_write | decimal(41,0) | YES  |     | NULL    |       |
| sum_timer_write           | decimal(42,0) | YES  |     | NULL    |       |
| count_misc                | decimal(42,0) | YES  |     | NULL    |       |
| sum_timer_misc            | decimal(42,0) | YES  |     | NULL    |       |
+---------------------------+---------------+------+-----+---------+-------+
10 rows in set (0.10 sec)

Example
=======

mysql> select * from schema_table_statistics\G
*************************** 1. row ***************************
     table_schema: sys
       table_name: sys_config
    total_latency: 0 ps
     rows_fetched: 0
    fetch_latency: 0 ps
    rows_inserted: 0
   insert_latency: 0 ps
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 8
          io_read: 2.28 KiB
  io_read_latency: 727.32 us
io_write_requests: 0
         io_write: 0 bytes
 io_write_latency: 0 ps
 io_misc_requests: 10
  io_misc_latency: 126.88 us


schema_table_statistics_with_buffer / x$schema_table_statistics_with_buffer
==================================================================================

Description
===========

Statistics around tables.

Ordered by the total wait time descending - top tables are most contended.

More statistics such as caching stats for the InnoDB buffer pool with InnoDB 
tables

Uses the x$ps_schema_table_statistics_io helper view from schema_table_statistics.

Structures
==========

mysql> desc schema_table_statistics_with_buffer;
+----------------------------+---------------------+------+-----+---------+-------
+
| Field                      | Type                | Null | Key | Default | Extra 
|
+----------------------------+---------------------+------+-----+---------+-------
+
| table_schema               | varchar(64)         | YES  |     | NULL    |       
|
| table_name                 | varchar(64)         | YES  |     | NULL    |       
|
| rows_fetched               | bigint(20) unsigned | NO   |     | NULL    |       
|
| fetch_latency              | text                | YES  |     | NULL    |       
|
| rows_inserted              | bigint(20) unsigned | NO   |     | NULL    |       
|
| insert_latency             | text                | YES  |     | NULL    |       
|
| rows_updated               | bigint(20) unsigned | NO   |     | NULL    |       
|
| update_latency             | text                | YES  |     | NULL    |       
|
| rows_deleted               | bigint(20) unsigned | NO   |     | NULL    |       
|
| delete_latency             | text                | YES  |     | NULL    |       
|
| io_read_requests           | decimal(42,0)       | YES  |     | NULL    |       
|
| io_read                    | text                | YES  |     | NULL    |       
|
| io_read_latency            | text                | YES  |     | NULL    |       
|
| io_write_requests          | decimal(42,0)       | YES  |     | NULL    |       
|
| io_write                   | text                | YES  |     | NULL    |       
|
| io_write_latency           | text                | YES  |     | NULL    |       
|
| io_misc_requests           | decimal(42,0)       | YES  |     | NULL    |       
|
| io_misc_latency            | text                | YES  |     | NULL    |       
|
| innodb_buffer_allocated    | text                | YES  |     | NULL    |       
|
| innodb_buffer_data         | text                | YES  |     | NULL    |       
|
| innodb_buffer_free         | text                | YES  |     | NULL    |       
|
| innodb_buffer_pages        | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_pages_hashed | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_pages_old    | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_rows_cached  | decimal(44,0)       | YES  |     | 0       |       
|
+----------------------------+---------------------+------+-----+---------+-------
+
25 rows in set (0.05 sec)

mysql> desc x$schema_table_statistics_with_buffer;
+----------------------------+---------------------+------+-----+---------+-------
+
| Field                      | Type                | Null | Key | Default | Extra 
|
+----------------------------+---------------------+------+-----+---------+-------
+
| table_schema               | varchar(64)         | YES  |     | NULL    |       
|
| table_name                 | varchar(64)         | YES  |     | NULL    |       
|
| rows_fetched               | bigint(20) unsigned | NO   |     | NULL    |       
|
| fetch_latency              | bigint(20) unsigned | NO   |     | NULL    |       
|
| rows_inserted              | bigint(20) unsigned | NO   |     | NULL    |       
|
| insert_latency             | bigint(20) unsigned | NO   |     | NULL    |       
|
| rows_updated               | bigint(20) unsigned | NO   |     | NULL    |       
|
| update_latency             | bigint(20) unsigned | NO   |     | NULL    |       
|
| rows_deleted               | bigint(20) unsigned | NO   |     | NULL    |       
|
| delete_latency             | bigint(20) unsigned | NO   |     | NULL    |       
|
| io_read_requests           | decimal(42,0)       | YES  |     | NULL    |       
|
| io_read                    | decimal(41,0)       | YES  |     | NULL    |       
|
| io_read_latency            | decimal(42,0)       | YES  |     | NULL    |       
|
| io_write_requests          | decimal(42,0)       | YES  |     | NULL    |       
|
| io_write                   | decimal(41,0)       | YES  |     | NULL    |       
|
| io_write_latency           | decimal(42,0)       | YES  |     | NULL    |       
|
| io_misc_requests           | decimal(42,0)       | YES  |     | NULL    |       
|
| io_misc_latency            | decimal(42,0)       | YES  |     | NULL    |       
|
| innodb_buffer_allocated    | decimal(43,0)       | YES  |     | NULL    |       
|
| innodb_buffer_data         | decimal(43,0)       | YES  |     | NULL    |       
|
| innodb_buffer_free         | decimal(44,0)       | YES  |     | NULL    |       
|
| innodb_buffer_pages        | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_pages_hashed | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_pages_old    | bigint(21)          | YES  |     | 0       |       
|
| innodb_buffer_rows_cached  | decimal(44,0)       | YES  |     | 0       |       
|
+----------------------------+---------------------+------+-----+---------+-------
+
25 rows in set (0.17 sec)

Example
=======

mysql> select * from schema_table_statistics_with_buffer limit 1\G
*************************** 1. row ***************************
                 table_schema: mem
                   table_name: mysqlserver
                 rows_fetched: 27087
                fetch_latency: 442.72 ms
                rows_inserted: 2
               insert_latency: 185.04 µs 
                 rows_updated: 5096
               update_latency: 1.39 s
                 rows_deleted: 0
               delete_latency: 0 ps
             io_read_requests: 2565
                io_read_bytes: 1121627
              io_read_latency: 10.07 ms
            io_write_requests: 1691
               io_write_bytes: 128383
             io_write_latency: 14.17 ms
             io_misc_requests: 2698
              io_misc_latency: 433.66 ms
          innodb_buffer_pages: 19
   innodb_buffer_pages_hashed: 19
      innodb_buffer_pages_old: 19
innodb_buffer_bytes_allocated: 311296
     innodb_buffer_bytes_data: 1924
    innodb_buffer_rows_cached: 2


schema_tables_with_full_table_scans / x$schema_tables_with_full_table_scans
==================================================================================

Description
===========

Finds tables that are being accessed by full table scans ordering by the number of 
rows scanned descending.

Structures
==========

mysql> desc schema_tables_with_full_table_scans;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| object_schema     | varchar(64)         | YES  |     | NULL    |       |
| object_name       | varchar(64)         | YES  |     | NULL    |       |
| rows_full_scanned | bigint(20) unsigned | NO   |     | NULL    |       |
| latency           | text                | YES  |     | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> desc x$schema_tables_with_full_table_scans;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| object_schema     | varchar(64)         | YES  |     | NULL    |       |
| object_name       | varchar(64)         | YES  |     | NULL    |       |
| rows_full_scanned | bigint(20) unsigned | NO   |     | NULL    |       |
| latency           | bigint(20) unsigned | NO   |     | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

Example
=======

mysql> select * from schema_tables_with_full_table_scans\G
*************************** 1. row ***************************
    object_schema: downloads
      object_name: downloads
rows_full_scanned: 118500
          latency: 381.66 ms
*************************** 2. row ***************************
    object_schema: downloads
      object_name: bogus_users
rows_full_scanned: 120
          latency: 4.31 ms


schema_unused_indexes
==================================================================================

Description
===========

Finds indexes that have had no events against them (and hence, no usage).

To trust whether the data from this view is representative of your workload, you 
should ensure that the server has been up for a representative amount of time 
before using it.

PRIMARY (key) indexes are ignored.

Structure
=========

mysql> desc schema_unused_indexes;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| object_schema | varchar(64) | YES  |     | NULL    |       |
| object_name   | varchar(64) | YES  |     | NULL    |       |
| index_name    | varchar(64) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.09 sec)

Example
=======

mysql> select * from schema_unused_indexes limit 5;
+--------------------+---------------------+--------------------+
| object_schema      | object_name         | index_name         |
+--------------------+---------------------+--------------------+
| mem30__bean_config | plists              | path               |
| mem30__config      | group_selections    | name               |
| mem30__config      | notification_groups | name               |
| mem30__config      | user_form_defaults  | FKC1AEF1F9E7EE2CFB |
| mem30__enterprise  | whats_new_entries   | entryId            |
+--------------------+---------------------+--------------------+


statement_analysis / x$statement_analysis
==================================================================================

Description
===========

Lists a normalized statement view with aggregated statistics, mimics the MySQL 
Enterprise Monitor Query Analysis view, ordered by the total execution time per 
normalized statement

Structures
==========

mysql> desc statement_analysis;
+-------------------+---------------------+------+-----+---------------------+----
---+
| Field             | Type                | Null | Key | Default             | 
Extra |
+-------------------+---------------------+------+-----+---------------------+----
---+
| query             | longtext            | YES  |     | NULL                |       
|
| db                | varchar(64)         | YES  |     | NULL                |       
|
| full_scan         | varchar(1)          | NO   |     |                     |       
|
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |       
|
| err_count         | bigint(20) unsigned | NO   |     | NULL                |       
|
| warn_count        | bigint(20) unsigned | NO   |     | NULL                |       
|
| total_latency     | text                | YES  |     | NULL                |       
|
| max_latency       | text                | YES  |     | NULL                |       
|
| avg_latency       | text                | YES  |     | NULL                |       
|
| lock_latency      | text                | YES  |     | NULL                |       
|
| rows_sent         | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_sent_avg     | decimal(21,0)       | NO   |     | 0                   |       
|
| rows_examined     | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_examined_avg | decimal(21,0)       | NO   |     | 0                   |       
|
| rows_affected     | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_affected_avg | decimal(21,0)       | NO   |     | 0                   |       
|
| tmp_tables        | bigint(20) unsigned | NO   |     | NULL                |       
|
| tmp_disk_tables   | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_sorted       | bigint(20) unsigned | NO   |     | NULL                |       
|
| sort_merge_passes | bigint(20) unsigned | NO   |     | NULL                |       
|
| digest            | varchar(32)         | YES  |     | NULL                |       
|
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |       
|
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |       
|
+-------------------+---------------------+------+-----+---------------------+----
---+
23 rows in set (0.26 sec)

mysql> desc x$statement_analysis;
+-------------------+---------------------+------+-----+---------------------+----
---+
| Field             | Type                | Null | Key | Default             | 
Extra |
+-------------------+---------------------+------+-----+---------------------+----
---+
| query             | longtext            | YES  |     | NULL                |       
|
| db                | varchar(64)         | YES  |     | NULL                |       
|
| full_scan         | varchar(1)          | NO   |     |                     |       
|
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |       
|
| err_count         | bigint(20) unsigned | NO   |     | NULL                |       
|
| warn_count        | bigint(20) unsigned | NO   |     | NULL                |       
|
| total_latency     | bigint(20) unsigned | NO   |     | NULL                |       
|
| max_latency       | bigint(20) unsigned | NO   |     | NULL                |       
|
| avg_latency       | bigint(20) unsigned | NO   |     | NULL                |       
|
| lock_latency      | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_sent         | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_sent_avg     | decimal(21,0)       | NO   |     | 0                   |       
|
| rows_examined     | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_examined_avg | decimal(21,0)       | NO   |     | 0                   |       
|
| rows_affected     | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_affected_avg | decimal(21,0)       | NO   |     | 0                   |       
|
| tmp_tables        | bigint(20) unsigned | NO   |     | NULL                |       
|
| tmp_disk_tables   | bigint(20) unsigned | NO   |     | NULL                |       
|
| rows_sorted       | bigint(20) unsigned | NO   |     | NULL                |       
|
| sort_merge_passes | bigint(20) unsigned | NO   |     | NULL                |       
|
| digest            | varchar(32)         | YES  |     | NULL                |       
|
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |       
|
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |       
|
+-------------------+---------------------+------+-----+---------------------+----
---+
23 rows in set (0.27 sec)

Example
=======

mysql> select * from statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT * FROM `schema_object_o ... MA` , `information_schema` .
               db: sys
        full_scan: *
       exec_count: 2
        err_count: 0
       warn_count: 0
    total_latency: 16.75 s
      max_latency: 16.57 s
      avg_latency: 8.38 s
     lock_latency: 16.69 s
        rows_sent: 84
    rows_sent_avg: 42
    rows_examined: 20012
rows_examined_avg: 10006
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 378
  tmp_disk_tables: 66
      rows_sorted: 168
sort_merge_passes: 0
           digest: 54f9bd520f0bbf15db0c2ed93386bec9
       first_seen: 2014-03-07 13:13:41
        last_seen: 2014-03-07 13:13:48


statements_with_errors_or_warnings / x$statements_with_errors_or_warnings
==================================================================================

Description
===========

Lists all normalized statements that have raised errors or warnings.

Structures
==========

mysql> desc statements_with_errors_or_warnings;
+-------------+---------------------+------+-----+---------------------+-------+
| Field       | Type                | Null | Key | Default             | Extra |
+-------------+---------------------+------+-----+---------------------+-------+
| query       | longtext            | YES  |     | NULL                |       |
| db          | varchar(64)         | YES  |     | NULL                |       |
| exec_count  | bigint(20) unsigned | NO   |     | NULL                |       |
| errors      | bigint(20) unsigned | NO   |     | NULL                |       |
| error_pct   | decimal(27,4)       | NO   |     | 0.0000              |       |
| warnings    | bigint(20) unsigned | NO   |     | NULL                |       |
| warning_pct | decimal(27,4)       | NO   |     | 0.0000              |       |
| first_seen  | timestamp           | NO   |     | 0000-00-00 00:00:00 |       |
| last_seen   | timestamp           | NO   |     | 0000-00-00 00:00:00 |       |
| digest      | varchar(32)         | YES  |     | NULL                |       |
+-------------+---------------------+------+-----+---------------------+-------+
10 rows in set (0.55 sec)

mysql> desc x$statements_with_errors_or_warnings;
+-------------+---------------------+------+-----+---------------------+-------+
| Field       | Type                | Null | Key | Default             | Extra |
+-------------+---------------------+------+-----+---------------------+-------+
| query       | longtext            | YES  |     | NULL                |       |
| db          | varchar(64)         | YES  |     | NULL                |       |
| exec_count  | bigint(20) unsigned | NO   |     | NULL                |       |
| errors      | bigint(20) unsigned | NO   |     | NULL                |       |
| error_pct   | decimal(27,4)       | NO   |     | 0.0000              |       |
| warnings    | bigint(20) unsigned | NO   |     | NULL                |       |
| warning_pct | decimal(27,4)       | NO   |     | 0.0000              |       |
| first_seen  | timestamp           | NO   |     | 0000-00-00 00:00:00 |       |
| last_seen   | timestamp           | NO   |     | 0000-00-00 00:00:00 |       |
| digest      | varchar(32)         | YES  |     | NULL                |       |
+-------------+---------------------+------+-----+---------------------+-------+
10 rows in set (0.25 sec)

Example
=======

mysql> select * from statements_with_errors_or_warnings LIMIT 1\G
*************************** 1. row ***************************
      query: CREATE OR REPLACE ALGORITHM =  ... _delete` AS `rows_deleted` ...
         db: sys
 exec_count: 2
     errors: 1
  error_pct: 50.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2014-03-07 12:56:54
  last_seen: 2014-03-07 13:01:01
     digest: 943a788859e623d5f7798ba0ae0fd8a9


statements_with_full_table_scans / x$statements_with_full_table_scans
==================================================================================

Description
===========

Lists all normalized statements that use have done a full table scan ordered by 
number the percentage of times a full scan was done, then by the statement 
latency.

This view ignores SHOW statements, as these always cause a full table scan, and 
there is nothing that can be done about this.

Structures
==========

mysql> desc statements_with_full_table_scans;
+--------------------------+------------------------+------+..+-----------------..
| Field                    | Type                   | Null |..| Default         ..
+--------------------------+------------------------+------+..+-----------------..
| query                    | longtext               | YES  |..| NULL            ..
| db                       | varchar(64)            | YES  |..| NULL            ..
| exec_count               | bigint(20) unsigned    | NO   |..| NULL            ..
| total_latency            | text                   | YES  |..| NULL            ..
| no_index_used_count      | bigint(20) unsigned    | NO   |..| NULL            ..
| no_good_index_used_count | bigint(20) unsigned    | NO   |..| NULL            ..
| no_index_used_pct        | decimal(24,0)          | NO   |..| 0               ..
| rows_sent                | bigint(20) unsigned    | NO   |..| NULL            ..
| rows_examined            | bigint(20) unsigned    | NO   |..| NULL            ..
| rows_sent_avg            | decimal(21,0) unsigned | YES  |..| NULL            ..
| rows_examined_avg        | decimal(21,0) unsigned | YES  |..| NULL            ..
| first_seen               | timestamp              | NO   |..| 0000-00-00 00:00..
| last_seen                | timestamp              | NO   |..| 0000-00-00 00:00..
| digest                   | varchar(32)            | YES  |..| NULL            ..
+--------------------------+------------------------+------+..+-----------------..
14 rows in set (0.01 sec)

mysql> desc x$statements_with_full_table_scans;
==================================================================================
+--------------------------+------------------------+------+..+-----------------..
| Field                    | Type                   | Null |..| Default         ..
+--------------------------+------------------------+------+..+-----------------..
| query                    | longtext               | YES  |..| NULL            ..
| db                       | varchar(64)            | YES  |..| NULL            ..
| exec_count               | bigint(20) unsigned    | NO   |..| NULL            ..
| total_latency            | bigint(20) unsigned    | NO   |..| NULL            ..
| no_index_used_count      | bigint(20) unsigned    | NO   |..| NULL            ..
| no_good_index_used_count | bigint(20) unsigned    | NO   |..| NULL            ..
| no_index_used_pct        | decimal(24,0)          | NO   |..| 0               ..
| rows_sent                | bigint(20) unsigned    | NO   |..| NULL            ..
| rows_examined            | bigint(20) unsigned    | NO   |..| NULL            ..
| rows_sent_avg            | decimal(21,0) unsigned | YES  |..| NULL            ..
| rows_examined_avg        | decimal(21,0) unsigned | YES  |..| NULL            ..
| first_seen               | timestamp              | NO   |..| 0000-00-00 00:00..
| last_seen                | timestamp              | NO   |..| 0000-00-00 00:00..
| digest                   | varchar(32)            | YES  |..| NULL            ..
+--------------------------+------------------------+------+..+-----------------..
14 rows in set (0.00 sec)

Example
=======

mysql> select * from statements_with_full_table_scans limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `schema_tables_w ... ex_usage` . `COUNT_..
                      db: sys
              exec_count: 1
           total_latency: 88.20 ms
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 0
           rows_examined: 1501
           rows_sent_avg: 0
       rows_examined_avg: 1501
              first_seen: 2014-03-07 13:58:20
               last_seen: 2014-03-07 13:58:20
                  digest: 64baecd5c1e1e1651a6b92e55442a288


statements_with_runtimes_in_95th_percentile / 
x$statements_with_runtimes_in_95th_percentile
==================================================================================

Description
===========

Lists all statements who's average runtime, in microseconds, is in the top 95th 
percentile.

Also includes two helper views:

* x$ps_digest_avg_latency_distribution
* x$ps_digest_95th_percentile_by_avg_us

Structures
==========

mysql> desc statements_with_runtimes_in_95th_percentile;
+-------------------+---------------------+------+-----+---------------------+..
| Field             | Type                | Null | Key | Default             |..
+-------------------+---------------------+------+-----+---------------------+..
| query             | longtext            | YES  |     | NULL                |..
| db                | varchar(64)         | YES  |     | NULL                |..
| full_scan         | varchar(1)          | NO   |     |                     |..
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |..
| err_count         | bigint(20) unsigned | NO   |     | NULL                |..
| warn_count        | bigint(20) unsigned | NO   |     | NULL                |..
| total_latency     | text                | YES  |     | NULL                |..
| max_latency       | text                | YES  |     | NULL                |..
| avg_latency       | text                | YES  |     | NULL                |..
| rows_sent         | bigint(20) unsigned | NO   |     | NULL                |..
| rows_sent_avg     | decimal(21,0)       | NO   |     | 0                   |..
| rows_examined     | bigint(20) unsigned | NO   |     | NULL                |..
| rows_examined_avg | decimal(21,0)       | NO   |     | 0                   |..
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| digest            | varchar(32)         | YES  |     | NULL                |..
+-------------------+---------------------+------+-----+---------------------+..
16 rows in set (0.00 sec)

mysql> desc x$statements_with_runtimes_in_95th_percentile;
+-------------------+---------------------+------+-----+---------------------+..
| Field             | Type                | Null | Key | Default             |..
+-------------------+---------------------+------+-----+---------------------+..
| query             | longtext            | YES  |     | NULL                |..
| db                | varchar(64)         | YES  |     | NULL                |..
| full_scan         | varchar(1)          | NO   |     |                     |..
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |..
| err_count         | bigint(20) unsigned | NO   |     | NULL                |..
| warn_count        | bigint(20) unsigned | NO   |     | NULL                |..
| total_latency     | bigint(20) unsigned | NO   |     | NULL                |..
| max_latency       | bigint(20) unsigned | NO   |     | NULL                |..
| avg_latency       | bigint(20) unsigned | NO   |     | NULL                |..
| rows_sent         | bigint(20) unsigned | NO   |     | NULL                |..
| rows_sent_avg     | decimal(21,0)       | NO   |     | 0                   |..
| rows_examined     | bigint(20) unsigned | NO   |     | NULL                |..
| rows_examined_avg | decimal(21,0)       | NO   |     | 0                   |..
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| digest            | varchar(32)         | YES  |     | NULL                |..
+-------------------+---------------------+------+-----+---------------------+..
16 rows in set (0.00 sec)

mysql> desc x$ps_digest_avg_latency_distribution;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| cnt    | bigint(21)    | NO   |     | 0       |       |
| avg_us | decimal(21,0) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc x$ps_digest_95th_percentile_by_avg_us;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| avg_us     | decimal(21,0) | YES  |     | NULL    |       |
| percentile | decimal(46,4) | NO   |     | 0.0000  |       |
+------------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Example
=======

mysql> select * from statements_with_runtimes_in_95th_percentile\G
*************************** 1. row ***************************
            query: SELECT * FROM `schema_object_o ... MA` , `information_schema`..
               db: sys
        full_scan: *
       exec_count: 2
        err_count: 0
       warn_count: 0
    total_latency: 16.75 s
      max_latency: 16.57 s
      avg_latency: 8.38 s
        rows_sent: 84
    rows_sent_avg: 42
    rows_examined: 20012
rows_examined_avg: 10006
       first_seen: 2014-03-07 13:13:41
        last_seen: 2014-03-07 13:13:48
           digest: 54f9bd520f0bbf15db0c2ed93386bec9


statements_with_sorting / x$statements_with_sorting
==================================================================================

Description
===========

Lists all normalized statements that have done sorts, ordered by total_latency 
descending.

Structures
==========

mysql> desc statements_with_sorting;
+-------------------+---------------------+------+-----+---------------------+..
| Field             | Type                | Null | Key | Default             |..
+-------------------+---------------------+------+-----+---------------------+..
| query             | longtext            | YES  |     | NULL                |..
| db                | varchar(64)         | YES  |     | NULL                |..
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |..
| total_latency     | text                | YES  |     | NULL                |..
| sort_merge_passes | bigint(20) unsigned | NO   |     | NULL                |..
| avg_sort_merges   | decimal(21,0)       | NO   |     | 0                   |..
| sorts_using_scans | bigint(20) unsigned | NO   |     | NULL                |..
| sort_using_range  | bigint(20) unsigned | NO   |     | NULL                |..
| rows_sorted       | bigint(20) unsigned | NO   |     | NULL                |..
| avg_rows_sorted   | decimal(21,0)       | NO   |     | 0                   |..
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| digest            | varchar(32)         | YES  |     | NULL                |..
+-------------------+---------------------+------+-----+---------------------+..
13 rows in set (0.12 sec)

mysql> desc x$statements_with_sorting;
+-------------------+---------------------+------+-----+---------------------+..
| Field             | Type                | Null | Key | Default             |..
+-------------------+---------------------+------+-----+---------------------+..
| query             | longtext            | YES  |     | NULL                |..
| db                | varchar(64)         | YES  |     | NULL                |..
| exec_count        | bigint(20) unsigned | NO   |     | NULL                |..
| total_latency     | bigint(20) unsigned | NO   |     | NULL                |..
| sort_merge_passes | bigint(20) unsigned | NO   |     | NULL                |..
| avg_sort_merges   | decimal(21,0)       | NO   |     | 0                   |..
| sorts_using_scans | bigint(20) unsigned | NO   |     | NULL                |..
| sort_using_range  | bigint(20) unsigned | NO   |     | NULL                |..
| rows_sorted       | bigint(20) unsigned | NO   |     | NULL                |..
| avg_rows_sorted   | decimal(21,0)       | NO   |     | 0                   |..
| first_seen        | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| last_seen         | timestamp           | NO   |     | 0000-00-00 00:00:00 |..
| digest            | varchar(32)         | YES  |     | NULL                |..
+-------------------+---------------------+------+-----+---------------------+..
13 rows in set (0.00 sec)

Example
=======

mysql> select * from statements_with_sorting limit 1\G
*************************** 1. row ***************************
            query: SELECT * FROM `schema_object_o ... MA` , `information_schema`..
               db: sys
       exec_count: 2
    total_latency: 16.75 s
sort_merge_passes: 0
  avg_sort_merges: 0
sorts_using_scans: 12
 sort_using_range: 0
      rows_sorted: 168
  avg_rows_sorted: 84
       first_seen: 2014-03-07 13:13:41
        last_seen: 2014-03-07 13:13:48
           digest: 54f9bd520f0bbf15db0c2ed93386bec9


statements_with_temp_tables / x$statements_with_temp_tables
==================================================================================

Description
===========

Lists all normalized statements that use temporary tables ordered by number of on 
disk temporary tables descending first, then by the number of memory tables.

Structures
==========

mysql> desc statements_with_temp_tables;
+--------------------------+---------------------+------+..+--------------------..
| Field                    | Type                | Null |..| Default            ..
+--------------------------+---------------------+------+..+--------------------..
| query                    | longtext            | YES  |..| NULL               ..
| db                       | varchar(64)         | YES  |..| NULL               ..
| exec_count               | bigint(20) unsigned | NO   |..| NULL               ..
| total_latency            | text                | YES  |..| NULL               ..
| memory_tmp_tables        | bigint(20) unsigned | NO   |..| NULL               ..
| disk_tmp_tables          | bigint(20) unsigned | NO   |..| NULL               ..
| avg_tmp_tables_per_query | decimal(21,0)       | NO   |..| 0                  ..
| tmp_tables_to_disk_pct   | decimal(24,0)       | NO   |..| 0                  ..
| first_seen               | timestamp           | NO   |..| 0000-00-00 00:00:00..
| last_seen                | timestamp           | NO   |..| 0000-00-00 00:00:00..
| digest                   | varchar(32)         | YES  |..| NULL               ..
+--------------------------+---------------------+------+..+--------------------..
11 rows in set (0.04 sec)

mysql> desc x$statements_with_temp_tables;
+--------------------------+---------------------+------+..+--------------------..
| Field                    | Type                | Null |..| Default            ..
+--------------------------+---------------------+------+..+--------------------..
| query                    | longtext            | YES  |..| NULL               ..
| db                       | varchar(64)         | YES  |..| NULL               ..
| exec_count               | bigint(20) unsigned | NO   |..| NULL               ..
| total_latency            | bigint(20) unsigned | NO   |..| NULL               ..
| memory_tmp_tables        | bigint(20) unsigned | NO   |..| NULL               ..
| disk_tmp_tables          | bigint(20) unsigned | NO   |..| NULL               ..
| avg_tmp_tables_per_query | decimal(21,0)       | NO   |..| 0                  ..
| tmp_tables_to_disk_pct   | decimal(24,0)       | NO   |..| 0                  ..
| first_seen               | timestamp           | NO   |..| 0000-00-00 00:00:00..
| last_seen                | timestamp           | NO   |..| 0000-00-00 00:00:00..
| digest                   | varchar(32)         | YES  |..| NULL               ..
+--------------------------+---------------------+------+..+--------------------..
11 rows in set (0.00 sec)

Example
=======

mysql> select * from statements_with_temp_tables limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `schema_object_o ... MA` , `information_..
                      db: sys
              exec_count: 2
           total_latency: 16.75 s
       memory_tmp_tables: 378
         disk_tmp_tables: 66
avg_tmp_tables_per_query: 189
  tmp_tables_to_disk_pct: 17
              first_seen: 2014-03-07 13:13:41
               last_seen: 2014-03-07 13:13:48
                  digest: 54f9bd520f0bbf15db0c2ed93386bec9


user_summary / x$user_summary
==================================================================================

Description
===========

Summarizes statement activity, file IO and connections by user.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| user                   | varchar(16)   | YES  |     | NULL    |       |
| statements             | decimal(64,0) | YES  |     | NULL    |       |
| statement_latency      | text          | YES  |     | NULL    |       |
| statement_avg_latency  | text          | YES  |     | NULL    |       |
| table_scans            | decimal(65,0) | YES  |     | NULL    |       |
| file_ios               | decimal(64,0) | YES  |     | NULL    |       |
| file_io_latency        | text          | YES  |     | NULL    |       |
| current_connections    | decimal(41,0) | YES  |     | NULL    |       |
| total_connections      | decimal(41,0) | YES  |     | NULL    |       |
| unique_hosts           | bigint(21)    | NO   |     | 0       |       |
| current_memory         | text          | YES  |     | NULL    |       |
| total_memory_allocated | text          | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

mysql> desc x$user_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| user                   | varchar(16)   | YES  |     | NULL    |       |
| statements             | decimal(64,0) | YES  |     | NULL    |       |
| statement_latency      | decimal(64,0) | YES  |     | NULL    |       |
| statement_avg_latency  | decimal(65,4) | NO   |     | 0.0000  |       |
| table_scans            | decimal(65,0) | YES  |     | NULL    |       |
| file_ios               | decimal(64,0) | YES  |     | NULL    |       |
| file_io_latency        | decimal(64,0) | YES  |     | NULL    |       |
| current_connections    | decimal(41,0) | YES  |     | NULL    |       |
| total_connections      | decimal(41,0) | YES  |     | NULL    |       |
| unique_hosts           | bigint(21)    | NO   |     | 0       |       |
| current_memory         | decimal(63,0) | YES  |     | NULL    |       |
| total_memory_allocated | decimal(64,0) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

Example
=======

mysql> select * from user_summary\G
*************************** 1. row ***************************
                  user: root
            statements: 4981
     statement_latency: 26.54 s
 statement_avg_latency: 5.33 ms
           table_scans: 74
              file_ios: 7792
       file_io_latency: 40.08 s
   current_connections: 1
     total_connections: 2
          unique_hosts: 1
        current_memory: 3.57 MiB
total_memory_allocated: 83.37 MiB
*************************** 2. row ***************************
                  user: background
            statements: 0
     statement_latency: 0 ps
 statement_avg_latency: 0 ps
           table_scans: 0
              file_ios: 1618
       file_io_latency: 4.78 s
   current_connections: 21
     total_connections: 23
          unique_hosts: 0
        current_memory: 165.94 MiB
total_memory_allocated: 197.29 MiB


user_summary_by_file_io / x$user_summary_by_file_io
==================================================================================

Description
===========

Summarizes file IO totals per user.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary_by_file_io;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| user       | varchar(16)   | YES  |     | NULL    |       |
| ios        | decimal(42,0) | YES  |     | NULL    |       |
| io_latency | text          | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.20 sec)

mysql> desc x$user_summary_by_file_io;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| user       | varchar(16)   | YES  |     | NULL    |       |
| ios        | decimal(42,0) | YES  |     | NULL    |       |
| io_latency | decimal(42,0) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

Example
=======

mysql> select * from user_summary_by_file_io;
+------------+-------+------------+
| user       | ios   | io_latency |
+------------+-------+------------+
| root       | 26457 | 21.58 s    |
| background |  1189 | 394.21 ms  |
+------------+-------+------------+


user_summary_by_file_io_type / x$user_summary_by_file_io_type
==================================================================================

Description
===========

Summarizes file IO by event type per user.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary_by_file_io_type;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| user        | varchar(16)         | YES  |     | NULL    |       |
| event_name  | varchar(128)        | NO   |     | NULL    |       |
| total       | bigint(20) unsigned | NO   |     | NULL    |       |
| latency     | text                | YES  |     | NULL    |       |
| max_latency | text                | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> desc x$user_summary_by_file_io_type;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| user        | varchar(16)         | YES  |     | NULL    |       |
| event_name  | varchar(128)        | NO   |     | NULL    |       |
| total       | bigint(20) unsigned | NO   |     | NULL    |       |
| latency     | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency | bigint(20) unsigned | NO   |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Example
=======

mysql> select * from user_summary_by_file_io_type limit 3\G
*************************** 1. row ***************************
       user: background
 event_name: wait/io/file/innodb/innodb_data_file
      total: 299
    latency: 4.10 s
max_latency: 2.31 s
*************************** 2. row ***************************
       user: background
 event_name: wait/io/file/sql/FRM
      total: 1209
    latency: 381.23 ms
max_latency: 41.63 ms
*************************** 3. row ***************************
       user: background
 event_name: wait/io/file/innodb/innodb_log_file
      total: 22
    latency: 182.82 ms
max_latency: 98.37 ms


user_summary_by_stages / x$user_summary_by_stages
==================================================================================

Description
===========

Summarizes stages by user, ordered by user and total latency per stage.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary_by_stages;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc x$user_summary_by_stages;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| event_name    | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

Example
=======

mysql> select * from user_summary_by_stages;
+------+--------------------------------+-------+---------------+-------------+
| user | event_name                     | total | total_latency | avg_latency |
+------+--------------------------------+-------+---------------+-------------+
| root | stage/sql/Opening tables       |   889 | 1.97 ms       | 2.22 us     |
| root | stage/sql/Creating sort index  |     4 | 1.79 ms       | 446.30 us   |
| root | stage/sql/init                 |    10 | 312.27 us     | 31.23 us    |
| root | stage/sql/checking permissions |    10 | 300.62 us     | 30.06 us    |
| root | stage/sql/freeing items        |     5 | 85.89 us      | 17.18 us    |
| root | stage/sql/statistics           |     5 | 79.15 us      | 15.83 us    |
| root | stage/sql/preparing            |     5 | 69.12 us      | 13.82 us    |
| root | stage/sql/optimizing           |     5 | 53.11 us      | 10.62 us    |
| root | stage/sql/Sending data         |     5 | 44.66 us      | 8.93 us     |
| root | stage/sql/closing tables       |     5 | 37.54 us      | 7.51 us     |
| root | stage/sql/System lock          |     5 | 34.28 us      | 6.86 us     |
| root | stage/sql/query end            |     5 | 24.37 us      | 4.87 us     |
| root | stage/sql/end                  |     5 | 8.60 us       | 1.72 us     |
| root | stage/sql/Sorting result       |     5 | 8.33 us       | 1.67 us     |
| root | stage/sql/executing            |     5 | 5.37 us       | 1.07 us     |
| root | stage/sql/cleaning up          |     5 | 4.60 us       | 919.00 ns   |
+------+--------------------------------+-------+---------------+-------------+


user_summary_by_statement_latency / x$user_summary_by_statement_latency
==================================================================================

Description
===========

Summarizes overall statement statistics by user.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary_by_statement_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| user          | varchar(16)   | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | text          | YES  |     | NULL    |       |
| max_latency   | text          | YES  |     | NULL    |       |
| lock_latency  | text          | YES  |     | NULL    |       |
| rows_sent     | decimal(42,0) | YES  |     | NULL    |       |
| rows_examined | decimal(42,0) | YES  |     | NULL    |       |
| rows_affected | decimal(42,0) | YES  |     | NULL    |       |
| full_scans    | decimal(43,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> desc x$user_summary_by_statement_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| user          | varchar(16)   | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | decimal(42,0) | YES  |     | NULL    |       |
| max_latency   | decimal(42,0) | YES  |     | NULL    |       |
| lock_latency  | decimal(42,0) | YES  |     | NULL    |       |
| rows_sent     | decimal(42,0) | YES  |     | NULL    |       |
| rows_examined | decimal(42,0) | YES  |     | NULL    |       |
| rows_affected | decimal(42,0) | YES  |     | NULL    |       |
| full_scans    | decimal(43,0) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.28 sec)

Example
=======

mysql> select * from user_summary_by_statement_latency limit 1\G
*************************** 1. row ***************************
         user: root
        total: 5171
total_latency: 26.74 s
  max_latency: 17.55 s
 lock_latency: 13.56 s
    rows_sent: 1338
rows_examined: 82152
rows_affected: 657
   full_scans: 86


user_summary_by_statement_type / x$user_summary_by_statement_type
==================================================================================

Description
===========

Summarizes the types of statements executed by each user.

When the user found is NULL, it is assumed to be a "background" thread.

Structures
==========

mysql> desc user_summary_by_statement_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| statement     | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
| lock_latency  | text                | YES  |     | NULL    |       |
| rows_sent     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_examined | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_affected | bigint(20) unsigned | NO   |     | NULL    |       |
| full_scans    | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
10 rows in set (0.21 sec)

mysql> desc x$user_summary_by_statement_type;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| statement     | varchar(128)        | YES  |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| lock_latency  | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_sent     | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_examined | bigint(20) unsigned | NO   |     | NULL    |       |
| rows_affected | bigint(20) unsigned | NO   |     | NULL    |       |
| full_scans    | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
10 rows in set (0.37 sec)

Example
=======

mysql> select * from user_summary_by_statement_type limit 2\G
*************************** 1. row ***************************
         user: root
    statement: show_fields
        total: 50
total_latency: 18.69 s
  max_latency: 14.08 s
 lock_latency: 10.76 s
    rows_sent: 442
rows_examined: 442
rows_affected: 0
   full_scans: 50
*************************** 2. row ***************************
         user: root
    statement: select
        total: 36
total_latency: 3.66 s
  max_latency: 1.58 s
 lock_latency: 1.86 s
    rows_sent: 82
rows_examined: 80740
rows_affected: 0
   full_scans: 29


wait_classes_global_by_avg_latency / x$wait_classes_global_by_avg_latency
==================================================================================

Description
===========

Lists the top wait classes by average latency, ignoring idle (this may be very 
large).

Structures
==========

mysql> desc wait_classes_global_by_avg_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| event_class   | varchar(128)  | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | text          | YES  |     | NULL    |       |
| min_latency   | text          | YES  |     | NULL    |       |
| avg_latency   | text          | YES  |     | NULL    |       |
| max_latency   | text          | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
6 rows in set (0.11 sec)

mysql> desc x$wait_classes_global_by_avg_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| event_class   | varchar(128)        | YES  |     | NULL    |       |
| total         | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency | decimal(42,0)       | YES  |     | NULL    |       |
| min_latency   | bigint(20) unsigned | YES  |     | NULL    |       |
| avg_latency   | decimal(46,4)       | NO   |     | 0.0000  |       |
| max_latency   | bigint(20) unsigned | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

Example
=======

mysql> select * from wait_classes_global_by_avg_latency limit 3\G
*************************** 1. row ***************************
  event_class: wait/io/table
        total: 3
total_latency: 47.62 ms
  min_latency: 32.81 us
  avg_latency: 15.87 ms
  max_latency: 26.56 ms
*************************** 2. row ***************************
  event_class: wait/io/file
        total: 9459
total_latency: 8.00 s
  min_latency: 0 ps
  avg_latency: 845.33 us
  max_latency: 2.31 s
*************************** 3. row ***************************
  event_class: wait/lock/table
        total: 10
total_latency: 52.24 us
  min_latency: 2.20 us
  avg_latency: 5.22 us
  max_latency: 21.76 us


wait_classes_global_by_latency / x$wait_classes_global_by_latency
==================================================================================

Description
===========

Lists the top wait classes by total latency, ignoring idle (this may be very 
large).

Structures
==========

mysql> desc wait_classes_global_by_latency;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| event_class   | varchar(128)  | YES  |     | NULL    |       |
| total         | decimal(42,0) | YES  |     | NULL    |       |
| total_latency | text          | YES  |     | NULL    |       |
| min_latency   | text          | YES  |     | NULL    |       |
| avg_latency   | text          | YES  |     | NULL    |       |
| max_latency   | text          | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc x$wait_classes_global_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| event_class   | varchar(128)        | YES  |     | NULL    |       |
| total         | decimal(42,0)       | YES  |     | NULL    |       |
| total_latency | decimal(42,0)       | YES  |     | NULL    |       |
| min_latency   | bigint(20) unsigned | YES  |     | NULL    |       |
| avg_latency   | decimal(46,4)       | NO   |     | 0.0000  |       |
| max_latency   | bigint(20) unsigned | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

Example
=======

mysql> select * from wait_classes_global_by_latency limit 3\G
*************************** 1. row ***************************
  event_class: wait/io/file
        total: 9557
total_latency: 8.00 s
  min_latency: 0 ps
  avg_latency: 837.15 us
  max_latency: 2.31 s
*************************** 2. row ***************************
  event_class: wait/io/table
        total: 3
total_latency: 47.62 ms
  min_latency: 32.81 us
  avg_latency: 15.87 ms
  max_latency: 26.56 ms
*************************** 3. row ***************************
  event_class: wait/synch/mutex
        total: 300
total_latency: 57.56 us
  min_latency: 79.17 ns
  avg_latency: 191.87 ns
  max_latency: 637.13 ns


waits_by_user_by_latency / x$waits_by_user_by_latency
==================================================================================

Description
===========

Lists the top wait events per user by their total latency, ignoring idle (this may 
be very large) per user.

Structures
==========

mysql> desc waits_by_user_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| event         | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc x$waits_by_user_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(16)         | YES  |     | NULL    |       |
| event         | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.30 sec)

Example
=======

mysql> select * from waits_by_user_by_latency limit 3\G
*************************** 1. row ***************************
         user: root
        event: wait/io/file/myisam/dfile
        total: 2353
total_latency: 1.44 s
  avg_latency: 610.59 us
  max_latency: 971.72 ms
*************************** 2. row ***************************
         user: root
        event: wait/io/file/myisam/kfile
        total: 4746
total_latency: 1.38 s
  avg_latency: 289.80 us
  max_latency: 163.77 ms
*************************** 3. row ***************************
         user: root
        event: wait/io/file/sql/FRM
        total: 651
total_latency: 282.14 ms
  avg_latency: 433.40 us
  max_latency: 40.34 ms


waits_by_host_by_latency / x$waits_by_host_by_latency
==================================================================================

Description
===========

Lists the top wait events per host by their total latency, ignoring idle (this may 
be very large) per host.

Structures
==========

mysql> desc waits_by_host_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event         | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.36 sec)

mysql> desc x$waits_by_host_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| host          | varchar(60)         | YES  |     | NULL    |       |
| event         | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.25 sec)

Example
=======

mysql> select * from waits_by_host_by_latency limit 3\G
*************************** 1. row ***************************
         host: background
        event: wait/io/file/innodb/innodb_data_file
        total: 299
total_latency: 4.10 s
  avg_latency: 13.72 ms
  max_latency: 2.31 s
*************************** 2. row ***************************
         host: background
        event: wait/io/file/sql/FRM
        total: 1209
total_latency: 381.23 ms
  avg_latency: 315.33 us
  max_latency: 41.63 ms
*************************** 3. row ***************************
         host: background
        event: wait/io/file/innodb/innodb_log_file
        total: 22
total_latency: 182.82 ms
  avg_latency: 8.31 ms
  max_latency: 98.37 ms


waits_global_by_latency / x$waits_global_by_latency
==================================================================================

Description
===========

Lists the top wait events by their total latency, ignoring idle (this may be very 
large).

Structures
==========

mysql> desc waits_global_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| events        | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc x$waits_global_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| events        | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

Example
=======

mysql> select * from waits_global_by_latency limit 3\G
*************************** 1. row ***************************
       events: wait/io/file/innodb/innodb_data_file
        total: 371
total_latency: 4.16 s
  avg_latency: 11.21 ms
  max_latency: 2.31 s
*************************** 2. row ***************************
       events: wait/io/file/myisam/dfile
        total: 2439
total_latency: 1.46 s
  avg_latency: 599.19 us
  max_latency: 971.72 ms
*************************** 3. row ***************************
       events: wait/io/file/myisam/kfile
        total: 4924
total_latency: 1.39 s
  avg_latency: 281.80 us
  max_latency: 163.77 ms


==================================================================================
NEW FUNCTIONS
==================================================================================

extract_schema_from_file_name
==================================================================================

Description
===========

Takes a raw file path, and attempts to extract the schema name from it.

Useful for when interacting with Performance Schema data concerning IO statistics, 
for example.

Currently relies on the fact that a table data file will be within a specified 
database directory (will not work with partitions or tables that specify an 
individual DATA_DIRECTORY).

Parameters
==========

* path (VARCHAR(512)): The full file path to a data file to extract the schema 
name from.

Returns
=======

VARCHAR(64)

Example
=======

mysql> SELECT 
sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd');
+----------------------------------------------------------------------------+
| sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd') |
+----------------------------------------------------------------------------+
| employees                                                                  |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)


extract_table_from_file_name
==================================================================================

Description
===========

Takes a raw file path, and extracts the table name from it.

Useful for when interacting with Performance Schema data concerning IO statistics, 
for example.

Parameters
==========

* path (VARCHAR(512)): The full file path to a data file to extract the table name 
from.

Returns
=======

VARCHAR(64)

Example
=======

mysql> SELECT 
sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd');
+---------------------------------------------------------------------------+
| sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd') |
+---------------------------------------------------------------------------+
| employee                                                                  |
+---------------------------------------------------------------------------+
1 row in set (0.02 sec)
         

format_bytes
==================================================================================

Description
===========

Takes a raw bytes value, and converts it to a human readable format.

Parameters
==========

* bytes (TEXT): A raw bytes value.

Returns
=======

TEXT

Example
=======

mysql> SELECT sys.format_bytes(2348723492723746) AS size;
+----------+
| size     |
+----------+
| 2.09 PiB |
+----------+
1 row in set (0.00 sec)

mysql> SELECT sys.format_bytes(2348723492723) AS size;
+----------+
| size     |
+----------+
| 2.14 TiB |
+----------+
1 row in set (0.00 sec)

mysql> SELECT sys.format_bytes(23487234) AS size;
+-----------+
| size      |
+-----------+
| 22.40 MiB |
+-----------+
1 row in set (0.00 sec)


format_path
==================================================================================

Description
===========

Takes a raw path value, and strips out the datadir or tmpdir replacing with 
@@datadir and @@tmpdir respectively. 

Also normalizes the paths across operating systems, so backslashes on Windows are 
converted to forward slashes.

Parameters
==========

* path (VARCHAR(260)): The raw file path value to format.

Returns
=======

VARCHAR(260) CHARSET UTF8

Example
=======

mysql> select @@datadir;
+-----------------------------------------------+
| @@datadir                                     |
+-----------------------------------------------+
| /Users/mark/sandboxes/SmallTree/AMaster/data/ |
+-----------------------------------------------+
1 row in set (0.06 sec)

mysql> select 
format_path('/Users/mark/sandboxes/SmallTree/AMaster/data/mysql/proc.MYD') AS 
path;
+--------------------------+
| path                     |
+--------------------------+
| @@datadir/mysql/proc.MYD |
+--------------------------+
1 row in set (0.03 sec)


format_statement
==================================================================================

Description
===========

Formats a normalized statement, truncating it if it's > 64 characters long by 
default.

To configure the length to truncate the statement to by default, update the 
`statement_truncate_len` variable with `sys_config` table to a different value. 
Alternatively, to change it just for just your particular session, use `SET 
@sys.statement_truncate_len := <some new value>`.

Useful for printing statement related data from Performance Schema from the 
command line.

Parameters
==========

* statement (LONGTEXT): The statement to format.

Returns
=======

VARCHAR(65)

Example
=======

mysql> SELECT sys.format_statement(digest_text)
    ->   FROM performance_schema.events_statements_summary_by_digest
    ->  ORDER by sum_timer_wait DESC limit 5;
+-------------------------------------------------------------------+
| sys.format_statement(digest_text)                                 |
+-------------------------------------------------------------------+
| CREATE SQL SECURITY INVOKER VI ... KE ? AND `variable_value` > ?  |
| CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `esc` . ... |
| CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `sys` . ... |
| CREATE SQL SECURITY INVOKER VI ...  , `compressed_size` ) ) DESC  |
| CREATE SQL SECURITY INVOKER VI ... LIKE ? ORDER BY `timer_start`  |
+-------------------------------------------------------------------+
5 rows in set (0.00 sec)


format_time
==================================================================================

Description
===========

Takes a raw picoseconds value, and converts it to a human readable form.
             
Picoseconds are the precision that all latency values are printed in within 
Performance Schema, however are not user friendly when wanting to scan output from 
the command line.

Parameters
==========

* picoseconds (TEXT): The raw picoseconds value to convert.

Returns
=======

TEXT

Example
=======

mysql> select format_time(342342342342345);
+------------------------------+
| format_time(342342342342345) |
+------------------------------+
| 00:05:42                     |
+------------------------------+
1 row in set (0.00 sec)

mysql> select format_time(342342342);
+------------------------+
| format_time(342342342) |
+------------------------+
| 342.34 µs              |
+------------------------+
1 row in set (0.00 sec)

mysql> select format_time(34234);
 +--------------------+
| format_time(34234) |
+--------------------+
| 34.23 ns           |
+--------------------+
1 row in set (0.00 sec)


ps_is_account_enabled
==================================================================================

Description
===========

Determines whether instrumentation of an account is enabled within Performance 
Schema.

Parameters
==========

* in_host VARCHAR(60): The hostname of the account to check.
* in_user (VARCHAR(16)): The username of the account to check.

Returns
=======

ENUM('YES', 'NO', 'PARTIAL')

Example
=======

mysql> SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES                                            |
+------------------------------------------------+
1 row in set (0.01 sec)


ps_is_consumer_enabled
==================================================================================

Description
===========

Determines whether a consumer is enabled (taking the consumer hierarchy into 
consideration) within the Performance Schema.

Parameters
==========

* in_consumer VARCHAR(64): The name of the consumer to check.

Returns
=======

ENUM('YES', 'NO')

Example
=======

mysql> SELECT sys.ps_is_consumer_enabled('events_stages_history');
+-----------------------------------------------------+
| sys.ps_is_consumer_enabled('events_stages_history') |
+-----------------------------------------------------+
| NO                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)


ps_is_instrument_default_enabled
==================================================================================

Description
===========

Returns whether an instrument is enabled by default in this version of MySQL.

Parameters
==========

* in_instrument VARCHAR(128): The instrument to check.

Returns
=======

ENUM('YES', 'NO')

Example
=======

mysql> SELECT sys.ps_is_instrument_default_enabled('statement/sql/select');
+--------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('statement/sql/select') |
+--------------------------------------------------------------+
| YES                                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)


ps_is_instrument_default_timed
==================================================================================

Description
===========

Returns whether an instrument is timed by default in this version of MySQL.

Parameters
==========

* in_instrument VARCHAR(128): The instrument to check.

Returns
=======

ENUM('YES', 'NO')

Example
=======

mysql> SELECT sys.ps_is_instrument_default_timed('statement/sql/select');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('statement/sql/select') |
+------------------------------------------------------------+
| YES                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)


ps_is_thread_instrumented
==================================================================================

Description
===========

Checks whether the provided connection id is instrumented within Performance 
Schema.

Parameters
==========

* in_connection_id (BIGINT UNSIGNED): the id of the connection to check.

Returns
=======

ENUM('YES', 'NO', 'UNKNOWN')


Example
=======

mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+------------------------------------------------+
| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+------------------------------------------------+
| YES                                            |
+------------------------------------------------+
1 row in set (0.10 sec)


ps_thread_id
==================================================================================

Description
===========

Return the Performance Schema THREAD_ID for the specified connection ID.

Parameters
==========

* in_connection_id (BIGINT UNSIGNED): The id of the connection to return the 
thread id for.

Returns
=======

BIGINT UNSIGNED

Example
=======

mysql> SELECT sys.ps_thread_id(79);
+----------------------+
| sys.ps_thread_id(79) |
+----------------------+
|                   98 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT sys.ps_thread_id(CONNECTION_ID());
+-----------------------------------+
| sys.ps_thread_id(CONNECTION_ID()) |
+-----------------------------------+
|                                98 |
+-----------------------------------+
1 row in set (0.00 sec)


ps_thread_stack
==================================================================================

Description
===========

Outputs a JSON formatted stack of all statements, stages and events within 
Performance Schema for the specified thread.

Parameters
==========

* thd_id (BIGINT): The id of the thread to trace. This should match the thread_id 
column from the performance_schema.threads table.

Example
=======

(line separation added for output)


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


sys_get_config
==================================================================================

Description
===========

Returns the value for the requested variable using the following logic:

1. If the option exists in sys.sys_config return the value from there.
2. Else fall back on the provided default value.

Notes for using sys_get_config():

* If the default value argument to sys_get_config() is NULL and case 2. is 
reached, NULL is returned.
  It is then expected that the caller is able to handle NULL for the given 
configuration option.
* The convention is to name the user variables @sys.<name of variable>. It is 
<name of variable> that
  is stored in the sys_config table and is what is expected as the argument to 
sys_get_config().
* If you want to check whether the configuration option has already been set and 
if not assign with
  the return value of sys_get_config() you can use IFNULL(...) (see example 
below). However this should
  not be done inside a loop (e.g. for each row in a result set) as for repeated 
calls where assignment
  is only needed in the first iteration using IFNULL(...) is expected to be 
significantly slower than
  using an IF (...) THEN ... END IF; block (see example below).

Parameters
==========

* in_variable_name (VARCHAR(128)): The name of the config option to return the 
value for.
* in_default_value (VARCHAR(128)): The default value to return if neither a use 
variable exists nor the variable exists in sys.sys_config.

Returns
=======

VARCHAR(128)

Example
=======

-- Get the configuration value from sys.sys_config falling back on 128 if the 
option is not present in the table.
mysql> SELECT sys.sys_get_config('statement_truncate_len', 128) AS Value;
+-------+
| Value |
+-------+
| 64    |
+-------+
1 row in set (0.00 sec)

-- Check whether the option is already set, if not assign - IFNULL(...) one liner 
example.
mysql> SET @sys.statement_truncate_len = IFNULL(@sys.statement_truncate_len, 
sys.sys_get_config('statement_truncate_len', 64));
Query OK, 0 rows affected (0.00 sec)

-- Check whether the option is already set, if not assign - IF ... THEN ... END IF 
example.
IF (@sys.statement_truncate_len IS NULL) THEN
    SET @sys.statement_truncate_len = sys.sys_get_config('statement_truncate_len', 
64);
END IF;


==================================================================================
NEW PROCEDURES
==================================================================================

create_synonym_db
==================================================================================

Description
===========

Takes a source database name and synonym name, and then creates the synonym 
database with views that point to all of the tables within the source database.

Useful for creating a "ps" synonym for "performance_schema", or "is" instead of 
"information_schema", for example.

Parameters
==========

* in_db_name (VARCHAR(64)):
** The database name that you would like to create a synonym for.
* in_synonym (VARCHAR(64)):
** The database synonym name.

Example
=======

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> CALL sys.create_synonym_db('performance_schema', 'ps');
+-------------------------------------+
| summary                             |
+-------------------------------------+
| Created 74 views in the ps database |
+-------------------------------------+
1 row in set (8.57 sec)

Query OK, 0 rows affected (8.57 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ps                 |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SHOW FULL TABLES FROM ps;
+-----------------------------------------+------------+
| Tables_in_ps                            | Table_type |
+-----------------------------------------+------------+
| accounts                                | VIEW       |
| cond_instances                          | VIEW       |
| events_stages_current                   | VIEW       |
| events_stages_history                   | VIEW       |
...


ps_setup_disable_background_threads
==================================================================================

Description
===========

Disable all background thread instrumentation within Performance Schema.

Parameters
==========

None.

Example
=======

mysql> CALL sys.ps_setup_disable_background_threads();
+--------------------------------+
| summary                        |
+--------------------------------+
| Disabled 18 background threads |
+--------------------------------+
1 row in set (0.00 sec)


ps_setup_disable_instrument
==================================================================================

Description
===========

Disables instruments within Performance Schema  matching the input pattern.

Parameters
==========

* in_pattern (VARCHAR(128)): A LIKE pattern match (using "%in_pattern%") of events 
to disable

Example
=======

To disable all mutex instruments:

mysql> CALL sys.ps_setup_disable_instrument('wait/synch/mutex');
+--------------------------+
| summary                  |
+--------------------------+
| Disabled 155 instruments |
+--------------------------+
1 row in set (0.02 sec)

To disable just a the scpecific TCP/IP based network IO instrument:

mysql> CALL 
sys.ps_setup_disable_instrument('wait/io/socket/sql/server_tcpip_socket');
+------------------------+
| summary                |
+------------------------+
| Disabled 1 instruments |
+------------------------+
1 row in set (0.00 sec)

To enable all instruments:

mysql> CALL sys.ps_setup_disable_instrument('');
+--------------------------+
| summary                  |
+--------------------------+
| Disabled 547 instruments |
+--------------------------+
1 row in set (0.01 sec)


ps_setup_disable_consumer
==================================================================================

Description
===========

Disables consumers within Performance Schema matching the input pattern.

Parameters
==========

* consumer (VARCHAR(128)): A LIKE pattern match (using "%consumer%") of consumers 
to disable

Example
=======

To disable all consumers:

mysql> CALL sys.ps_setup_disable_consumer('');
+--------------------------+
| summary                  |
+--------------------------+
| Disabled 15 consumers    |
+--------------------------+
1 row in set (0.02 sec)


To disable just the event_stage consumers:

mysql> CALL sys.ps_setup_disable_consumer('stage');
+------------------------+
| summary                |
+------------------------+
| Disabled 3 consumers   |
+------------------------+
1 row in set (0.00 sec)


ps_setup_disable_thread
==================================================================================

Description
===========

Disable the given connection/thread in Performance Schema.

Parameters
==========

* in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from 
performance_schema.threads or the ID shown within SHOW PROCESSLIST)

Example
=======

mysql> CALL sys.ps_setup_disable_thread(3);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

To disable the current connection:

mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)


ps_setup_enable_background_threads
==================================================================================

Description
===========

Enable all background thread instrumentation within Performance Schema.

Parameters
==========

None.

Example
=======

mysql> CALL sys.ps_setup_enable_background_threads();
+-------------------------------+
| summary                       |
+-------------------------------+
| Enabled 18 background threads |
+-------------------------------+
1 row in set (0.00 sec)


ps_setup_enable_consumer
==================================================================================

Description
===========

Enables consumers within Performance Schema matching the input pattern.

Parameters
==========

* consumer (VARCHAR(128)): A LIKE pattern match (using "%consumer%") of consumers 
to enable

Example
=======

To enable all consumers:

mysql> CALL sys.ps_setup_enable_consumer('');
+-------------------------+
| summary                 |
+-------------------------+
| Enabled 10 consumers    |
+-------------------------+
1 row in set (0.02 sec)


To enable just "waits" consumers:

mysql> CALL sys.ps_setup_enable_consumer('waits');
+-----------------------+
| summary               |
+-----------------------+
| Enabled 3 consumers   |
+-----------------------+
1 row in set (0.00 sec)


ps_setup_enable_instrument
==================================================================================

Description
===========

Enables instruments within Performance Schema matching the input pattern.

Parameters
==========


* in_pattern (VARCHAR(128)): A LIKE pattern match (using "%in_pattern%") of events 
to enable

Example
=======

To enable all mutex instruments:

mysql> CALL sys.ps_setup_enable_instrument('wait/synch/mutex');
+-------------------------+
| summary                 |
+-------------------------+
| Enabled 155 instruments |
+-------------------------+
1 row in set (0.02 sec)

To enable just a the scpecific TCP/IP based network IO instrument:

mysql> CALL 
sys.ps_setup_enable_instrument('wait/io/socket/sql/server_tcpip_socket');
+-----------------------+
| summary               |
+-----------------------+
| Enabled 1 instruments |
+-----------------------+
1 row in set (0.00 sec)

To enable all instruments:

mysql> CALL sys.ps_setup_enable_instrument('');
+-------------------------+
| summary                 |
+-------------------------+
| Enabled 547 instruments |
+-------------------------+
1 row in set (0.01 sec)


ps_setup_enable_thread
==================================================================================

Description
===========

Enable the given connection/thread in Performance Schema.

Parameters
==========


* in_connection_id (BIGINT): The connection ID (PROCESSLIST_ID from 
performance_schema.threads or the ID shown within SHOW PROCESSLIST)

Example
=======

mysql> CALL sys.ps_setup_enable_thread(3);
+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)

To enable the current connection:

mysql> CALL sys.ps_setup_enable_thread(CONNECTION_ID());
+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.00 sec)


ps_setup_reload_saved
==================================================================================

Description
===========

Reloads a saved Performance Schema configuration, so that you can alter the setup 
for debugging purposes, but restore it to a previous state.
             
Use the companion procedure - ps_setup_save(), to save a configuration.

Requires the SUPER privilege for "SET sql_log_bin = 0;".

Parameters
==========

None.

Example
=======

mysql> CALL sys.ps_setup_save();
Query OK, 0 rows affected (0.08 sec)

mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 
'YES';
Query OK, 547 rows affected (0.40 sec)
Rows matched: 784  Changed: 547  Warnings: 0

/* Run some tests that need more detailed instrumentation here */

mysql> CALL sys.ps_setup_reload_saved();
Query OK, 0 rows affected (0.32 sec)


ps_setup_reset_to_default
==================================================================================

Description
===========

Resets the Performance Schema setup to the default settings.

Parameters
==========

* in_verbose (BOOLEAN): Whether to print each setup stage (including the SQL) 
whilst running.

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 in set (0.00 sec)

*************************** 1. row ***************************
status: Resetting: setup_actors
INSERT IGNORE INTO performance_schema.setup_actors
VALUES ('%', '%', '%')
1 row in set (0.00 sec)
...

mysql> CALL sys.ps_setup_reset_to_default(false)G
Query OK, 0 rows affected (0.00 sec)


ps_setup_save
==================================================================================

Description
===========

Saves the current configuration of Performance Schema, so that you can alter the 
setup for debugging purposes, but restore it to a previous state.

Use the companion procedure - ps_setup_reload_saved(), to restore the saved 
config.

Requires the SUPER privilege for "SET sql_log_bin = 0;".

Parameters
==========

None.

Example
=======

mysql> CALL sys.ps_setup_save();
Query OK, 0 rows affected (0.08 sec)

mysql> UPDATE performance_schema.setup_instruments 
    ->    SET enabled = 'YES', timed = 'YES';
Query OK, 547 rows affected (0.40 sec)
Rows matched: 784  Changed: 547  Warnings: 0

/* Run some tests that need more detailed instrumentation here */

mysql> CALL sys.ps_setup_reload_saved();
Query OK, 0 rows affected (0.32 sec)


ps_setup_show_disabled
==================================================================================

Description
===========

Shows all currently disable Performance Schema configuration.

Parameters
==========

* in_in_show_instruments (BOOLEAN): Whether to print disabled instruments (can 
print many items)
* in_in_show_threads (BOOLEAN): Whether to print disabled threads

Example
=======

mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

+--------------------+
| enabled_users      |
+--------------------+
| 'mark'@'localhost' |
+--------------------+
1 row in set (0.00 sec)

+-------------+----------------------+---------+-------+
| 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    |
+-------------+----------------------+---------+-------+
15 rows in set (0.00 sec)

+----------------------------------+
| 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        |
+----------------------------------+
10 rows in set (0.00 sec)

Empty set (0.00 sec)
             
+----------------------------------------------------------..------------+-------+
| disabled_instruments                                     ..            | timed |
+----------------------------------------------------------..------------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc                ..            | NO    |
| wait/synch/mutex/sql/LOCK_des_key_file                   ..            | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit          ..            | NO    |
...
| memory/sql/servers_cache                                 ..            | NO    |
| memory/sql/udf_mem                                       ..            | NO    |
| wait/lock/metadata/sql/mdl                               ..            | NO    |
+----------------------------------------------------------..------------+-------+
547 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


ps_setup_show_disabled_consumers
==================================================================================

Description
===========

Shows all currently disabled consumers.

Parameters
==========

None
 
Example
=======


mysql> CALL sys.ps_setup_show_disabled_consumers();

+---------------------------+
| disabled_consumers        |
+---------------------------+
| events_statements_current |
| global_instrumentation    |
| thread_instrumentation    |
| statements_digest         |
+---------------------------+
4 rows in set (0.05 sec)


ps_setup_show_disabled_instruments
==================================================================================

Description
===========

Shows all currently disabled instruments.

Parameters
==========

None
			 
Example
=======


mysql> CALL sys.ps_setup_show_disabled_instruments();


ps_setup_show_enabled
==================================================================================

Description
===========

Shows all currently enabled Performance Schema configuration.

Parameters
==========

* in_show_instruments (BOOLEAN): Whether to print enabled instruments (can print 
many items)
* in_show_threads (BOOLEAN): Whether to print enabled threads

Example
=======

mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

+---------------+
| enabled_users |
+---------------+
| '%'@'%'       |
+---------------+
1 row in set (0.01 sec)

+----------------------+---------+-------+
| objects              | enabled | timed |
+----------------------+---------+-------+
| mysql.%              | NO      | NO    |
| performance_schema.% | NO      | NO    |
| information_schema.% | NO      | NO    |
| %.%                  | YES     | YES   |
+----------------------+---------+-------+
4 rows in set (0.01 sec)

+---------------------------+
| enabled_consumers         |
+---------------------------+
| events_statements_current |
| global_instrumentation    |
| thread_instrumentation    |
| statements_digest         |
+---------------------------+
4 rows in set (0.05 sec)

+--------------------------+-------------+
| enabled_threads          | thread_type |
+--------------------------+-------------+
| innodb/srv_master_thread | BACKGROUND  |
| root@localhost           | FOREGROUND  |
| root@localhost           | FOREGROUND  |
| root@localhost           | FOREGROUND  |
| root@localhost           | FOREGROUND  |
+--------------------------+-------------+
5 rows in set (0.03 sec)

+-------------------------------------+-------+
| enabled_instruments                 | timed |
+-------------------------------------+-------+
| wait/io/file/sql/map                | YES   |
| wait/io/file/sql/binlog             | YES   |
...
| statement/com/Error                 | YES   |
| statement/com/                      | YES   |
| idle                                | YES   |
+-------------------------------------+-------+
210 rows in set (0.08 sec)

Query OK, 0 rows affected (0.89 sec)


ps_setup_show_enabled_consumers
==================================================================================

Description
===========

Shows all currently enabled consumers.

Parameters
==========

None

Example
=======


mysql> CALL sys.ps_setup_show_enabled_consumers();

+---------------------------+
| enabled_consumers         |
+---------------------------+
| events_statements_current |
| global_instrumentation    |
| thread_instrumentation    |
| statements_digest         |
+---------------------------+
4 rows in set (0.05 sec)


ps_setup_show_enabled_instruments

Description
===========

Shows all currently enabled instruments.

Parameters
==========

None

Example
=======


mysql> CALL sys.ps_setup_show_enabled_instruments();


ps_statement_avg_latency_histogram
==================================================================================

Description
===========

Outputs a textual histogram graph of the average latency values across all 
normalized queries tracked within the Performance Schema 
events_statements_summary_by_digest table.

Can be used to show a very high level picture of what kind of latency distribution 
statements running within this instance have.

Parameters
==========

None.

Example
=======

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 - 38ms)     240 | ############################################################
(38 - 77ms)    38  | ......................................
(77 - 115ms)   3   | ...
(115 - 154ms)  62  | *******************************
(154 - 192ms)  3   | ...
(192 - 231ms)  0   |
(231 - 269ms)  0   |
(269 - 307ms)  0   |
(307 - 346ms)  0   |
(346 - 384ms)  1   | .
(384 - 423ms)  1   | .
(423 - 461ms)  0   |
(461 - 499ms)  0   |
(499 - 538ms)  0   |
(538 - 576ms)  0   |
(576 - 615ms)  1   | .

  Total Statements: 350; Buckets: 16; Bucket Size: 38 ms;


ps_trace_statement_digest
==================================================================================

Description
===========

Traces all instrumentation within Performance Schema for a specific Statement 
Digest. 

When finding a statement of interest within the 
performance_schema.events_statements_summary_by_digest table, feed the DIGEST MD5 
value in to this procedure, set how long to poll for, and at what interval to 
poll, and it will generate a report of all statistics tracked within Performance 
Schema for that digest for the interval.

It will also attempt to generate an EXPLAIN for the longest running example of the 
digest during the interval.

Note this may fail, as Performance Schema truncates long SQL_TEXT values (and 
hence the EXPLAIN will fail due to parse errors).

Requires the SUPER privilege for "SET sql_log_bin = 0;".

Parameters
==========

* in_digest VARCHAR(32): The statement digest identifier you would like to analyze
* in_runtime (INT): The number of seconds to run analysis for (defaults to a 
minute)
* in_interval (DECIMAL(2,2)): The interval (in seconds, may be fractional) at 
which to try and take snapshots (defaults to a second)
* in_start_fresh (BOOLEAN): Whether to TRUNCATE the events_statements_history_long 
and events_stages_history_long tables before starting (default false)
* in_auto_enable (BOOLEAN): Whether to automatically turn on required consumers 
(default false)

Example
=======

mysql> call ps_analyze_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 µs |
| stage/sql/statistics                     |    16 | 546.92 µs |
| stage/sql/freeing items                  |    18 | 520.11 µs |
| stage/sql/init                           |    51 | 466.80 µs |
...
| stage/sql/cleaning up                    |    18 | 11.92 µs  |
| stage/sql/executing                      |    16 | 6.95 µs   |
+------------------------------------------+-------+-----------+
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 µs | 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 µs   |
| stage/sql/Waiting for query cache lock   | 453.23 µs |
| stage/sql/init                           | 331.07 ns |
| stage/sql/checking query cache for query | 43.04 µs  |
...
| stage/sql/freeing items                  | 30.46 µs  |
| 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)


ps_trace_thread
==================================================================================

Description
===========

Dumps all data within Performance Schema for an instrumented thread, to create a 
DOT formatted graph file. 

Each resultset returned from the procedure should be used for a complete graph

Requires the SUPER privilege for "SET sql_log_bin = 0;".

Parameters
==========

* in_thread_id (INT): The thread that you would like a stack trace for
* in_outfile  (VARCHAR(255)): The filename the dot file will be written to
* in_max_runtime (DECIMAL(20,2)): The maximum time to keep collecting data. Use 
NULL to get the default which is 60 seconds.
* in_interval (DECIMAL(20,2)): How long to sleep between data collections. Use 
NULL to get the default which is 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 
consumers/instruments. This will also reset the settings at the end of the run.
* in_debug (BOOLEAN): Whether you would like to include file:lineno 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)


ps_truncate_all_tables
==================================================================================

Description
===========

Truncates all summary tables within Performance Schema, resetting all aggregated 
instrumentation as a snapshot.

Parameters
==========

* in_verbose (BOOLEAN): Whether to print each TRUNCATE statement before running

Example
=======

mysql> CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary             |
+---------------------+
| Truncated 44 tables |
+---------------------+
1 row in set (0.10 sec)