performance_schema实战

// 查看监控的角色
// 看到所有的host，所有用户，所有角色都会被监控
mysql> select * from performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

// 使能性能监控指标
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 213  Changed: 0  Warnings: 0

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
Query OK, 115 rows affected (0.00 sec)
Rows matched: 131  Changed: 115  Warnings: 0

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1  Warnings: 0

mysql> 
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

// 执行操作
mysql> use cara_testing_db; select * from books;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+----+------+--------+
| id | name | author |
+----+------+--------+
|  1 | cara | cara   |
+----+------+--------+
1 row in set (0.00 sec)

// 获取event id
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%books%';
+----------+----------+---------------------+
| EVENT_ID | Duration | SQL_TEXT            |
+----------+----------+---------------------+
|      238 |   0.0006 | select * from books |
+----------+----------+---------------------+
1 row in set (0.03 sec)

// 查看每个stage的耗时
mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=238;
+------------------------------------------------+----------+
| Stage                                          | Duration |
+------------------------------------------------+----------+
| stage/sql/starting                             |   0.0000 |
| stage/sql/Executing hook on transaction begin. |   0.0000 |
| stage/sql/starting                             |   0.0000 |
| stage/sql/checking permissions                 |   0.0000 |
| stage/sql/Opening tables                       |   0.0000 |
| stage/sql/init                                 |   0.0000 |
| stage/sql/System lock                          |   0.0000 |
| stage/sql/optimizing                           |   0.0000 |
| stage/sql/statistics                           |   0.0000 |
| stage/sql/preparing                            |   0.0000 |
| stage/sql/executing                            |   0.0000 |
| stage/sql/end                                  |   0.0000 |
| stage/sql/query end                            |   0.0000 |
| stage/sql/waiting for handler commit           |   0.0000 |
| stage/sql/closing tables                       |   0.0000 |
| stage/sql/freeing items                        |   0.0001 |
| stage/sql/cleaning up                          |   0.0000 |
+------------------------------------------------+----------+
17 rows in set (0.01 sec)