揪出MySQL延迟上千秒的元凶

　　

揪出MySQL延迟上千秒的元凶

揪出MySQL延迟上千秒的元凶

背景


MySQL的延迟告警想必大家一定不陌生，MySQL引起从库延迟的原因有很多，从硬件上讲可能是网卡，磁盘，内存达到瓶颈，从数据库层面来讲，可能是SQL效率低下，或者大批量写入引起的。本文的案例将剖析一个由binlog格式引发的延迟问题，看完本文，再遇到这类告警的时候，相信你可以瞬间定位到问题所在！



binlog_format

PropertyValue--binlog-format=formatbinlog_formatGlobal, SessionYes (>=5.5.31-ndb-7.2.13)enumeration (>=5.5.15-ndb-7.2.1, <=5.5.30-ndb-7.2.12)enumerationenumeration (>=5.5.31-ndb-7.2.13)MIXED (>=5.5.15-ndb-7.2.1, <=5.5.30-ndb-7.2.12)STATEMENTSTATEMENT (>=5.5.31-ndb-7.2.13)

ROW

STATEMENT

MIXED

 (>=5.5.15-ndb-7.2.1, <=5.5.30-ndb-7.2.12)

ROW

STATEMENT

MIXED

ROW

STATEMENT

MIXED

众所周知，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  set 拷贝;(0.08,sec)


<强>

对于混合格式来说,在如下情况的时候,binlog会自动转为行格式记录

1. ndb引擎

2. sql语句里包含了UUID()函数。

3。自增长字段被更新了。

4。包含了插入延迟语句。

5。使用了用户定义函数(UDF) .

6。使用了临时表。

7。？还有一种情况会导致mixed格式转换为ROW，本文会加以复现。


实战


我们看出，在凌晨2点的时候，从库的延迟陡增，而此时从库的机器负载和网卡并未达到瓶颈。


揪出MySQL延迟上千秒的元凶

我们可以看出，从2点06起，binlog刷新非常快，基本上几十秒就可以写满一个1.1GB的binlog文件。这样基本就能够确定，是因为写入量过大导致的。

写入量过大又有两种情况：

  1. 单纯的业务量激增，QPS增长引起；

  2. binlog转为了ROW格式导致存储内容激增引起。


我们使用pt工具pt-query-digest或者命令行，都能够分析出binlog做了哪些操作。使用pt-query-digest的话可以结合mysqlbinlog命令，对日志进行分析。


delete from tablename where xxxx limit 100;

这种语法会将MIXED格式的binlog，转为ROW格式记录，而笔者案例中的这张表包含TEXT大字段，每次delete都会把整个TEXT大字段带入binlog，进而导致binlog激增，从库追不上主库产生延迟的情况。


根本原因找到后，解决起来就得心应手了，找到相关开发，去掉delete from table where xxx limit 这种用法，就能够避免row格式的记录。


其实，delete/update limit、insert .....select limit这种用法是危险的，很容易产生问题。真的要使用这种这种方法的话，也需要结合order by语句来保证limit的有效性。

遇到此类语句时：

当使用STATEMENT模式时，会发出一个警告，说明语句对于基于语句的复制是不安全的。

揪出MySQL延迟上千秒的元凶