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
toserver_id
@@master_info_repository
toNULL
(3 times).@@relay_log_info_repository
toNULL
(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 | +--------------------------------------+-------+---------------+-------------+-------------+
- Shinguz's blog
- Log in or register to post comments
Comments
PERFORMANCE_SCHEMA in newer MariaDB releases
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: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.6PERFORMANCE_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???MariaDB sys Schema
I could not resist! I have forked the MySQL sys Schema on GitHub.
MariaDB sys Schema