转载

使用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

使用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)