找出 OWNER_THREAD_ID为内部线程id,可以查询threads表找出对应会话id:
select * from performance_schema.threads where THREAD_ID in (31,30)\G;
*************************** 1. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140514110025712
LOCK_TYPE: SHARED
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: lock.cc:1110
OWNER_THREAD_ID: 31 持有锁的内部线程id为31
OWNER_EVENT_ID: 43
*************************** 2. row ***************************
OBJECT_TYPE: COMMIT
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140514110303360
LOCK_TYPE: SHARED
LOCK_DURATION: EXPLICIT
LOCK_STATUS: GRANTED
SOURCE: lock.cc:1194
OWNER_THREAD_ID: 31 持有锁的内部线程id为31
OWNER_EVENT_ID: 97
*************************** 3. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140514110036384
LOCK_TYPE: INTENTION_EXCLUSIVE n意向排他锁
LOCK_DURATION: STATEMENT
LOCK_STATUS: PENDING 状态为pending,表示正在等待被授权
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 30 被阻塞
OWNER_EVENT_ID: 2507
3 rows in set (0.04 sec)
找出谁持有MDL锁:
select * from performance_schema.threads where THREAD_ID =30\G;
找出谁持有行级锁:
mysql8
select * from performance_schema.data_locks\G;
mysql 5.7
select * from sys.innodb_lock_waits\G;
查询最近的topsql语句:
select thread_id,event_name,source,sys.format_time(timer_wait),sys.format_time(lock_time),sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined from performance_schema.events_statements_history where current_schema!='performance_schema' order by timer_wait desc limit 10\G;
统计后sql:
select schema_name,digest_text,COUNT_STAR,sys.format_time(sum_timer_wait) as sum_time,sys.format_time(min_timer_wait) as min_time,
sys.format_time(avg_timer_wait) as avg_time,sys.format_time(max_timer_wait) as max_time,sys.format_time(sum_lock_time) as sum_lock_time,sum_rows_affected,sum_rows_sent,sum_rows_examined from performance_schema.events_statements_summary_by_digest where schema_name is not null order by count_star desc limit 10\G;
查看最近执行失败的sql语句:
select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time, sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined ,mysql_errno from performance_schema.events_statements_history where mysql_errno=1064\G;
select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time, sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined ,mysql_errno ,errors from performance_schema.events_statements_history where errors>0\G;
select schema_name,digest_text,
COUNT_STAR
,sys.format_time(sum_timer_wait) as sum_time,sys.format_time(min_timer_wait) as min_time,sys.format_time(avg_timer_wait) as avg_time,sys.format_time(max_timer_wait) as max_time,sys.format_time(sum_lock_time) as sum_lock_time,sum_errors,first_seen,last_seen from performance_schema.events_statements_summary_by_digest where sum_errors!=0\G;
查看sql语句执行阶段和进度信息: