26.2 Using the sys Schema

You can make the *note 'sys': sys-schema. schema the default schema so that references to its objects need not be qualified with the schema name:

 mysql> USE sys;
 Database changed
 mysql> SELECT * FROM version;
 +-------------+------------------+
 | sys_version | mysql_version    |
 +-------------+------------------+
 | 1.5.1       | 5.7.24-debug-log |
 +-------------+------------------+

(The note 'version': sys-version. view shows the note 'sys': sys-schema. schema and MySQL server versions.)

To access *note 'sys': sys-schema. schema objects while a different schema is the default (or simply to be explicit), qualify object references with the schema name:

 mysql> SELECT * FROM sys.version;
 +-------------+------------------+
 | sys_version | mysql_version    |
 +-------------+------------------+
 | 1.5.1       | 5.7.24-debug-log |
 +-------------+------------------+

The 'sys' schema contains many views that summarize Performance Schema tables in various ways. Most of these views come in pairs, such that one member of the pair has the same name as the other member, plus a 'x$' prefix. For example, the *note 'host_summary_by_file_io': sys-host-summary-by-file-io. view summarizes file I/O grouped by host and displays latencies converted from picoseconds to more readable values (with units);

 mysql> SELECT * FROM sys.host_summary_by_file_io;
 +------------+-------+------------+
 | host       | ios   | io_latency |
 +------------+-------+------------+
 | localhost  | 67570 | 5.38 s     |
 | background |  3468 | 4.18 s     |
 +------------+-------+------------+

The *note 'x$host_summary_by_file_io': sys-host-summary-by-file-io. view summarizes the same data but displays unformatted picosecond latencies:

 mysql> SELECT * FROM sys.x$host_summary_by_file_io;
 +------------+-------+---------------+
 | host       | ios   | io_latency    |
 +------------+-------+---------------+
 | localhost  | 67574 | 5380678125144 |
 | background |  3474 | 4758696829416 |
 +------------+-------+---------------+

The view without the 'x′prefixisintendedtoprovideoutputthatismoreuserfriendlyandeasierforhumanstoread.Theviewwiththe′x' prefix that displays the same values in raw form is intended more for use with other tools that perform their own processing on the data. For additional information about the differences between non-'x′and′x' views, see *note sys-schema-views::.

To examine note 'sys': sys-schema. schema object definitions, use the appropriate note 'SHOW': show. statement or note 'INFORMATION_SCHEMA': information-schema. query. For example, to examine the definitions of the note 'session': sys-session. view and *note 'format_bytes()': sys-format-bytes. function, use these statements:

 mysql> SHOW CREATE VIEW sys.session;
 mysql> SHOW CREATE FUNCTION sys.format_bytes;

However, those statements display the definitions in relatively unformatted form. To view object definitions with more readable formatting, access the individual '.sql' files found under the 'scripts/sys_schema' in MySQL source distributions. Prior to MySQL 5.7.28, the sources are maintained in a separate distribution available from the *note 'sys': sys-schema. schema development website at https://github.com/mysql/mysql-sys.

Neither note 'mysqldump': mysqldump. nor note 'mysqlpump': mysqlpump. dump the note 'sys': sys-schema. schema by default. To generate a dump file, name the note 'sys': sys-schema. schema explicitly on the command line using either of these commands:

 mysqldump --databases --routines sys > sys_dump.sql
 mysqlpump sys > sys_dump.sql

To reinstall the schema from the dump file, use this command:

 mysql < sys_dump.sql

 File: manual.info.tmp, Node: sys-schema-progress-reporting, Next: sys-schema-reference, Prev: sys-schema-usage, Up: sys-schema