1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查看长时间运行的查询
SELECT id, user, host, db, command, time, state, LEFT(info, 100) as query
FROM information_schema.processlist
WHERE time > 30
  AND command != 'Sleep'
ORDER BY time DESC;

-- 杀死长时间运行的查询
KILL QUERY <process_id>;

1
2
3
4
5

# 找出mysql 的进程号
ps -ef | grep mysql

# 根据进程号，找出占用CPU靠前的线程号
top -H -p <mysqld进程id>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
    a.USER,
    a.HOST,
    a.db,
    b.thread_os_id,
    b.thread_id,
    a.id processlist_id,
    a.command,
    a.time,
    a.state,
    a.info
FROM
    information_schema.PROCESSLIST a,
    performance_schema.threads b
WHERE
    a.id = b.processlist_id
AND b.thread_os_id = <具体线程id>;