大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

如何使用PerformanceSchema查看Profiling

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

10年的平湖网站建设经验,针对设计、前端、开发、售后、文案、推广等六对一服务,响应快,48小时及时工作处理。全网整合营销推广的优势是能够根据用户设备显示端的尺寸不同,自动调整平湖建站的显示方式,使网站能够适用不同显示终端,在浏览器中调整网站的宽度,无论在任何一种浏览器上浏览网站,都能展现优雅布局与设计,从而大程度地提升浏览体验。成都创新互联公司从事“平湖网站设计”,“平湖网站推广”以来,每个客户项目都认真落实执行。

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”有了一定的了解,如果想了解更多相关知识,欢迎关注创新互联行业资讯频道,感谢各位的阅读!


文章标题:如何使用PerformanceSchema查看Profiling
当前链接:http://dzwzjz.com/article/pejhds.html
在线咨询
服务热线
服务热线:028-86922220
TOP