You are here

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 for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. 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. According to a well informed source the project on GitHub is not dead but the developers have just been working on other priorities. And the source announced another release soon (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 unfortunately did not integrate the sys Schema. Which PERFORMANCE_SCHEMA version is integrated in MariaDB can be found here.

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 instance.

In MariaDB 10.2 the MySQL 5.6 PERFORMANCE_SCHEMA is integrated so 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...

But also the sys_56.sql installation script will cause you some little troubles which 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 'server_uuid'
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 hack to make the sys Schema work I changed the following information:

  • server_uuid to server_id
  • @@master_info_repository to NULL (3 times).
  • @@relay_log_info_repository to NULL (3 times).

For the future the 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 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                       |
+-----------------------------------------------+

One query as an example: Top 10 MariaDB global I/O latency files on my system:

mariadb> SELECT * FROM sys.waits_global_by_latency LIMIT 10;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/innodb/innodb_log_file  |   112 | 674.18 ms     | 6.02 ms     | 23.75 ms    |
| wait/io/file/innodb/innodb_data_file |   892 | 394.60 ms     | 442.38 us   | 29.74 ms    |
| wait/io/file/sql/FRM                 |   668 | 72.85 ms      | 109.05 us   | 20.17 ms    |
| wait/io/file/sql/binlog_index        |    10 | 21.25 ms      | 2.13 ms     | 15.74 ms    |
| wait/io/file/sql/binlog              |    19 | 11.18 ms      | 588.56 us   | 10.38 ms    |
| wait/io/file/myisam/dfile            |    79 | 10.48 ms      | 132.66 us   | 3.78 ms     |
| wait/io/file/myisam/kfile            |    86 | 7.23 ms       | 84.01 us    | 789.44 us   |
| wait/io/file/sql/dbopt               |    35 | 1.95 ms       | 55.61 us    | 821.68 us   |
| wait/io/file/aria/MAI                |   269 | 1.18 ms       | 4.40 us     | 91.20 us    |
| wait/io/table/sql/handler            |    36 | 710.89 us     | 19.75 us    | 125.37 us   |
+--------------------------------------+-------+---------------+-------------+-------------+

Taxonomy upgrade extras: 

Comments

PERFORMANCE_SCHEMA is invented/developed by Oracle/MySQL. So for MariaDB it is a foreign component and not supported too well...

First of all it is relevant which MariaDB version you are using and which (MySQL) PERFORMANCE_SCHEMA version is built into this MariaDB version. This information can be found with the following query:

SQL> SELECT @@version, plugin_name, plugin_auth_version
  FROM information_schema.plugins WHERE plugin_name = 'PERFORMANCE_SCHEMA';
+---------------------+--------------------+---------------------+
| @@version           | plugin_name        | plugin_auth_version |
+---------------------+--------------------+---------------------+
| 10.2.25-MariaDB-log | PERFORMANCE_SCHEMA | 5.6.40              |
+---------------------+--------------------+---------------------+

SQL> SELECT @@version, plugin_name, plugin_auth_version
  FROM information_schema.plugins WHERE plugin_name = 'PERFORMANCE_SCHEMA';
+---------------------+--------------------+---------------------+
| @@version           | plugin_name        | plugin_auth_version |
+---------------------+--------------------+---------------------+
| 10.3.16-MariaDB-log | PERFORMANCE_SCHEMA | 5.6.40              |
+---------------------+--------------------+---------------------+

SQL> SELECT @@version, plugin_name, plugin_auth_version
  FROM information_schema.plugins WHERE plugin_name = 'PERFORMANCE_SCHEMA';
+--------------------+--------------------+---------------------+
| @@version          | plugin_name        | plugin_auth_version |
+--------------------+--------------------+---------------------+
| 10.4.6-MariaDB-log | PERFORMANCE_SCHEMA | 5.6.40              |
+--------------------+--------------------+---------------------+

So you can see that up to MariaDB 10.4 (I tested always the newest available release) the PERFORMANCE_SCHEMA version is 5.6.40.

You can further check the number of tables in PERFORMANCE_SCHEMA (52) compared to MySQL 5.6 (52 tables), MySQL 5.7 (87 tables) and MySQL 8.0 (102 tables). So you can see again MariaDB still uses the old 5.6 PERFORMANCE_SCHEMA version.

If you are using the (adapted) 5.6 script against the newest 10.2, 10.3 and 10.4 MariaDB releases it still works as described above.

Possibly we (the community) should think about branching the sys Schema project for MariaDB???

Shinguzcomment

I could not resist! I have forked the MySQL sys Schema on GitHub.

Shinguzcomment

The story goes on here.
Shinguzcomment