MYSQL SYS SCHEMA IN MARIADB 10.2

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:

1. Views that summarize Performance Schema data into more easily understandable form.
2. Stored procedures that perform operations such as Performance Schema configuration and generating diagnostic reports.
3. 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.

[mysqld]


performance_schema

performance_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:

1. server_uuid to server_id
2. master_info_repository to NULL(just replace it 3 times)
3. 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                       |
+-----------------------------------------------+