使用性能模式查看资源消耗
设置收集信息
查看当前设置
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;
