一个参数救活被hang住的数据库!

贺春旸 2017-02-08 09:45:24

作者介绍

贺春旸普惠金融MySQL专家,《MySQL管理之道》第一版、第二版作者。曾任职于中国移动飞信、机锋安卓市场,拥有丰富的数据库管理经验。目前致力于MySQL、Linux等开源技术的研究。

现象

开年头一天上班,开发说程序连接不上数据库了,程序伴随着有大量的update锁超时,试着引导他们用SQLYOG客户端连接均无问题,然后查看监控图发现有大量的锁,如下图:

 

 

 

排查

  • 数据库版本为:10.0.28-MariaDB-enterprise - MariaDB Enterprise Certified Binary

  • DELL R730XD 128G内存(BP 70G)/14块SAS 15000转RAID10

  • Update操作/秒30-50左右 innodb_lock_wait_timeout锁等待超时设置为10秒

 
 
 
 
 

 

在MySQL中information_schema库下有三个经典的数据字典表:INNODB_LOCK_WAITS、PROCESSLIST、INNODB_TRX,三者可以结合起来,就能够查到相对比较完整的阻塞信息和事务的情况。

 

1、通过以下SQL语句查看

SELECT 
  a.trx_id,
  trx_state,
  trx_started,
  b.id AS thread_id,
  b.info,
  b.user,
  b.host,
  b.db,
  b.command,
  b.state 
FROM
  information_schema.`INNODB_TRX` a,
  information_schema.`PROCESSLIST` b 
WHERE a.trx_mysql_thread_id = b.id 
ORDER BY a.trx_started;

 

查询结果如下:

 

请注意红色标识的,trx_state事务状态是RUNNING,但command那里查不到正在执行的SQL,显示的是Sleep状态。

2、通过以下SQL语句查看

SHOW ENGINE INNODB STATUS\G

 

查询结果如下:

 

请注意红色标识,事务ID和线程ID的状态为ACTIVE且运行了563秒,凭着以往处理故障的经验,这是N多条未提交事务的SQL引起的。

 

分析

当时慢查询日志里并没有记录慢SQL,线上设置的为1秒,询问开发是哪个SQL被锁了,也不清楚,说是通过框架生成的SQL语句,不好排查。

然后我们开启了general_log抓包,得到了很多简单的update,每次更新为1条记录,例如update t1 set name='aa' where id=XX,通过explain查看执行计划,where后面的字段都用到了索引,正常情况下执行这种SQL只需零点几毫秒的时间,但由于会话A对该记录更改未提交,会话B又对该记录进行更改,此时就会出现锁等待,直到超过了innodb_lock_wait_timeout参数设置的阈值。

在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量的连接数资源,造成严重的性能问题,甚至拖跨数据库。最终我们断定为开发的代码里应忘加了commit提交事务的操作,导致这一惨案的发生,可参考下面的重现操作。

前端应用JAVA Mybatis连接池一直不释放,积压过多的请求无法被处理,最终呈现给开发的现象是数据库又挂了。通俗来讲相当于在银行里办理业务,一个人办理不完,就得排队等待,越排越多,最终造成银行里人流混乱。


重现

MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update t5 set name = 'aa' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

记住!千万别COMMIT!等待一会(抽一根烟的时间),然后重复执行上述命令,即可查看到跟我这里的截图完全一致。

 

解决方案

让开发再排查代码,已经不太现实了,时间不等人,业务不等人,不能再增加用户的投诉量了,DBA首先要保证的是数据库别跑挂了,先恢复!

下面介绍给大家一个参数innodb_kill_idle_transaction,意思为当一个事务长时间未提交,那么这个连接就不能关闭,内存就不释放,并发一大,导致DB连接数增多,就会对性能产生影响。

默认是0秒,你可以根据自己的情况设定阈值。超过这个阈值,服务端自动杀死未提交的空闲事务。

下面的截图展示了这一过程:

 

从图中结果上可以看出,当未提交的事务超过空闲时间30秒时,被后台进程自动KILL掉,执行COMMIT提交后,连接被强行断开。

 

设置这个参数后只针对新的连接有效,正在执行的连接无效,固我们让运维又依次重启前端应用后,数据库终于复活成功!

注:官方MySQL5.5/5.6是没有这个参数的。


参考文献:

 
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告