使用性能模式查看资源消耗

设置收集信息

查看当前设置

SELECT * FROM performance_schema.setup_actors;

禁用前台线程的历史时间收集和监视

UPDATE performance_schema.setup_actors 
SET ENABLED = 'NO', HISTORY = 'NO' 
WHERE HOST = '%' AND USER = '%';

启用信息监视

INSERT INTO performance_schema.setup_actors
 (HOST,USER,ROLE,ENABLED,HISTORY)
 VALUES('localhost','root','%','YES','YES');

启用语句和阶段监视

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%'; 

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage/%';

启用events_statements_和events_stages_使用者

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements_%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_stages_%';

在监视的账号下运行语句

查看语句资源消耗

查看语句ID

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT 
FROM performance_schema.events_statements_history_long 
WHERE SQL_TEXT like '%10001%'; 
+----------+----------+--------------------------------------------------------+ 
| event_id | duration | sql_text | 
+----------+----------+--------------------------------------------------------+ 
| 31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 | 
+----------+----------+--------------------------------------------------------+

查看语句资源消耗

表结构

mysql> desc events_stages_history_long;
+--------------------+------------------------------------------------+------+-----+---------+-------+
| Field              | Type                                           | Null | Key | Default | Extra |
+--------------------+------------------------------------------------+------+-----+---------+-------+
| THREAD_ID          | bigint(20) unsigned                            | NO   |     | NULL    |       |
| EVENT_ID           | bigint(20) unsigned                            | NO   |     | NULL    |       |
| END_EVENT_ID       | bigint(20) unsigned                            | YES  |     | NULL    |       |
| EVENT_NAME         | varchar(128)                                   | NO   |     | NULL    |       |
| SOURCE             | varchar(64)                                    | YES  |     | NULL    |       |
| TIMER_START        | bigint(20) unsigned                            | YES  |     | NULL    |       |
| TIMER_END          | bigint(20) unsigned                            | YES  |     | NULL    |       |
| TIMER_WAIT         | bigint(20) unsigned                            | YES  |     | NULL    |       |
| WORK_COMPLETED     | bigint(20) unsigned                            | YES  |     | NULL    |       |
| WORK_ESTIMATED     | bigint(20) unsigned                            | YES  |     | NULL    |       |
| NESTING_EVENT_ID   | bigint(20) unsigned                            | YES  |     | NULL    |       |
| NESTING_EVENT_TYPE | enum('TRANSACTION','STATEMENT','STAGE','WAIT') | YES  |     | NULL    |       |
+--------------------+------------------------------------------------+------+-----+---------+-------+

语句

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration 
FROM performance_schema.events_stages_history_long 
WHERE NESTING_EVENT_ID=31;