Mysql新增字段到大数据表导致锁表

mysql 同时被 2 个专栏收录
29 篇文章 0 订阅
2 篇文章 0 订阅

  昨天晚上7点左右,对一张表进行加字段,大概200多万条记录,字段90多个的大表,结果造成mysql锁表,进而导致服务不可用。执行语句如下:

[sql]  view plain  copy
  1. ALTER TABLE `sc_stockout_order` ADD `route_remarks` VARCHAR(1024)  CHARACTER SET utf8mb4  NULL  DEFAULT   
   

     mysql配置如下:



打开服务期日志,发现有如下报错:

[java]  view plain  copy
  1. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection;   
  2.         nested exception is org.apache.tomcat.jdbc.pool.PoolExhaustedException:   
  3.         [DubboServerHandler-10.162.99.129:20880-thread-105] Timeout: Pool empty.   
  4.         Unable to fetch a connection in 50 seconds, none available[size:80; busy:79; idle:0; lastwait:50000].  
  5.         at org.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  copy
  1. Waiting for table metadata lock  


从图中也可以看到活跃连接数到160之后就不变了,kill掉进程后恢复。


事后查找资料:

Mysql在5.6版本之前,直接修改表结构的过程中会锁表,具体的操作步骤如下:
(1)首先创建新的临时表,表结构通过命令ALTAR TABLE新定义的结构
(2)然后把原表中数据导入到临时表
(3)删除原表
(4)最后把临时表重命名为原来的表名

具体ddl如何工作

参考:http://www.cnblogs.com/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、后续可现在预发环境或测试环境先行模拟,评估风险

  • 2
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值