温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

如何使用Performance Schema查看Profiling

发布时间:2021-10-27 17:35:48 来源:亿速云 阅读:133 作者:小新 栏目:MySQL数据库

小编给大家分享一下如何使用Performance Schema查看Profiling,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!

SHOW PROFILE可以用来MySQL执行语句时候所使用的资源(诸如IO,上下文切换,CPU,Memory等等),但是从MySQL 5.6.7开始此特性将被移除,而使用Performance Schema代替,如下:

如何使用Performance Schema查看Profiling

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

如何使用Performance Schema查看Profiling

注:此处只为了说明问题,可能还查询到很多其他的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显示的选项几乎一样,只是各项的值好像不太一致。

看完了这篇文章,相信你对“如何使用Performance Schema查看Profiling”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI