昨天晚上7点左右,对一张表进行加字段,大概200多万条记录,字段90多个的大表,结果造成mysql锁表,进而导致服务不可用。执行语句如下:
[sql] view plain copyALTERTABLE`sc_stockout_order`ADD`route_remarks`VARCHAR(1024)CHARACTERSETutf8mb4NULLDEFAULT
mysql配置如下:
打开服务期日志,发现有如下报错:
[java] view plain copyCause:org.springframework.jdbc.CannotGetJdbcConnectionException:CouldnotgetJDBCConnection;nestedexceptionisorg.apache.tomcat.jdbc.pool.PoolExhaustedException:[DubboServerHandler-10.162.99.129:20880-thread-105]Timeout:Poolempty.Unabletofetchaconnectionin50seconds,noneavailable[size:80;busy:79;idle:0;lastwait:50000].atorg.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)~[mybatis-3.2.8.
我们发现数据库jdbc拿不到链接,虽然没有到300最大数据库连接数,但是两台服务器80*2=160个链接数已经达到配置的客户端最大连接数。这边也说明我们客户端配置的链接数不太合理,可以再稍微调大一点。
发现这个问题后,为了尽快恢复线上服务,用show processlist发现ALTER TABLE这条语句导致大量查询语句处于等待状态,赶紧kill 掉修改表格语句的进程,此时系统恢复正常。在这个当中,发现了一条语句如下:
[sql] view plain copyWaitingfortablemetadatalock
从图中也可以看到活跃连接数到160之后就不变了,kill掉进程后恢复。
事后查找资料:
Mysql在5.6版本之前,直接修改表结构的过程中会锁表,具体的操作步骤如下:
(1)首先创建新的临时表,表结构通过命令ALTAR TABLE新定义的结构
(2)然后把原表中数据导入到临时表
(3)删除原表
(4)最后把临时表重命名为原来的表名
具体ddl如何工作
参考:/cchust/p/4639397.html
Mysql 5.6 虽然引入了Online DDL,但是并不是修改表结构的时候,一定不会导致锁表,在一些场景下还是会锁表的,比如
①某个慢SQL或者比较大的结果集的SQL在运行,执行ALTER TABLE时将会导致锁表发生;
②存在一个事务在操作表的时候,执行ALTER TABLE也会导致修改等待;
查看我们mysql的版本:SELECT VERSION(); 给出:5.6.16-log
我们通过Mysql的慢SQL控制台,也在发生问题的时间段内没有出现慢SQL,所以需要排除第一种可能性;
由于当时没有保留现场,所以当时是不是由于事物导致的锁表,现在也无从查起,这只能下次查看分析了。
根据这次教训,得到注意项:
1、尽量选择流量小的事后执行。当天20:00要大促,所以19:00大量供应商在操作。当我们选择在22:00左右再次执行时,就没再出现这个问题
2、执行时先看一下有没有未提交的事务,注意查看事物information_schema.innodb_trx表
3、随时关注服务器日志状况,已有问题要先行解决。
4、后续可现在预发环境或测试环境先行模拟,评估风险