适用范围

操作系统：Linux
数据库版本：MySQL 8

概述

当mysql数据库在运行CPU使用率很高时，需要找到是什么SQL消耗了很多的CPU，下面就是本课程中介绍的通过操作系统工具结合mysql性能视图来找到top sql的一种方法。

操作步骤

1）使用 top -H 命令找到cpu使用率高的线程（mysql数据库是单进程多线程，不能通过查找cpu高的进程来获取top sql）
如下可以看到5575的mysqld线程占用了98%的CPU

2）查看performance_schema.threads 性能视图查找该线程的详细信息

mysql> select * from performance_schema.threads where THREAD_OS_ID=5575\G;
*************************** 1. row ***************************
          THREAD_ID: 145
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 11
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: xhy
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 216
  PROCESSLIST_STATE: executing
   PROCESSLIST_INFO: select count(*) from t t1,t t2 where t1.a<>t2.a
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 5575
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

ERROR: 
No query specified

可以看到5575的线程执行的SQL语句为select count(*) from t t1,t t2 where t1.a<>t2.a，执行了216秒，还正在执行。
下面通过kill 杀掉该线程应急来降低CPU
使用kill query 线程id(PROCESSLIST_ID值)杀掉该查询语句（会话还存在）

mysql> kill query 11;
Query OK, 0 rows affected (0.00 sec)

杀掉之后，CPU降到了10% 以下。

如果使用kill (PROCESSLIST_ID值)方式可以看到会话可能会重启，原来语句继续执行。

下面我们再看下5094的线程(占用了7.6%的CPU)

mysql> select * from performance_schema.threads where THREAD_OS_ID=5094\G;
*************************** 1. row ***************************
          THREAD_ID: 70
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 8
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: xhy
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 2599
  PROCESSLIST_STATE: waiting for handler commit
   PROCESSLIST_INFO: insert into t values(i, i, i)
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 5094
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

ERROR: 
No query specified

可以看到5094的线程执行的是insert 语句，执行了2599秒
继续使用kill 来杀掉这个线程。
我们先使用 kill query 方式来

mysql> kill query 8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.threads where THREAD_OS_ID=5094\G;
*************************** 1. row ***************************
          THREAD_ID: 70
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 8
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: xhy
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 2896
  PROCESSLIST_STATE: waiting for handler commit
   PROCESSLIST_INFO: insert into t values(i, i, i)
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 5094
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

ERROR: 
No query specified

可以看到kill query 8虽然提示执行成功了，但并没有真的kill掉这个线程，因为该线程是执行一个存过，里面有多个语句。
再使用kill 方式

mysql> kill 8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.threads where THREAD_OS_ID=5094\G;
Empty set (0.00 sec)

ERROR: 
No query specified

可以看到5094的线程已经被杀掉了。

总结

发现mysql主机CPU使用率很高时，可以通过如下步骤来找到top 线程，并可通过kill/kill query方式来应急杀掉top线程。
1）使用 top -H 找到cpu使用率高的线程
2）使用 select * from performance_schema.threads where THREAD_OS_ID={操作系统线程ID}\G; 语句找到该线程执行内容。
3）如果是单条长select 语句可以使用 kill query (PROCESSLIST_ID值)来杀掉该查询语句,kill 方式可能杀掉后又重启。
4）如果是多条语句执行可以使用 kill (PROCESSLIST_ID值)来杀掉该线程，kill query方式杀掉一个语句又会起新的语句等于还是没杀掉高CPU线程。