2nd post is here

While working on Performance schema for my customers, I have realised the importance of sys schema which is rarely being used. In this blog, I will cover 2 articles on “SYS” schema. SYS schema is  used to monitor your database performance and facilitates easier way to use instruments and consumer.

You can find sys schema in MySQL 5.7 by querying :

show databases;

“SYS” schema is introduced in 5.7+ versions default . In previous versions : we need to download from GITHUB:

https://github.com/mysql/mysql-sys

Previously, this sys schema was known by ps_helper which was deigned by Mark Leith.

SYS schema is a collection of various objects i.e. Procedures, functions, Triggers,Views etc which help us to gather information required for troubleshooting issues like slow queries or inspection of any instrument and can be diagnosed based on the gathered information.

So, when i say view, I mean that in “sys” schema we have varieties of views which are derived from Performance schema’s tables. For example : host_summary_by_file_io

When i say procedures , i mean sys schemas has various procedure which allow us to perform various task . Take example of “create_synonym_db(‘db_name’, ‘synonym_name’)“.  This procedure creates a duplicate database with all the objects present in it.

When i say function , i mean sys schemas contains various functions to achieve a specific task. Take an example of “extract_schema_from_file_name“

So  if i want to know schema name of a particular data file , i can use this function . For example:

SELECT extract_schema_from_file_name(‘/data/data_dir/test_db/t1.ibd’);

this will provide result “test_db“.

Now , to use sys schema , one should remember :

1. Performance Schema must be enabled.
2. Wait,Stage,Statement instrument must be enabled. If you want to know how to enable this , read my previous blogs on MySQL instrument.

Alternatively , we can use inbuilt function of sys schema ( ps_setup_enable_instrument) to enable it.

Conclusion–
Rare topics have covered the sys schema and its insights. This blogs gives an introduction to the sys schema.