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 :
“SYS” schema is introduced in 5.7+ versions default . In previous versions : we need to download from GITHUB:
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 :
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:
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 :
- Performance Schema must be enabled.
- 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.