Part1: 写在最前
MySQL 的延迟告警想必大家一定不陌生, MySQL 引起从库延迟的原因有很多, 从硬件上讲可能是网卡, 磁盘, 内存达到瓶颈, 从数据库层面来讲, 可能是 SQL 效率低下, 或者大批量写入引起的. 本文的案例将剖析一个由 binlog 格式引发的延迟问题, 看完本文, 再遇到这类告警的时候, 相信你可以瞬间定位到问题所在!
Part2: 重点参数分析
https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_binlog_format
Property | Value |
---|---|
Command-Line Format
| --binlog-format=format |
System Variable
| https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_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) | ROW
STATEMENT
MIXED
|
Valid Values
(>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) | ROW
STATEMENT
MIXED
|
Valid Values
| 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 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 containing LIMIT even when they also have an ORDER 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 格式, 以及常见的延迟原因和解决办法. 由于笔者的水平有限, 编写时间也很仓促, 文中难免会出现一些错误或者不准确的地方, 不妥之处恳请读者批评指正.
来源: https://www.thinksaas.cn/group/topic/839465/