怎样使用Performance Schema查看Profiling
发布时间：2022-01-12 12:21:45 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下如何使用Performance Schema查看Profiling，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ SHOW PROFILE可以用来MySQL执行语句时候所使用的资源（诸如IO，上下文切换，CPU，Memory等等）,但是从MySQL 5.6.7开始此特性
|
小编给大家分享一下如何使用Performance Schema查看Profiling，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ SHOW PROFILE可以用来MySQL执行语句时候所使用的资源（诸如IO，上下文切换，CPU，Memory等等）,但是从MySQL 5.6.7开始此特性将被移除，而使用Performance Schema代替，如下： setup_actors 配置 MySQL 5.7.8, 可以通过setup_actors表来配置host, user, or account的信息被收集，如下： #默认情况下setup_actors 的配置是允许监控和收集所有前台线程的历史事件信息 >SELECT * FROM performance_schema.setup_actors; +------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +------+------+------+---------+---------+ | % | % | % | YES | YES | +------+------+------+---------+---------+ 1 row in set (0.03 sec) #修改默认的配置，关闭对所有前台线程的监控和收集。并且插入新的行为指定的用户开启监控和收集信息 mysql> UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' -> WHERE HOST = '%' AND USER = '%'; mysql> INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) -> VALUES('localhost','test_user','%','YES','YES'); #修改后的配置如下： mysql> SELECT * FROM performance_schema.setup_actors; +-----------+-----------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +-----------+-----------+------+---------+---------+ | % | % | % | NO | NO | | localhost | test_user | % | YES | YES | +-----------+-----------+------+---------+---------+ #只监控和收集test_user@localhost用户相关的事件信息 Query Profiling Using Performance Schema 下文简单尝试下使用Performance Schema来查询profile相关信息，使用方法如下 1.开启setup_instruments表中statement和stage instrumentation相关配置,其中有些可能默认已经开启 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' -> WHERE NAME LIKE '%statement/%'; mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' -> WHERE NAME LIKE '%stage/%'; 2.开启events_statements_* and events_stages_*相关消费（consumers），有些项目已经默认开启 mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' -> WHERE NAME LIKE '%events_statements_%'; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' -> WHERE NAME LIKE '%events_stages_%'; 3.为了与show profile的结果做对比，开启profiling功能 mysql >set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql >show warnings; #此处，也可以看到此特性将被移除的警告 +---------+------+----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------+ | Warning | 1287 | '@@profiling' is deprecated and will be removed in a future release. | +---------+------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) 4.执行SQL语句 mysql >select * from t; +----+------+ | 9 | 15 | | 10 | 15 | | 2 | 20 | | 3 | 20 | | 8 | 25 | +----+------+ 5 rows in set (0.00 sec)5 rows in set (0.00 sec) 5.查看profiling结果 mysql>show profiles; +----------+------------+-----------------+ | Query_ID | Duration | Query | +----------+------------+-----------------+ | 1 | 0.00010150 | show warnings | | 2 | 0.00032075 | select * from t | +----------+------------+-----------------+ 2 rows in set, 1 warning (0.00 sec) mysql>show profile for query 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000038 | | checking permissions | 0.000009 | | Opening tables | 0.000048| | init | 0.000022 | | System lock | 0.000012 | | optimizing | 0.000007 | | statistics | 0.000016 | | preparing | 0.000015 | | executing | 0.000005 | | Sending data | 0.000063 | | end | 0.000008 | | query end | 0.000009 | | closing tables | 0.000013 | | freeing items | 0.000012 | | cleaning up | 0.000050 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec) 6.查找刚才执行SQL的EVENT_ID,这步骤类似于show profiles查看query id.通过查询表events_statements_history_long获得对应的EVENT_ID 注：此处只为了说明问题，可能还查询到很多其他的SQL，但是我们自己知道我们执行的SQL是哪条，其他的SQL此处都被省略了 7.通过查询events_stages_history_long表（NESTING_EVENT_ID=EVENT_ID）获得最终结果 mysql>SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration >FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=79; +--------------------------------+----------+ | Stage | Duration | +--------------------------------+----------+ | stage/sql/init | 0.000048 | | stage/sql/checking permissions | 0.000008 | | stage/sql/Opening tables | 0.000051 | | stage/sql/init | 0.000019 | | stage/sql/System lock | 0.000012 | | stage/sql/optimizing | 0.000006 | | stage/sql/statistics | 0.000016 | | stage/sql/preparing | 0.000015 | | stage/sql/executing | 0.000004 | | stage/sql/Sending data | 0.000066 | | stage/sql/end | 0.000005 | | stage/sql/query end | 0.000008 | | stage/sql/closing tables | 0.000013 | | stage/sql/freeing items | 0.000011 | | stage/sql/cleaning up | 0.000001 | +--------------------------------+----------+ 15 rows in set (0.01 sec) 如上，实现了通过Performance Schema来查询profileing相关信息，最终能看到的选项跟show profile显示的选项几乎一样，只是各项的值好像不太一致。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
