Mysql 之 show processlist 神器

标签： mysql show processlist | 发表时间：2019-10-30 11:20 | 作者：
出处：https://www.telami.cn/

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

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

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

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

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

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

神器登场。

1     
show full processlist;

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

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

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

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

命令有三种执行方式：

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

1     
2     
3     
4     
5     
6     
7     
8     
9     
10     
11     
12     
13     
14     
15     
16     
17     
18     
19     
20     
21     
22     
23     
24     
25     
26     
27     
28     
29     
30     
31     
32     
33     
34     
35     
36     
37     
mysql> show full processlist;     
+--------+------+----------------------+-------+---------+------+----------+-----------------------+     
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |     
+--------+------+----------------------+-------+---------+------+----------+-----------------------+     
| 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |     
| 449001 | root | 127.123.213.11:59900 | stark | Sleep   | 1241 |          | NULL                  |     
| 449002 | root | 127.123.213.11:59958 | stark | Sleep   | 1216 |          | NULL                  |     
| 449003 | root | 127.123.213.11:60088 | stark | Sleep   | 1159 |          | NULL                  |     
| 449004 | root | 127.123.213.11:60108 | stark | Sleep   | 1151 |          | NULL                  |     
| 449005 | root | 127.123.213.11:60280 | stark | Sleep   | 1076 |          | NULL                  |     
| 449006 | root | 127.123.213.11:60286 | stark | Sleep   | 1074 |          | NULL                  |     
| 449007 | root | 127.123.213.11:60344 | stark | Sleep   | 1052 |          | NULL                  |     
| 449008 | root | 127.123.213.11:60450 | stark | Sleep   | 1005 |          | NULL                  |     
| 449009 | root | 127.123.213.11:60498 | stark | Sleep   |  986 |          | NULL                  |     
| 449013 | root | localhost            | NULL  | Query   |    0 | starting | show full processlist |     
+--------+------+----------------------+-------+---------+------+----------+-----------------------+     
11 rows in set (0.01 sec)     
     
mysql> show full processlist\G;     
*************************** 1. row ***************************     
     Id: 449000     
   User: root     
   Host: 127.123.213.11:59828     
     db: stark     
Command: Sleep     
   Time: 1283     
  State:      
   Info: NULL     
*************************** 2. row ***************************     
     Id: 449001     
   User: root     
   Host: 127.123.213.11:59900     
     db: stark     
Command: Sleep     
   Time: 1254     
  State:      
   Info: NULL

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

1     
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掉上面有问题的行 

1      
kill 449000

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

1      
2      
3      
4      
5      
6      
-- 查询执行时间超过3分钟的线程，然后拼接成 kill 语句      
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 表中查看当前未提交的事务 

1      
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/server-system-variables.html#sysvar_lock_wait_timeout

默认值为一年。。。。

将其调整为30分钟

  • set session lock_wait_timeout = 1800;
  • set global lock_wait_timeout = 1800;

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

All done

相关 [mysql show processlist] 推荐：

Mysql 之 show processlist 神器

- - Telami
今天在同步测试数据时，网突然断了，等到重连之后，发现表打不开了. 可以看到表的数据长度已有112192kb，可惜打不开了. 事情往往没这么简单，果然删不掉，truncate也不行，然后navicat卡死，遂登上数据库，执行dorp操作，还是不行. 估计是网络错误，导致了一些奇怪的事情发生. 那么就一起看看，到底发生了什么吧.

MySQL的show系列命令总结

- QQ - 服务器运维与网站架构|Linux运维|互联网研究
PS：以下MySQL的show命令很常用，总结以下：. 01、show tables或show tables from database_name; — 显示当前数据库中所有表的名称. 02、show databases; — 显示mysql中所有数据库的名称. 03、show columns from table_name from database_name; 或show columns from database_name.table_name; — 显示表中列名称.

[MySQL FAQ]系列 — processlist中哪些状态要引起关注

- - MySQL中文网
插图来自网络并作简单加工，如果觉得不当还请及时告知 ：）. 一般而言，我们在processlist结果中如果经常能看到某些SQL的话，至少可以说明这些SQL的频率很高，通常需要对这些SQL进行进一步优化. 今天我们要说的是，在processlist中，看到哪些运行状态时要引起关注，主要有下面几个：.

MySQL processlist中哪些状态要引起关注

- - OurMySQL
   一般而言，我们在processlist结果中如果经常能看到某些SQL的话，至少可以说明这些SQL的频率很高，通常需要对这些SQL进行进一步优化.    今天我们要说的是，在processlist中，看到哪些运行状态时要引起关注，主要有下面几个：. 执行ALTER TABLE修改表结构时. 建议：放在凌晨执行或者采用类似pt-osc工具.

Office Show: 微信广州新办公地

- - DamnDigital
在“微信·公众”合作伙伴大会召开期间，另一件让微信团队高兴的事也正在悄悄发生：他们搬进了位于广州TIT创意园的新办公地点. 此次搬迁的部门包括整个腾讯广州研发部. 随着微信团队人数的扩张，寻求新的办公地点成为部门的重点工作. 微信广东办公室最终的选址定于广州TIT创意园，一个被称为广州创意名片和文化地标的地带.

Linux Ksplice，MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统，为 Kernel 打补丁无需重启动，做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户，超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

MySQL Replication 线程

- - CSDN博客推荐文章
Replication 线程. Mysql 的Replication 是一个异步的复制过程，从一个Mysql instace（我们称之为Master）复制到另一个Mysql instance（我们称之Slave）. 在Master 与Slave 之间的实现整个复制过程主. 要由三个线程来完成，其中两个线程（Sql 线程和IO 线程）在Slave 端，另外一个线程（IO 线程）在Master 端.

mysql backup 脚本

- - ITeye博客
网上备份脚本很多，但考虑都不周全. 保证创建备份文件只能是创建者跟root可以访问，其他用户没有权限，保证了数据库备份的安全. 上面脚本是负责备份的份数管理，. 已有 0 人发表留言，猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇，这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来，也算是前面博文中PPT的一个文字版解读吧. Oracle，MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大，国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业，包括运营商以及金融机构都已经开始加入到这个潮流之中.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大，MySQL自然成为瓶颈，因此最近我一直在研究 MySQL  的优化，第一步自然想到的是 MySQL 系统参数的优化，作为一个访问量很大的网站（日20万人次以上）的数据库系统，不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中，MySQL对于性能的影响最大，也是关键的核心部分.