今天在同步测试数据时,网突然断了,等到重连之后,发现表打不开了。
可以看到表的数据长度已有 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 | 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、通过查询链接线程相关的表来查看快照
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 语句
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的详细内容,更多请关注php中文网其它相关文章!
已抢61934个
抢已抢110637个
抢已抢133279个
抢已抢32074个
抢已抢19350个
抢