【MySQL运维】如何通过系统的进程ID查找对应MySQL的进程ID与线程ID

一、查找方法

1、操作系统进程ID
MySQL是一个单进程多线程的服务程序，其进程ID使用ps命令即可看到

ps aux | grep mysqld

2、操作系统线程ID与MySQL线程ID

mysql -uroot -p123456  #假设通过show processlist查到该用户连接ID为25
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+
| Id | User | Host      | db    | Command | Time | State | Info             | Rows_sent | Rows_examined |
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+
| 25 | root | localhost | mymgr | Query   |    0 | init  | show processlist |         0 |             0 |
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+

begin; select *,sleep(1000) from t1 for update;   #这个SQL会运行很长时间，方便观察

3、通过performance_schema.threads查看THREAD_OS_ID（MySQL 5.7之前没有该字段），该列记录了MySQL内部线程所对应的操作系统线程ID

SELECT * FROM threads WHERE PROCESSLIST_ID=25\G                   # 通过用户线程查询具体操作

*************************** 1. row ***************************
          THREAD_ID: 65                                           # MySQL内部线程ID
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 25  <-- MySQL连接ID
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: mymgr
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 246
  PROCESSLIST_STATE: User sleep
   PROCESSLIST_INFO: select *,sleep(1000) from t1 for update      # 正在运行的SQL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 58412                                        # 对应操作系统的线程ID
     RESOURCE_GROUP: USR_default

4、查看对应的系统线程

ps -Lef | grep 58412 
mysql    38801     1 58412  0   40 14:46 ?        00:00:00 /usr/local/GreatSQL-8.0.22/bin/mysqld --defaults-file=/mysql/data06/my.cnf

二、实战案例

1、通过top、imstat以及iotop命令确定磁盘IO过高。这里通过iotop命令可以看到与MySQL相关的进程ID 22233的IO消耗为52.59%，读为2.56M/s，同时进程 ID 8879的写为27.31M/s

#多余内容已经删除
Total DISK READ :       5.29 M/s | Total DISK WRITE :      29.42 M/s
Actual DISK READ:       5.29 M/s | Actual DISK WRITE:      26.83 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                  
22233 be/4 actionte    2.56 M/s  473.07 K/s  0.00 % 52.59 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306
 8879 be/4 actionte    0.00 B/s   27.31 M/s  0.00 % 17.76 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306
 8887 be/4 actionte    0.00 B/s    0.00 B/s  0.00 % 10.10 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306
 8909 be/4 actionte  418.78 K/s    0.00 B/s  0.00 %  1.08 % mysqld --defaults-file=/opt/mysql/etc/3306/my.cnf --d~ --socket=/opt/mysql/data/3306/mysqld.sock --port=3306

2、登录MySQL查询22233和8879两个进程ID当前的操作

mysql> select * from performance_schema.threads where THREAD_OS_ID=22233  \G

*************************** 1. row ***************************
          THREAD_ID: 14880
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 14830               #这里的ID对应了mysql线程ID，也就是show processlist看到的可以被KILL的ID
   PROCESSLIST_USER: sun
   PROCESSLIST_HOST: 10.186.61.16
     PROCESSLIST_DB: testdb
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: updating
   PROCESSLIST_INFO: UPDATE sbtest1 SET k=k+1 WHERE id=7899611
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 22233          # THREAD_OS_ID为linux系统中的线程ID
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

mysql> select * from performance_schema.threads where THREAD_OS_ID=8879 \G
*************************** 1. row ***************************
          THREAD_ID: 21
               NAME: thread/innodb/page_flush_coordinator_thread   # MySQL内部刷脏线程，因为用户的频繁写操作导致该线程一直在刷盘，所以它并不是罪魁祸首
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 264523
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 8879
     RESOURCE_GROUP: SYS_default
1 row in set (0.00 sec)

3、杀掉正在频繁写入的线程，IO恢复

mysql > kill 14830