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

news/2024/12/15 2:43:02/

为了让大家更容易理解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_schemaperformance_schema用于收集数据库运行时的性能数据,并将其存储在内存中以供分析。

如何启用performance_schema

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

SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

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

修改MySQL配置文件(my.cnfmy.ini):

[mysqld]
performance_schema = ON

用户权限要求

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

  • SELECT 权限:用于查询sys库中的所有表和视图。
  • EXECUTE 权限:用于执行sys系统库中的存储过程和函数。
  • INSERTUPDATE 权限:对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_schemaINFORMATION_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');

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

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

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

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

恢复默认配置

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

恢复默认配置:
CALL sys.ps_setup_reset_to_default(TRUE);

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

举例说明:

假设数据库突然出现性能瓶颈,可以通过启用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数据库


http://www.ppmy.cn/news/1555182.html

相关文章

前端如何安全存储密钥,防止信息泄露

场景 把公钥硬编码在前端代码文件里,被公司安全检测到了要整改,于是整理几种常见的前端密钥存储方案。 1. 设置环境变量再读取 在打包或部署前端应用时,可以将密钥配置为环境变量,在应用运行时通过环境变量读取密钥。这样可以将…

僵尸网络开发了新的攻击技术和基础设施

臭名昭著的 Quad7 僵尸网络(也称为 7777 僵尸网络)不断发展其运营,最近的发现表明其目标和攻击方法都发生了重大变化。 根据 Sekoia.io 的最新报告,Quad7 的运营商正在开发新的后门和基础设施,以增强僵尸网络的弹性&a…

基于HTML的个人博客系统的设计与实现

一、前言 随着互联网的飞速发展,人们分享生活、表达观点和展示自我的需求日益增长。个人博客作为一种重要的网络交流平台,为用户提供了便捷的信息发布和分享渠道。它不仅可以记录个人的成长经历、专业知识、兴趣爱好等,还能促进用户之间的互动…

Rust学习路线图

‌Rust是一种现代的系统编程语言,专注于性能、安全性和并发性。它在没有垃圾回收器的情况下实现了这些目标,使其成为许多其他语言不擅长的用例中的有用语言。其语法与C相似,但Rust在保持高性能的同时提供了更好的内存安全性。 获取路线图 你…

群控系统服务端开发模式-应用开发-生成用户操作日志

一、操作日志记录方法 在根目录下app文件夹下controller文件夹下找到Base.php文件,在最下面添加如下代码: /*** 操作日志记录* User: 龙哥三年风水* Date: 2024/12/12* Time: 17:11* param $tokenType 操作类型* param $menuName 权限名称*/protected …

大数据挖掘建模平台案例分享

大数据挖掘建模平台是由泰迪自主研发,面向企业级用户的大数据挖掘建模平台。平台采用可视化操作方式,通过丰富内置算法,帮助用户快速、一站式地进行数据分析及挖掘建模,可应用于处理海量数据、高复杂性的数据挖掘任务,…

opencv礼帽和黑帽运算

礼帽 原始输入 - 开运算结果,留存的以白色毛刺为主 黑帽 闭运算 - 原始输入,保留的更多是原始轮廓 # 导入OpenCV库,用于图像处理 import cv2 import numpy as np # 从matplotlib库中导入pyplot模块,用于绘制图像 from …

差异基因富集分析(R语言——GOKEGGGSEA)

接着上次的内容,上篇内容给大家分享了基因表达量怎么做分组差异分析,从而获得差异基因集,想了解的可以去看一下,这篇主要给大家分享一下得到显著差异基因集后怎么做一下通路富集。 1.准备差异基因集 我就直接把上次分享的拿到这…