In last topic i tried to cover Sys schema introduction and how it is useful for a DBA in daily task :

Continuing this further :

As you know that sys schema helps DBA to get insight into what Mysql is doing with the help of its inbuilt views,stored procedure and functions.

Now to know which object is available , you can use information schema with help of below 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’;

Now to check version of sys you are using;

select sys_version from sys.version;

Now , if i want to  check the file I/O by hostname and latency, I can use below view and it will display data in picoseconds which is really useful for optimising large scale application.

host_summary_by_file_io;

Now going more inside , if I want to see each stage of long running DML and its status, I can use views like processlist, session. To use this concerned instruments and consumers must be enabled, as i described in my earlier sessions related to the performance schema.

For example, if I would like to see current progress on alter table operation, I can use views like host_summary_by_stages for below mentioned stages :

1. copying to tmp table
2. alter table (end)
3. alter table (sort)

We can also check lock latency, total rows examined, whether it is a full scan etc and then DBA can further optimise the query.

Please note , you cannot use mysqldump to take dump of sys schema rather you need to declare sys schema explicitly.

mysqldump -u -p —databases —routines sys > sys.sql

mysqlpump sys >sys.sql

Naming convention :
On doing show tables in sys schema, for every view there will be one more view prefixed with x$ which means that output is in human readable format.

Conclusion –
In this topic, we have learnt how sys schema can be useful for us if we don’t want to make use of the performance schema.