资讯专栏INFORMATION COLUMN

MySQL Online DDL导致全局锁表案例分析

paraller / 1793人阅读

摘要:后来在阿里云上面还看到过他们特定写过类似的答疑解决锁导致无法操作数据库的问题使用阿里云建议主要是这样操作这里需要找到的是一直在占用该表的会话,而不是正在等待锁解除的会话,注意区分。可以根据列的状态和列的命令内容来进行分析判断。

MySQL Online DDL导致全局锁表案例分析 我这边遇到了什么问题?

线上给某个表执行新增索引SQL, 然后整个数据CPU打到100%, 连接数暴增到极限, 最后导致所有访问数据库的应用都奔溃.

SQL如下:

ALTER TABLE `book` 
ADD INDEX `idx_sub_title` (`sub_title` ASC);

能看到什么?

"10063293", "root", "10.0.0.1:35252", "novel", "Query", "50", "Waiting for table metadata lock", "ALTER TABLE `lemon_novel`.`book` 
ADD INDEX `idx_sub_title` (`sub_title` ASC)"


"10094494", "root", "172.16.2.112:42808", "novel", "Query", "31", "Waiting for table metadata lock", "SELECT 
            book_trend.book_id AS book_id,
   

很奇怪, 这两边都在等"Waiting for table metadata lock"

反手查一下"Waiting for table metadata lock"是什么

MySQL出现Waiting for table metadata lock的原因以及解决方法

mysql: Waiting for table metadata lock

How do I find which transaction is causing a “Waiting for table metadata lock” state?

MySQL:8.11.4 Metadata Locking

MySQL:14.13.1 Online DDL Operations

初步的一些结论

看下来下面的一些结论:

MySQL 5.6以后的版本,支持在线DDL,新增index/删除index之类的可以直接InPlace操作,不需要rebuild整张表,理论上效果是很快的,详细资料见Online DDL Operations

DDL add index 操作会lock table metadata,此操作是导致我们服务不可用的原因

有怀疑过lock tabel matadata和MySQL autocommit有关,但是实践下来两者看起来没有关联。

后来在阿里云上面还看到过他们特定写过类似的答疑.

解决MDL锁导致无法操作数据库的问题

RDS for MySQL Online DDL 使用

阿里云建议主要是这样操作.

这里需要找到的是一直在占用该表的会话,而不是正在等待MDL锁解除的会话,注意区分。可以根据State列的状态和Info列的命令内容来进行分析判断。

您也可以用如下命令查询长时间未完成的事务,如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话。

select concat("kill ",i.trx_mysql_thread_id,";") from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = "Waiting for table metadata lock"
                     and substring(info, 1, 5) in ("alter" , "optim", "repai", "lock ", "drop ", "creat"))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

然而在我的场景, 上面的SQL并没有任何的进程输出.

陷入僵局的...

不过上面给了一些思路, 现在我们主要是因为有东西占用着 table metadata lock, 导致当前所有的东西都没有执行.

show full processlist;

看一眼没什么卵用, 处理那两个奇怪的wait lock, 其他的都挺正常的.

那么, 看下现在谁占用着锁?

怎么看呢?

select * from information_schema.innodb_trx;

神奇了, 真有两个东西在占用锁.

那kill 了他们看看.

额, 解决了.

最终结论

某个奇怪的程序开了查询或者奇怪的操作, lock了 table metadata, 之后连接一直都没有被释放, 导致以上各种问题.

现在的问题来了, 究竟是哪个程序或者哪个代码导致的呢?

抱歉, 我现在也还不知道...

理论上可以查, 但是上次去查的时候发现数据库显示的host对应机器的端口早就没东西了, 死无对证ing.

最后建议

online DDL前,最好确认一下当前数据库有没有类似lock存在

最好的方案还是主从切换来搞

全文完.

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/49058.html

相关文章

  • mysql 5.6 原生Online DDL解析

    摘要:引入之后,创建二级索引时会对原表加上一个锁,创建过程不需要重建表删除二级索引只需要更新内部视图,并标记这个索引的空间可用,去掉数据库元数据上该索引的定义即可。添加列时由于需要,会重放到新表上临时文件,直到最后一个,锁住原表禁止更新。 做MySQL的都知道,数据库操作里面,DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的,特别是在单表上千万的情况下,加个索引或改个列类...

    hightopo 评论0 收藏0
  • Mysql DDL出现长时间等待MDL问题分析

    摘要:会加如下锁只访问元数据比如表结构,不访问数据。特点是允许,防止会加该锁加入下锁,,可升级锁,访问表结构并且读写表数据,并且禁止其它事务写。 给表新增字段时,发现锁表了,查看进程,提示Waiting for table metadata lock,等待锁释放;然而蛋疼的是几分钟过去了,依然没有任何的进展,特此记录下这个问题的定位过程以及MDL的相关背景知识 看到上面的表现,基本问题就来了...

    qiangdada 评论0 收藏0
  • 2018MySQL面试知识点整理

    摘要:查询子句多个字段,前可使用聚合函数,对查询后结果的筛选和后面的语法类似字段别名表别名子查询子查询结果作为父查询的表子查询子查询结果命名子查询作为字段使用子查询可以跨多个表隔离级别未提交读,可能产生脏读,不可重复读,幻读读已提交,可能产生不可 mysql 查询子句: group by 多个字段,group by 前可使用聚合函数, having: 对查询后结果的筛选 和where后...

    myshell 评论0 收藏0
  • 单表60亿记录等大数据场景的MySQL优化和运维之道 | 高可用架构

    摘要:此文是根据杨尚刚在高可用架构群中,针对在单表海量记录等场景下,业界广泛关注的问题的经验分享整理而成,转发请注明出处。杨尚刚,美图公司数据库高级,负责美图后端数据存储平台建设和架构设计。 此文是根据杨尚刚在【QCON高可用架构群】中,针对MySQL在单表海量记录等场景下,业界广泛关注的MySQL问题的经验分享整理而成,转发请注明出处。 杨尚刚,美图公司数据库高级DBA,负责美图后端数据...

    xcc3641 评论0 收藏0
  • pt-online-schema-change使用说明、限制与比较

    摘要:个别情况是,当操作就是在列上建立主键时,触发器将基于列。创建和修改新表,但不会创建触发器复制数据和替换原表。错误处理存在表上存在触发器,不适用。参考如何使用之说明和原文链接地址 如果正在看这篇文章,相信你已经知道自己的需求了。 在 mysql 5.5 版本以前,修改表结构如添加索引、修改列,需要锁表,期间不能写入,对于大表这简直是灾难。从5.5特别是5.6里,情况有了好转,支持Onli...

    evin2016 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<