MySQL神器之show full processlist

朱雀 2020-05-10 16:16 600阅读 0赞

# MySQL神器之show full processlist
今天在同步测试数据时，网突然断了，等到重连之后，发现表打不开了。

可以看到表的数据长度已有 112192kb，可惜打不开了。

打不开，就准备删掉重来。

事情往往没这么简单，果然删不掉，truncate 也不行，然后 navicat 卡死，遂登上数据库，执行 dorp 操作，还是不行。

估计是网络错误，导致了一些奇怪的事情发生。

那么就一起看看，到底发生了什么吧。

神器登场。

**show full processlist;**

show full processlist 返回的结果是实时变化的，是对 mysql 链接执行的现场快照，所以用来处理突发事件非常有用。

这个 sql，一般就是充当救火队员的角色，解决一些突发性的问题。

它可以查看当前 mysql 的一些运行情况，是否有压力，都在执行什么 sql，语句耗时几何，有没有慢 sql 在执行等等。

当发现一些执行时间很长的 sql 时，就需要多注意一下了，必要时 kill 掉，先解决问题。

命令有三种执行方式：

1、这种是直接在命令行查询，末尾带 \G 是表示将查询结果进行按列打印，可以使每个字段打印到单独的行。

2、通过查询链接线程相关的表来查看快照

SELECT id, db, USER, HOST, command, time, state, info FROM information_schema. PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC;

3、通过 navicat 中的【工具】=> 【服务器监控】进行查看。

这种方式比较方便，还可以排序。

简单介绍一下，每列的含义：

Id：链接 mysql 服务器线程的唯一标识，可以通过 kill 来终止此线程的链接。

User：当前线程链接数据库的用户

Host：显示这个语句是从哪个 ip 的哪个端口上发出的。可用来追踪出问题语句的用户

db: 线程链接的数据库，如果没有则为 null

Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）

Time: 线程处在当前状态的时间，单位是秒

State：显示使用当前连接的 sql 语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state 只是语句执行中的某一个状态，一个 sql 语句，已查询为例，可能需要经过 copying to tmp table，Sorting result，Sending data 等状态才可以完成

Info: 线程执行的 sql 语句，如果没有语句执行则为 null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句

发现问题之后怎样解决它呢？

1、可以单独 kill 掉上面有问题的行

kill 449000

2、也可以批量结束时间超过 3 分钟的线程

-- 查询执行时间超过3分钟的线程，然后拼接成 kill 语句

```mysql
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 3*60
order by time desc;
```

当然问题到这，一般都能解决了，但是本次在 show processlist 过程中，只是看到了前面的 truncate 和 drop 操作，把这两个线程 kill 了，也没啥用。。。。

当然上面这些不是废话昂，这就是类似方法论的东西，就像【中国机长】里面，遇到飞行事故时，首先按照手册，检查一遍，排查原因，解决问题。

继续

紧接着，又用 navicat 执行了修复表操作，结果返回了 Waiting for table metadata lock

当 MySQL 在进行一些 alter table 等 DDL 操作时，如果该表上有未提交的事务则会出现 Waiting for table metadata lock，而一旦出现 metadata lock，该表上的后续操作都会被阻塞。

解决办法：

1、从 information_schema.innodb_trx 表中查看当前未提交的事务

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G

字段意义：

trx_state: 事务状态，一般为 RUNNING

trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理

trx_mysql_thread_id: MySQL 的线程 ID，用于 kill

trx_query: 事务中的 sql

一般只要 kill 掉这些线程，DDL 操作就不会 Waiting for table metadata lock。

2、调整锁超时阈值

lock_wait_timeout 表示获取 metadata lock 的超时（单位为秒），允许的值范围为 1 到 31536000（1 年）。 默认值为 31536000。

详见 `https://dev.mysql.com/doc/refman/5.6/en/se...`

默认值为一年。。。。

将其调整为 30 分钟

set session lock_wait_timeout = 1800;

set global lock_wait_timeout = 1800;

好让出现该问题时快速失败（failfast）。

MySQL神器之show full processlist

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL中 show processlist 和 show full processlist命令详解

相关 mysql : show processlist 详解

相关 mysql结束processlist_MySQL - Show Processlist 整理

相关 mysql show processlist

相关 mysql "show processlist"命令

相关 MYSQL性能分析之SHOW PROCESSLIST

相关 mysql show processlist

相关 mysql: show processlist 详解

相关 mysql show full processlist 分析问题

相关 MySQL神器之show full processlist