专栏首页杨建荣的学习笔记MySQL中insert阻塞问题的分析

MySQL中insert阻塞问题的分析

今天收到一个业务报警,提示某个数据库实例的连接数暴涨,然后瞬间又恢复了,这种情况持续反复了几次,和开发同学沟通时,他们也希望能够得到更多的信息,比如是哪个数据库的连接数异常暴涨,我也想知道啊,但是苦于没有合适的工具和方法能够实现更细粒度的监控/统计,于是我着手开始分析这个问题。

这是一套MySQL 5.7.16的环境,事务隔离级别为RR

等我连接到这套环境的时候,show processlist的输出已经恢复了正常,查看相关的数据库日志也没有任何额外的输出,查看慢日志发现了有一部分的慢日志,提示是在insert into的语句,看起来着实蹊跷,计。

# User@Host: testdb[testdb] @  [xxxx.xx3]
# Query_time: 3.461818  Lock_time: 0.000067 Rows_sent: 0  Rows_examined: 0
SET timestamp=1597826800;
INSERT INTO `device_confignew_clientup` (`device_type`, `device_model`, `chipset_model`, `manufacturer`, `score`, `match_type`, `physic_memory`, `created_on`, `updated_on`, `is_open`) VALUES ('4', 'JEF-AN00', 'Hisilicon Kirin985', 'HUAWEI', '3000', '0', '7503', '1597826797', '1597826797', '1');

一条insert语句怎么会执行3秒多,往前继续翻,有些甚至都达到了10多秒,

在没有更多日志支撑的前提下,根据负载情况,我在主库打开了general log查看整个实例的操作明细,可以看到如下的日志信息,我截取了一段比较有代表性的日志。

首先,根据行首的id可以看到线程id增长会快,目前已经是4000万左右了,根据线程的连接情况可以看到,整个业务操作是基于短连接的形式处理的。

同时整个操作中涉及的表也很明显,是device_confignew_clientup,和慢日志里面显示的表和信息是可以互相呼应的。

表device_confignew_clientup的结构如下:

CREATE TABLE `device_confignew_clientup` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `device_model` varchar(200) NOT NULL DEFAULT '' ,
  `device_match` varchar(200) NOT NULL DEFAULT '' ,
  `score` int(11) unsigned NOT NULL DEFAULT '0' ,
  `chipset_model` varchar(200) NOT NULL DEFAULT '' ,
  `manufacturer` varchar(100) NOT NULL DEFAULT '' ,
 ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dm_ma` (`device_model`,`manufacturer`,`chipset_model`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=50160457 DEFAULT CHARSET=utf8 ;

结合这些信息,我们似乎可以找到问题的突破口,那就是里面的那个唯一性索引。按照这个约束,主键值id是从SQL里面自增完成的,唯一性索引基于3个字段,如果仔细观察上面的日志就户发现,基于同样的列值,竟然在日志里面两个不同的客户端发起了同样的SQL.

顺着这个思路,我继续进行排查,发现问题是越来越清晰了,我基于一个字段开始梳理,发现这个编码的数据相关的Insert有5000多条,也就意味着这个业务里面存在着大量冗余的数据写入。

grep -B4 JXX-AN00 general.log |wc -l
5295

整个业务和数据库的数据链路如下:

业务服务器会不断发起短连接请求,整个过程中是无状态的,发起的数据写入很可能是冗余的,为了在数据库中达到唯一性,设置了这个唯一性索引,而业务的持续不断的写入,因为唯一性索引会额外有检测数据库冲突的逻辑,所以相关的SQL都会阻塞,积累起来就会发现是1/N的写入命中率。

从这一点也可以看出,很多业务对于分布式应用的理解还是有限,应用服务器水平扩展就不考虑整个链路里面的数据一致性和唯一性了,导致数据库最后成了瓶颈,况且在这个层面的ACID代价其实就很高了。

而和业务的沟通来看,他们后续会做一些修正:

1)将短连接模式修改为长连接模式

2)在业务层进行数据操作时,先进行数据探测,如果已经存在则不做后续的处理,否则写入

3)对于应用分布式架构中对于数据库唯一性校验和数据一致性方面进行更进一步的测试,从设计理念上需要做一些转变。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:杨建荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL两地三中心方案初步设计

    随着互联网业务快速发展,多IDC的业务支撑能力和要求也逐步提升,行业内的“两地三中心”方案较为流行。

    jeanron100
  • MySQL数据库升级的一些坑

    对于商业数据库而言,数据库升级是一个优先级很高的事情,有版本升级路线图,有相应的补丁,而且对于方案还有一系列的演练,显然是一场硬仗。而在MySQL方向上,升级这...

    jeanron100
  • 假日期间常见的数据库磁盘空间处理小结

    数据库的报警可以拆分为很多类别,但是有一点是无论如何都跑不掉的,而且花样百出,那就是磁盘空间报警。

    jeanron100
  • Unix编程/应用问答中文版 ---6./etc/system可调资源限制

    本文出自:[url]http://www.nsfocus.com[/url] 维护:小四 6. /etc/system可调资源限制 6.1 Solari...

    一见
  • 基于空间矢量的机器人动力学建模与对比分析

    普通的矢量属于3D矢量,即每个3D矢量是由空间的三个标量表示,举例来说,空间的某个位置矢量是由三个XYZ轴的标量值得到,空间的力矢量是力在XYZ轴的标量值合成,...

    ZC_Robot机器人技术
  • 亿级Web系统搭建:单机到分布式集群

    当一个Web系统从日访问量10万逐步增长到1000万,甚至超过1亿的过程中,Web系统承受的压力会越来越大,在这个过程中,我们会遇到很多的问题。为了解决这些性能...

    逸鹏
  • 亿级Web系统搭建:单机到分布式集群

    当一个Web系统从日访问量10万逐步增长到1000万,甚至超过1亿的过程中,Web系统承受的压力会越来越大,在这个过程中,我们会遇到很多的问题。为了解决这些性能...

    CSDN技术头条
  • 【问底】徐汉彬:亿级Web系统搭建——单机到分布式集群

    大规模流量的网站架构,从来都是慢慢“成长”而来。而这个过程中,会遇到很多问题,在不断解决问题的过程中,Web系统变得越来越大。并且,新的挑战又往往出现在旧的解决...

    CSDN技术头条
  • Spring 切面失败和目标类空指针问题

    由于想记录 Controller 前后的处理情况,为什么不用 filter 处理是因为项目中有作业等其他请求,并不想做太多记录。

    haoming1100
  • 事务的 ACID 属性我总是傻傻分不清

    对于大部分程序员来说,他们的任务就是把现实世界的业务场景映射到数据库世界。比如银行为了存储人们的账户信息会建立一个account表:

    JiekeXu之路

扫码关注云+社区

领取腾讯云代金券