MySQL8.0线程排障实用SQL

分享常用的MySQL8.0线程排障实用SQL。

杀掉sleep的会话
拼接杀掉sleep的会话

SELECT GROUP_CONCAT(CONCAT('kill ',id) SEPARATOR '; ') AS cmd FROM information_schema.processlist 
WHERE USER='xxx' 
AND command='Sleep'
AND db='xxx'

查找阻塞(常用)
8.0里：

SELECT
b.trx_mysql_thread_id as 被阻塞id,
b.trx_query as 被阻塞sql,
c.trx_mysql_thread_id as 阻塞id,
c.trx_query as 阻塞sql,
d.OBJECT_SCHEMA,
d.OBJECT_NAME,
d.INDEX_NAME
FROM performance_schema.data_lock_waits a
LEFT JOIN
information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id
LEFT JOIN
information_schema.INNODB_TRX c ON a.BLOCKING_ENGINE_TRANSACTION_ID = c.trx_id
LEFT JOIN
performance_schema.data_locks d ON a.REQUESTING_ENGINE_LOCK_ID = d.ENGINE_LOCK_ID

其中trx_mysql_thread_id与processlist中id相同，用于kill。

查看占元数据锁的线程

SELECT a.OWNER_THREAD_ID,b.PROCESSLIST_INFO,b.processlist_id
from performance_schema.metadata_locks a
INNER join performance_schema.threads b 
ON a.OWNER_THREAD_ID = b.THREAD_ID
WHERE a.object_name = 'xxx'

查找阻塞头的id
8.0查找阻塞头的id，以便kill

SELECT a.*
FROM information_schema.PROCESSLIST a
inner join performance_schema.threads b
on a.ID = b.PROCESSLIST_ID
inner join performance_schema.data_lock_waits c
on b.THREAD_ID = c.BLOCKING_THREAD_ID
inner join performance_schema.threads d
on d.THREAD_ID = c.REQUESTING_THREAD_ID
where d.PROCESSLIST_ID = 被阻塞的会话id

查看未提交的事务

select t1.id, t2.thread_id, t3.sql_text
from information_schema.processlist t1, 
 performance_schema.threads t2,
performance_schema.events_statements_current t3
where t1.DB='scm'
and t1.id=t2.processlist_id 
and t2.thread_id = t3.thread_id

未完待续