为了让大家更容易理解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 前缀的视图显示的是未经处理的原始数据,而不带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数据库。