MySQL sys schema is fully functional in Azure Database for MySQL 5.7 and it allows customers to troubleshoot performance issues due to the availability of performance and information schema. Previously, it was the part of MySQL5.5 and was responsible for managing vital resources such as stored programs, memory allocation, and metadata locking. The efficiency of the performance schema was dependent on the tables present in it which one can join together to get certain information.
The above image demonstrates a way of using schema which contains 52 views with various prefixes. Host_summary is related to I/O latencies while memory describes host and users. Likewise, buffer status and locks are associated with Innodb and schema mentions indexes, increments, and other schema related information. User and wait events mention resources grouped by user and events respectively.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Profiling slow MySQL queries in Azure with EXPLAIN. [/su_note]
Sys.user_summary_by_file_io view is used to find out average IO latency and it’s the most expensive operation in the database. 15 seconds are required for a default 125GB storage due to the fact that it scales IO with respect to storage. Increase capacity to reduce IO latency and boost overall performance.
A full table scan is another problem in SQL Database which is resource intensive and reduces the speed. sys.schema_tables_with_full_table_scans query is helpful in this regard:
The sys.user_summary_by_statement_type view helps you to troubleshoot performance problems and events inside the database:
The above example demonstrates how much time consumed the query as it took 53 minutes and slogged the query log for 44, 579 times. The speed can be increased by disabling slow query log or decreasing the frequency of the query log.
[su_note note_color=”#eeeeee” text_color=”#151212″]Bonus Tip: How to auto-scale Azure SQL. [/su_note]
InnoDB buffer pool works as the main cache between storage and DBMS which can be found in the memory. One needs to choose different product SKUs to change the size of InnoDB buffer pool as it’s strictly attached to performance tier. You can inspect the data by querying sys.innodb_buffer_stats_by_table view:
Indexes are another way of improving the read performance. However, it extra costs are involved for storage and inserting. Get an insight into the supplicated content by querying Sys.schema_unused_indexes and sys.schema_redundant_indexes:
Sys schema is a great tool to take advantage of for both performance tuning and database maintenance in Azure Database for MySQL.
