揪出MySQL延迟上千秒的元凶
揪出MySQL延迟上千秒的元凶
背景
Part1:写在最前
MySQL的延迟告警想必大家一定不陌生,MySQL引起从库延迟的原因有很多,从硬件上讲可能是网卡,磁盘,内存达到瓶颈,从数据库层面来讲,可能是SQL效率低下,或者大批量写入引起的。本文的案例将剖析一个由binlog格式引发的延迟问题,看完本文,再遇到这类告警的时候,相信你可以瞬间定位到问题所在!
Part2:重点参数分析
Property | Value |
---|---|
Command-Line Format | --binlog-format=format |
System Variable | binlog_format |
Scope | Global, Session |
Dynamic | Yes |
Type (>= 5.5.31-ndb-7.2.13) | enumeration |
Type (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) | enumeration |
Type | enumeration |
Default (>= 5.5.31-ndb-7.2.13) | MIXED |
Default (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) | STATEMENT |
Default | STATEMENT |
Valid Values (>= 5.5.31-ndb-7.2.13) |
|
Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) |
|
Valid Values |
|
众所周知,binlog_format是设置binlog格式的参数,我们可以配置为STATEMENT、MIXED、ROW三种格式,可以动态调节。三种格式各有有缺。我们的线上生产库统一配置的是MIXED格式。MIXED格式会在STATEMENT格式和ROW格式中根据场景不同来使用不同的格式进行切换。
mysql> show global variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.08 sec)
Part3:知识储备
对于MIXED格式来说,在如下情况的时候,binlog会自动转为ROW格式记录
1.NDB引擎
2.SQL语句里包含了UUID()函数。
3.自增长字段被更新了。
4.包含了insert delayed语句。
5.使用了用户定义函数(UDF)。
6.使用了临时表。
7.?还有一种情况会导致mixed格式转换为ROW,本文会加以复现。
实战
Part1:监控
我们看出,在凌晨2点的时候,从库的延迟陡增,而此时从库的机器负载和网卡并未达到瓶颈。
Part2:延迟原因分析
我们可以看出,从2点06起,binlog刷新非常快,基本上几十秒就可以写满一个1.1GB的binlog文件。这样基本就能够确定,是因为写入量过大导致的。
写入量过大又有两种情况:
单纯的业务量激增,QPS增长引起;
binlog转为了ROW格式导致存储内容激增引起。
我们使用pt工具pt-query-digest或者命令行,都能够分析出binlog做了哪些操作。使用pt-query-digest的话可以结合mysqlbinlog命令,对日志进行分析。
Part3:rootcase
delete from tablename where xxxx limit 100;
这种语法会将MIXED格式的binlog,转为ROW格式记录,而笔者案例中的这张表包含TEXT大字段,每次delete都会把整个TEXT大字段带入binlog,进而导致binlog激增,从库追不上主库产生延迟的情况。
Part4:解决办法
根本原因找到后,解决起来就得心应手了,找到相关开发,去掉delete from table where xxx limit 这种用法,就能够避免row格式的记录。
Warning:警告其实,delete/update limit、insert .....select limit这种用法是危险的,很容易产生问题。真的要使用这种这种方法的话,也需要结合order by语句来保证limit的有效性。
遇到此类语句时:
当使用STATEMENT模式时,会发出一个警告,说明语句对于基于语句的复制是不安全的。
当使用STATEMENT模式时,即使它们也有一个ORDER BY子句(因此是确定性的),也会为包含LIMIT的DML语句发出警告。 这是一个已知的问题。 (BUG#42851)
当使用MIXED模式时,语句使用row的模式复制。
Part5:官方文档
When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions: When a DML statement updates an NDBCLUSTER table. When a function contains UUID(). When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT. When any INSERT DELAYED is executed. When a call to a UDF is involved. If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped. This is true whether or not any temporary tables are actually logged. Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables. When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244) When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086) When a statement refers to one or more system variables. (Bug #31168)
可以看出,在官方文档中,何时MIXED格式会转换为ROW格式中,并未提到limit语句会将MIXED格式转换为ROW,国内不少书籍和博客上也未有提及,本文记录这个案例,希望对遇到这个问题和未来可能遇到这个问题的读者能够节省处理时间,尽快定位到根源。
官方文档对于MIXED格式在使用limit语法时转换为ROW格式记录在其他章节,是如下描述的:
Statement-based replication of LIMIT
clauses in DELETE
, UPDATE
, and INSERT ... SELECT
statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY
clause.) When such a statement is encountered:
When using
STATEMENT
mode, a warning that the statement is not safe for statement-based replication is now issued.When using
STATEMENT
mode, warnings are issued for DML statements containingLIMIT
even when they also have anORDER BY
clause (and so are made deterministic). This is a known issue. (Bug #42851)When using
MIXED
mode, the statement is now automatically replicated using row-based mode.
——总结——
通过这个案例,我们能够了解到什么情况下binlog_format会由MIXED格式转为ROW格式,以及常见的延迟原因和解决办法。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。喜欢笔者的文章,右上角点一波关注,谢谢!
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
分布式爬虫系统设计、实现与实战:爬取京东、苏宁易购全网手机商品数据+MySQL、HBase存储
[TOC] 1 概述 在不用爬虫框架的情况,经过多方学习,尝试实现了一个分布式爬虫系统,并且可以将数据保存到不同地方,类似MySQL、HBase等。 基于面向接口的编码思想来开发,因此这个系统具有一定的扩展性,有兴趣的朋友直接看一下代码,就能理解其设计思想,虽然代码目前来说很多地方还是比较紧耦合,但只要花些时间和精力,很多都是可抽取出来并且可配置化的。 因为时间的关系,我只写了京东和苏宁易购两个网站的爬虫,但是完全可以实现不同网站爬虫的随机调度,基于其代码结构,再写国美、天猫等的商品爬取,难度不大,但是估计需要花很多时间和精力。因为在解析网页的数据时,实际上需要花很多时间,比如我在爬取苏宁易购商品的价格时,价格是异步获取的,并且其api是一长串的数字组合,我花了几个小时的时间才发现其规律,当然也承认,我的经验不足。 这个系统的设计,除了基本的数据爬取以外,更关注以下几个方面的问题: 1.如何实现分布式,同一个程序打包后分发到不同的节点运行时,不影响整体的数据爬取 2.如何实现url随机循环调度,核心是针对不同的顶级域名做随机 3.如何定时向url仓库中添加种子url,达到不让爬虫系统停...
- 下一篇
mysql主从复制读写分离与高可用配置
一、说明 前面我们说了mysql的安装配置(并提供一键安装脚本),mysql语句使用以及备份恢复mysql数据;本次要介绍的是mysql的主从复制,读写分离;及高可用MHA;环境如下:master:CentOS7_x64 mysql5.721 172.16.3.175 db1slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql 架构图: 说明:配置测试时为了方便关闭了防火墙头,selinux安全策略;现实中请开放防火墙策略;myslqdb的安装已经有脚本一键安装并配置好;这里就不在重复配置;只对对应的角色贴出对应的配置或安装与之相关的软件; 二、主从复制配置 一台主数据库,N从节点;从节点开启两个线程,通过Slave_IO_Running线程和主节点上有权限的账号从 主数据库节点复制binlog日志到本地,能过Slave_SQL_Runn...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- Hadoop3单机部署,实现最简伪集群
- CentOS关闭SELinux安全模块
- CentOS7,8上快速安装Gitea,搭建Git服务器
- MySQL8.0.19开启GTID主从同步CentOS8
- SpringBoot2初体验,简单认识spring boot2并且搭建基础工程
- CentOS8编译安装MySQL8.0.19
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- 设置Eclipse缩进为4个空格,增强代码规范