MySQL SQL性能分析 Profiling Using Performance Schema

MySQL一直以来提供show profile命令来获取某一条SQL执行过程中的资源使用与耗时情况,这个命令对于分析具体SQL的性能瓶颈有非常大的帮助,但是这个功能在MySQL新的版本里将会被废弃,取而代之的是使用Performance Schema来提供同样的功能。本文将介绍如何使用Performance Schema来实现show profile SQL性能分析的功能。 ### 1. 测试环境配置 测试版本:MySQL 5.7.19 配置参数:performance_schema=ON,该参数配置在my.cnf文件中,生效需要重启MySQL。 ### 2. 配置performance_schema #### 2.1 配置表setup_actors 默认情况下,performance_schema功能打开后,将会收集所有用户的SQL执行历史事件,因为收集的信息太多,对数据库整体性能有一定影响,而且也不利于排查指定SQL的性能问题,因此需要修改setup_actors表的配置,只收集特定用户的历史事件信息。setup_actors表配置如下: mysql> select * from performance_schema.setup_actors; +-----------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +-----------+------+------+---------+---------+ | % | % | % | NO | NO | | localhost | root | % | YES | YES | +-----------+------+------+---------+---------+ 2 rows in set (0.00 sec) 只收集本地root用户的SQL执行历史事件。 #### 2.2 配置表setup_instruments 启用statement和stage监视器。 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%'; #### 2.3 配置表setup_consumers 启用events_statements_*,events_stages_* 开头的事件类型消费。 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%'; ### 3. 收集具体SQL的性能分析 #### 3.1 执行业务SQL 在上述配置完成之后,执行一个需要分析的业务SQL,比如: select * from blog; #### 3.2 获取业务SQL的事件ID 通过以下SQL先查询事件ID。 SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%blog%'; +----------+----------+-------------------------+ | EVENT_ID | Duration | SQL_TEXT | +----------+----------+-------------------------+ | 243 | 0.002698 | select * from blog.blog | +----------+----------+-------------------------+ 1 row in set (0.00 sec) #### 3.3 根据事件ID,获取各阶段执行耗时 根据上一步获取的事件ID(EVENT_ID),查询该SQL各个阶段的耗时情况。如下: SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=243; +--------------------------------+----------+ | Stage | Duration | +--------------------------------+----------+ | stage/sql/starting | 0.000117 | | stage/sql/checking permissions | 0.000010 | | stage/sql/Opening tables | 0.000031 | | stage/sql/init | 0.000055 | | stage/sql/System lock | 0.000024 | | stage/sql/optimizing | 0.000003 | | stage/sql/statistics | 0.000020 | | stage/sql/preparing | 0.000015 | | stage/sql/executing | 0.000001 | | stage/sql/Sending data | 0.002321 | | stage/sql/end | 0.000004 | | stage/sql/query end | 0.000019 | | stage/sql/closing tables | 0.000018 | | stage/sql/freeing items | 0.000048 | | stage/sql/cleaning up | 0.000001 | +--------------------------------+----------+ 15 rows in set (0.00 sec) 上述结果与show profile的输出结果类似,能够看到每个阶段的耗时情况。相对于show profile来说,似乎更加繁琐,不过performance schema是MySQL未来性能分析的趋势,提供了非常丰富的性能诊断工具,熟悉performance schema的使用将有助于更好的优化MySQL。
联系我们

邮箱 626512443@qq.com
电话 18611320371(微信)
QQ群 235681453

Copyright © 2015-2022

备案号:京ICP备15003423号-3