Using the MySQL sys schema to optimize and maintain a database

The MySQL sys schema, which is fully enabled in Azure Database for MySQL 5.7, provides a powerful collection of user friendly views in a read-only database. Building on the MySQL Performance and Information Schemas, you can use the MySQL sys schema to troubleshoot performance issues and manage resources efficiently.

The MySQL Performance Schema, first available in MySQL 5.5, provides instrumentation for many vital server resources such as memory allocation, stored programs, metadata locking, etc. However, the Performance Schema contains more than 80 tables, and getting the necessary information often requires joining tables within the Performance Schema, as well as tables from the Information Schema. Let’s look more closely at how to use the MySQL sys schema.

There are 52 views in the sys schema, and each view is prefixed by one of the following:

◉ Host_summary or IO: I/O related latencies.

◉ Innodb: Innodb buffer status and locks.

◉ Memory: Memory usage by the host and users.

◉ Schema: Schema related information, such as auto increment, indexes, etc.

◉ Statement: Information on SQL statements; this can be statements that resulted in a full table scan or long query time.

◉ User: Resources consumed and grouped by users. Examples are file I/Os, connections, and memory.

◉ Wait: Wait events, grouped by host or user.

Performance tuning

◉ IO is the most expensive operation in the database. We can find out the average IO latency by querying the sys.user_summary_by_file_io view. With the default 125GB of provisioned storage, my IO latency is about 15 seconds.

Because Azure Database for MySQL scales IO with respect to storage, after I increase my provisioned storage to 1TB, my IO latency reduces to 571ms, representing a 26X performance increase!

◉ Despite careful planning, many queries still result in full table scans. Full table scans are resource intensive and degrade your database performance. The quickest way to find tables with full table scan is to query the sys.schema_tables_with_full_table_scans view.

◉ To troubleshoot database performance issues, it may be beneficial to identify the events happening inside of your database, and using the sys.user_summary_by_statement_type view may just do the trick.

In this example, Azure Database for MySQL spent 53 minutes flushing the slog query log 44,579 times. That’s a long time and a lot of IOs. You can reduce this activity by either disabling your slow query log or decreasing the frequency of slow query log in Azure portal.

Database maintenance

◉ The InnoDB buffer pool resides in memory and is the main cache mechanism between the DBMS and storage. The size of the InnoDB buffer pool is tied to the performance tier and cannot be changed unless a different product SKU is chosen. As with memory in your operating system, old pages are swapped out to make room for fresher data. To find out which tables consume most of the InnoDB buffer pool memory, you can query the sys.innodb_buffer_stats_by_table view.

In the graphic above, it is apparent that other than system tables and views, each table in the mysqldatabase033 database, which hosts one of my WordPress sites, occupies 16KB, or 1 page, of data in memory.

◉ Indexes are great tools to improve read performance, but they incur additional costs for inserts and storage. Sys.schema_unused_indexes and sys.schema_redundant_indexes provide insights into unused or duplicate indexes.

In summary, the sys schema is a great tool for both performance tuning and database maintenance. Make sure to take advantage of this feature in your Azure Database for MySQL.