如何在 Azure Database for MySQL 中使用 sys_schema 进行性能优化和数据库维护How to use sys_schema for performance tuning and database maintenance in Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

MySQL performance_schema 首先在 MySQL 5.5 中推出,针对许多关键服务器资源提供检测数据,例如内存分配、存储的程序、元数据锁定,等等。但是,performance_schema 包含超过 80 多个表,获取必要的信息通常需要联接 performance_schema 和 information_schema 中的表。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. sys_schema 在 performance_schema 和 information_schema 的基础上构建,在一个只读的数据库中提供用户友好视图的强大集合,并且完全在 Azure Database for MySQL 版本 5.7 中启用。Building on both performance_schema and information_schema, the sys_schema provides a powerful collection of user-friendly views in a read-only database and is fully enabled in Azure Database for MySQL version 5.7.

sys_schema 的视图

sys_schema 中有 52 个视图,每个视图具有以下前缀之一:There are 52 views in the sys_schema, and each view has one of the following prefixes:

  • Host_summary 或 IO:与 I/O 相关的延迟。Host_summary or IO: I/O related latencies.
  • InnoDB:InnoDB 缓冲区状态和锁。InnoDB: InnoDB buffer status and locks.
  • 内存: 按主机和用户列出的内存用量。Memory: Memory usage by the host and users.
  • 架构:与架构相关的信息,如增量、索引等。Schema: Schema-related information, such as auto increment, indexes, etc.
  • 语句:有关 SQL 语句(导致全表扫描或长时间查询的语句)的信息。Statement: Information on SQL statements; it can be statement that resulted in full table scan, or long query time.
  • 用户:按用户分组和消耗的资源。User: Resources consumed and grouped by users. 示例包括文件 I/O、连接和内存。Examples are file I/Os, connections, and memory.
  • 等待:等待按主机或用户分组的事件。Wait: Wait events grouped by host or user.

现在,让我们了解 sys_schema 的一些常见使用模式。Now let's look at some common usage patterns of the sys_schema. 首先,我们将使用模式分为两类:“性能调优”和“数据库维护” 。To begin with, we'll group the usage patterns into two categories: Performance tuning and Database maintenance.

性能调优Performance tuning

sys.user_summary_by_file_iosys.user_summary_by_file_io

IO 是数据库中开销最高的操作。IO is the most expensive operation in the database. 我们可以通过查询 sys.user_summary_by_file_io 视图找出平均 IO 延迟。We can find out the average IO latency by querying the sys.user_summary_by_file_io view. 使用 125 GB 默认预配存储时,IO 延迟大约为 15 秒。With the default 125 GB of provisioned storage, my IO latency is about 15 seconds.

sys_schema 的视图

由于 Azure Database for MySQL 可根据存储缩放 IO,将预配存储增大到 1 TB 后,IO 延迟减小为 571 毫秒。Because Azure Database for MySQL scales IO with respect to storage, after increasing my provisioned storage to 1 TB, my IO latency reduces to 571 ms.

sys_schema 的视图

sys.schema_tables_with_full_table_scanssys.schema_tables_with_full_table_scans

尽管经过认真规划,但许多查询仍可能导致全表扫描。Despite careful planning, many queries can still result in full table scans. 有关索引类型及其优化方式的其他信息,请参阅此文:如何排查查询性能问题For additional information about the types of indexes and how to optimize them, you can refer to this article: How to troubleshoot query performance. 全表扫描属于资源密集型操作,会降低数据库性能。Full table scans are resource-intensive and degrade your database performance. 查找执行全表扫描的表的最快方法是查询 sys.schema_tables_with_full_table_scans 视图。The quickest way to find tables with full table scan is to query the sys.schema_tables_with_full_table_scans view.

sys_schema 的视图

sys.user_summary_by_statement_typesys.user_summary_by_statement_type

若要排查数据库性能问题,识别数据库中发生的事件可能很有帮助,而使用 sys.user_summary_by_statement_type 视图就能实现此目的。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.

sys_schema 的视图

在此示例中,Azure Database for MySQL 花费了 53 分钟来刷新 slog 查询日志 44579。In this example Azure Database for MySQL spent 53 minutes flushing the slog query log 44579 times. 此时间很长,并且消耗了大量 IO 资源。That's a long time and many IOs. 可以在 Azure 门户中禁用慢速查询日志或降低慢速查询日志的频率,来减少此类活动。You can reduce this activity by either disable your slow query log or decrease the frequency of slow query login Azure portal.

数据库维护Database maintenance

sys.innodb_buffer_stats_by_tablesys.innodb_buffer_stats_by_table

[!IMPORTANT]

查询此视图可能会影响性能。Querying this view can impact performance. 建议在非高峰营业时间执行此故障排除。It is recommended to perform this troubleshooting during off-peak business hours.

InnoDB 缓冲池驻留在内存中,是 DBMS 与存储之间的主要缓存机制。The InnoDB buffer pool resides in memory and is the main cache mechanism between the DBMS and storage. InnoDB 缓冲池大小与性能层密切相关,除非选择不同的产品 SKU,否则不能更改。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. 若要了解哪些表占用了大部分 InnoDB 缓冲池内存,可以查询 sys.innodb_buffer_stats_by_table 视图。To find out which tables consume most of the InnoDB buffer pool memory, you can query the sys.innodb_buffer_stats_by_table view.

sys_schema 的视图

在上图中,很明显,除系统表和视图以外,mysqldatabase033 数据库中的每个表(托管某个 WordPress 站点)占用了 16 KB 或 1 个页面的内存中数据。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 16 KB, or 1 page, of data in memory.

Sys.schema_unused_indexes & sys.schema_redundant_indexesSys.schema_unused_indexes & sys.schema_redundant_indexes

索引是提高读取性能的极佳工具,但它们确实会产生额外的插入和存储开销。Indexes are great tools to improve read performance, but they do incur additional costs for inserts and storage. Sys.schema_unused_indexessys.schema_redundant_indexes 提供未使用或重复索引的洞察信息。Sys.schema_unused_indexes and sys.schema_redundant_indexes provide insights into unused or duplicate indexes.

sys_schema 的视图

sys_schema 的视图

结论Conclusion

总而言之,sys_schema 是用于优化性能和维护数据库的极佳工具。In summary, the sys_schema is a great tool for both performance tuning and database maintenance. 请务必在 Azure Database for MySQL 中利用此功能。Make sure to take advantage of this feature in your Azure Database for MySQL.

后续步骤Next steps

  • 若要查找同行对你最关心问题的解答,或者要发布新的问题/答案,请访问 MSDN 论坛Stack OverflowTo find peer answers to your most concerned questions or post a new question/answer, visit MSDN forum or Stack Overflow.