MySQL神器之show full processlist

风磊吖_5458

风磊吖_5458

2020-05-09

5370人浏览

转载

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

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

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

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

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

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

神器登场。

show full processlist;

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

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

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

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

命令有三种执行方式:

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

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   | 1241241 |          | 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、通过查询链接线程相关的表来查看快照

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 分钟的线程

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

-- 查询执行时间超过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 表中查看当前未提交的事务

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教程》《Navicat》

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5143

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1051

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1349

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7261

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3863

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7240

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

10181

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1390

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1212

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程