26.2 Using the sys Schema

You can make the sys 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 version view shows the sys schema and MySQL server versions.)

To access sys 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 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 x$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$ prefix is intended to provide output that is more user friendly and easier for humans to read. The view with the 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 Section 26.4.3, “sys Schema Views”.

To examine sys schema object definitions, use the appropriate SHOW statement or INFORMATION_SCHEMA query. For example, to examine the definitions of the session view and 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 sys schema development website at https://github.com/mysql/mysql-sys.

Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys 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