【转】MySQL SHOW PROCESSLIST详解

原文地址 https://www.modb.pro/db/337567

当前MySQL服务正在做什么？ 第一个想到的命令就是processlist是获取信息。返回的结果集有 线程id，IP ，端口，数据库，命令类型，状态维持时间，状态，具体命令行,还有最下面的当前总连接数 信息组成。通过这些指标可以一眼就能了解 当前执行的操作的状态值，并且查看mysql是否存在异常 消耗时间长的SQL语句。

• User信息可以知道 是哪个用户链接进来的
• ID作为紧急情况下 kill的 pid
• Host 可以统计出 客户端链接的ip信息
• db 提供操作的数据库
• Command 类型状态
• Time 状态执行时间，除去Sleep情况，操作10S 以上就要注意了
• State 当前SQL语句状态，能判断出是否穷主
• Info 具体执行的SQL ，通过语句进行优化。

这些信息也是非常有助于了解MySQL运行情况，排查问题。

1. processlist结构

在进一步了解processlist的表结构 ，从目前8.0.28版本中的结构中，映射到2张表（5.7只存在information库）

• information_schema.processlist（TEMPORARY临时表，还是utf8mb3字符集，要是碰到僻生字乱码）
• performance_schema.processlist（PERFORMANCE_SCHEMA 表 就是内存表）。 当服务关闭的时，会自动drop。 所以这个信息是无法保存的。

mysql > show create table information_schema.processlist;
CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint unsigned NOT NULL DEFAULT '0',
  `USER` varchar(32) NOT NULL DEFAULT '',
  `HOST` varchar(261) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


mysql > show create table performance_schema.processlist;
CREATE TABLE `processlist` (
  `ID` bigint unsigned NOT NULL,
  `USER` varchar(32) DEFAULT NULL,
  `HOST` varchar(261) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) DEFAULT NULL,
  `TIME` bigint DEFAULT NULL,
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  PRIMARY KEY (`ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Q：为什么有时PROCESSLIST会穷住的情况发生：

• 默认的SHOW PROCESSLIST持有全局互斥量的同时，从线程管理器中跨活动线程进行迭代。特别是在繁忙的系统上执行，将对性能产生负面影响。
• 另一种SHOW PROCESSLIST实现基于Performance Schema的PROCESSLIST表，并且不需要互斥锁。

其他

• Info表示线程正在执行的语句，如果没有执行任何语句，则为NULL。对于SHOW PROCESSLIST，这个值只包含语句的前100个字符。要查看完整的语句，使用SHOW FULL PROCESSLIST(或查询不同的进程信息源)。
• 拥有PROCESS特权，可以看到所有线程，甚至那些属于其他用户的线程。否则(没有PROCESS权限)，非匿名用户可以访问自己线程的信息

mysql > grant process  on  *.* to 'tester01'@'%';

• processlist表不允许使用TRUNCATE TABLE。
• performance_schema.processlist受到 performance_schema_max_thread_instances，performance_schema_max_stage_classes， performance_schema=ON 三个参数的影响。所以建议采用默认值即可。

2. processlist组成结构

除了一些基础信息，其实还有更深一次的信息，目前8个方面的信息（命令行，运行状态，复制进程，NDB进程，Event进程）。

Command类型

总共31个命令类型，前面5个应该是经常能看到的，有一定的了解即可：

Thread States线程状态

总共73指标，其中42多个是日常中常看到的。

其他

只显示日常的常看到的状态：

3. 常见问题状态分析

下面日常运维中，都会碰到的状态，都会反馈那些问题点：

1.Opening tables：mysql打开的文件可能超过设置参数，需要把以下指标调大 操作系统：limits.conf 参数：innodb_open_files，open_files_limit ，table_open_cache，table_definition_cache

2.Killed：一直维持kill状态 可能事务回滚，或则穷住。条件允许可以等待，但紧急境况下重新启动mysql服务，回滚速度会更快。

3.Sending data：从innodb层获取大量的数据，包含读磁盘，传输数据 等等。这个状态持续时间过长会导致cpu ，io耗尽。影响性能可以kill掉

4.Sorting index：因为语句没有可选索引进行排序，通过临时表进行排序，现象是cpu耗尽，建议添加索引

5.statistics：选出执行计划，如果长时间处于这种状态，则服务器可能是在磁盘上执行其他工作导致。

6.Waiting for commit lock：READ LOCK命令正在等待提交锁

7.waiting for handler commit：提交堵塞，瓶颈可能存在io 或 大批量事务导致。

8.Waiting for table flush：FLUSH TABLES关闭表，但还存在使用的表，所以穷住。需要把对应的使用sql 语句kill掉。主要场景就是xtrabackup时候常碰到。

9.Waiting for table metadata lock：元表锁 ，ddl和dml 冲突，需要对应的dml kill 掉 或停止ddl

10.Writing to net：网络堵塞。网络打满 或 max_allowed_packet设置过小导致。

11.copying to tmp： 大批量生成临时表，sql多表jion 或则 参数（max_heap_table_size tmp_table_size)可能能临时表大小，磁盘io 有关系。

12.高负载下show processlist 谨慎使用。这时候选择performance_schema.processlist也可以的。

13.有些语句在INFO信息不是显示SQL语句这时可以通过performance_schema.events_waits_current 查看具体SQL语句

4. 总结

PROCESSLIST就要MySQL运行的仪表盘一样，随时随刻告诉运行情况。

5.参考

https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html