EmergencyEMERGENCY? Get 24/7 Help Now!

Sys Schema for MySQL 5.6 and MySQL 5.7

 | November 20, 2014 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

Installation

If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema.

sys schema mysql workbench

If you don’t use MySQL Workbench you need to download sys_56.sql or sys_57.sql (depends if you use 5.6 or 5.7) from the github repository. Then, just import the sql file as usual:

Usage

After the import, you will have a new “sys” schema with some very descriptive table names. Let’s see an example. Do you want to know what tables are using most of our InnoDB buffer memory? Easy:

Pretty easy and useful, right? You can also get what is the database using more memory in the buffer pool querying innodb_buffer_stats_by_schema.

For each table there is another similar ones that it’s name starts with x$. For example, you have user_summary_by_file_io and x$user_summary_by_file_io. The difference is that the first table has a human readable output values while the second one has the real values. Let’s see an example:

For humans, at least for me, it is easier to read seconds rather than picoseconds 🙂

There are multiple tables with very descriptive names.

– io_by_thread_by_latency
– schema_unused_indexes
– statements_with_temp_tables
– statements_with_errors_or_warnings
– user_summary_by_statement_type
– waits_by_host_by_latency

There are lot more, and they are explained with examples in project’s README file.

Configuration

On the MySQL side nothing special is needed. Just enable performance_schema:

sys schema also provides some procedures to enable/disable some features. For example:

– ps_setup_enable_background_threads
– ps_setup_enable_consumers
– ps_setup_enable_instrument
and so on…

We also have the same procedures with “disable”. After you have made the changes you can save them calling ps_setup_save() and reload it later on if you want calling ps_setup_reload_saved(). If you want to reset the configuration to default values just call ps_setup_reset_to_default().

For example, we can check that some consumers are disabled. It is possible to enable them and save the configuration:

Conclusion

Performance Schema is very useful when we want to know what is happening inside the database. It is getting more features with each new GA and will probably be the single point of information in near future. Now thanks to sys schema it is also easy to use.

PREVIOUS POST
NEXT POST
Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.

6 Comments

  • Lately I’ve found the SYS schema even more useful than pt-query-digests for the silliest of reasons: while the slow log is not enabled by default, the SYS schema just uses the underlying PERFORMANCE_SCHEMA, that is on by default since 5.6. That is a quick way to evaluate bad queries before the consultancy started since minute 1.

    That doesn’t mean that pt-query-digest is no longer useful (not at all), it is still a great tool, but more or less convenient depending on the particular situation. For example, I still prefer it for long-term reporting and analysis.

  • Be careful with the innodb_buffer_stats_by_table. On servers with alot of RAM running that query has correlated with I/O cpu wait bumps in my environment.

  • There is a syntax error on the name of the second procedure:

    mysql> CALL sys.ps_setup_enable_consumers(‘events’);
    ERROR 1305 (42000): PROCEDURE sys.ps_setup_enable_consumers does not exist

    This works (consumer vs. consumerS):
    mysql> CALL sys.ps_setup_enable_consumer(‘events’);
    +———————+
    | summary |
    +———————+
    | Enabled 0 consumers |
    +———————+
    1 row in set (0.00 sec)

  • Is there a way to upgrade sys schema from 1.1 to latest version in MySQL 5.6.12 version? Plese let me know the process to do the same.

Leave a Reply