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.
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:
mysql -u root -p < ./sys_56.sql
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:
mysql> select * from sys.innodb_buffer_stats_by_table; +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+ | test | t | 63.61 MiB | 58.06 MiB | 4071 | 4071 | 4071 | 2101222 | | InnoDB System | SYS_FOREIGN | 32.00 KiB | 0 bytes | 2 | 2 | 2 | 0 | | InnoDB System | SYS_TABLES | 32.00 KiB | 1.11 KiB | 2 | 2 | 2 | 10 | | InnoDB System | SYS_COLUMNS | 16.00 KiB | 4.68 KiB | 1 | 1 | 1 | 71 | | InnoDB System | SYS_DATAFILES | 16.00 KiB | 324 bytes | 1 | 1 | 1 | 6 | | InnoDB System | SYS_FIELDS | 16.00 KiB | 722 bytes | 1 | 1 | 1 | 17 | | InnoDB System | SYS_INDEXES | 16.00 KiB | 836 bytes | 1 | 1 | 1 | 12 | | InnoDB System | SYS_TABLESPACES | 16.00 KiB | 318 bytes | 1 | 1 | 1 | 6 | | mysql | innodb_index_stats | 16.00 KiB | 274 bytes | 1 | 1 | 1 | 3 | | mysql | innodb_table_stats | 16.00 KiB | 53 bytes | 1 | 1 | 1 | 1 | +---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
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:
mysql> select * from sys.user_summary_by_file_io; +------------+-------+------------+ | user | ios | io_latency | +------------+-------+------------+ | root | 19514 | 2.87 s | | background | 5916 | 1.91 s | +------------+-------+------------+ 2 rows in set (0.00 sec) mysql> select * from sys.x$user_summary_by_file_io; +------------+-------+---------------+ | user | ios | io_latency | +------------+-------+---------------+ | root | 19514 | 2871847094292 | | background | 5916 | 1905079715132 | +------------+-------+---------------+
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:
performance_schema="on"
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:
mysql> CALL sys.ps_setup_show_disabled_consumers();
+--------------------------------+
| disabled_consumers |
+--------------------------------+
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_statements_history |
| events_statements_history_long |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
+--------------------------------+
mysql> CALL sys.ps_setup_enable_consumers('events');
+---------------------+
| summary |
+---------------------+
| Enabled 8 consumers |
+---------------------+
mysql> CALL sys.ps_setup_show_disabled_consumers();
Empty set (0.00 sec)
mysql> CALL sys.ps_setup_save(TRUE);
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.
The post Sys Schema for MySQL 5.6 and MySQL 5.7 appeared first on MySQL Performance Blog.