mysql性能工具Performance Schema的介绍与使用示例

最新推荐文章于 2024-09-18 14:04:04 发布

星星落在海那边

最新推荐文章于 2024-09-18 14:04:04 发布

阅读量3.3k

点赞数 1

文章标签： mysql 数据库 sql 性能优化

本文链接：https://blog.csdn.net/m0_52586092/article/details/131131275

版权

PERFORMANCE_SCHEMA（官方建议）

performance_schema是MySQL中用于性能分析和诊断的工具，它提供了一系列的表和视图，用于收集和分析MySQL的性能数据。

performance_schema可以监控MySQL的内部运行状态，如CPU使用率、内存使用率、I/O操作等，以及MySQL的各种操作，如查询、锁等。

performance_schema是MySQL中的一个系统库，用于存储MySQL服务器的性能指标数据和状态信息。
该库包含了多个表，用于记录MySQL服务器的各种性能指标数据，如CPU使用率、内存使用率、磁盘I/O、查询执行时间等等。

performance_schema库中的表可以用于查询和分析MySQL服务器的性能指标数据。

通过查询这些表，可以了解MySQL服务器的负载情况、性能瓶颈等信息，以便进行性能优化和故障排查。

注意，Performance Schema的数据收集会对MySQL服务器的性能产生一定的影响，因此在生产环境中使用时需要谨慎。

performance_schema使用具体示例

查看performance_schema启用情况

可使用

SHOW VARIABLES LIKE 'performance_schema';

通过执行这个查询语句，可以查看MySQL服务器中是否启用了Performance Schema功能。

该查询语句中，LIKE 'performance_schema’是一个通配符表达式，用于匹配所有以performance_schema开头的配置参数。

如果查询结果中的Value列显示为ON，则表示MySQL服务器已经启用了Performance Schema功能；
如果查询结果中的Value列显示为OFF，则表示MySQL服务器没有启用Performance Schema功能。

Performance Schema功能默认是关闭的，需要手动开启才能使用。MySQL 5.7开始默认启用。

如何开启performance_schema

要开启MySQL中的Performance Schema功能，需要在MySQL配置文件中进行相应的配置。

下面是具体的步骤：

1.打开MySQL的配置文件my.cnf（或者my.ini），可以使用以下命令查找my.cnf文件的位置：

mysql --help | grep my.cnf

2.在配置文件中添加以下内容：

[mysqld]
performance_schema=ON

这里的[mysqld]表示该配置项适用于MySQL服务器进程，performance_schema=ON表示开启Performance Schema功能。

3.保存并关闭配置文件。
4.重启MySQL服务器，使配置生效。

systemctl restart mysql

5.验证Performance Schema是否已经启用。

SHOW VARIABLES LIKE 'performance_schema';

如果查询结果中的Value列显示为ON，则表示MySQL服务器已经启用了Performance Schema功能。

开启performance_schema工具之后，下面使用这个性能监控工具去实现SHOW PROFILE类似的效果：

查看MySQL服务器中的连接和用户的使用情况

setup_actors表

setup_actors表是Performance Schema中的一个系统表，用于记录MySQL服务器中的所有连接和用户。
该表包含了每个连接和用户的ID、名称、角色等信息，可以用于分析MySQL服务器中的连接和用户的使用情况。

mysql> SELECT * FROM performance_schema.setup_actors;

在这里插入图片描述

通过执行该语句，可以查询setup_actors表中的所有数据。
查询结果包含了每个连接和用户的ID、名称、角色等信息。
这些信息可以用于分析MySQL服务器中的连接和用户的使用情况，以便进行性能优化和安全管理。

需要注意的是，查询setup_actors表需要具备相应的权限，否则会报错。
同时，setup_actors表中的数据可能会随着时间的推移而变化，因此在进行分析和优化时需要结合实际情况进行。

禁用所有连接和用户的历史记录

mysql> UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';

setup_actors表中的ENABLED和HISTORY字段分别表示连接和用户是否启用历史记录功能。通过将这两个字段设置为’NO’，可以禁用所有连接和用户的历史记录功能。

向setup_actors表中添加一个新的连接和用户，并启用历史记录；

mysql> INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','test_user','%','YES','YES');

这个语句的作用是向setup_actors表中添加一个新的连接和用户。
这个新连接和用户的名称是test_user，它可以从本地主机（localhost）连接到MySQL服务器，并拥有所有角色（‘%’)。
同时，这个新连接和用户启用了历史记录功能。

通过上述两个操作，performance_schema就只监控localhost机器上test_user用户发送过来的SQL。而其他主机、其他用户发过来的SQL不监控。

再次查看MySQL服务器中的连接和用户的使用情况

mysql> SELECT * FROM performance_schema.setup_actors;

在这里插入图片描述

开启相关监控项：

在MySQL中，setup_instruments和setup_consumers是Performance Schema中的两个表，用于配置Performance Schema的仪器（instruments）和消费者（consumers）。

setup_instruments表

包含了Performance Schema中所有可用的仪器的信息，包括仪器名称、计数器（counter）的数量、状态等信息。
通过对该表进行修改，可以启用或禁用Performance Schema中的某些仪器。

setup_consumers表

则用于配置Performance Schema的消费者，即Performance Schema的数据输出方式。
通过对该表进行修改，可以将Performance Schema的数据输出到文件、内存表、网络等不同的位置。

需要注意的是，对Performance Schema的配置修改需要谨慎进行，不当的配置可能会影响MySQL的性能或稳定性。建议在进行配置修改前，先备份相关的表和数据，以便出现问题时可以快速恢复。

启用Performance Schema中仪器instruments（统计指标）

启用statement仪器

statement仪器是用于统计SQL语句执行的性能指标数据的仪器。
通过启用statement仪器，可以收集关于SQL语句执行的各种性能指标数据，如执行次数、执行时间、等待时间、锁等待时间等。

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';

ENABLED = 'YES’和TIMED = 'YES’是UPDATE performance_schema.setup_instruments语句中的两个条件，用于启用Performance Schema中某些仪器的计时器（timers）。

ENABLED字段表示该仪器是否启用，'YES’表示启用，‘NO’表示禁用。
通过将ENABLED字段设置为’YES’，可以启用Performance Schema中的某些仪器。

TIMED字段表示该仪器是否启用计时器，'YES’表示启用，‘NO’表示禁用。
通过将TIMED字段设置为’YES’，可以启用Performance Schema中某些仪器的计时器，以便统计其执行时间等性能指标。

因此，ENABLED = ‘YES’, TIMED = 'YES’表示启用Performance Schema中某些仪器，并启用其计时器。
具体来说，该语句启用了所有以statement/开头的仪器，并启用它们的计时器，以便统计MySQL语句执行的性能指标。

'%statement/%'是一个通配符表达式，用于匹配Performance Schema中所有以statement/开头的仪器名称。
具体来说，它匹配了所有与MySQL语句执行相关的仪器，包括statement/sql、statement/execute、statement/com/…等等。

如果你希望只启用某一类语句的仪器，可以根据具体需求修改这个通配符表达式。
例如，如果你只想启用SELECT语句的仪器，可以将通配符表达式修改为’statement/sql/select’或’statement/sql/%select%'等等。

启用stage仪器

stage仪器是用于统计MySQL内部执行阶段的性能指标数据的仪器。
通过启用stage仪器，可以收集关于MySQL内部执行阶段的各种性能指标数据，如排序、聚合、索引扫描、文件排序等。

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';

对应的consumers（收集和存储指标）

可以通过修改Performance Schema中的setup_consumers表中的数据，来启用instruments对应的消费者。默认情况下，某些consumers可能已启用。

启用刚刚开启的两个仪器对应的 events_statements_* 和 events_stages_* 的consumers。

启用 events_statements_* （SQL语句执行）

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';

setup_consumers表中的ENABLED字段表示消费者是否启用。

通过将这个字段设置为’YES’，可以启用所有以events_statements_开头的消费者。
这些消费者主要用于收集和存储SQL语句执行的性能指标数据。

启用events_stages_* （内部执行阶段）

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

这个语句的作用是启用所有以events_stages_开头的消费者。
与第一个语句类似，通过将ENABLED字段设置为’YES’，可以启用所有以events_stages_开头的消费者。
这些消费者主要用于收集和存储MySQL内部执行阶段的性能指标数据。

实例分析：分析特定SQL语句

该实例为：查询emp_no为10001的员工记录）的执行性能，包括整体执行时间以及在MySQL内部执行阶段的性能。

1）从employees数据库中的employees表中查询emp_no为10001的员工记录。执行这个查询语句后，将返回emp_no为10001的员工记录。

mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;

在这里插入图片描述

在进行性能分析前，先看两个概念。
events_statements_history_long表和events_stages_history_long表
这两个都是MySQL Performance Schema中的表，用于收集和存储SQL语句执行的性能指标数据以及MySQL内部执行阶段的性能指标数据。

events_statements_history_long表

这个表用于收集和存储SQL语句执行的性能指标数据。
它包含了每个SQL语句的执行信息，如执行次数、执行时间、等待时间等。

events_statements_history_long表中的一些重要列包括：

EVENT_ID: 事件ID，用于唯一标识每个事件。
SQL_TEXT: SQL语句文本。
TIMER_WAIT: 事件持续时间，以皮秒（1秒=10^12皮秒）为单位。
ROWS_AFFECTED: 受影响的行数。
CREATED_TMP_DISK_TABLES: 创建的临时磁盘表的数量。
CREATED_TMP_TABLES: 创建的临时表的数量。
NO_INDEX_USED: 是否未使用索引。
NO_GOOD_INDEX_USED: 是否未使用好的索引。

events_stages_history_long表

这个表用于收集和存储MySQL内部执行阶段的性能指标数据。
它包含了每个执行阶段的详细信息，如执行时间、等待时间等。
通过查询这个表，可以了解到特定SQL语句在MySQL内部执行过程中的性能表现，从而找到性能瓶颈并进行优化。

events_stages_history_long表中的一些重要列包括：

EVENT_ID: 事件ID，用于唯一标识每个事件。
NESTING_EVENT_ID: 嵌套事件ID，用于关联events_statements_history_long表中的事件。
event_name: 执行阶段名称。
TIMER_WAIT: 事件持续时间，以皮秒（1秒=10^12皮秒）为单位。

在实际使用中，您可能需要结合这两个表的查询结果来分析特定SQL语句的执行性能，以便找到性能瓶颈和优化方向。
需要注意的是，这些表中的数据可能会随着时间的推移而变化，因此在进行分析和优化时需要结合实际情况进行。

2）从Performance Schema的events_statements_history_long表中查询包含10001的SQL语句执行记录，获得语句的EVENT_ID。

查询结果将返回：事件ID（EVENT_ID）、执行持续时间（Duration，以秒为单位）和SQL文本（SQL_TEXT）。

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';

在这里插入图片描述

这一步类似于 SHOW PROFILES。

3）从Performance Schema的events_stages_history_long表中查询嵌套事件ID为31的MySQL内部执行阶段记录。
查询结果将返回执行阶段名称（Stage）和执行持续时间（Duration，以秒为单位）。
这可以帮助您分析特定SQL语句在MySQL内部执行过程中的性能。

mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;

在这里插入图片描述

将这三个查询语句结合起来，可以分析特定SQL语句（如查询emp_no为10001的员工记录）的执行性能，包括整体执行时间以及在MySQL内部执行阶段的性能。

星星落在海那边

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫