MySQL如何定位消耗CPU的SQL

原创

食品安全辛吉飞 2023-08-07 21:06:51 博主文章分类：Mysql ©著作权

文章标签 CPU 问题定位 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

Mysql5.7版本以上在pfc下面的表threads表中添加了THREAD_OS_ID字段

[root@mysql.sock][test]> desc performance_schema.threads;
+---------------------+------------------+------+-----+---------+-------+
| Field               | Type             | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| THREAD_ID           | bigint unsigned  | NO   | PRI | NULL    |       |
| NAME                | varchar(128)     | NO   | MUL | NULL    |       |
| TYPE                | varchar(10)      | NO   |     | NULL    |       |
| PROCESSLIST_ID      | bigint unsigned  | YES  | MUL | NULL    |       |
| PROCESSLIST_USER    | varchar(32)      | YES  | MUL | NULL    |       |
| PROCESSLIST_HOST    | varchar(255)     | YES  | MUL | NULL    |       |
| PROCESSLIST_DB      | varchar(64)      | YES  |     | NULL    |       |
| PROCESSLIST_COMMAND | varchar(16)      | YES  |     | NULL    |       |
| PROCESSLIST_TIME    | bigint           | YES  |     | NULL    |       |
| PROCESSLIST_STATE   | varchar(64)      | YES  |     | NULL    |       |
| PROCESSLIST_INFO    | longtext         | YES  |     | NULL    |       |
| PARENT_THREAD_ID    | bigint unsigned  | YES  |     | NULL    |       |
| ROLE                | varchar(64)      | YES  |     | NULL    |       |
| INSTRUMENTED        | enum('YES','NO') | NO   |     | NULL    |       |
| HISTORY             | enum('YES','NO') | NO   |     | NULL    |       |
| CONNECTION_TYPE     | varchar(16)      | YES  |     | NULL    |       |
| THREAD_OS_ID        | bigint unsigned  | YES  | MUL | NULL    |       |
| RESOURCE_GROUP      | varchar(64)      | YES  | MUL | NULL    |       |
+---------------------+------------------+------+-----+---------+-------+
18 rows in set (0.01 sec)

THREAD_OS_ID 这对应Linux中的线程PID

找到mysql的进程

[root@MyLinux ~]# ps -ef| grep mysqld
mysql     9014     1  0 12:52 pts/1    00:00:00 /bin/sh ./mysqld_safe --defaults-file=/etc/my.cnf
mysql    10107  9014  0 12:52 pts/1    00:00:35 /usr/local/mysql-8.0.22-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql-8.0.22-linux-glibc2.12-x86_64 --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql-8.0.22-linux-glibc2.12-x86_64/lib/plugin --log-error=/data/mysql/logs/error.log --pid-file=mysql.pid --port=3306

找到mysql线程中消耗CPU最高的一个PID

Threads:  46 total,   3 running,  43 sleeping,   0 stopped,   0 zombie
%Cpu(s): 24.9 us, 39.5 sy,  0.0 ni, 17.0 id, 15.4 wa,  0.0 hi,  3.1 si,  0.0 st
KiB Mem :  1879692 total,    70376 free,   711560 used,  1097756 buff/cache
KiB Swap:  2064380 total,  2063284 free,     1096 used.   904968 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
10359 mysql     20   0 2263804 456844  16296 R 10.7 24.3   0:06.87 mysqld
10132 mysql     20   0 2263804 456844  16296 S  2.7 24.3   0:04.49 mysqld
10134 mysql     20   0 2263804 456844  16296 R  1.3 24.3   0:03.70 mysqld
10131 mysql     20   0 2263804 456844  16296 S  1.0 24.3   0:03.09 mysqld
10133 mysql     20   0 2263804 456844  16296 R  0.7 24.3   0:03.03 mysqld
10173 mysql     20   0 2263804 456844  16296 S  0.7 24.3   0:00.33 mysqld
10128 mysql     20   0 2263804 456844  16296 S  0.3 24.3   0:00.56 mysqld
10172 mysql     20   0 2263804 456844  16296 S  0.3 24.3   0:03.30 mysqld
10107 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:11.63 mysqld
10110 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.17 mysqld
10111 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.18 mysqld
10112 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.19 mysqld
10113 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.20 mysqld
10114 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.23 mysqld
10115 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.22 mysqld
10116 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.22 mysqld
10117 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.28 mysqld
10118 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.19 mysqld
10119 mysql     20   0 2263804 456844  16296 S  0.0 24.3   0:00.19 mysqld

通过10359 定位执行的sql语句

[root@mysql.sock][(none)]> select * from performance_schema.threads where THREAD_OS_ID=10359\G
*************************** 1. row ***************************
          THREAD_ID: 57
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 9
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 209
  PROCESSLIST_STATE: waiting for handler commit
   PROCESSLIST_INFO: insert into t values (n,"abc", n)
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 10359
     RESOURCE_GROUP: USR_default
1 row in set (0.01 sec)

这个功能让我们更容易定位到消耗CPU的sql