This is general guidance for DBAs who are looking to use the MySQL sys schema.
Introduction
The MySQL sys schema is a collection of views, stored procedures, and stored functions to help MySQL database administrators get insight into what MySQL is doing and to help use the Performance Schema. However while the sys schema primarily use the Performance Schema, it also includes information from the Information Schema.
The views and stored programs available depends on the version of the sys schema that is installed. To get a complete list of objects use the following query:
( SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' ) UNION ( SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'sys' );
The sys schema supports MySQL 5.6 and later.
Installation
To verify whether the sys schema is installed:
mysql> SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'version' AND COLUMN_NAME = 'sys_version'), 'YES', 'NO') AS SysSchemaInstalled; +--------------------+ | SysSchemaInstalled | +--------------------+ | YES | +--------------------+ 1 row in set (0.00 sec)
To verify the version of the sys schema that is installed:
mysql> SELECT sys_version FROM sys.version; +-------------+ | sys_version | +-------------+ | 1.5.1 | +-------------+ 1 row in set (0.00 sec)
Installing the Sys Schema: MySQL 5.7.7 and Later
MySQL 5.7.7 and later comes with the sys schema installed by default when initializing MySQL or upgrading. If you do not want to install the sys schema, mysql_upgrade and mysql_install_db supports the –skip-sys-schema option.
The recommended way to initialize a new MySQL installation in MySQL 5.7.6 and later is to use mysqld –initialize. However this does not support skipping the sys schema installation. As of MySQL 5.7.9, the mysql.sys@localhost user is created as part of the installation when the sys schema is created with mysqld –initialize. This is a system user used as the definer for the objects in the sys schema.
Installing the Sys Schema: MySQL 5.7.6 and Earlier and MySQL 5.6
If you want to use the sys schema with releases prior to MySQL 5.7.7, then you need to download and install the sys schema yourself. The steps are:
- Download the sys schema from the MySQL github repository. The installation is the same irrespectively of the branch you choose. The main branches to consider are:
- master: the latest stable release
- development: includes all of the latest additions and changes
- Unzip the downloaded file
- The directory created in 2. will include an installation file for each supported MySQL version with the name reflecting the version. For example sys_56.sql is the installation file for MySQL 5.6 and sys_57.sql is the installation file for MySQL 5.7. To complete the installation:
- Go to the directory with the installation file (i.e. make the directory the current directory in the shell or prompt).
- Start the mysql command line client.
- Source the content of this file into MySQL, for example:
mysql> SOURCE sys_57.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ...
Alternatively combine steps 2. and 3. like:
shell$ mysql < sys_57.sql
The above creates all views and stored programs with root@localhost as the definer. See Advanced Installations below for details how to install the sys schema with a custom definer.
Advanced Installations
In some cases it may be desirable to customize the installation for example if you are using RDS and you cannot install the sys schema using root@localhost as the definer. In this case you need to use the generate_sql_file.sh script that is included with the sys schema source. The script supports the following arguments:
- -v: this argument is required and is used to specify the version of MySQL to create the installation file for. Specify the MySQL version without dots and without the patch release number, for example 56 for MySQL 5.6 or 57 for MySQL 5.7.
- -b: by default the sys schema turns off binary logging whenever a procedure may generate binary log events (for example if temporary tables are used). The -b option removes this. This is mainly useful if you install the sys schema on a MySQL instance where you do not have the SUPER privilege, for example for RDS./li>
- -m: create an installation file that is compatible with mysql_install_db or mysqld --bootstrap.
- -u: change the definer; this is for example useful for RDS.
As an example consider if you want to create an installation file for MySQL 5.7 using myadmin@localhost as the definer:
shell$ ./generate_sql_file.sh -v 57 -u myadmin@localhost
Wrote file: /mysql/source/mysql-sys/sys_1.5.0_57_inline.sql
Object Definer: myadmin@localhost
sql_log_bin: enabled
MySQL Workbench 6.1 and later includes support for generating performance reports based on the views in the sys schema. As part of this support, you can also use MySQL Workbench to install the version of the sys schema shipped with the MySQL Workbench. To install the sys schema, see the MySQL Workbench Reference Manual.
One disadvantage of installing the sys schema using MySQL Workbench is that you will get the version that comes with the version of MySQL Workbench you use. If you need a specific version (e.g. the latest release) of the sys schema, you must install it manually.
Configuration
The sys schema configuration is done through the sys_config table and user variables. The sys_config table is used as the source of the default values for the configuration options:
mysql> SELECT * FROM sys.sys_config; +--------------------------------------+-------+---------------------+--------+ | variable | value | set_time | set_by | +--------------------------------------+-------+---------------------+--------+ | diagnostics.allow_i_s_tables | OFF | 2017-08-07 16:08:38 | NULL | | diagnostics.include_raw | OFF | 2017-08-07 16:08:38 | NULL | | ps_thread_trx_info.max_length | 65535 | 2017-08-07 16:08:38 | NULL | | statement_performance_analyzer.limit | 100 | 2017-08-07 16:08:38 | NULL | | statement_performance_analyzer.view | NULL | 2017-08-07 16:08:38 | NULL | | statement_truncate_len | 64 | 2017-08-07 16:08:38 | NULL | +--------------------------------------+-------+---------------------+--------+ 6 rows in set (0.00 sec)
For values that were set as part of the initial installation of the sys schema, set_by will have a value of NULL (however, see Bug 26629164), otherwise a trigger will set the value to the user who inserted or updated the row. set_time defaults both on insert and update to the current time. For example:
mysql> UPDATE sys.sys_config SET value = 128 WHERE variable = 'statement_truncate_len'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM sys.sys_config WHERE variable = 'statement_truncate_len'; +------------------------+-------+---------------------+------------------+ | variable | value | set_time | set_by | +------------------------+-------+---------------------+------------------+ | statement_truncate_len | 128 | 2017-08-15 13:32:33 | root@localhost | +------------------------+-------+---------------------+------------------+ 1 row in set (0.00 sec)
If you want to use a different value for a configuration option than the one specified in sys_config, but you do not want to change what you use by default, you can set the corresponding user variable. The convention is that the name of the user variable is the variable name from sys_config prefixed with @sys.. To change back to use the default value, set the user variable to NULL. For example to set statement_truncate_len to 26 for the next use of sys.format_statement() and then change back to use the value from sys_config:
mysql> SET @sys.statement_truncate_len = 26;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT sys.format_statement('SELECT ''abcdefghijklmnopqrstuvwxyz''');
+---------------------------------------------------------------+
| sys.format_statement('SELECT ''abcdefghijklmnopqrstuvwxyz''') |
+---------------------------------------------------------------+
| SELECT 'abc ... qrstuvwxyz' |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @sys.statement_truncate_len = NULL; Query OK, 0 rows affected (0.00 sec)
See the documentation of each stored program to see which configuration options the program supports.
Naming Conventions For Views
If you look at the list of views included in the sys schema, you will find that there are two sets of views: one prefixed with x$ and one without. When there is a plain and an x$ version of the same view, the plain version will have the output formatted whereas the x$ version will not. For example:
mysql> SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME LIKE '%processlist'; +---------------+ | TABLE_NAME | +---------------+ | processlist | | x$processlist | +---------------+ 2 rows in set (0.00 sec)
Here the two views are:
- The processlist view contains a formatted output, for example statements are formatted using sys.format_statement() and the Performance Schema latencies are scaled using sys.format_time().
- The x$processlist view contains the unformatted output. This is for example useful if you want to post process the output such a change the sorting or use your own formatting functions.
The Sys Schema and mysqldump/mysqlpump
If you use mysqldump or mysqlpump from MySQL 5.7.8 or later, the sys schema is skipped by default. If you want to include the sys schema you can specify it explicitly, for example to backup the sys and world databases:
shell$ mysqldump --routines --triggers --databases sys world > backup.sql