DBdoctor定位MySQL性能案例(一)：MySQL表锁阻塞读写

一、问题背景

此事件是网上一位下载我们产品的用户发出来的，纳入到我们第一个案例中。
发现DBdoctor数据源中某个实例发现大量性能事件，事件详情为 Waiting for table metadata lock异常，该事件表示部分表在持续一段时间内无法进行读写。
根据DBdoctor的操作手册的介绍，通过Average Action Session（AAS）曲线图可以查看数据库活跃会话。
首先查看24小时的活跃会话情况，如下图橘色所示（7月13日16:00~7月14日16:00），发现全天都有Waiting for table metadata lock出现，基本每小时一次。

然后，选取了两个比较有代表的时间段，一个是7月14日零点前后的，如下图绿色区域（从图例上看到，绿色区域代表的是Waiting for table metadata lock）所示。说明零点时段的锁事件持续了20分钟左右。

又查看了一个其他时段的，如下图蓝色区域（Waiting for table metadata lock）所示，发现非零点时段锁事件持续时间不到1分钟。

二、问题分析过程

1. 由于凌晨时间段MetaData Lock持续时间最久，首先该段时间场景入手，同时怀疑凌晨可能有备份任务，通过运维同事确认该库备份时间段0:15-0:45，与Lock时间吻合，猜测跟备份有关，但是需要找出哪个SQL受备份导致了其他业务SQL的MetaData Lock，比较常见的如DDL。

2. 通过DBdoctor查看凌晨Lock时段AAS活跃会话情况，观察其间是否有哪些可疑SQL
   

3. 如上图，通过DBdoctor AAS，可以看到
   a. 等待Metadata Lock的SQL（上图绿色）有两类，一类是业务查询语句，一类是Lock Tables语句。
   b. 同时发现有全表SELECT的备份语句：SELECT /*!40001 SQL_NO_CACHE */ * FROM ad_rule
   c. 业务查询语句、Lock语句、备份语句都涉及到ad_rule表或者ad_allocation表。

4. 问题直接原因：
   Lock Tables语句受备份影响，造成了业务阻塞。

三、问题根因定位

与业务开发同事确认上述锁表SQL是特意设计的，每小时锁一次表，在写数据期间不允许读，预期1-2分钟完成写入并解锁；但是备份导致不可读写的时间变长，超出了预期时间。
分析引发MedataLock 时间变长的根因：

1. mysqldump期间，从表中select全表数据，为表添加Metadata共享锁，共享锁不影响正常业务读写操作，但是加锁时间较长。
2. 此时业务下发LOCK TABLES xx Write语句，该SQL需要Metadata排他锁，需要等待该表备份完成后才能拿到排他锁，同时也会阻塞其他SQL申请新的metadta锁。
3. 此时新的业务SQL发出该表的读写请求，都不再允许申请Metadata共享锁，进而不能操作该表，需等待LOCK TABLES XX Write语句完成，释放排他锁。
   锁等待关系总结为：
   业务SQL需要MD共享锁，需等待LOCK TABLES xx Write执行完成后释放MD排他锁；LOCK TABLES xx Write需要MD排他锁，需等待该表备份完成后释放MD共享锁。

四、优化建议

1. 开发优化建议：考虑凌晨时间段锁问题对的业务的影响大小，考虑整张表加写锁的是否必须，表加写锁可能造成阻塞业务、导致死锁的问题。
2. 运维优化建议：与开发确认锁表时间是否固定，不会因服务重启改变；在锁表时间固定的前提下可以通过修改备份时间来缓解问题。
   回顾这个问题的发现和解决过程，DBdoctor这个工具对分析的提效还是挺有帮助的，也帮助业务提前发现了一个潜在问题。
3. 能直观看出Metadata Lock的分布情况，无DBdoctor时无法统计metadatalock出现规律。
4. 方便排查可疑SQL，无DBdoctor时需要从慢查询SQL中检索与梳理。此时的slow query log里已经是满屏都是慢SQL了，会干扰我们查找真正导致慢的根因SQL，而DBdoctor可以快速展示出消耗最大的SQL来，这一功能还是很便利的。

关于我们

聚好看科技是海信集团旗下的互联网科技公司，以云计算、大数据与 AI 技术服务 B 端行业客户，连续五年获得“独角兽企业”称号。先后承担国家级项目 10 项，相关科研成果“支撑千万级用户的智能设备云平台及其应用”“JuDB 云数据库 PaaS 平台”经鉴定达到国际领先水平。
聚好看研发团队的核心成员来自阿里、华为、腾讯、EMC 等国内外一线科技企业，在数据库、云计算领域有着深厚的技术沉淀和实践经验。我们致力于通过持续的技术和产品创新，打造业界领先的云数据库产品。

产品可以在官网免费下载使用，这是我们的产品官网：https://www.hisensecloud.com/h-col-126.html?statId=9
我们也在招人，希望数据库达人可以加入我们一起让数据库使用更简单、高效。