Sys系统库 · MySQL 5.8 MySQL 配置详解

为了让大家更容易理解sys系统库及其在MySQL性能问题排查中的重要性，我们将基于MySQL 5.8来讨论如何使用sys系统库进行常见的数据库性能问题排查，并通过一些简单的例子加以说明。

什么是sys系统库？

sys系统库是MySQL中的一个辅助库，专门为数据库管理员（DBA）设计，旨在帮助更轻松地分析和解决数据库性能问题。它基于performance_schema，通过各种预定义的视图和函数，简化了性能数据的访问和分析。如果手动查询performance_schema中的表数据，这个过程可能较为复杂，而sys系统库将这些复杂查询封装为便捷的视图和函数，使得DBA可以更快获得有价值的性能信息。

为什么先介绍 performance_schema？

performance_schema是MySQL内置的性能监控和诊断工具。sys系统库的大部分数据都依赖于performance_schema，因此要理解sys系统库，首先需要启用并熟悉performance_schema。通过它，MySQL可以收集到关于数据库各个层面的性能数据，比如查询的执行时间、等待事件、锁等待、I/O操作等。

sys系统库使用的基础环境

MySQL版本要求

sys系统库支持 MySQL 5.6 及以上版本。需要使用 MySQL 5.8 或更高版本，因为这些版本已经充分支持sys系统库的所有功能。MySQL 5.5及以下版本不支持sys系统库。

要查看当前的MySQL版本，可以使用以下命令：

SELECT VERSION();

+-----------+
| VERSION() |
+-----------+
| 5.8.31    |
+-----------+

启用**performance_schema**

sys系统库的数据来源于performance_schema，所以必须启用performance_schema。performance_schema用于收集数据库运行时的性能数据，并将其存储在内存中以供分析。

如何启用performance_schema？

在MySQL 5.8中，默认情况下performance_schema通常是启用的。可以使用以下命令来确认它是否启用：

SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

如果输出结果为OFF，则可以通过以下方式启用它：

修改MySQL配置文件（my.cnf或my.ini）：

[mysqld]
performance_schema = ON

用户权限要求

要完全使用sys系统库，用户需要具备一些特定权限：

• SELECT 权限：用于查询sys库中的所有表和视图。
• EXECUTE 权限：用于执行sys系统库中的存储过程和函数。
• INSERT 和 UPDATE 权限：对sys_config表需要插入和更新权限。
• 某些存储过程（例如ps_setup_save()）可能还需要临时表相关的权限。

如何为用户分配权限？

假设有一个名为dba_user的用户，可以使用以下命令为其分配权限：

GRANT SELECT ON sys.* TO 'dba_user'@'localhost';
GRANT EXECUTE ON sys.* TO 'dba_user'@'localhost';
GRANT INSERT, UPDATE ON sys.sys_config TO 'dba_user'@'localhost';

查询当前用户的权限：

SHOW GRANTS FOR 'dba_user'@'localhost';

访问相关表的权限

sys系统库访问并处理其他系统库中的表数据，如performance_schema、INFORMATION_SCHEMA等。因此，用户还需要具备以下权限：

• SELECT 权限：需要访问performance_schema的表和视图。
• PROCESS 权限：用于访问INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表，该表提供关于InnoDB缓冲池页面的信息。

如果需要为用户添加PROCESS权限，可以执行：

GRANT PROCESS ON *.* TO 'dba_user'@'localhost';

然后，查询INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表：

SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 10;

此查询会返回InnoDB缓冲池页面的相关信息。

启用所需的**instruments**和**consumers**

sys系统库依赖performance_schema中的instruments（性能事件监控）和consumers（性能数据消费者）来收集不同的性能事件。要确保sys系统库可以正常工作，需要启用以下instruments和consumers：

• wait instruments：监控等待事件，如锁等待。
• stage instruments：监控查询的各个执行阶段。
• statement instruments：监控SQL语句的执行信息。
• 对于以上类型事件，启用其对应的current（当前事件）和history_long（历史长事件）的consumers。

启用这些配置：

可以通过sys库中的存储过程来启用这些配置，而不必手动执行复杂的SQL语句：

CALL sys.ps_setup_enable_instrument('wait');
CALL sys.ps_setup_enable_instrument('stage');
CALL sys.ps_setup_enable_instrument('statement');
CALL sys.ps_setup_enable_consumer('current');
CALL sys.ps_setup_enable_consumer('history_long');

这些命令会启用所有相关的instruments和consumers，使sys库能够全面收集和处理性能数据。

操作示例： 假设想查看某些查询的执行时间，可以启用statement监控并运行以下查询：

CALL sys.ps_setup_enable_instrument('statement');
SELECT * FROM sys.statement_analysis;

sys.statement_analysis视图会显示当前SQL语句的执行次数、平均执行时间等重要性能指标。

恢复默认配置

在调试或分析性能问题时，可能会启用过多的instruments或consumers，这可能会对性能产生影响。在这种情况下，可以通过sys系统库的存储过程快速恢复performance_schema的默认配置。

恢复默认配置：

CALL sys.ps_setup_reset_to_default(TRUE);

这个命令将重置performance_schema中的所有instruments和consumers，恢复其默认的性能数据收集配置，避免过多的性能开销。

举例说明：

假设数据库突然出现性能瓶颈，可以通过启用sys系统库的分析工具来快速找出原因。例如，可以启用查询阶段监控来查看哪些SQL语句在执行过程中出现了瓶颈：

• 启用stage监控：

CALL sys.ps_setup_enable_instrument('stage');

• 查询当前查询阶段的分析数据：

SELECT * FROM sys.x$host_summary_by_stage;

这将显示按主机汇总的SQL语句在不同执行阶段的性能数据。如果某个阶段耗时过长（如解析阶段或执行阶段），可以进一步分析该阶段的性能瓶颈。

使用sys系统库的初体验

在使用sys系统库时，可以通过USE sys;将sys库设为默认库，然后就可以像查询普通表一样查询sys库中的视图和函数。此外，也可以使用全称访问库中的对象，如sys.view_name或sys.function_name。其中很多视图是成对出现的：带有x$前缀的视图显示的是未经处理的原始数据，而不带x$前缀的视图则展示了经过单位换算后的数据（如将时间从纳秒转换为秒或分钟）。

举个例子：

假如想查看某台服务器上的文件I/O性能，可以执行：

SELECT * FROM sys.host_summary_by_file_io;

它会以更加友好的方式展示I/O性能数据，包括读写次数、平均延迟等。如果需要查看原始数据，可以查询带有x$前缀的版本：

SELECT * FROM sys.x$host_summary_by_file_io;

你会看到这些原始数据以皮秒为单位，适用于更精细的分析。

进度报告功能

从MySQL 5.7.9版本开始，sys系统库加入了进度报告功能。这一功能可以帮助DBA监控长时间运行的事务或查询的进度信息，特别是在数据库负载较重的情况下，这一功能非常实用。

sys系统库通过processlist和session视图提供了进度信息：

• processlist视图：展示所有前台和后台线程的当前事件信息。
• session视图：过滤掉了后台线程和Daemon类型的线程，专注于前台会话的进度。

举个例子：

假如有一个查询运行了很长时间，可以通过sys库来查看它的执行进度。启用events_stages_current后，可以运行以下查询来获取某个事务的当前阶段以及执行进度：

ELECT * FROM sys.session WHERE trx_state = 'ACTIVE';

这将显示所有当前正在执行的事务的进度信息，例如某个事务可能显示为50%，意味着它已完成一半工作。通过这种方式，可以更好地了解长时间运行查询的执行情况。

结论

sys系统库通过封装复杂的performance_schema查询，提供了一套简便易用的工具集，大大降低了数据库性能分析的复杂度。无论是了解查询执行情况、事务进度，还是分析系统负载，sys系统库都可以帮助DBA迅速找到问题的根源。通过合理配置和使用sys系统库，可以更轻松地维护和优化MySQL数据库。