Identifying a Blocking Query After the Issuing Session Becomes Idle

#查看阻塞信息

select * from sys.innodb_lock_waits\G

#根据blocking_pid得到源头会话的最后一个SQL

select a.*,c.thread_id,c.sql_text blocking_sql from sys.innodb_lock_waits a

inner join performance_schema.threads b

on a.blocking_pid=b.processlist_id

inner join performance_schema.events_statements_current c

on b.thread_id = c.thread_id\G

#当前statements不匹配，到历史记录表查询

select * from performance_schema.events_statements_history where thread_id=\G

select * from performance_schema.events_statements_history_long where thread_id=\G

#查看是否开启

select * from performance_schema.setup_consumers;

update performance_schema.setup_consumers set enabled = 'YES' where name = 'events_statements_history_long';

#高频执行的语句

select a.*,b.sql_text,b.digest_text from sys.statement_analysis a

left join performance_schema.events_statements_current b

on a.digest=b.digest

where b.digest is not null

order by exec_count desc\G

select a.*,b.sql_text,b.digest_text from sys.statement_analysis a

left join performance_schema.events_statements_history b

on a.digest=b.digest

where b.digest is not null

order by exec_count desc\G