The MySQL SYS Schema in MySQL 5.7.7

For those unfamiliar with the sys schema project, it is a database schema with a set of objects (views, stored procedures, stored functions, and table with a couple of triggers on it) that were implemented to give easy, human readable, DBA and Developer based use case access to the wealth of instrumentation data implemented primarily within Performance Schema, but also with various INFORMATION_SCHEMA tables as well.

First, what does included by default mean?

It means that when you initialize your MySQL 5.7 server for the first time, with either mysql_install_db, or the new mysqld --initialize option, the sys schema is added alongside the other standard schemas, nothing further for you to do, just initialize your database instance as you normally would.

When upgrading from a previous version, and running mysql_upgrade, the instance is checked to see whether the sys schema already exists or not, and creates or upgrades the schema appropriately. The version of the sys schema bundled with 5.7.7 is 1.4.0, so if you already have that version installed, mysql_ugprade will do nothing. However, if you have a version prior to that, mysql_upgrade will re-create the schema with the updated version:

mysql_upgrade with outdated sys schema

Shell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

$/opt/mysql/5.7.7/bin/mysql_upgrade-uroot-p-h127.0.0.1-P5707

Enter password:

Checking ifupdate isneeded.

Checking server version.

Running queries toupgrade MySQL server.

Checking system database.

mysql.columns_privOK

...snip...

mysql.userOK

Found outdated sys schema version1.3.0.

Upgrading the sys schema.

Checking databases.

sys.sys_configOK

Upgrade process completed successfully.

Checking ifupdate isneeded.

$

If the sys schema exists on upgrade, but the sys.version view does not exist, then mysql_upgrade will return an error, as it will assume that the sys schema that is within the instance is a user created schema:

If you already have a schema called sys when upgrading to version 5.7.7 or later, you should ensure that it is renamed before running mysql_upgrade.

If you want to skip creating the sys schema with either mysql_install_db or mysql_upgrade, you can use the new --skip-sys-schema option. You can always install it by hand at later time, using the $install_dir/share/mysql_sys_schema.sql file laid down with each installation (though this is not used within any of the above options, the DDL statements are compiled in for those).

After installation or upgrade, you then get access to the objects within the sys schema, enabling you to dive deeper in to the statistics needed to be able to answer some of the questions you get, or problems that you have to solve, on a day to day basis.

Questions like “Who is taking up all the resources on my database server?” can be quickly and easily answered with either the user summary views:

The reference documentation for sys is currently being worked on (it’s a huge job), but in the mean time, if you want to see the full power of what you get from the sys schema bundled within MySQL 5.7.7, see the README on the GitHub project.

Try it out, and give us your feedback! If you spot any bugs, please report them on http://bugs.mysql.com/ under the new “MySQL Server: SYS Schema” category.