Sys Schema MySQL 5.7+

Hi Guys ,

Its been  more than a month since i come up with my new blogs and topics which are interested to read . Today , I am again going to start an episode of “SYS” schema which i will cover in number of sub topics . SYS schema is  used to monitor your database performance and facilitates easier way to use instruments and consumer ( which i covered in my previous blog ).

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.

So let us start :

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 will create 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 result “test_db”.

——————————-

I will cover each events per blog with real life example of data world & how sys schema can be proved as a alternate way to access performance schema.

——————————

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. https://jinglespreparatorycom.wordpress.com/2018/05/31/mysql-instrument-https://jinglespreparatorycom.wordpress.com/2018/05/03/instruments-in-mysql/

 

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


 

Keep in touch , i will continue Sys schema.

 

Regards

Ankit

 

 

 

 

 

 

 

 

Advertisements

One thought on “Sys Schema MySQL 5.7+

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s