MySQL has introduced
the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL
5.6 and added some enhancements in MySQL 5.7 and 8.0.
Unfortunately the
PERFORMANCE_SCHEMA was not really intuitive or effective for the broader
audience and didn’t provide the deep details easily. Therefore Mark Leith
introduced the sys Schema for an easier
access for the Developer, DBA and DevOps and Then Daniel Fischer has enhanced
it further. sys schema is a set of
objects that helps DBAs and developers interpret data collected by the
Performance Schema. sys schema objects can be used for typical tuning and
diagnosis use cases. Objects in this schema include:
- Views that summarize Performance Schema data into more easily understandable form.
- Stored procedures that perform operations such as Performance Schema configuration and generating diagnostic reports.
- Stored functions that query Performance Schema configuration and provide formatting services.
For latest
installations, The sys Schema is installed by default during data directory
initialization and is not comes separately for now.
Fortunately the sys
Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it
for MariaDB as well. The version of the
sys Schema in MySQL 8.0 is 1.6.0 and seems not to be on GitHub yet. But you can
extract it from the MySQL 8.0 directory structure:
mysql-8.0/share/mysql_sys_schema.sql.
The GitHub developers are just been working on other priorities. and I
believe they will announced another release soon (I think they are working on
it at the moment).
MariaDB has integrated the PERFORMANCE_SCHEMA
based on MySQL 5.6 into its own MariaDB 10.2
server but they did not integrate the sys Schema. Which PERFORMANCE_SCHEMA
version is integrated in MariaDB can be found By this Link.
To install the sys
Schema into MariaDB we first have to check if the PERFORMANCE_SCHEMA is
activated in the MariaDB server:
mariadb> SHOW GLOBAL VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+
To enable the
PERFORMANCE_SCHEMA just add the following line to your my.cnf:
[mysqld]performance_schema = 1
and restart the
Services.
If restarting mysql service gives an error about performance schema then you can add below code to my.cnf file it will work the same way.
If restarting mysql service gives an error about performance schema then you can add below code to my.cnf file it will work the same way.
[mysqld]performance_schemaperformance_schema_events_statements_history_long_size=10000
If you don't know on the location of My.cnf, the default location is /etc/mysql/my.cnf, for RHEL Servers it will located at /etc/my.cnf, for windows server it is my.ini and it will located at (mysql\bin\my.ini)
if are using some other servers or unable to find it you can just search for it on the internet.
In MariaDB 10.2 the
MySQL 5.6 PERFORMANCE_SCHEMA is integrated so we can not to Install sys_57.sql Instead of it we have to run the sys_56.sql
installation script. If you try to run the sys_57.sql script you will get a lot
of errors which are very difficult to solve.
But also the
sys_56.sql installation script also have some little troubles which but these are are
easy to fix:
unzip mysql-sys-1.5.1.zip mysql -uroot < sys_56.sql
ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable '
ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'master_info_repository'
ERROR 1193 (HY000) at line 20 in file: './procedures/diagnostics.sql': Unknown system variable 'relay_log_info_repository'
For a quick fix and make the sys Schema work I changed the following information:
- server_uuid to server_id
- master_info_repository to NULL(just replace it 3 times)
- relay_log_info_repository to NULL (just replace it 3 times)
For the future the DB community has to think about if the sys Schema should be aware of the 2
branches MariaDB and MySQL and act accordingly or if the sys Schema has to be
forked to work properly for MariaDB and implement MariaDB specific
functionality.
When the sys Schema
finally is installed properly. you have the following tables to get your performance
metrics:
mariadb> use sys mariadb> SHOW TABLES; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | | host_summary_by_file_io | | host_summary_by_file_io_type | | host_summary_by_stages | | host_summary_by_statement_latency | | host_summary_by_statement_type | | innodb_buffer_stats_by_schema | | innodb_buffer_stats_by_table | | innodb_lock_waits | | io_by_thread_by_latency | | io_global_by_file_by_bytes | | io_global_by_file_by_latency | | io_global_by_wait_by_bytes | | io_global_by_wait_by_latency | | latest_file_io | | metrics | | processlist | | ps_check_lost_instrumentation | | schema_auto_increment_columns | | schema_index_statistics | | schema_object_overview | | schema_redundant_indexes | | schema_table_statistics | | schema_table_statistics_with_buffer | | schema_tables_with_full_table_scans | | schema_unused_indexes | | session | | statement_analysis | | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | | sys_config | | user_summary | | user_summary_by_file_io | | user_summary_by_file_io_type | | user_summary_by_stages | | user_summary_by_statement_latency | | user_summary_by_statement_type | | version | | wait_classes_global_by_avg_latency | | wait_classes_global_by_latency | | waits_by_host_by_latency | | waits_by_user_by_latency | | waits_global_by_latency | +-----------------------------------------------+
